How to compare data in 2 time buckets and alert based on results?

[hvaithia]

bucket 1 -> Last 30 mins (say 10.30 AM to 11 AM)

bucket 2 -> Get avg count of events for the same time period for the last 7 days (10.30 AM to 11 AM)

compare bucket 1 and bucket 2. If bucket 1 is 50% less than bucket 2, then send me an alert Email

I am trying to get the number of URL hits and if its substantially less than avg of last 7 days for the same time period, I need to be notified. How can I achieve this in splunk?

[woodcock · Accepted solution]

DO NOT USE the "free" date_* fields; they are PRE-TZ-normalization artifacts. Try this:

index=trendingnow earliest=-24h@h
 sourcetype=yapache_access_trending status=200 colo=bf1 url="/tn?category=general&locale=en_us&source=mobile_distro"
| eval date_hourmin=strftime(_time, "%H%M")
| eval date_hourmin_thisHour = strftime(relative_time(now(), "@h"), "%H%M")
| where date_hourmin>=date_hourmin_thisHour AND date_hourmin<=tonumber(date_hourmin_thisHour + 59)
| stats count(url) AS count1
| appendcols [search
       index=trendingnow earliest=-7d@d
       sourcetype=yapache_access_trending status=200 colo=bf1 url="/tn?category=general&locale=en_us&source=mobile_distro"
   | eval date_hourmin=strftime(_time, "%H%M")
   | eval date_hourmin_thisHour = strftime(relative_time(now(), "@h"), "%H%M")
   | where date_hourmin>=date_hourmin_thisHour AND date_hourmin<=tonumber(date_hourmin_thisHour + 59)
   | stats count(url) as count2]
| eval count3=round(count2/7,0)
| eval issue1=if(count1<=(count3*.5),"true","false")
| eval issue2=if(count1>=(count3*1.5),"true","false")
| eval condition=if((issue1="true") OR (issue2="true"),"true","false")
| table count1 count3 issue1 issue2 condition

[hvaithia]

That makes much more sense. I thought we were going down the date_hour route again and you made a typo in the search line here -> date_hourmin.

I didn't know that where can be used to specify time ranges like above! Thats really useful! I will keep that in mind.

And the above query works exactly as expected 🙂

Thanks a lot!!
Hari V

[hvaithia]

Hi @woodcock

I am trying to get the time range from the hour/min when the search is executed. Lets say I set up a hourly cron to run the search at 59th minute of each hour

At 10.59 AM the time comparison will be between 10 - 10.59 today and the average of 10-10.59 AM for the past 7 days
Same when the search is ran again at 11.59 AM. Time range will be 11 - 11.59.

How can I modify the above search to do that? Basically how to modify the search time dynamically?

Thanks,
Hari

[woodcock]

I have updated the answer accordingly; try it again.

[jkat54]

Set this up as an alert that triggers an email if the results count is greater than 0.

index=indexName date_hour=10 date_minute>=30  
| stats c AS count1 
| appendcols [ search index=indexName date_hour=11 date_hour=10 date_minute>=30 earliest=-7d@d | stats avg(count) AS count2] 
| eval issue=if(count1<=(count2*.5),"true","false") 
| table count1 count2 
| where issue=true

[hvaithia]

Thanks for your response. I have got parts of what I need from your answer. Here is how my current search looks

index=abc url="/xyz"
date_minute>=30 earliest=-24h@h | 
date_hour=eval strftime(_time, "%H") |
stats c as count1 |
appendcols [search index=abc
url="/xyz"
date_minute>=30 earliest=-7d@d | 
date_hour=eval strftime(_time, "%H") |
stats count(url) as count2] | 
eval count3=round(count2/7,0) | 
eval issue1=if(count1<=(count3*.5),"true","false") | 
eval issue2=if(count1>=(count3*1.5),"true","false") | 
eval condition=if((issue1="true") OR (issue2="true"),"true","false") | 
table count1 count3 issue1 issue2 condition

I am planning to run this search every half hour or hourly. So I need to find the date_hour dynamically every time. I figured the above method would help but somehow I get a Unknown search command 'date'. error. If I remove the eval strftime(_time, "%H") in both the searches and just put in plain integers, it works! How do I solve this?

[jkat54]

I was afraid you'd say that. I've been trying to figure out how to do it dynamically for hours now 😉

Will let you know if I ever solve the problem.

[hvaithia]

sure! Thanks. I have been trying to do the same for the past couple hours now. Will let the forum know if anything turns up.

[hvaithia]

My bad in the above search, there was an extra pipe. But if I remove it

index=trendingnow sourcetype=yapache_access_trending status=200 colo=bf1 url="/tn?category=general&locale=en_us&source=mobile_distro"
date_minute>=30 earliest=-24h@h date_hour=eval strftime(_time, "%H") |
stats c as count1 |
appendcols [search index=trendingnow sourcetype=yapache_access_trending status=200 colo=bf1
url="/tn?category=general&locale=en_us&source=mobile_distro"
date_minute>=30 earliest=-7d@d date_hour=eval strftime(_time, "%H") |
stats count(url) as count2] | 
eval count3=round(count2/7,0) | 
eval issue1=if(count1<=(count3*.5),"true","false") | 
eval issue2=if(count1>=(count3*1.5),"true","false") | 
eval condition=if((issue1="true") OR (issue2="true"),"true","false") | 
table count1 count3 issue1 issue2 condition

I still get count1 and count3 as zero. Is it because of timezone issues?