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?
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.
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.
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?
That makes sense. I had tried it in the past, but let me see what I can figure out.
@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
Hey Vijeta, This does not work. I get 0 results returned.