Splunk Search

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

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)
1 Solution
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)
``````
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)
``````
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.

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.

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

.conf21 CFS Extended through 5/20!

### Don't miss your chance to share your Splunk wisdom in-person or virtually at .conf21!Call for Speakers hasbeen extended throughThursday, 5/20! Submit Now! >

Get Updates on the Splunk Community!