How Do I Create an Alert Comparing Today's Current Hour vs Yesterday's Current Hour?

[skoelpin]

I have an index which has around 50,000 errors per day and I need to create an alert which will take the number of errors for the previous day, by the hour, and will compare it to the current day, by the hour, and the alert will fire if the current days hour is atleast 10% higher then the previous days hour.

Example:

Yesterday
7:00am-7:59am we had 10,000 errors
8:00am-8:59am we had 6,000 errors
9:00am-9:59am we had 4,000 errors

Today
7am we had 11,000 errors
8am we had 20,000 errors
9am we had 3,000 errors

The alert will fire for only 7am and 8am because the errors were 10% higher than the previous day.. I need this for all 24 hours

I have a previous thread here which is similar to what I'm looking for now, but this one is slightly different

http://answers.splunk.com/answers/302401/moving-average-of-errors-for-a-2-hour-time-period.html?minQ...

My current search

index=vertex7-access RTG_Error="500" | eval date_hour = strftime(_time, "%H") | where date_hour=0 | bucket _time span=1d  | timechart count | where count >  1.1 * [ search index=vertex7-access RTG_Error="500" earliest=-1d@d latest=@d | eval date_hour = strftime(_time, "%H") | where date_hour=0 | bucket _time span=1d | stats count by _time | stats avg(count) as AvgDailyError500Count | return $AvgDailyError500Count ]

Lastly, should I create a separate alert for each hour?

[woodcock · Accepted solution]

The answers by @somesoni2 were very good and very close but have a couple of problems. I believe this cleaned up search will give you your ask:

index=vertex7-access RTG_Error="500" earliest=-1h@h latest=@h | stats count AS TodayLastHour | appendcols [search index=vertex7-access RTG_Error="500" earliest=-25h@h latest=-24h@h | stats count AS YesterdayLastHour] | where TodayLastHour >  1.1 * YesterdayLastHour

You can run the search in the search bar to test the result. Once you're fine with the result, set it as alert with these parameters:

Earliest= -h@h
Latest = 0h@h
0 * * * *

This runs it every hour on the hour for the previous hour (keep in mind that the Earliest and Latest fields are useless and for commenting purposes only because our search overrides these).

The real problem is that I disagree with your day-over-day premise and think you should be comparing last hours values with the same hour last week (so Friday compares to Friday, etc.) Otherwise you will get MANY false positives going from Sunday->Monday, etc. To do this, use this very similar search:

index=vertex7-access RTG_Error="500" earliest=-1h@h latest=@h | stats count AS TodayLastHour | appendcols [search index=vertex7-access RTG_Error="500" earliest=-169h@h latest=-168h@h | stats count AS LastWeekLastHour] | where TodayLastHour >  1.1 * LastWeekLastHour

[skoelpin]

Exactly what I needed. Thanks Gregg!!

[somesoni2]

Try something like this

 index=vertex7-access RTG_Error="500" earliest=-1d@d latest=now() | timechart span=1h count | eval Day=if(_time<relative_time(now(),"@d"),"Yesterday","Today") | eval Hour=strftime(_time,"%H").":00" | chart avg(count) as avg over Hour by Day | where Today> Yesterday*1.1

---

And if you want to keep your current search format try this

 index=vertex7-access RTG_Error="500" | timechart span=1d count as Today | append [ search index=vertex7-access RTG_Error="500" earliest=-1d@d latest=@d | timechart span=1d count as Yesterday] | eval date_hour = strftime(_time, "%H") | stats values(*) as * by date_hour  | where Today >  1.1 * Yesterday

[skoelpin]

Thanks for the help @somesoni2

Will this search run every hour to compare the results to yesterdays hour? Is there anyway I can test this other than generating errors within an hour?

[somesoni2]

This search is currently searching all hours of Yesterday and comparing with all available hours for today. Based on your comment above, I believe you're looking for a search to run every hour and compare the count in previous hour with same hour yesterday. If I'm right, try something like this:-

index=vertex7-access RTG_Error="500" earliest=-1h@h latest=@h | eval date_hour = strftime(_time, "%H")  by date_hour| stats count as today | appendcols [search index=vertex7-access RTG_Error="500" earliest=-25h@h latest=-24h@h | eval date_hour = strftime(_time, "%H")| stats count as Yesterday by date_hour] | where Today >  1.1 * Yesterday

You can run the search in the search dashboard to test the result. Once you're fine with the result, set it as alert.

[skoelpin]

Yes I want Splunk to run a search every hour comparing today's number of errors for the hour vs yesterday's errors for that same hour. So say it is currently 11:00am, I want Splunk to run a search comparing the number of errors from 10:00:00am-10:59:59am for the current day and compare the number of errors from yesterday at 10:00:00am-10:59:59am.. If the number of errors yesterday was 10% higher then today's errors then I want the alert to go off.

When creating the alert, should I set up a CRON schedule to search each hour?
If so, should it look like this?

Earliest= -h@h
Latest = @h
* * * * *

I tried your query and got the following error

"Error in 'eval' command: The operator at 'by date_hour' is invalid."