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?
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
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
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
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
I have updated the answer accordingly; try it again.
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
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?
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.
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.
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?