Using results from one searchin a second and combining the results

[BryanScovill]

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

[Vijeta]

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>

[BryanScovill]

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.

[BryanScovill]

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.

[Vijeta]

Do both your DHCP and firewall logs have source_ip and start_time fields ?

[nplamondon]

Sample data and desired results would help here.