topic Re: Today count match with same day in last week and prior week in Splunk Search

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

john_q — Wed, 01 Aug 2018 05:14:06 GMT

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%(-/+).

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

niketn — Wed, 01 Aug 2018 06:02:39 GMT

@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

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

john_q — Wed, 01 Aug 2018 07:37:05 GMT

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

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

niketn — Wed, 01 Aug 2018 08:02:21 GMT

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

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

john_q — Wed, 01 Aug 2018 08:15:51 GMT

same Wednesday only in last week and prior week

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

john_q — Tue, 29 Sep 2020 20:42:05 GMT

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

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

john_q — Thu, 02 Aug 2018 05:31:42 GMT

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

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

john_q — Thu, 02 Aug 2018 05:40:02 GMT

@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 ??

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

niketn — Sat, 04 Aug 2018 16:24:23 GMT

@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)]