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
Thanks!
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.
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:
{code}
| 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
{code}
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!