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.
@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, 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>
Thank you very much, i tried the 1st approach and it seems working.
Thanks again