voulme data comparison

[selvam_sekar]

Hi,

I have below SPL, which return todays count vs yesterday count and difference between them. I want to see, if i run this search on monday, then the "yesterday" should be last Friday data instead of weekend. could you pls help ?

SPL:

base search  earliest=@d latest=now
| append
[ base search earliest=-1d@d latest=-1d ]
| eval Day=if(_time<relative_time(now(),"@d"),"Yesterday","Today")
| chart count by Name, Day
| eval percentage_variance=abs(round(((Yesterday-Today)/Yesterday)*100,2))
| table Name Today Yesterday percentage_variance

[VatsalJagani]

@selvam_sekar - You can filter the data beforehand and then do what you would do otherwise.

Something like this with streamstats command:

basesearch  earliest=-4d@d latest=now
| bin span=1d _time
| stats count by Name, _time, date_wday
| search NOT date_wday="saturday" OR date_wday="sunday"
| streamstats current=f window=1 last(count) as Yesterday by Name
| rename count as Today
| stats first(*) as * by Name
| eval percentage_variance=abs(round(((Yesterday-Today)/Yesterday)*100,2))
| table Name Today Yesterday percentage_variance

I hope this helps!!! Kindly upvote if it does!!!

[selvam_sekar]

@VatsalJagani  Many thanks for the response.

The SPL/query seems to be picking up the data slightly different. What i'm getting is today colunm volume is friday data and yesterday count also coming from friday data.

can you help pls?

[VatsalJagani]

@selvam_sekar - Here is the query with slight modification. Though in my case even with original query I'm getting the right count for today and yesterday.

basesearch  earliest=-4d@d latest=now
| bin span=1d _time
| search NOT date_wday="saturday" OR date_wday="sunday"
| stats count by Name, _time
| streamstats current=f window=1 last(count) as Yesterday by Name
| rename count as Today
| where strftime(_time, "%F")==strftime(now(), "%F")
| stats first(*) as * by Name
| eval percentage_variance=abs(round(((Yesterday-Today)/Yesterday)*100,2))
| table Name Today Yesterday percentage_variance

I hope this helps!!! Kindly upvote & accept the answer if it does!!!