Splunk Search

Trouble Joining Firewall and DHCP logs by IP address

tjr1775
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, dest_ip, 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: dest_ip
log 2: bro_dhcp_ip, bro_dhcp_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 (dhcp_time) 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 dhcp_ip 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, src_ip, src_port, src_interface, direction, dest_interface, dest_ip, dest_translated_ip, dest_port, action | eval t=_time | join type=inner rename dest_ip AS bro_dhcp_ip [search sourceype=bro_dhcp | where (the fw time matches the LAST bro time the most closely??) | fields earliest_time, bro_dhcp_uid, bro_dhcp_mac, bro_dhcp_ip, lease_time] | lookup mac_lookup.csv mac_addr1 AS bro_dhcp_mac OUTPUT mac_addr1 AS user_mac | search user_mac="*" | table t, bro_dhcp_ip, user_mac

Any help would be greatly appreciated!

1 Solution

jkat54
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

jkat54
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

tjr1775
Path Finder

Yes, this works- I think:) I validated a couple of the results and it came out good. I did substitute in bro_dhcp_mac instead of user_mac because user_mac 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.

jplumsdaine22
Influencer

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

0 Karma

tjr1775
Path Finder

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

tjr1775
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.

tjr1775
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

jkat54
SplunkTrust
SplunkTrust

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

0 Karma

jkat54
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

jplumsdaine22
Influencer

Thanks for coming back to accept!

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...