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

[daryllj]

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.

[daryllj]

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.....

[hoaxm3]

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

| makeresults 
| eval ip = "10.0.0.0" 
| rename ip as src_ip 
| stats count by src_ip 
| eval event="list" 
| join src_ip 
    [| makeresults 
    | eval src_ip = "10.0.0.0", 
        hostname = "desktop",
        event = "append"]