Splunk Search
Highlighted

Trouble Joining Firewall and DHCP logs by IP address

Path Finder

Hello All, been banging the head against the desk for awhile on this one; tried join, transaction, and a few other things.

We have fw logs that record the ip address (dhcp) of the users, but no other information about the user. We have bro dhcp logs that record the user's ip address and mac address. I need to be able to "join" these searches to show what user (by MAC address). At a high level:

log 1: fw data (time, destip, etc.)
log 2: dhcp data (epoch time earliest seen, dhcp_ip, mac)

In this example, log 1 contains variable A (dest_ip), log 2 contains variable A (ip) and B(mac). Thus, I need to "join" log 1 and log 2 by the common value (not common field name!) of A. The actual field names are:

log 1: destip
log 2: bro
dhcpip, brodhcp_mac

The part that makes this tough is the time factor. In the firewall logs, the time is the time field which I can convert easily to epoch time by eval t=time. In the dhcp logs there is also a epoch time field (dhcptime) which basically stamped on every event. So, I need to not only join logs 1 and 2, but I also need to find out how the time correlates so I know which MAC had the dhcpip at the time of the fw entry. I'm using a lookup to get the username from the mac.

This fails miserably lol and I can't quite find out where.... I left the time part out in this example because not sure how to include it. I'm using fields to cut down on search time/data.

sourcetype=firewall | fields time, transport, srcip, srcport, srcinterface, direction, destinterface, destip, desttranslatedip, destport, action | eval t=time | join type=inner rename destip AS brodhcpip [search sourceype=brodhcp | where (the fw time matches the LAST bro time the most closely??) | fields earliesttime, brodhcpuid, brodhcpmac, brodhcpip, leasetime] | lookup maclookup.csv macaddr1 AS brodhcpmac OUTPUT macaddr1 AS usermac | search usermac="*" | table t, brodhcpip, usermac

Any help would be greatly appreciated!

Highlighted

Re: Trouble Joining Firewall and DHCP logs by IP address

SplunkTrust
SplunkTrust

Can you give this a shot?

sourcetype=firewall | dedup dest_ip | eval ip=dest_ip | join usetime=true type=inner ip [search sourceype=bro_dhcp | dedup bro_dhcp_ip | eval ip=bro_dhcp_ip | table ip, user_mac ] | table  dest_ip, bro_dhcp_ip, user_mac

If that works... try this:

sourcetype=firewall | stats latest(dest_ip) as dest_ip by _time | eval ip=dest_ip | join usetime=true type=inner ip [search sourceype=bro_dhcp | stats latest(bro_dhcp_ip) AS bro_dhcp_ip by user_mac _time | eval ip=bro_dhcp_ip | table ip, user_mac, _time ] | table  dest_ip, bro_dhcp_ip, user_mac, _time

View solution in original post

Highlighted

Re: Trouble Joining Firewall and DHCP logs by IP address

Path Finder

Yes, this works- I think:) I validated a couple of the results and it came out good. I did substitute in brodhcpmac instead of usermac because usermac was a renamed field from the lookup table process. Now I'm going to try it with the lookup table and validate a full set of data, but thank you- this is a huge boost. I'll return with the results to confirm this fits the bill.

Highlighted

Re: Trouble Joining Firewall and DHCP logs by IP address

Influencer

make sure to accept jkat54 s answer if it worked for you - will help future people

0 Karma
Highlighted

Re: Trouble Joining Firewall and DHCP logs by IP address

Path Finder

I'm working on it... I don't have an accept button anywhere.

Highlighted

Re: Trouble Joining Firewall and DHCP logs by IP address

Path Finder

Does anybody know why I don't see an accept button? I'm having trouble accepting this answer. Can one of the moderators accept it? It is a valid answer.

Highlighted

Re: Trouble Joining Firewall and DHCP logs by IP address

SplunkTrust
SplunkTrust

Hey, sorry... you needed to click on the gear next to my original comment and convert to answer. Not sure if you can do that with low karma or not. Anyways, I changed the comment to answer so you can mark it as the answer.

Furthermore, thanks for coming back to mark as the answer, and give updates! Keep that up and I'll always be happy to help you as I was this time.

0 Karma
Highlighted

Re: Trouble Joining Firewall and DHCP logs by IP address

Influencer

Thanks for coming back to accept!

Highlighted

Re: Trouble Joining Firewall and DHCP logs by IP address

Path Finder

OK, was able to accept it.... my low karma doesn't give me full options lol... guess I better go answer some questions myself! Thanks JKAT

Highlighted

Re: Trouble Joining Firewall and DHCP logs by IP address

SplunkTrust
SplunkTrust

well then... have some karma! -upvoted every comment you made and awarded 4 points to get your karma over 100-

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.