I'm looking to get a list of results of events that should have occured in the last day by running a search with the date range earliest=-7d@d latest=-24h
then running the same search for the range earliest=-24h
, then subtracting the second result from the first to tell me what events happened over the last week but not in the last 24 hours.
The best I could come up with was to write the results the last 24 hours to a file using outputlookup
:
index="theindex" earliest=-24h | stats count by theevent| eval seen="yes" | outputlookup lastday.csv
then run a second query that looked back a week
index="theindex" earliest=-7d@d latest=-24h | stats count by theevent| lookup lastday.csv theevent OUTPUT seen | where NOT seen="yes"
Is there a better way to do this? A single search would make me very happy!
Thanks in advance
Absolutely, you can do this in a single search. It's all about stitching it together. Essentially you can follow the steps here, but adapt it to your needs.
index=theindex earliest=-7d latest=-24h theevent=* NOT [search index=theindex earliest=-24h theevent=* | dedup theevent | fields + theevent] | stats c by theevent
The resulting table will contain the events that occurred in the last week, but not in the last 24 hours.
The inner search (in square brackets) will get executed first and produce a (deduplicated) list of 'theevent', which are appended with a NOT to the outer search. Then you do your stats count.
You may even do it in a single search without subsearches, but maybe it won't be more efficient;
index=theindex earliest=-7d theevent=* | eval AAA = if(_time < (now() - 86400), "last_week", "today") | dedup theevent AAA | transaction theevent max_events=2| where eventcount=1 AND AAA="last_week"
... I think, haven't tested the last one. But it - or something very close - will work as well.
/k
Absolutely, you can do this in a single search. It's all about stitching it together. Essentially you can follow the steps here, but adapt it to your needs.
index=theindex earliest=-7d latest=-24h theevent=* NOT [search index=theindex earliest=-24h theevent=* | dedup theevent | fields + theevent] | stats c by theevent
The resulting table will contain the events that occurred in the last week, but not in the last 24 hours.
The inner search (in square brackets) will get executed first and produce a (deduplicated) list of 'theevent', which are appended with a NOT to the outer search. Then you do your stats count.
You may even do it in a single search without subsearches, but maybe it won't be more efficient;
index=theindex earliest=-7d theevent=* | eval AAA = if(_time < (now() - 86400), "last_week", "today") | dedup theevent AAA | transaction theevent max_events=2| where eventcount=1 AND AAA="last_week"
... I think, haven't tested the last one. But it - or something very close - will work as well.
/k
That worked perfectly! Thanks for your excellent help.