All Apps and Add-ons

Using results from one searchin a second and combining the results

BryanScovill
Explorer

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

Tags (1)
0 Karma

Vijeta
Influencer

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>
0 Karma

BryanScovill
Explorer

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.

0 Karma

BryanScovill
Explorer

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.

0 Karma

Vijeta
Influencer

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

0 Karma

nplamondon
Communicator

Sample data and desired results would help here.

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...