Splunk Query to compute the count of consecutive hourly violations per day

[iTechEvent]

I have data like this...

• Date - Hour - Sample Number
• 05/01/2014 - 10 - 200
• 05/01/2014 - 11 - 201
• 05/01/2014 - 12 - 202
• 05/01/2014 - 15 - 205
• 05/01/2014 - 16 - 206
• 05/01/2014 - 20 - 210

On 05/01/2014 the max number of consecutive hourly violations i.e. sample 200,201,202 is 3 and occurred between hours 10 to 12.

I have data like this for each day for a month.

I need a splunk query to computer the per day max consecutive hourly violations and the time range between in which it occurred.

Similarly for the entire month I would like the date in which the max number of hourly violations occurred for the month and time range of day.

Any help will be appreciated.

Thanks in advance.

[lguinn2 · Accepted solution]

This solution assumes that your event timestamp (_time) corresponds to the Date and Hour in your events.

yoursearchhere
| transaction Date maxpause=61m
| eval earliestHour=strftime(_time,"%H")
| eval latestHour=strftime(_time+duration,"%H")
| eval errorCount = eventcount
| table Date earliestHour latestHour errorCount
| sort Date -errorCount
| dedup Date

This should work great as long as you have less than 1000 events per Date. Otherwise, Splunk can get a little picky about compiling the transactions - and at that point the solution might start to slow down anyway.

[manish_singh_77]

@lguinn2

I have a query, there is a table where we have the job_result column, if we get consective 5 jobs failed then we need to be alerted.

[iTechEvent]

Awesome lguinn! Works well. Great Insight.