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

[noah10]

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... , 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?

[woodcock]

Ah, never mind: map uses subsearch, too!

[woodcock]

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$"

[noah10]

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.

[noah10]

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...

[MuS]

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

[noah10]

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?

[MuS]

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 😉

[noah10]

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.

[woodcock]

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.

[MuS]

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 😉