topic Re: find all events of type X that do not have an event of type Y within 1 minute on either side in Splunk Search

find all events of type X that do not have an event of type Y within 1 minute on either side

noah10 — Mon, 04 May 2015 07:52:24 GMT

I'm new to Splunk and trying to figure out how to find all events of type X that do NOT have an event of type Y within 1 minute (before or after) of them. I found http://answers.splunk.com/answers/137069/find-all-events-not-having-a-corresponding-event-matched-by-one-fields-value-where-not-exists.html , but in my case the events have nothing to correlate them except for time, and I haven't been able to adapt the answer for that question to my case. Any suggestions about the best way to accomplish such a search?

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

MuS — Mon, 04 May 2015 08:17:22 GMT

You should provide, if possible, some example events; otherwise it will be like asking the m-a-g-i-c glass ball for help 😉

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

noah10 — Mon, 04 May 2015 08:25:32 GMT

There's really not a lot to go on - the events basically look like this:

timestamp "No valid access for "
timestamp "Error while reloading"

So the question is: How do I find all instances of "No valid access for" that do not have an instance of "Error while reloading" within 1 minute (past or future) of them?

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

MuS — Mon, 04 May 2015 09:20:48 GMT

take a look at this answer http://answers.splunk.com/answers/185829/how-to-create-a-custom-macro-function-inside-the-s.html to get an idea what can be done in your case. This answer is a complete different use case but you see what you can do by using | eval 1min_ago = if(last_time > exact(relative_time(now(),"-2min@min")) AND last_time <= exact(relative_time(now(),"-1min@min")) , per_min_count ,"0") and do some further splunk-fu with it.

I assume there is no need for running any transaction nor any sub search 😉

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

noah10 — Mon, 28 Sep 2020 19:49:33 GMT

Hmmm... that looks like an interesting way to bucket by time, but I'm not clear how to combine that with my two eventtypes and yield results where one eventtype is null. Maybe this example will help:

eventtype=type_X | localize maxpause=2m | map search="search eventtype=type_Y starttimeu=$starttime$ endtimeu=$endtime$"

That query successfully buckets by time and returns events of type_Y that fall within two minutes of events of type_X. What I need is something that says "when the map clause returns 0 results, output the corresponding event of type_X". I tried using stats count | where to achieve that, but that (not too surprisingly) doesn't do what I want - it just outputs 0 results, presumably because I'm asking it to output events of type_Y where there are 0 events of type_Y.

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

MuS — Tue, 05 May 2015 09:11:08 GMT

Sorry, I don't have any Splunk instance handy currently ..... Just ping me if you cannot get it work and I have a look at it 😉

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

woodcock — Tue, 05 May 2015 17:48:36 GMT

I suspect the command that you will have to use is "streamstats". My foggy brain has concluded that this can be done that way, but I have not taken the time to work out the search.

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

woodcock — Mon, 28 Sep 2020 19:50:02 GMT

OK, I think I have it. I had to turn your question inside out and do a search that asks, "Find type_y events that are at least 2 minutes apart and show any type_x events between them". In other words, you asked your question backwards!
Try this:

eventtype=type_y 
| streamstats max(_time) as prevTime 
| eval myTime=_time 
| eval delta=myTime-prevTime 
| where delta <= 120 
| map maxsearches=10000 search="search eventtype=type_x _time<=$prevTime$ _time>=$mYtime$"

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

noah10 — Mon, 28 Sep 2020 19:45:04 GMT

Hmmm... I'm not sure this formulation is equivalent. Consider this example:

10:19:19 type_x
10:22:08 type_x
10:22:47 type_y
10:29:03 type_x
10:37:14 type_x

The query I'm looking for would return the type_x events from 10:19:19, 10:29:03 and 10:37:14 and would not return the type_x event from 10:22:08. The formulation "Find type_y events that are at least two minutes apart..." doesn't work here because there's only one type_y event in the range.

If I understand your query correctly, though, it isn't actually finding type_y events that are two minutes apart - it's finding all type_y events, and then doing a subquery for type_x events that happened up to 2 minutes before the type_Y event. With a little tweaking that might work - I'll try it out and report back.

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

noah10 — Wed, 06 May 2015 13:24:39 GMT

Ah, no I see I misread your query - it really is querying type_y events that are two minutes apart. Still tweaking to see if I can get what I'm looking for, though...

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

woodcock — Fri, 08 May 2015 04:14:31 GMT

OK, I realized that I forgot to discriminate one more time once I find values in the map. This will work for sure:

eventtype=type_y
| streamstats current=f window=1 max(_time) as prevTime
| eval myTime=_time
| eval delta=myTime-prevTime
| where delta>120
| map maxsearches=10000 search="search eventtype=type_x earliest=$prevTime$ latest=$myTime$
| eval lowDelta=_time-$prevTime$
| eval highDelta=$mYtime$-_time
| where lowDelta>60 highDelta>60"

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

MuS — Mon, 11 May 2015 04:01:39 GMT

Now do it without the map and I will accept the answer 😉

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

vganjare — Mon, 11 May 2015 06:02:25 GMT

HI,

How about finding the different time ranges for every occurance of X and then use this to find all the events which do not have the occurance of Y within specified time range (i.e. 1 min in your case). Following query can give you some idea:

index=_internal  [ search index=_internal   log_level="ERROR"  | eval latestTime = (strptime(strftime(_time,"%m/%d/%Y:%H:%M:%S"),"%m/%d/%Y:%H:%M:%S") + (1* 60) ) | eval earliestTime = (latestTime-(1 * 60)) | table latestTime earliestTime | eval QueryToken = "(earliest=".earliestTime." latest=".latestTime.") OR" | stats values(QueryToken) as QueryValues | makemv delim="||" QueryValues | eval QueryFilter = substr(QueryValues , 1, len(QueryValues)-3)  | return $QueryFilter] log_level!="WARNING" |chart count over _time by log_level usenull=f

Following is the logic:

Search for events which has log_level as ERROR (using subsearch) (this is filer X).
Find out the latest time and earliest time for every event ( 1 min early an 1 min later).
Construct the dynamic query filter which will have the different earliest and latest times (depending on the events from point 1)
Use this dynamic query along with log_level!="WARNING" filter (i.e. not Y filter) for the main search.

Hope this will help to solve the problem.

Thanks!!

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

woodcock — Mon, 11 May 2015 12:53:48 GMT

I think the choice is between map and subsearch and map is better so why bother?

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

MuS — Wed, 13 May 2015 01:57:24 GMT

Because of this http://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-join-append-or-use-of-subsearches.html 😉

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

woodcock — Wed, 13 May 2015 04:36:52 GMT

Yes, it makes my point, not yours: non-subsearch (non-join) options such as stats + map are generally preferable. Does my answer work or not?

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

noah10 — Thu, 21 May 2015 11:35:16 GMT

OK - After playing with it for a while I came up with this, which seems to work:

eventtype=type_y 
| sort _time 
| streamstats current=f window=1 max(_time) as prevTime 
| eval myTime=_time 
| eval delta=myTime-prevTime 
| where delta>120 
| map search="search eventtype=type_x earliest=$prevTime$ latest=$myTime$ 
| eval lowDelta=_time-$prevTime$ 
| eval highDelta=$myTime$-_time 
| where lowDelta>60 highDelta>60"

Pretty close to the above, obviously, although the sort _time clause and the current=f window=1 params for the streamstats clause are critical to make sure that delta actually turns out as expected. Thanks for all of the help, everyone - this turned out to be much more complicated than I originally thought!

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

woodcock — Sun, 24 May 2015 01:47:18 GMT

I am glad it was close enough for you to adjust to perfection without too much hassle.

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

acharlieh — Sun, 24 May 2015 02:30:52 GMT

This isn't going to work how you think it is. Namely, returning multiple earliest and latest values from a subsearch doesn't cause the parent search to look at multiple earliest and latest segments. Searches span a single time range each. Therefore only one earliest and latest winds up being effective.

Re: find all events of type X that do not have an event of type Y within 1 minute on either side

jwalzerpitt — Wed, 01 Jun 2016 18:05:49 GMT

When I run the query above I get the following error:

"Error in 'map': Did not find value for required attribute 'prevTime'."