SubSearch Based on initial results

[Kieffer87]

I have a single search that allows users to enter an IP address and return the workstation hostname that was associated to the IP address during a specific time frame in DHCP. The search then display's a table with the time, Source Hostname and Source Mac address, all pulled from the infoblox index. I want to add a subsearch that then searches index=wineventlog and returns the Logon_Account and uses the src_hostname field as the common field to match on. I was able to do this with a join but kept running into the 50,000 line limit causing the wineventlog results to show up sporadically.

What are my options here? I've tried using chart but searching the entire infoblox and wineventlog indexes and then filtering down didn't seem to work well as they are too high volume.

High level:
Search A: index=infoblox sourcetype="infoblox:dhcp" src_ip=10.10.10.10 dhcp_type=DHCPREQUEST
| dedup src_hostname
| eval newTime=strftime(_time, "%m/%d/%y %I:%M:%S:%p")
| table newTime,src_hostname,src_mac
| rename newTime AS "Last DHCP Request",src_hostname AS "Source Hostname",src_mac AS "Source MAC Address"

Search B: use the src_hostname field found in Search A and search index=wineventlog src_hostname=$src_hostname$ and output the Logon_User field from the wineventlog index to the final table.

[jplumsdaine22 · Accepted solution]

Run the smaller search first as a subsearch, then run it again as a join. Like this:

index=wineventlog [search index=infoblox sourcetype="infoblox:dhcp" src_ip=10.10.10.10 dhcp_type=DHCPREQUEST 
| fields src_hostname] 
| stats latest("Logon_User") as "Logon User" by src_hostname 
| join src_hostname [
          search index=infoblox sourcetype="infoblox:dhcp" src_ip=10.10.10.10 dhcp_type=DHCPREQUEST
          | stats latest(_time) as _time latest(src_mac) as "Source MAC Address" by src_hostname
           ]
| eval "Last DHCP Request"=strftime(_time, "%m/%d/%y %I:%M:%S:%p"),"Source Hostname"=src_hostname
| fields - _time src_hostname

Your mileage may vary depending on the amount of results you're expecting back from the infoblox. Filtering the wineventlog by EventId would be a good idea as well.

[Kieffer87]

This worked wonderfully. Thank you for your help.

[jplumsdaine22]

If src_hostname is common to both indexes you can do this in one search (I also recommend using stats latest() instead of dedup)

 index=wineventlog OR (index=infoblox sourcetype="infoblox:dhcp" src_ip=10.10.10.10 dhcp_type=DHCPREQUEST) 
| stats latest("Logon_User") as "Logon User" latest(_time) as _time latest(src_mac) as "Source MAC Address" by src_hostname
| eval "Last DHCP Request"=strftime(_time, "%m/%d/%y %I:%M:%S:%p"),"Source Hostname"=src_hostname
| fields - _time src_hostname

[Kieffer87]

I'm not sure this is what I'm trying to achieve. I want to get the value of src_hostname from the index=infoblox search and then pull in the logon_user field from wineventlog where an event matches src_hostname.

The search posted returns all wineventlog events AND infoblox events where src_ip is 10.10.10.10. I was hoping to not have to pull all wineventlogs and then match but rather only search wineventlog for src_hostname as this is a very busy index

[jplumsdaine22]

For sure it will depend on the timerange of your search - a wineventlog index can get very large.

And yuo're right - in my search you'll get ALL LogonUsers. You can filter that out with

| where  NOT isnull(src_mac)

I'll post another answer that might be of more use to you if you have a very large event set