topic Splunk - Join two queries from same index and eventtype in Splunk Search

Splunk - Join two queries from same index and eventtype

mikeyemane — Wed, 21 Mar 2018 22:39:04 GMT

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)

Re: Splunk - Join two queries from same index and eventtype

richgalloway — Thu, 22 Mar 2018 01:02:06 GMT

What are your two searches that you want to combine?

Re: Splunk - Join two queries from same index and eventtype

mikeyemane — Tue, 29 Sep 2020 18:35:58 GMT

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

Re: Splunk - Join two queries from same index and eventtype

kyaparla — Thu, 22 Mar 2018 02:47:08 GMT

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

Re: Splunk - Join two queries from same index and eventtype

BearMormont — Thu, 22 Mar 2018 02:54:11 GMT

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

Re: Splunk - Join two queries from same index and eventtype

MuS — Thu, 22 Mar 2018 02:58:18 GMT

Always worth to read this answer https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-join-append-or-use-of-subsearches.html 😉

cheers, MuS

Re: Splunk - Join two queries from same index and eventtype

richgalloway — Thu, 22 Mar 2018 03:17:12 GMT

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

Re: Splunk - Join two queries from same index and eventtype

mikeyemane — Thu, 22 Mar 2018 03:48:32 GMT

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