So I need to get the latest sales stats by country over many different timescales (like right now, so far today, last 7 days. so far this month, last 4 weeks, so far this year, etc)
So I created a an efficient saved search for the year, which summarises sales by country,day
Then i created the multiple charts for different timeframes, but no matter how I try and set earliest and latest times, they are ignored and all charts are for 'All-Time'
Saved Search:
index=blah host=xxx sourcetype=sales COUNTRY!=NULL earliest=@y latest=@d date_hour>22 | append [|search index=blah host=xxx sourcetype=sales COUNTRY!=NULL earliest=@d latest=now] | table _time VALUE_IN_EURO COUNTRY date_wday date_mday date_month date_year
PostProcess Search:
stats latest(VALUE_IN_EURO) as euro by COUNTRY,date_mday| stats sum(euro) by COUNTRY
Now lets say I want to try and get a relative timescale like last 7 days, I have tried the following:
-7d@d
using the earliest
tag in the search tag -7d@d
using using the earliestTime
tag in the search tag search earliest=-7d@d latest=now | stats latest(VALUE_IN_EURO) as euro by COUNTRY,date_mday| stats sum(euro) by COUNTRY
(produces zero results)Its driving me mad! Any help appreciated!!! Or maybe its just not possible to filter the results of a saved search by time? Though it seems like a fairly obvious use of a saved search to produce an efficient dasboard 🙂
OK - after a nights sleep I have some solution, though it seems strange that its the only way and I expect what I am missing here is some limitation of postprocessing.
But if I use a where clause in the postprocess search, it works.
e.g. for last 7 days:
| where _time>=now()-604800 | stats latest(VALUE_IN_EURO) as euro by COUNTRY,date_mday | stats sum(euro) by COUNTRY
Would be very interested to know of the reason why timepickers on the panel editor and timespecifications in the simple xml do not work on a savedsearch.
OK - after a nights sleep I have some solution, though it seems strange that its the only way and I expect what I am missing here is some limitation of postprocessing.
But if I use a where clause in the postprocess search, it works.
e.g. for last 7 days:
| where _time>=now()-604800 | stats latest(VALUE_IN_EURO) as euro by COUNTRY,date_mday | stats sum(euro) by COUNTRY
Would be very interested to know of the reason why timepickers on the panel editor and timespecifications in the simple xml do not work on a savedsearch.
Hi try to do something like this:
1-if you are using splunk 6.2.....
0</
earliest>
.
.
.
.
<search>
<query>
here goes your query
</query>
<earliest>$time.earliest$</earliest>
<latest>$time.latest$</latest>
</search>
2- if you are unsing a version prior to 6.2......
0</
earliestTime>
.
.
.
.
here goes your query</
searchString>
$
latestTime>
3-and remove the first timestamp (earliesti=.. latest=....)in you savedsearch.
Thanks for the answer, but doing what you suggest would remove the whole purpose of the savedsearch in the first place. The whole point of the saved search is to get the whole years data in one efficient search, then twist the results in different ways for each of 12 charts on the dashboard. If I used the method you suggest, it would a) get all data for all time which would be slow, plus it would essentially be 12 searches of the a huge amount of data in the browser process.
I have all the data I need in the one search which takes just 8 seconds and has already been executed by the server - now I just need to present/filter the data from the saved search according to different time scales. The month, year and so on are simple as I extracted date_month, date_year etc.... but its the relative timescales that seem to be a problem like last 7 days, last 28 days etc