topic Re: timechart comparison for 15 mints current working day vs previous working day in Splunk Search

timechart comparison for 15 mints current working day vs previous working day

selvam_sekar — Tue, 23 Jan 2024 12:53:59 GMT

Hi,

I have the below SPL and I would like to get the comparison for 15 mints time span i.e if we run today at 5 am then we should expect the table like for every 15 mints data count vs yesterday same time count. Please could you help?

Current SPL: basesearch earliest=-3d@d latest=now | eval date_wday=strftime(_time,"%A") |search NOT (date_wday=Saturday OR date_wday=Sunday) | eval last_weekday=strftime(now(),"%A") | eval previous_working_day=case(match(last_weekday,"Monday"),"Friday",match(last_weekday,"Tuesday"),"Monday",match(last_weekday,"Wednesday"),"Tuesday",match(last_weekday,"Thursday"),"Wednesday",match(last_weekday,"Friday"),"Thursday") | where date_wday=last_weekday OR date_wday=previous_working_day | eval DAY=if(date_wday=last_weekday,"TODAY","YESTERDAY") | chart count by Name,DAY | eval percentage_variance=abs(round(((YESTERDAY-TODAY)/YESTERDAY)*100,2)) | table Name TODAY YESTERDAY percentage_variance

Re: timechart comparison for 15 mints current working day vs previous working day

ITWhisperer — Tue, 23 Jan 2024 13:31:40 GMT

Have a look at the timewrap command timewrap command overview - Splunk Documentation

Re: timechart comparison for 15 mints current working day vs previous working day

dural_yyz — Tue, 23 Jan 2024 16:04:19 GMT

| tstats count where index=<your-index-here> earliest=-3d@d latest=now() by _time span=15m 
| eval date_wday=strftime(_time,"%A"), date_hourmin=strftime(_time,"%H:%M")
| search date_wday!=Saturday date_wday!=Sunday 
| eval current_weekday=strftime(now(),"%A") 
| eval previous_working_day=case(match(current_weekday,"Monday"),"Friday",match(current_weekday,"Tuesday"),"Monday",match(current_weekday,"Wednesday"),"Tuesday",match(current_weekday,"Thursday"),"Wednesday",match(current_weekday,"Friday"),"Thursday") 
| table _time count date_wday date_hourmin current_weekday previous_working_day
| where date_wday=current_weekday OR date_wday=previous_working_day
| chart sum(count) as count by date_hourmin, date_wday

Ok that is the closest I could get to what you originally tried. However, there are some flaws with this solution you may want to consider. Specifically partial time bins can not be filtered out without using the time chart command. So it could look like the count for the most recent time span has dangerously dropped when in reality you only have 2 or 3 minutes of the 15 minute window to measure.

Working with the timewrap command is more correct way to do this as you can leverage timechart which allows you to disable partial windows. You will find though that filtering out week-ends and the -3d@d makes for odd visualizations.

index=<your-index-here> date_wday!=saturday date_wday!=sunday earliest=-3d@d latest=+1d@d
| timechart span=15m partial=f count
| timewrap 1day align=end

Splunk time extracts date_* fields for you already. The +1d@d is only important if you want your graph to go midnight to midnight, replace with now() if you are ok with the visualization start and end moving as the day progresses.