Splunk Search

How to correlate the current period count with last week value and average of last x weeks

kpuunwire
Explorer

I need help with a correlation query where the aim is to find a particular type of event count in last 1 hour , the event count for the same hour for same day in last week , and the average event count for the same hour, same day but last x weeks. These values can then be compared to raise alerts in the system.

Example:

Lets say measuring period is 1h, say we are running the query on Monday at 10:10 AM in week32. Then we need

  1. [some search] event count for the measuring period which : is Monday 9AM-10AM in week32
  2. [some search] event count for the last week for the same period which is: Monday 9AM - 10AM in week 31
  3. [some search] average event count for the last X ( say 4 ) weeks for the same period which is : Monday 9AM - 10AM in week 31, Monday 9AM - 10AM in week 30, Monday 9AM - 10AM in week 29, Monday 9AM - 10AM in week 28

I really would appreciate your help in pointing me the right direction or any example.

Tags (2)
0 Karma
1 Solution

wpreston
Motivator

One way to accomplish this is by using an eval function called strftime along with stats. Perhaps something along these lines:

[some search] date_wday="monday" date_hour=9 | eval WeekNumber=strftime(_time, "%v") | search WeekNumber=32 OR WeekNumber=31 | stats count(someField) by WeekNumber

Or you could give a WeekNumber range, like

[some search] date_wday="monday" date_hour=9 | eval WeekNumber=strftime(_time, "%v") | search WeekNumber>27 WeekNumber<32 | stats count by WeekNumber | stats avg(count)

View solution in original post

wpreston
Motivator

One way to accomplish this is by using an eval function called strftime along with stats. Perhaps something along these lines:

[some search] date_wday="monday" date_hour=9 | eval WeekNumber=strftime(_time, "%v") | search WeekNumber=32 OR WeekNumber=31 | stats count(someField) by WeekNumber

Or you could give a WeekNumber range, like

[some search] date_wday="monday" date_hour=9 | eval WeekNumber=strftime(_time, "%v") | search WeekNumber>27 WeekNumber<32 | stats count by WeekNumber | stats avg(count)

kpuunwire
Explorer

Hi

Thank you - I ( one of my colleague ) have used parts of above and got a generic search query that can take "no of weeks as input" uses relative_time going backs weeks in the past and got the final alerts to work.

0 Karma

wpreston
Motivator

Of course, you would set your time range in the time range picker or via earliest and latest commands to encompass all of the search results you want to receive.

0 Karma

lukejadamec
Super Champion

kpuunwire
Explorer

Hi

Thank you - but summary index is mainly for report acceleration and performance. but my question still remains on such a query , now query on summary index then. my intention is to find if we have any support in spulnk in mentioning the multiple time ranges as described in the above requirements.

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 ...