Why does splunk timepicker dashboard shows result for today and yesterday?

[mikeyty07]

I have a requirement to build a dashboard, when selected between through date and time range
suppose  8/16/2023 17:00:00 and  8/16/2023 18:00:00. And want to show results for these dates as well as the previous day same hour results

todays count	yesterdays count
100	200

is it possible to have auto search and show two results through one time picker selection?

[ITWhisperer]

You could try something like this

<your index> [| makeresults
| addinfo
| eval row=mvrange(0,2)
| mvexpand row
| eval earliest=relative_time(info_min_time,"-".row."d")
| eval latest=relative_time(info_max_time,"-".row."d")
| table earliest latest]

[mikeyty07]

I added in top of query its showing data in _raw format instead of table. Am I missing anything?
my query

index=main source="*eligible*" "/api/info/eligible"
[| makeresults
| addinfo
| eval row=mvrange(0,2)
| mvexpand row
| eval earliest=relative_time(info_min_time,"-".row."d")
| eval latest=relative_time(info_max_time,"-".row."d")
| table earliest latest]

[ITWhisperer · Accepted solution]

Yes, you still need to do the counts - I assumed you knew how to do that - all I was showing was how to get the previous day's events using the timepicker (as you asked).

index=main source="*eligible*" "/api/info/eligible"
[| makeresults
| addinfo
| eval row=mvrange(0,2)
| mvexpand row
| eval earliest=relative_time(info_min_time,"-".row."d")
| eval latest=relative_time(info_max_time,"-".row."d")
| table earliest latest]
| bin _time span=1d
| stats count by _time

[mikeyty07]

thanks, It worked. Also in the same query is it possible to get from the  last week day too including today and yesterday?

[ITWhisperer]

index=main source="*eligible*" "/api/info/eligible"
[| makeresults
| addinfo
| eval row=mvrange(0,3)
| mvexpand row
| eval row=if(row=2,7,row)
| eval earliest=relative_time(info_min_time,"-".row."d")
| eval latest=relative_time(info_max_time,"-".row."d")
| table earliest latest]
| bin _time span=1d
| stats count by _time

[mikeyty07]

I wanted to check the percent increase/ decrease on the same query is it possible to run it on same?

[ITWhisperer]

Try something like this

index=main source="*eligible*" "/api/info/eligible"
[| makeresults
| addinfo
| eval row=mvrange(0,3)
| mvexpand row
| eval row=if(row=2,7,row)
| eval earliest=relative_time(info_min_time,"-".row."d")
| eval latest=relative_time(info_max_time,"-".row."d")
| table earliest latest]
| bin _time span=1d
| stats count by _time
| eval day=case(_time>=relative_time(now(),"@d"),"Today",_time>=relative_time(now(),"-1d@d"),"Yesterday",true(),"LastWeek")
| eval {day}=count
| fields - count _time
| stats values(*) as *
| eval dailychange=100*Today/Yesterday
| eval weeklychange=100*Today/LastWeek

[mikeyty07]

when i try to use time picker like certain date and time it is showing me like 

lastweek	day
25027 25161 25231	lastweek

but when i do last 4 hours or minutes all the values are showing like

today	yesterday	lastweek	dailychange	day	weeklychange
999249	102972	101160	-3.75	lastweek today yesterday	-1.92

can the timepicker show results like all values in the second table?

[ITWhisperer]

Perhaps you need to calculate based on info_min_time?

index=main source="*eligible*" "/api/info/eligible"
[| makeresults
| addinfo
| eval row=mvrange(0,3)
| mvexpand row
| eval row=if(row=2,7,row)
| eval earliest=relative_time(info_min_time,"-".row."d")
| eval latest=relative_time(info_max_time,"-".row."d")
| table earliest latest]
| bin _time span=1d
| stats count by _time
| addinfo
| eval day=case(_time>=relative_time(info_min_time,"@d"),"Today",_time>=relative_time(info_min_time,"-1d@d"),"Yesterday",true(),"LastWeek")
| eval {day}=count
| fields - count _time info_*
| stats values(*) as *
| eval dailychange=100*Today/Yesterday
| eval weeklychange=100*Today/LastWeek

[mikeyty07]

It still shows same but instead gives today

today	day
25027 25161 25231	today

[ITWhisperer]

Sorry I missed removing the day field

index=main source="*eligible*" "/api/info/eligible"
[| makeresults
| addinfo
| eval row=mvrange(0,3)
| mvexpand row
| eval row=if(row=2,7,row)
| eval earliest=relative_time(info_min_time,"-".row."d")
| eval latest=relative_time(info_max_time,"-".row."d")
| table earliest latest]
| bin _time span=1d
| stats count by _time
| addinfo
| eval day=case(_time>=relative_time(info_min_time,"@d"),"Today",_time>=relative_time(info_min_time,"-1d@d"),"Yesterday",true(),"LastWeek")
| eval {day}=count
| fields - count _time info_* day
| stats values(*) as *
| eval dailychange=100*Today/Yesterday
| eval weeklychange=100*Today/LastWeek

[mikeyty07]

It only showed this now,

today

25027 25161 25231

[ITWhisperer]

Please share your full query in a code block </>

[mikeyty07]

index=main source="*eligible*" "/api/info/eligible"
[| makeresults
| addinfo
| eval row=mvrange(0,3)
| mvexpand row
| eval row=if(row=2,7,row)
| eval earliest=relative_time(info_min_time,"-".row."d")
| eval latest=relative_time(info_max_time,"-".row."d")
| table earliest latest]
| bin _time span=1d
| stats count by _time
| addinfo
| eval day=case(_time>=relative_time(info_min_time,"@d"),"Today",_time>=relative_time(info_min_time,"-1d@d"),"Yesterday",true(),"LastWeek")
| eval {day}=count
| fields - count _time info_* day
| stats values(*) as *
| eval dailychange=100*Today/Yesterday
| eval weeklychange=100*Today/LastWeek

[ITWhisperer]

Sorry, I should have used info_max_time

index=main source="*eligible*" "/api/info/eligible"
[| makeresults
| addinfo
| eval row=mvrange(0,3)
| mvexpand row
| eval row=if(row=2,7,row)
| eval earliest=relative_time(info_min_time,"-".row."d")
| eval latest=relative_time(info_max_time,"-".row."d")
| table earliest latest]
| bin _time span=1d
| stats count by _time
| addinfo
| eval day=case(_time>=relative_time(info_max_time,"@d"),"Today",_time>=relative_time(info_max_time,"-1d@d"),"Yesterday",true(),"LastWeek")
| eval {day}=count
| fields - count _time info_* day
| stats values(*) as *
| eval dailychange=100*Today/Yesterday
| eval weeklychange=100*Today/LastWeek

[mikeyty07]

now its showing on two column, today column is missing

lastweek	yesterday
101160 102972	999249

[ITWhisperer]

What time span did you have in your search?

[mikeyty07]

I selected from time picker like 8/14/23 00:00:00 8/15/23 00:00:00

[ITWhisperer]

index=main source="*eligible*" "/api/info/eligible"
[| makeresults
| addinfo
| eval row=mvrange(0,3)
| mvexpand row
| eval row=if(row=2,7,row)
| eval earliest=relative_time(info_min_time,"-".row."d")
| eval latest=relative_time(info_max_time,"-".row."d")
| table earliest latest]
| bin _time span=1d
| stats count by _time
| addinfo
| eval day=case(_time>=relative_time(info_max_time,"-1d"),"Today",_time>=relative_time(info_max_time,"-2d"),"Yesterday",true(),"LastWeek")
| eval {day}=count
| fields - count _time info_* day
| stats values(*) as *
| eval dailychange=100*Today/Yesterday
| eval weeklychange=100*Today/LastWeek

[mikeyty07]

thank you, it worked but for percentage calculation i did this

| eval dailychange=(((Today-Yesterday)/Today)*100)
| eval weeklychange=(((Today-LastWeek)/Today)*100)