Compare today's data with yesterday's data, or today's data with last Friday's data if today is a Monday

[klee310]

As the title states, I'm trying to compare some data between today and yesterday. If yesterday is a Sunday, then use Friday's data instead. I'm having trouble with this and I'm wondering if anyone has any experience or thoughts in this area.

I have already tried setting earliest=-2d@w5 to snap the earliest date to the closet Friday. However the problem with this solution is that the data for Saturday and Sunday will also be included.

Given this use case, I'm wondering if someone could point me in the right direction: If I want to compare the CPU usage of a Windows system, provided I already have this data from the WMI app; I want to make sure my comparison excludes Saturdays and Sundays. But also with the same search string, be able to search Tuesdays and Mondays data accordingly (if today is a Tuesday).

Any thought?

<.edit>
obviously, I will be using sub-searches to compare the data. The question is how should I retrieve last Friday's data if yesterday is a Sunday; but use yesterday's data if yesterday was anything but Sunday or Saturday

furthermore, if today is Saturday or Sunday, then use the data for last Thursday and last Friday instead... I think this last part might be a bit of a stretch for Splunk to handle. Any thought?

[klee310]

ok, so this is what I've come up with to get yesterday's data/ or Friday's data (if today is a Monday). I'm not absolutely sure if it will work as plan, so I'm waiting for three more days (today is Friday) to confirm this.

earliest=-3d@d cpu | eval dayofweek=strftime(_time,"%u") | search NOT (dayofweek=6 OR dayofweek=7) | eval yesterday=dayofweek-1 | eval yesterday=if(dayofweek==1,5,yesterday) | search [search earliest=@d cpu | eval dayofweek=strftime(_time,"%u") | search NOT (dayofweek=6 OR dayofweek=7) | eval yesterday=dayofweek-1 | eval yesterday=if(dayofweek==1,5,yesterday) | dedup dayofweek | eval dayofweek=yesterday | table dayofweek]

[klee310]

Confirmed. This works. Today is Monday and I am able to retrieve last Friday's data. However if today is Saturday or Sunday, no data will be returned.

[gkanapathy · Accepted solution]

I don't know what your query or data is like, and really it's not "obvious" that subsearches are the best way to compare the data (e.g., a combination of timechart, and streamstats/delta seems more effective to me), but for any given event or timechart time bucket:

... | eval today=strftime(_time,"%u")-1 | eval yesterday=(today-1)%5 | eval daybeforeyesterday=(today-2)%5

gives you a day number for the events, with 0 being Monday and 4 being Friday. (You can add 1 to all of them if you want)

[klee310]

actually, what I want to do is draw a line chart comparing the two days... so if today is Thursday, then compare today and yesterday (Wednesday). But if today was Monday, then compare today and last Friday... Also if today is either Saturday or Sunday, then compare last Thursday and last Friday.

[gkanapathy]

probably deal with the error with parentheses, spaces, or by wrapping terms with tonumber()

[klee310]

Thanks on the post. I see how I can build on what you have provided. btw, the '-1' in the eval today term.. seems to be throwing an error