Sorry, this is probably easy and I'm missing something, but but I've been beating my head on it so...
I am trying to that the results from one search and take fields from that search and utilize them in a second search combining the results.
Specifically, I am trying to search my firewall logs, take a source_ip and start_time and utilize those in a "data enrichment" type search against our DHCP logs to find mac & hostname. I've had some success with sub-searches, but that doesn't seem like the right mech. It seems like either a macro or a lookup but I'm dead ending on both. My latest attempt was with map...
index=firewall | head 1 | map search="search index=dhcp DHCPACK dest_ip=$src_ip$ timeformat="%m-%d-%Y %h:%m:%s" latest=$start_time$ | | head 1 | fields dest_mac | fields - _time "
... which seems to eliminate all of my firewall results, leaving me with dhcp results.
Any suggestions/pointers?
Thanks
I am not sure if I understood correctly, but try the below search to get you latest destip based on srcip
index=firewall | rename src_ip as ip|join ip [search index=dhcp DHCPACK |stats latest(*) as *, latest(_time) as _time by dest_ip| rename dest_ip as ip]| fields dest_mac <your other field names>
Well, I was trying to avoid join because it was so intensely slow over a log the size of a firewall log. I was trying to go down the route of a macro that I could feed ip & time and get a mac returned as a new field.
I have been playing with the search above and a couple of thoughts. First is I am unclear why we'd go the stats realm. Is that returning the last instance of dhcp records with the dest_ip fed? Which leads me to the next thing... I need to go off of the start_time from the firewall log. The concern being dhcp logs for the same dest_ip after the start_time may reflect the dhcp address having been reassigned.
Re: sample data...
from the firewall. source_ip and start_time are bolded
Dec 15 15:28:38 firewall-host 1,2018/12/15 15:28:38,,TRAFFIC,end,0,,10.21.94.190,192.229.210.163,132.177.238.65,192.229.210.163,rule 230,rmw1031,,incomplete,,Inside,Inet,ethernet1/19.384,ethernet1/20.100,,2018/12/15 15:28:38,,,51132,443,29213,443,,tcp,allow,286,146,140,4,2018/12/15 15:28:27,,any,0,,,10.0.0.0-10.255.255.255,United States,0,2,2,tcp-rst-from-client,0,0,0,0,,firewall-host,from-policy,,,0,,0,,N/A
From the DHCP log. the dest_ip, dest_mac, and hostname are bolded. I've been using _time from this log.
Dec 15 15:12:42 132.177.128.99 dhcpd[17818]: DHCPACK on 10.21.94.190 to c4:98:80:ee:45:64 (ConstanesiPhone) via eth3 relay eth3 lease-duration 7200 (RENEW) uid 01:c4:98:80:ee:45:64
My desired result would simply be to maintain the firewall log and append the dest_mac and hostname fields as new fields.
But I should mention one of my struggles has been making sure the DHCP log entry is the latest entry before the firewall log's "start_time" field. I know that is one of the trippy bits.
firewall does, but the DHCP logs only have an ip field (dest_ip) and the standard _time field. The _time field certainly wouldn't match the firewall's start_time but what I am shooting for is the the last DHCP log entry matching that IP before the start_time.
Do both your DHCP and firewall logs have source_ip and start_time fields ?
Sample data and desired results would help here.