Join 3 tables together with conditions

[horsefez]

Hi there,

I'm into correlation searches now and I'm stuck on a problem combining tree tables, while certain conditions need to be met.

In my company, administrators do configuration changes according to tickets in a ticket manager.

Table 1 (srctype=Changes) contains Change-Sessions by the Admins

02-10-15 07:40:00 LOGON 172.16.9.11 192.168.122.199 user1
02-10-15 07:41:00 CHNGE 172.16.9.11 192.168.122.199 user1
02-10-15 07:41:10 CHNGE 172.16.9.11 192.168.122.199 user1
02-10-15 07:41:20 CHNGE 172.16.9.11 192.168.122.199 user1
02-10-15 07:42:20 LOGOF 172.16.9.11 192.168.122.199 user1

Table 2 (deviceip_devicename) lists known devices an their corresponding IP address

192.168.122.187,lan-switch-1
192.168.122.199,lan-switch-2

Table 3 (srctype=Tickets) lists all changes that admins were allowed to do

02-10-15 07:35:00 configchange user1 lan-switch-2 IM735903

You see here that I first have to join Table 1 and Table 2 with the IP, to then join them together with Table 3.
The conditions I want to check are if there is a valid change-ticket (Ticket-ID IM*) to a change-session.

You also notice that the time of the change-ticket is different to the time of the change-session. When there is a change ticket for 07:35 the change should be possible for one hour. When the change is done after this period of time the change should be invalid.

---

So far I've written the following search:

index=_* OR index=* sourcetype=Changes 
|transaction Source_IP Destination_IP Username startswith="LOGON" endswith="LOGOF" 
| join Destination_IP [inputlookup deviceip_devicename | rename Device_IP AS Destination_IP]
| join type=left date_hour [search sourcetype=Ticketcenter | fields Ticket_ID Device_Name date_hour] 
| where Ticket_ID!="" 

Thanks in advance! 🙂

[jplumsdaine22 · Accepted solution]

There are a bunch of splunk answers already on this subject. First read the answer here: https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo...

Specificically in your case I would start with something like this:

 (sourcetype=changes OR  sourcetype=Ticketcenter) | lookup deviceip_devicename deviceip AS <fieldname of ip address in sourcetype=changes> OUTPUT devicename | eval session_time=if(sourcetype=="changes",_time,NULL) | eval ticket_time=if(sourcetype=="Ticketcenter",_time,NULL) | stats values(*) as * by devicename | fields <as desired> 

You should be able to validate the times with an additional |where command comparing the two time fields. It can be hard to shake the idea of requiring joins - try to think of Splunk as one big table!

[horsefez]

Thank you very much!

I also found that posting, but I for myself find it hard to change your thinking to working with stats and chart. Join and append are more intuitive. But I'll try! 🙂