Enchance search results with subsearch on different sourcetypes? (DNS src ip & timestamp with DHCP ip & timestamp)

[Neur0mencer]

Hello Splunkers!

For some time I'm trying to figure out how to feed results of a DNS blacklist check versus DHCP logs with respect to the time of event in DNS log and it's counterpart DHCP log.

Let's say I run the following query to get results of my DNS Blacklist hits:

index="msad" sourcetype="msad:nt6:dns" questionname="BLACKLISTED_DOMAINS" source_ip!="8.8.8.8" 
| table _time source_ip 
| dedup source_ip

This gives me a nice table showing the host (by IP) attempting access to blacklisted domain and most recent time that it happened.

Now I wish to use the resulting table as input into a search (DHCP or any other log that can correlate IP to Hostname with Time) that will resolve/correlate the resulting IPs with hostnames at the time of the resulting event.

I can't figure this out. I've tried running a subsearch but to my understanding it accepts only single values as input (thus I can feed it IPs, but I loose the time and the results might indicate different host in a dynamic DHCP enviroment for past events).

Is this possible? How? 🙂

[HiroshiSatoh]

There is time base lookup as a function to associate with DHCP. It is necessary to create a LOOKUP file.

https://docs.splunk.com/Documentation/Splunk/7.1.1/Knowledge/Configureatime-boundedlookup

[Neur0mencer]

I'm not sure I understand - I don't have DHCP data entered as a lookup. Will this create a lookup table based of DHCP logs and later use this lookup to add data into queries?

[HiroshiSatoh]

Periodically create a LOOKUP table from the DHCP log by schedule search etc.
Time base lookup is the most efficient way.