Splunk Search

How to edit my search to compare the same 1 hour time frame, week over week?

jpaulovich
Explorer

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") 
Tags (3)
0 Karma
1 Solution

mdsnmss
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

mdsnmss
SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

jpaulovich
Explorer

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 (?)

0 Karma

mdsnmss
SplunkTrust
SplunkTrust

Maybe "values" would work rather than count then:

| chart values(ALOC_secs) by marker
0 Karma

jpaulovich
Explorer

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?

0 Karma

mdsnmss
SplunkTrust
SplunkTrust

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.

0 Karma

jpaulovich
Explorer

good stuff, mdsnmss 🙂

Just what I need! Thank you!!!

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.