How do you add a field from a lookup that doesn't exist in the outer search?

[ragedsparrow]

I have a DHCP search that I filter based on a lookup:

index=DHCP_IDX sourcetype="infoblox:dhcp" signature IN (DHCPACK, DHCPREQUEST) 
| eval unified_mac=if(isnull(src_mac),dest_mac,src_mac) 
| eval unified_ip=if(isnull(src_ip),dest,src_ip) 
| eval unified_host=if(isnull(src_nt_host),if(isnull(dest_nt_host),"Unknown", dest_nt_host),src_nt_host) 
| search 
    [| inputlookup dhcp_lookup
    | eval unified_mac=lookup_mac 
    | eval unified_ip=cidr_ip 
    | eval unified_host=lookup_hostname 
    | fields unified_mac, unified_ip, unified_host] 
| table _time action signature src_ip src_nt_host src_mac dest dest_nt_host dest_mac
| sort -_time

Inside this lookup are 4 columns: cidr_ip, lookup_mac, lookup_hostname, and Notes

Right now, the outer search is being filtered by the first 3 fields. However, I need to find a way to add in the Notes column to the outer search for the results.

An example row for the dhcp_lookup would be:

cidr_ip      |     lookup_mac        |     lookup_hostname  |     Notes
0.0.0.0/0   |  aa:22:33:44:11:55 |              *                       |  Bad Device by MAC

I need to be able to use Wildcards in the MAC and Hostname columns, but I need to somehow add the Notes column as a field into the outer search while still filtering using the lookup.

Any ideas?

[ragedsparrow · Accepted solution]

I think I got what I was needing to happen here. Taking lakshman239, I took another look at the lookup and realized I wasn't able to match with wildcards on the MAC and hostname columns in the lookup. So, I modified the transforms.conf:

[dhcp_lookup]
match_type=  CIDR(cidr_ip), WILDCARD(lookup_mac), WILDCARD(lookup_hostname)

After that, I reqorked the query and realized I could simplify it as well:

index=dhcp_index sourcetype="infoblox:dhcp" signature IN (DHCPACK, DHCPREQUEST) 
| eval unified_mac=if(isnull(src_mac),dest_mac,src_mac) 
| eval unified_ip=if(isnull(src_ip),dest,src_ip) 
| eval unified_host=if(isnull(src_nt_host),if(isnull(dest_nt_host),"Unknown", dest_nt_host),src_nt_host) 
| lookup dhcp_lookup cidr_ip AS unified_ip, lookup_mac AS unified_mac, lookup_hostname AS unified_host OUTPUT Notes 
| where Notes!="OK" AND isnotnull(Notes)
| table _time action signature src_ip src_nt_host src_mac dest dest_nt_host dest_mac Notes 
| sort -_time

This now gives me the Notes column that I was needing as well as filters down the base query results.

[lakshman239]

How about instead of sub-search, you directly use lookup using the unified_* fields and OUPUT the matching records and notes? Is that not an option?

[ragedsparrow]

That makes sense. I had tried it in the past, but let me see what I can figure out.

[Vijeta]

@ragedsparrow Try this-

index=DHCP_IDX sourcetype="infoblox:dhcp" signature IN (DHCPACK, DHCPREQUEST) 
 | eval unified_mac=if(isnull(src_mac),dest_mac,src_mac) 
 | eval unified_ip=if(isnull(src_ip),dest,src_ip) 
 | eval unified_host=if(isnull(src_nt_host),if(isnull(dest_nt_host),"Unknown", dest_nt_host),src_nt_host) 
 | join unified_mac ,unified_ip , unified_host
     [| inputlookup dhcp_lookup
     | eval unified_mac=lookup_mac 
     | eval unified_ip=cidr_ip 
     | eval unified_host=lookup_hostname 
     | fields unified_mac, unified_ip, unified_host, notes] 
 | table _time action signature src_ip src_nt_host src_mac dest dest_nt_host dest_mac notes
 | sort -_time

[ragedsparrow]

Hey Vijeta, This does not work. I get 0 results returned.