Stats for exception reporting

[lukeh]

Hi 🙂

I am trying to setup some exception reports for our capacity planners and I can construct a search that generates a table, eg.

          _time                  is_match    count
1    25/10/2013 00:00:00.000     Breach      1
2    26/10/2013 00:00:00.000     Breach      1
3    27/10/2013 00:00:00.000     Breach      1
4    22/10/2013 00:00:00.000     Prediction  1
5    23/10/2013 00:00:00.000     Prediction  1

What I ultimately want is a count of "Breach" and the first time that "Prediction" occurred, eg.

Count     Time
3         22/10/2013 00:00:00.000

is this possible with just one stats command?

Thanks in advance,

Luke.

[sideview · Accepted solution]

This is the best approach I can think of:

  <your search that returns _time, count and is_match>
| eval foo="1" 
| chart min(_time) sum(count) as count over foo by is_match 
| rename "count: Breach" as Count "min(_time): Prediction" as Time 
| fields Count Time 
| eval Time=strftime(Time,"%d/%m/%Y %H:%M:%S")

The foo="1" trick is useful surprisingly often to trick the chart command into doing the "by" part without the "over" part. At the end you always throw the foo away. Here our foo dies a quiet death when we get to fields Count Time.

Aside from that we rely on the functionality in chart and timechart to do more than one split-by field - it ends up combining them as "valueN: valueM" across the columns and here we use that to get both our Breach count and our Prediction time in one row, and then we rename the strange colon-separated syntax away.

[lukeh]

Thank you Nick!