Hi and Thanks ..
I've been researching and trying methods to do this (even tried timewrap) and am (finally) asking for some help.
Objective: Compare "stats avg(Dur) as ALOC_secs" for a recent single hour with a single hour 1 week ago. Initially, just to get it to 'work', then to trigger an alert based on an unacceptable (yet undefined) delta.
When I use the below search without the stats line, I get a count of results for the desired hours. However, since it is not a count that I need, the 'chart count ...' line (I suppose) is problematic. I haven't found the proper command/syntax to retrieve results that can be displayed and used to build an alert.
Thank you for your help.
sourcetype=sbc2 Sip_Resp=200 earliest=-60m@h latest=-0m@h
| stats avg(Dur) as ALOC_secs
| eval marker="today"
| append [search sourcetype=sbc2 Sip_Resp=200 earliest=-10140m@h latest=-10080m@h
| stats avg(Dur) as ALOC_secs
| eval marker="weekAgo"
| eval w1_time=_time+(7*24*60*60)]
| eval _time=if(isnotnull(w1_time), w1_time, _time)
| chart count(eval(marker=="today")) as lastHour , count(eval(marker=="weekAgo")) as sameTimeLastWeek by _time span=1h
| rename _time AS Time | eval Time=strftime(Time, "%H:%M")
Instead of two counts, try:
| chart count by marker
Since the markers are already specified it will give you the count for each time period. If you want to do the renaming you can just name your markers accordingly.
Instead of two counts, try:
| chart count by marker
Since the markers are already specified it will give you the count for each time period. If you want to do the renaming you can just name your markers accordingly.
Thanks for your response, mdsnmss. Unfortunately, this change gives me the count for each data point (=1), and not the avg(Dur) value that I need. The "stats avg(Dur)=ALOC_secs" provide that value; how to then carry that through to the output for display and alerting?
I borrowed this structure from a posting. Perhaps, my situation requires a different approach (?)
Maybe "values" would work rather than count then:
| chart values(ALOC_secs) by marker
Indeed, that works!
I wasn't familiar with the values function
marker ALOC_secs
today 146.308386
weekAgo 155.819740
Thanks for that!
Part deux:
Any idea how to alert if the difference exceeds a percentage, or am I asking too much?
I think it might be easier if the chart is transposed. Try:
| transpose
| rename "row 1" to today
| rename "row 2" to weekAgo
| where column!="marker"
This should get you the chart:
column today weekAgo
ALOC_secs 146.308386 155.819740
After that:
| eval difference = abs(today-weekAgo)
| eval perc_difference = round(difference/(weekAgo*100),0)
Then you can alert when perc_difference exceeds some theshold you define (Trigger alert when "custom" and perc_difference>X). You can adjust the value it rounds to and what the difference the percentage is of as needed. I just used the "weekAgo" value for my example. Hope that helps.
good stuff, mdsnmss 🙂
Just what I need! Thank you!!!