Join two searches based on a condition

[rakes568]

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

[woodcock]

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]

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.

[woodcock]

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

[horsefez]

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

[woodcock]

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?

[horsefez]

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.