Making same query run for different times

[deepak02]

Hi,

I have a dashboard with a query that currently runs for the time range 'Today' everyday. I want the time range to be modified dynamically based on when the dashboard is viewed because data may not be available on some of the days.

Viewed on Sunday: Run the query for Saturday (i.e.: if Sunday, run for -1 days)
Viewed on Monday: Run the query for Saturday (i.e.: if Monday, run for -2 days)

Viewed from Tuesday-Saturday:
Before 9 am: Run for yesterday
After 9 am: Run for today till 9 am

E.g: If dashboard is loaded at 8 am on Wednesday, time range should be Tuesday
If dashboard is loaded at 10 am on Wednesday, time range should be Wednesday(midnight till 9 am)

The query is the same in all the cases, only the time range varies.
Is it possible to implement the above in Splunk?

Thanks,
Deepak

[niketn · Accepted solution]

Following is a run anywhere example that should work based on conditions you have provided. I am running base search to calculate earliest and latest time to be used in the Dashboard Search Query. Then progress Search Event handler (for Splunk Enterprise version 6.5 onward) has been used to pass on the same to Required second search query.

PS: For Splunk versions prior to 6.5 you should use preview instead of progress. Refer to Splunk Search Event Handlers on Splunk Documentation specific to the version you are using.

Second search runs a dummy query against Splunk's _internal index to count number of Errors in splunkd, which you can replace with your own query.

Notice token $queryTimeRange$ passed from base search which is used in the second query for defining earliest and latest time instead of <earliest> and <latest> search nodes.

  <search>
    <query>
      | makeresults
      | eval WeekDay=strftime(_time,"%a")
      | eval Hour=strftime(_time,"%H")
      | eval Time=case(
                        WeekDay=="Sun","earliest=-1d@d latest=now",
                        WeekDay=="Mon","earliest=-2d@d latest=now",
                        (WeekDay=="Tue" OR WeekDay=="Wed" OR WeekDay=="Thu" OR WeekDay=="Fri" OR WeekDay=="Sat") AND Hour<="09","earliest=-1d@d latest=-0d@d",
                        (WeekDay=="Tue" OR WeekDay=="Wed" OR WeekDay=="Thu" OR WeekDay=="Fri" OR WeekDay=="Sat") AND Hour>"09","earliest=-0d@d latest=-0d@d+9h",
                        true(),"earliest=-0d@d latest=now"
                      )
      | table _time WeekDay Hour Time
    </query>
    <progress>
      <set token="queryTimeRange">$result.Time$</set>
    </progress>
  </search>
<row>
  <panel>
    <table>
      <title>Time: $queryTimeRange$</title>
    <search>
      <query>index=_internal sourcetype=splunkd log_level="ERROR" $queryTimeRange$
| stats count as Errors
| addinfo
| fieldformat info_min_time=strftime(info_min_time,"%c")
| fieldformat info_max_time=strftime(info_max_time,"%c")
| fieldformat info_search_time=strftime(info_search_time,"%c")
| table Errors info_min_time info_max_time info_search_time
      </query>
    </search>
    </table>
  </panel>
</row>