Splunk Search

Stats for exception reporting

lukeh
Contributor

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.

Tags (1)
1 Solution

sideview
SplunkTrust
SplunkTrust

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.

View solution in original post

sideview
SplunkTrust
SplunkTrust

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
Contributor

Thank you Nick!

0 Karma
Get Updates on the Splunk Community!

Federated Search for Amazon S3 | Key Use Cases to Streamline Compliance Workflows

Modern business operations are supported by data compliance. As regulations evolve, organizations must ...

New Dates, New City: Save the Date for .conf25!

Wake up, babe! New .conf25 dates AND location just dropped!! That's right, this year, .conf25 is taking place ...

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud

Introduction to Splunk Observability Cloud - Building a Resilient Hybrid Cloud  In today’s fast-paced digital ...