How to show last 7 days data compared to date selected in drop down.

[koreamit3483]

I have a dashboard where i have date filter in DD/MM/YYY format and have a table which shows data for the dates selected in the drop down and it filters based on the date selected.

Now i have a required to additionally show data of 7 days back too. Means the data currently showing for the date selected along with the data which was 7 days back too.

For example : If date selected on drop down is 07/01/2021 then 1st table should show data for 7th Jan and 2nd table should show data for 1st Jan.

My fields are like HOST (server hostname) and RESULT (shows 2 values as either PASS or FAIL). so the table i have created is 

<index = XXX | | stats count(eval(searchmatch("PASS"))) AS PASS count(eval(searchmatch("FAIL"))) AS FAIL by HOST>

This gives me 2 fields as PASS and FAIL count against the HOST for the date selected.

My requirement is merge both the dates data into one table, but even i can make it in 2 separate tables then it should do..

Can any one help guide me..

[koreamit3483]

@ldongradi_splun 

Thanks for your reply..

My dates from the drop down are in DD/MM/YYY format and i need another table in the dashboard which should show data of before 7 days from the date selected in drop down.

[ldongradi_splun]

You can propose any format for the date, and do some calculation with the token  as I mentioned.

If the date format is DD/MM/YYYY,

1. use strptime($timetoken$,"%d/%m/%Y) to make it in epoch format
2. do the required math to create another token by subtracting 186400s (that's -1week)
3. if you need to display, then reformat with strftime($weekago$,"%d/%m/%Y")

You need to set or eval tokens in XML code to make them compatible with the search or the display. Then the tokens can be used anywhere in the dashboard, for any panel, or any chart.

[ldongradi_splun]

So, your dropdown input gives you a date from any search (let's populate the input with this example to propose today and the previous 14 days):

| makeresults count=14
| streamstats count as offset
| eval _time=_time-offset*86400 | eval _time=strftime(_time,"%a %d %B %Y")

Because _time is stored in epoch format but displayed in human format you can use the input settings:

• Field for Label=_time 
• Field for Value=_time

The trick for your 2nd dashboard panel is to use a time calculated from the first token. This can be done in the dashboard XML code inside the input definition like this :

 <change>
<eval token="weekago">strftime(strptime($time_tok$,"%a %d %B %Y")-7*86400,"%a %d %B %Y")</eval>
</change>

As you can see, I substracted 7*86400 seconds to the chosen time to make it 1 week earlier. Then I use both strptime and strftime for the calculation and display.

Now I have $time_tok$ shown and stored in human format from the dropdown, and $weekago$ as well in human format.

If they are needed in a search for the earliest/latest settings, I'd need to reformat them with strptime.

Complete code :

<title>Chosen date: $time_tok$ / 1 Week ago= $weekago$</title>
<input type="dropdown" token="time_tok">
<label>Pick a date</label>
<fieldForLabel>_time</fieldForLabel>
<fieldForValue>_time</fieldForValue>
<search>
<query>| makeresults count=14 | streamstats count as offset |eval _time=_time-offset*86400 | eval _time=strftime(_time,"%a %d %B %Y")</query>
<earliest>-24h@h</earliest>
<latest>now</latest>
</search>
<change>
<eval token="weekago">strftime(strptime($time_tok$,"%a %d %B %Y")-7*86400,"%a %d %B %Y")</eval>
</change>
</input>