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: brodhcpip, 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!
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
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.
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.
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.
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
well then... have some karma! -upvoted every comment you made and awarded 4 points to get your karma over 100-