Splunk Search

Subtract one search from another based on time of searches

leatherface
Explorer

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

Tags (1)
0 Karma
1 Solution

kristian_kolb
Ultra Champion

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

View solution in original post

kristian_kolb
Ultra Champion

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

leatherface
Explorer

That worked perfectly! Thanks for your excellent help.

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...