Using both field values and aggregate functions as tokens within a Splunk Email Alert

[samsam48]

For each of my events, I have a field error_type with values "high", "medium", "low" and a field description that summarizes the specific error. I want to send out an email alert that captures both the sums of each error_type along with descriptions for the top five events for each type.

How can I access each of these descriptions while also applying aggregate functions in the query? Is it possible to store the results of the sums as new fields that I can then just access with the $results.aField$ syntax?

Ideally, the email would be formatted as below:

Error counts
-------------------
Number of High Errors: $stats sum(error_type.high)$
Number of Medium Errors: $stats sum(error_type.medium)$
Number of Low Errors: $stats sum(error_type.low)$

Error Descriptions
--------------------------

<top 5 descriptions where error_type = "high">
<top 5 descriptions where error_type = "medium">
<top 5 descriptions where error_type = "low">

[DalJeanis]

You are going to need to aggregate the results that you want to present as part of the alert. That means, either you need to build it into the alert itself, or have that alert kick off another search that builds the stuff you want to present.

Alternatively, you COULD use an appendpipe to create the lists and then eventstats command to add your overall stats to every single event in the alert...

This looks more complicated than it actually is..

| makeresults | eval mydata="high,ho1;high,ho1;high,ho1;high,ho1;high,ho2;high,ho1;high,ho1;high,ho2;high,ho1;high,ho1;high,ho3;high,ho2;high,ho4;high,ho3;high,ho5;high,ho6;high,ho7;high,ho1;high,ho7;high,ho2;low,hello1;low,hello5;low,hello5;low,hello1;low,hithere;low,hello5;low,hello2;low,hithere;low,hello5;low,hello5;low,hello3;low,hithere;low,hello4;low,hello3;low,hello5;low,hello6;low,hello7;low,hello5;low,hello7;low,hithere;medium,no;medium,maybe;medium,no;medium,yes,medium,this is a test;medium,this is a test;medium,this is a test"|makemv delim=";" mydata|mvexpand mydata| rex field=mydata "(?<error_type>[^,]*),(?<description>[^,]*)" 
| fields error_type description
| rename COMMENT as "The above just enters test data..."

| appendpipe [
     | rename COMMENT as "add up count for each combination of error_type and description"
     | stats count as countbydesc by error_type description 

     | rename COMMENT as "find the total count for each error_type"
     | eventstats sum(countbydesc) as countbyerr by error_type 

     | rename COMMENT as "sort in order so that the most common descriptions for each error_type are first"
     | sort error_type - countbydesc

     | rename COMMENT as "assign counts to each description in an error_type, then keep the five highest"
     | streamstats count as reccount by error_type
     | where reccount <=5 

     | rename COMMENT as "roll up each error type into a single record, with description as a multivalue field with five values"
     | stats list(description) as listdescription max(countbyerr) as countbyerr by error_type

     | rename COMMENT as "build a fieldname for the count and the list for each error type... this assumes the error_type is a word made up of letters and/or numbers, no spaces etc"
     | eval countfield = "Count".error_type
     | eval listfield="List".error_type

     | rename COMMENT as "assign the values to the fields Count* and List*"
     | rename COMMENT as "For List*, squeeze the descriptions together with mvjoin so we can unwind them later"
     | eval {countfield}=countbyerr
     | eval {listfield}=mvjoin(listdescription,"!!!!")

     | rename COMMENT as "set a flag so we can kill these records that come out of the appendpipe"
     | eval killflag="killme"
     ]

 | rename COMMENT as "roll the data from the appendpipe over to the other records using eventstats, then kill the appendpipe records"
 | eventstats max(List*) as List* max(Count*) as Count*
 | where isnull(killflag)

 | rename COMMENT as "take each List* field and unsqueeze the values to break out the multivalue field"
 | foreach List* [| eval <<FIELD>>=split(<<FIELD>>,"!!!!")]

---

Note: Because of the way stats and eventstats work with multivalue fields, we had to combine the appendpipe results together with "!!!!" (a useful delimiter that almost never appears in normal data) and then split them apart again afterwards.

With regard to the results, this creates fields Counthigh Countlow Countmedium Listhigh Listlow Listmedium that are added to every result, so they are available on the first result back from the alert search.

[samsam48]

Thanks for response. Any chance you can break down what that query is doing exactly? I'm not sure how to apply it, it looks like its counting by error type in descending order (although I'm not sure what the - in error_type - countbydesc deos). Then you're using streamstats and recount <=5 to only include the first 5 results? the eval statements are also fairly foreign to me.

Also, how would one build it "into the alert itself" or have an alert kick off a secondary search? Would these results be merged into one another for an alert email body?

Thanks!

[DalJeanis]

@samsam48 - Okay, it's fully commented now. Make sense?

Your alert would replace everything before the comment in line 3 that says "the above just provides test data".

If you don't need the detail, then change line 36 from

  | eventstats max(List*) as List* max(Count*) as Count*

to

  | stats max(List*) as List* max(Count*) as Count* 

and kill everything after it. You could also kill the appendpipe command itself and the square braces around the subsearch, since you wouldn't be keeping the individual events that the appendpipe is there to avoid affecting.