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

[kpuunwire]

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.

[wpreston · Accepted solution]

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]

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.

[wpreston]

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.

[lukejadamec]

http://www.splunk.com/view/SP-CAAACZW

[kpuunwire]

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.