Splunk Search

Compare result to other results on same day of the week

Amohlmann
Communicator

I apologize for my awkward phrasing. I am looking at some data that shows me whenever a certain event happens at any of our 3000 sites. I would like to set up an alert to tell me when any site's data is trending downward. To do this a bit more fairly I would like results from a Monday only compared to other Monday results and so on. So every Friday it would check the sites count of events and compare it to the average of all previous Fridays. I feel like I am almost there but cannot get the last little bit. Here is what I have so far:

base search
| bucket _time span=1d
| stats count as TotalMessages by _time,site
| sort 1-_time
| eval weekday=strftime(_time,"%a")
| eval today=strftime(now(), "%a")
| eval sameDay=if(today=weekday,1,0)
| WHERE sameDay=1
| eventstats avg(TotalMessages) as Mean by site
| eval Trend = ((TotalMessages-Mean)/Mean)

I cannot quite get it to only check the latest result for each site (I do not care if 2 weeks ago they trended downward but have since started to trend upward). Also, in a perfect world, the latest result would not affect the average but I could not quite work that out.

Thanks for any suggestions.

1 Solution

HeinzWaescher
Motivator

What about the streamstats command to use it like this:

| bucket _time span=1d
| stats count as TotalMessages by _time, site
| sort 1-_time
| eval weekday=strftime(_time,"%a")
| eval today=strftime(now(), "%a")
| eval sameDay=if(today=weekday,1,0)
| search sameDay=1
| streamstats avg(TotalMessages) AS avg BY site
| streamstats current=f global=f window=1 latest(avg) as last_avg by site
| stats latest(TotalMessages) as TotalMessages, latest(last_avg) AS last_avg BY site
| eval trend=TotalMessages-last_avg

View solution in original post

HeinzWaescher
Motivator

What about the streamstats command to use it like this:

| bucket _time span=1d
| stats count as TotalMessages by _time, site
| sort 1-_time
| eval weekday=strftime(_time,"%a")
| eval today=strftime(now(), "%a")
| eval sameDay=if(today=weekday,1,0)
| search sameDay=1
| streamstats avg(TotalMessages) AS avg BY site
| streamstats current=f global=f window=1 latest(avg) as last_avg by site
| stats latest(TotalMessages) as TotalMessages, latest(last_avg) AS last_avg BY site
| eval trend=TotalMessages-last_avg

martin_mueller
SplunkTrust
SplunkTrust

streamstats walks through the events one by one, potentially remembering everything it has seen up to that event on each step. current=f tells the latest() to not consider the current event, only all events up to the previous event. global=f tells the window to apply per by-field rather than globally. window=1 tells the latest() to only consider one event, namely the previous one.

In English, it's a convoluted way to tell Splunk "copy over a field value from the previous event into this event".

Have you tried my search?

Amohlmann
Communicator

I have. I had to modify it ever so slightly but it works great. Thanks 🙂

0 Karma

Amohlmann
Communicator

I read the documentation but do not quite get what the current=f global=f and window=f does. Can you shed some light for me?

Thanks

martin_mueller
SplunkTrust
SplunkTrust

Try this:

base search [stats count as date_wday | eval date_wday = strftime(relative_time(now(), "-d"), "%A")]
| bin span=1d _time
| stats count as TotalMessages by _time site
| eventstats sum(TotalMessages) as sum count as days
| tail 1
| eval Mean = (sum - TotalMessages) / (days - 1)
| eval Trend = (TotalMessages - Mean) / Mean

The subsearch at the top will calculate the weekday for yesterday and only load those days' events. To allow not counting yesterday towards the mean I've split the calculation up into first getting a sum and count, then calculating the mean without yesterday after throwing out all other days using tail.

0 Karma

aholzel
Communicator

maybe you can use TimeWrap for this so you don't have to create the query yourself.
see: https://splunkbase.splunk.com/app/1645/

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...