Today count match with same day in last week and prior week

[john_q]

Hi Experts,

Below is my search,

index=something source=something "error" | stats count

I want to create an alert for today count compare with the same day in last week and prior week and make sure that count should be match with 20%(-/+).

[niketn · Accepted solution]

@john_q try the following run anywhere search

index=_internal sourcetype=splunkd log_level!="INFO" earliest=-0d@d latest=now
| stats count as Today
| appendcols [search index=_internal sourcetype=splunkd log_level!="INFO" earliest=-7d@d latest=-7d@s
| stats count as LastWeekSameDay]
| eval perc=abs(round(((LastWeekSameDay-Today)/LastWeekSameDay)*100,2))

You can use search perc>20 as Alert Trigger Condition.

From Dashboard perspective do check out following Splunk Blog: https://www.splunk.com/blog/2012/02/19/compare-two-time-ranges-in-one-report.html

Also one of recent commands introduced in Splunk 6.5: Timewrap

[john_q]

@niketnilay actually i am looking for 3 days data comparison perc like today Thursday (08/02/2018) right as per IST. So we should compare with 07/26/2018 and 07/19/2018. 3 days data count percentage difference ?? Can you please let me know ??

[john_q]

@niketnilay Thanks for you answer and its almost working fine but i would like to include prior week same day (before week of last week same day) as well.

[niketn]

Sorry so if today is Wed 08/01/2018, what is the other date you need to pick?

[john_q]

same Wednesday only in last week and prior week

[john_q]

Is this correct???

index=_internal sourcetype=splunkd log_level!="INFO" earliest=-0d@d latest=now
| stats count as Today
| appendcols [search index=_internal sourcetype=splunkd log_level!="INFO" earliest=-7d@d latest=-6d@d
| stats count as LastWeekSameDay]
| appendcols [search index=_internal sourcetype=splunkd log_level!="INFO" earliest=-14d@d latest=-13d@d
| stats count as PriorWeekSameDay]
| eval perc=abs(round(((PriorWeekSameDay-LastWeekSameDay-Today)/PriorWeekSameDay)*100,2))

Expecting output:

Today LastWeekSameDay PriorWeekSameDay Perc
20 120 3649 8x.xx

[niketn]

@john_q, while appendcols seems correct, I dont think percent works the way you have calculated (unless that is what you want). You should have two separate percent calculated with Today's volume as compared for each of the two previous weeks. Try the following and see if it fits your needs!

index=_internal sourcetype=splunkd log_level!="INFO" earliest=-0d@d latest=now 
| stats count as Today 
| appendcols 
    [ search index=_internal sourcetype=splunkd log_level!="INFO" earliest=-7d@d latest=-6d@d 
    | stats count as OneWeekAgoSameDay] 
| appendcols 
    [ search index=_internal sourcetype=splunkd log_level!="INFO" earliest=-14d@d latest=-13d@d 
    | stats count as TwoWeeksAgoSameDay] 
| foreach *SameDay 
    [| eval Perc<<MATCHSTR>>=round(((<<FIELD>>-Today)/<<FIELD>>)*100,2)]

[john_q]

How to add prior week same day as well and calculate data percentage for 3days ??? Can you please tell me??