We have a dashboard, which is pulling the data for Current and last 7 days. In the screenshot below the data has split between the two colomns, but we are trying to display the colomns side by side and the _time colomn should only display the selected time period say 10/13/2019 12:00 - 10/13/2019 13:00 where here it shows both times 10-06-2019 and 10-13-2019. Here is the query
| multisearch [ search index=foo id=ABCDE env=PROD sourcetype="bar" earliest=$new_earliest$ latest=$new_latest$ | eval label="Customer_Count_Today" | fields -_raw _time dcn label ] [ search index=foo id=ABCDE env=PROD sourcetype="bar" earliest=$new_earliest1$ latest=$new_latest1$ | eval label="Customer_Count_LastWeek" | fields -_raw _time dcn label ] | bin _time span=30m | chart dc(dcn) over _time by label | fields _time Customer_Count_Today Customer_Count_LastWeek
Here is the dashboard/table it looks like
Test Start Date (mm/dd/yyyy hh:mm AM/PM) 10/13/2019 12:00 End Date Start Date (mm/dd/yyyy hh:mm AM/PM) 10/13/2019 01:00 First Panel _time Customer_Count_Today Customer_Count_LastWeek 2019-10-06 00:00:00 0 5906 2019-10-06 00:30:00 0 4978 2019-10-13 00:00:00 5529 0 2019-10-13 00:30:00 4689 0
I'm not able to upload the screenshot, I copied/pasted the sample results Count_today VS Count Last week results are
0 5906 0 4978 5529 0 4689 0
Hey! This was one of the ways I came up with solving this - basically, you create an identifier to tie together the logs from last week and this week, in this case, I used the hour and minute components of your _time field. In general, when you're running into a case where you have some fields w/ nulls or 0s and others without, you want to leverage the
stats command to collapse the rows. Take a look at my example:
| makeresults count=4
| streamstats count
| eval time=case(count=1, "1574118859", count=2, "1574120659", count=3, "1573514058", count=4, "1573515858")
| eval customerCountToday=case(count=1, "5", count=2, "3")
| eval customerCountYesterday=case(count=3, "7", count=4, "8")
| eval hourMinute=strftime(time, "%H:%M")
| stats values(customerCountToday) as customerCountToday, values(customerCountYesterday) as customerCountYesterday by hourMinute
If you go line by line, you can see that I have times a week apart, each having either the customerCountToday or customerCountYesterday filled in. I then create the "identifier", a field that uses the
strftime command to pull out the hour and minute. Finally, I take the set of each of those fields by that identifier. You might want to leverage
sum as the function instead of
values, as it looks like your dataset contains 0s. In your case, you only need the last two pipes.
Hope this helps!
index=foo id=ABCDE env=PROD sourcetype="bar" [| makeresults count=2 | streamstats count | addinfo | eval earliest=if(count==2,relative_time(info_min_time,"-7d"),info_min_time) | eval latest=if(count==2,relative_time(info_max_time,"-7d"),info_max_time) | table earliest latest | format "" "(" "" ")" "OR" ""] | addinfo | eval label=if(strftime(_time,"%d")==strftime(info_max_time,"%d"),"Customer_Count_Today","Customer_Count_LastWeek") | bin _time span=30m | chart dc(kbps) over _time by label | fields _time Customer_Count_Today Customer_Count_LastWeek
Hi, please use the time picker.
Search one week before that time interval.