How to use calculated timeframe in splunk query?

[shehenshah14]

Hello,
I am trying to write a query which results in the subtraction of $datetimepicker value events counts & $datetimepicker-1w value events count.

Query tried below does not work and gives me count1 & count2 for two diff timeframes and I can subtract and get the result.

index=app | search eventName=*** | stats count as count1
| addinfo | eval start_time=strftime(relative_time(info_min_time,"-1w"),"%m/%d/%Y:%H:%M:%S") | eval end_time=strftime(relative_time(info_max_time,"-1w"),"%m/%d/%Y:%H:%M:%S") 
| append [search eventName=*** earliest=start_time latest=end_time
|  stats  count as count2 ]

Suggestions to use other parameters are welcome.

[niketn · Accepted solution]

@shehenshah14, you can use default search job tokens to get the earliest and latest job time by running a dummy search and then use <eval> to set the tokens for last week using relative_time(). For details on this approach refer to my answer: https://answers.splunk.com/answers/578984/running-one-of-two-searches-based-on-time-picker-s.html

Also, I have set both earliest and latest to "-1w". You can set as per your need.

Following example shows two approaches for stats. Using append and multisearch. The multisearch command does not face sub-search limitation (like silent truncation of results when number of events for selected time range exceeds the limit). However, it can only run streaming command. So, you would need to choose the one which suits better for your use case.

<form>
  <label>Stats for two different days</label>
  <search>
    <query>| makeresults
    </query>
    <earliest>$tokTime.earliest$</earliest>
    <latest>$tokTime.latest$</latest>
    <done>
      <eval token="tokEarliestWeekBack">relative_time(strptime($job.earliestTime$,"%Y-%m-%dT%H:%M:%S"),"-1w")</eval>
      <eval token="tokLatestWeekBack">relative_time(strptime($job.latestTime$,"%Y-%m-%dT%H:%M:%S"),"-1w")</eval>
    </done>
  </search>
  <fieldset submitButton="false">
    <input type="time" token="tokTime" searchWhenChanged="true">
      <label></label>
      <default>
        <earliest>-1d@d</earliest>
        <latest>@d</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <chart>
        <search>
          <query>| multisearch [search index=_internal sourcetype=splunkd log_level!="INFO" earliest="$tokTime.earliest$" latest="$tokTime.latest$" | eval data="ErrorCountSelectedTime"] 
[ search index=_internal sourcetype=splunkd log_level!="INFO" earliest="$tokEarliestWeekBack$" latest="$tokLatestWeekBack$" | eval data="ErrorCountWeekBeforeSelectedTime"]
| stats count by data</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.chart">column</option>
        <option name="charting.chart.showDataLabels">all</option>
        <option name="charting.drilldown">none</option>
      </chart>
    </panel>
  </row>
  <row>
    <panel>
      <chart>
        <search>
          <query>index=_internal sourcetype=splunkd log_level!="INFO" earliest="$tokTime.earliest$" latest="$tokTime.latest$"
| stats count as ErrorToday
| appendcols [ search index=_internal sourcetype=splunkd log_level!="INFO" earliest="$tokEarliestWeekBack$" latest="$tokLatestWeekBack$"
| stats count as ErrorWeekBack]
| transpose column_name="ReportKey"
| rename "row 1" as "ErrorCount"</query>
          <earliest>-24h@h</earliest>
          <latest>now</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.chart">column</option>
        <option name="charting.chart.showDataLabels">all</option>
        <option name="charting.drilldown">none</option>
      </chart>
    </panel>
  </row>
</form>

[shehenshah14]

Thank you very much, i tried the 1st approach and it seems working.
Thanks again