Splunk Search

How to use calculated timeframe in splunk query?

shehenshah14
New Member

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.

0 Karma
1 Solution

niketn
Legend

@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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

View solution in original post

0 Karma

niketn
Legend

@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>
____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

shehenshah14
New Member

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...