Dashboards & Visualizations
Highlighted

How to display the data side by side within two colomns in a dashboard?

Path Finder

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!

0 Karma
Highlighted

Re: How to display the data side by side within two colomns in a dashboard?

Builder

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!

0 Karma
Highlighted

Re: How to display the data side by side within two colomns in a dashboard?

Ultra Champion
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.

0 Karma