How to add hostname field to main search results with a subsearch using approximate time field?

[spj2]

I am generating a daily report for all IP addresses that are bypassing internal DNS server.

For e.g.

index=fw_logs earliest=-24h dst_port=53 src_ip!=DNS_Servers  dst_ip!=Intranet_Subnet| eventstats count by src_ip dst_ip  | dedup src_ip | sort -count|table receive_time, src_ip, src_host, dst_ip,count,action

Result of the query:

receive_time                            src_ip            dst_IP    count     action
2014/10/01 13:33:47              10.1.1.1        8.8.8.8    5             Allow

Now, I would like to add another hostname column to the report which is the FQDN for the IP under src_ip. If I was doing it in real time or every couple of hours, I can use the internal dnslookup and it works. But if I am looking at older events a few hours later, in a DHCP environment, IP-Name mapping changes. I have that information available in a different index though.

Here are the log entries from the index dhcp:

10/1/14 1:12:48.000 PM  32,10/01/14,13:12:48,DNS Update Successful,10.1.1.1,cool7234.somecompany.com,,,0,6,,,
10/1/14 1:45:08.000 PM  32,10/01/14,13:45:08,DNS Update Successful,10.1.1.1,cool7234.somecompany.com,,,0,6,,,

I am trying to populate the host part in the daily report above. The problem is I cannot provide exact time to DHCP in a subsearch. The time will be around the receive_time when the event in the report was triggered. How do I get the FQDN from index=dhcp using the IP and time from the first search where index=fw_logs?

[lguinn2 · Accepted solution]

This might work, but it depends. There could be a problem if there are multiple matches for the same src_ip

index=fw_logs earliest=-24h dst_port=53 src_ip!=DNS_Servers  dst_ip!=Intranet_Subnet
| eventstats count by src_ip dst_ip  
| dedup src_ip 
| sort -count
|table receive_time, src_ip, src_host, dst_ip,count,action
| join src_ip type=outer usetime=t earlier=t [ search index=dhcp earliest=-48h "DNS Update Successful" 
                      | fields src_ip FQDN ]

[spj2]

Thanks lguinn! That worked.