Splunk - Join two queries from same index and eventtype

[mikeyemane]

I have the following two events from the same index (VPN). I've been unable to try and join two searches to get a table of users logged in to VPN, srcip, and sessions (if logged out 4911 field). I'm able to pull out this infor if I search individually but unable to combine. Thanks for the help.

259 <166>1 2018-03-21T10:13:45-04:00 abc.defg.net PulseSecure: - - - 2018-03-21 10:13:45 - ive - [12.34.56.78] DHI\john(VPN)[VPN] - Login succeeded for FHI\john/VPN (session:be1c9211) from 12.34.56.78 with Open AnyConnect VPN Agent v7.08

276 <166>1 2018-03-21T11:35:39-04:00 abc.defg.net PulseSecure: - - - 2018-03-21 11:35:39 - ive - [12.34.56.78] DHI\john(VPN)[VPN] - Closed connection to TUN-VPN port 443 after 4911 seconds, with 2171883 bytes read (in 6 chunks) and 90236 bytes written (in 8 chunks)

[richgalloway · Accepted solution]

If I interpret your events correctly, this query should do the job. The three rex commands extract the desired fields then the stats command puts the events together by userid.

index=juniper-mag host=abc.defg.net ("Login succeeded" OR "Closed connection") | rex "\]\s(?<userid>[^\(]+)" | rex "Login succeeded.*from (?<srcip>[^\s]+)" | rex "after\s(?<duration>\d+)\sseconds" | stats earliest(_time) as sessionStart latest(_time) as sessionEnded values(duration) as duration values(srcip) as srcip by userid | fieldformat sessionStart=strftime(sessionStart,"%Y-%m-%dT%H:%M:%S%:z") | fieldformat sessionEnded=strftime(sessionEnded,"%Y-%m-%dT%H:%M:%S%:z")

[mikeyemane]

Thank you. This gave me what I was looking for. Response from kyaparla was also good. Thank you both.

[kyaparla]

please try this.

index=juniper-mag host=abc.defg.net "Login succeeded" OR "Closed connection" | eval fields=split(_raw, " ") | eval user=mvindex(fields,14) | eval duration=mvindex(fields,23)| eval srcip=mvindex(fields, 13) | stats latest(_time) as time latest(duration) as duration by user,srcip | convert ctime(time) as time

[BearMormont]

Didn't even notice they were the same index and host. This is much better. 🙂

[richgalloway]

What are your two searches that you want to combine?

[mikeyemane]

Thanks for help Rich. First event shows userid, time session started, and srcip. Second event shows the same, except time session ended and session duration (4911 seconds). I want to generate a table of userid, srcip, time session started, time session ended, and duration. Here are the two searches that kind of get me what I'm looking for:

index=juniper-mag host=abc.defg.net _raw="Login succeeded" | eval fields=split(_raw, " ") | eval user=mvindex(fields,14) | eval srcip=mvindex(fields, 13) | table _time user srcip

index=juniper-mag host=abc.defg.net _raw="Closed connection" | eval fields=split(_raw, " ") | eval duration=mvindex(fields,23) | table _time user duration

[MuS]

Always worth to read this answer https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo... 😉

cheers, MuS