Splunk Search

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

SplunkTrust
SplunkTrust

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?

1 Solution

SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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.

View solution in original post

0 Karma

SplunkTrust
SplunkTrust

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?

SplunkTrust
SplunkTrust

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

0 Karma

Influencer

@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
0 Karma

SplunkTrust
SplunkTrust

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!