Splunk query to report stats on one subset as fraction of the whole event set

Question

My mandate is to calculate the percent of one class of exceptions as a function of all events.

                      count(specific exception)
percent exceptions =  ---------------------------
                          count(all events)

I can get either count fairly easily but I am struggling to get both counts so that I can calculate the required percentage.

index=my_index source=my_source
  | fields logger exception message 
  | fields - _raw
  | eval date=strfTime(_time, "%F")
  | eval exception=case( isnull(exception),
                            "null",
                         like(exception,"%TaskDecorator%"),
                            "ThreadPool Exhausted",
                         like(exception,"%which is larger than%"),
                             "Message too large",
                         like(exception, "%has passed since batch creation"),
                             "Expiring records",
                         like(exception, "Disconnected from node%"),
                             "Disconnected from node",
                         true(),
                             exception )
   | stats count as dailyEventCount by date
   | stats count as exceptionCount by date exception
   | eval exceptionPct=round(exceptionCount/dailyEventCount*100,2)
   | where exception="Message too large"
   | table date exceptionCount dailyEventCount

either of the two stats commands above works independently and populates the respective columns of the final table, but the two together fail, and give me any empty table with no data.

I have been reading the Splunk docs on stats and eventstats and so far not come up with an answer on my own. So, What am I missing?

score 1 · Accepted Answer · answered Mar 14 '23 at 00:01

The stats commands transforms the results - what's passed on to the next command is just the fields mentioned in stats. If you have two consecutive stats commands then the second is counting the results from the first rather than the original events.

In a situation like this, as you suspected, you need a combination of stats and eventstats. Use stats to get the per-exception counts then use eventstats to calculate the total count.

index=my_index source=my_source
  | fields logger exception message 
  | fields - _raw
  | eval date=strfTime(_time, "%F")
  | eval exception=case( isnull(exception),
                            "null",
                         like(exception,"%TaskDecorator%"),
                            "ThreadPool Exhausted",
                         like(exception,"%which is larger than%"),
                             "Message too large",
                         like(exception, "%has passed since batch creation"),
                             "Expiring records",
                         like(exception, "Disconnected from node%"),
                             "Disconnected from node",
                         true(),
                             exception )
   | stats count as exceptionCount by date exception
   | eventstats sum(exceptionCount) as dailyEventCount by date
   | eval exceptionPct=round(exceptionCount/dailyEventCount*100,2)
   | where exception="Message too large"
   | table date exceptionCount dailyEventCount

Splunk query to report stats on one subset as fraction of the whole event set

1 Answers1