topic Re: How to combine events with time range as a variable in Splunk Search

How to combine events with time range as a variable

dougmartin — Tue, 29 Sep 2020 06:41:58 GMT

so 1199 should matchup with 2200 and 2244 match up with 4488 & 4499 in a result like this:
user_id | other user_id (MV field ok)
1199 | 2200
2244 | 4488, 4499

Part of the problem I'm having is how to construct a subsearch, or join (or appendcols, etc) where I need to use the event_timestamp as a search ( event_timestamp-90 as the lower range and event_timestamp as the upper range).
I could end up with the final result table, or some other join/transaction that can group these pre/post login user_ids.

Re: How to combine events with time range as a variable

jacobwilkins — Wed, 15 Jul 2015 16:44:46 GMT

Eww. True time windowing is hard. Bucketing is easier, but loses potential matches across a minute boundary. And, if your logs are dense and full over overlapping events, this is hopeless.

Try something like this:

yoursearch | eval post_login=if(match(page_referer,"blah.com/sign_in "),user_id,null())
 | pre_login=if(match(page_referer,"blah.com/before_sign_in "),user_id,null())
 | stats values(pre_login) AS pre_login, values(post_login) AS post_login  by _time

That makes a ton of assumptions about your data, but if your events are sparse it might be good enough.

Another approach, closer to what you are thinking, is to use localize & map. If you have lots of data, this won't scale. If you have lots of overlapping logins, this won't work.

This is a rough example of what to try, I don't use this command, so my syntax may be sketchy.

page_referer ="blah.com/sign_in" | localize timebefore=90s timeafter=0 
| map search="search page_referer=* starttimeu=$starttime$ endtimeu=$endtime$ | eval post_login=if(match(page_referer,'blah.com/sign_in '),user_id,null()) | pre_login=if(match(page_referer,'blah.com/before_sign_in '),user_id,null()) |  stats values(pre_login) AS pre_login, values(post_login) AS post_login"

That is a pretty ghetto example. Expect to do some debugging. I just hope it gives you some ideas.

Re: How to combine events with time range as a variable

martin_mueller — Wed, 15 Jul 2015 22:02:27 GMT

Here's an alternative approach:

your search for blah.com/sign_in OR blah.com/before_sign_in | transaction maxspan=90s endswith="blah.com/sign_in" startswith="blah.com/before_sign_in"

This may need a "dummy" transaction field, if so you could for example use sourcetype if all events are from the same sourcetype. This also will not work if you have overlapping transactions, this problem is intrinsic to your log files - you'd need some differentiator such as a session ID to tell overlapping transactions apart. This also may not succeed in adding both pre-events to your login event.

Re: How to combine events with time range as a variable

dougmartin — Fri, 17 Jul 2015 21:11:18 GMT

I used the map. The key for me was both endtimeu and the fact the map search command fails quietly if you try somelike eval="$myvar$" and you don't escape the quotes: eval=\"$myvar$\" within that map search.