topic Re: Creating a correlation search between two different indexes (DHCP and Firewall Data) in Splunk Search

Creating a correlation search between two different indexes (DHCP and Firewall Data)

daryllj — Tue, 23 Mar 2021 18:41:08 GMT

Hi all- we want to get a bit more elegant with correlation searching between two different indexes. There seems to be a lot of different approaches, but ultimately this is what we are trying to do:

1) we have a set of events returned from a firewall index search

EXAMPLE: (index=XXXXXX) level=warning host="XXXXXXXX" category="Malicious Websites" | stats count by srcip

2) we have the record of the IP in question in our DHCP index:

EXAMPLE: index="dhcp" host="XXXXXXXX" | stats count by ip, hostname

What is the most elegant approach to searching so that values from our firewall report are returned using the hostname information that was listed in DHCP?

I assume I would need to use the rename command to ensure srcip and ip match up, and see a lot of different ways to potentially achieve this and could use some direction on which is the simplest path to take (ie: subsearch?)

Desired End Result:

A report that lists firewall data that includes both IP and Hostname at the time of the log, vs what a DNS lookup would provide, preserving and confirming what IP was assigned to what hostname at the time of the firewall log.

Re: Creating a correlation search between two different indexes (DHCP and Firewall Data)

hoaxm3 — Tue, 23 Mar 2021 19:31:10 GMT

Look into the join option here. Append and transaction would work, but I think Join would be the best bet. Example below:

- https://docs.splunk.com/Documentation/Splunk/8.1.3/SearchReference/Join

Re: Creating a correlation search between two different indexes (DHCP and Firewall Data)

daryllj — Tue, 23 Mar 2021 21:36:51 GMT

Thanks for the guidelines here- I played around with a very basic join command and this resulted in the following:

(index=fortfw) level=warning host="XXXXXXXX" category="Malicious Websites" | rename src as ip | join ip [search index="dhcp"] | stats count by hostname, ip

This seemed to do the trick and I now get my stats that include the hostname.....