Splunk Search

Join two searches based on a condition

rakes568
Explorer

Hi, We have two kind of logs for our system:
First one logs all the user sessions with user name, src ip, dst ip, and login/logout time.
Jun 22 10:11:00 : UserSession: User=user1, SRC=192.168.0.1, Login=2017-06-22 10:00:00, Logout=2017-06-22 10:10:00
Jun 22 10:21:00 : UserSession: User=user2, SRC=192.168.0.2, Login=2017-06-22 10:11:00, Logout=2017-06-22 10:20:00
Jun 22 10:31:00 : UserSession: User=user3, SRC=192.168.0.1, Login=2017-06-22 10:21:00, Logout=2017-06-22 10:30:00
Jun 22 10:41:00 : UserSession: User=user1, SRC=192.168.0.1, Login=2017-06-22 10:31:00, Logout=2017-06-22 10:40:00
Jun 22 10:51:00 : UserSession: User=user1, SRC=192.168.0.4, Login=2017-06-22 10:41:00, Logout=2017-06-22 10:50:00

SRC IP above comes from a pool, and can be reassigned to another user, if it's not being used by anyone else at the time.

Another log is from IPTable, and lets say logs src and dst ip for each tcp connection for all users between their login/logout time.
1. Jun 22 10:04:00 Iptable: SRC=192.168.0.1 DST=10.0.0.10
2. Jun 22 10:06:00 Iptable: SRC=192.168.0.1 DST=10.0.0.10
3. Jun 22 10:09:00 Iptable: SRC=192.168.0.1 DST=10.0.0.11
4. Jun 22 10:15:00 Iptable: SRC=192.168.0.2 DST=10.0.0.12
5. Jun 22 10:25:00 Iptable: SRC=192.168.0.1 DST=10.0.0.13
6. Jun 22 10:35:00 Iptable: SRC=192.168.0.1 DST=10.0.0.13
7. Jun 22 10:44:00 Iptable: SRC=192.168.0.4 DST=10.0.0.10
8. Jun 22 10:45:00 Iptable: SRC=192.168.0.4 DST=10.0.0.14

Now I want to correlate these two logs on SRC, and get a table with number of src to dst ip connections for each user. Not that each connection is valid only between login and logout time.

So query should be something like:
join UserSession Iptable where UserSession.SRC == Iptable.SRC and (Iptable._time>Login and Iptable._time

0 Karma

woodcock
Esteemed Legend

Like this:

(index=YouShouldAlwaysSpecifyAnIndes sourcetype=AndAlsoAlwaysSourcetype UserSession stuff here) AND (index=SecondIndexHere sourcetype=SecondSourcetypeHere IPtable stuff here)
| eval comboSRC=coalesce(UserSession.SRC, Iptable.SRC)
| reverse
| streamstats last(User) AS lastUser BY comboSRC
| search sourcetype=SecondSourcetypeHere

This pulls the User value from UserSession and merges it into the associated IPtable data and then discards the UserSession events.

rakes568
Explorer

Hey thanks for answering. Unfortunately this got posted by mistake, while I was editing the question. Please see this https://answers.splunk.com/answers/550677/join-two-searches-based-on-a-condition-2.html for complete question. Your answer looks good, but I need a bit more than that. Can you go to another link and fix your answer? I'd appreciate that. I am going to delete this post.

0 Karma

woodcock
Esteemed Legend

OK, so no Accept here but how about an Up-vote 😆

0 Karma

horsefez
Motivator

^ this guy wants to catch up to somesoni so badly 😄

0 Karma

woodcock
Esteemed Legend

It is essentially impossible at this point. I suspect that @somesoni2 will slow down once he crosses 100K but I though that he would slow down when he solidly grabbed the #1 slot and he didn't. My goal is to win the karma contest (if it ever starts) and to cross 50K. Then I will slow down for a while. I wonder if tweeking for Karma in Answers is what killed @somesoni1?

0 Karma

horsefez
Motivator

That is some interesting trivia I was not aware about. I did not know there was a somesoni(1) account back in the days. I strongly believe that somesoni isn't actually a real person and in fact a wicked smart AI that is now in his second version of his development release cycle (somesoni v2). After 100k karma there could be a somesoni3... who knows.

I strongly hope you and somesoni will slow down for a few (weeks|months|years|decades|centuries) so I get the chance to rule over splunk answers and catch up. At least I will get more karma on regular-expression postings, because there then will be no strong competition anymore.

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...