topic Re: Subtract one search from another based on time of searches in Splunk Search

Subtract one search from another based on time of searches

leatherface — Wed, 11 Dec 2013 00:27:22 GMT

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

Re: Subtract one search from another based on time of searches

kristian_kolb — Wed, 11 Dec 2013 01:33:05 GMT

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.

Re: Subtract one search from another based on time of searches

leatherface — Wed, 11 Dec 2013 11:17:50 GMT

That worked perfectly! Thanks for your excellent help.