Splunk Search

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

ragedsparrow
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

ragedsparrow
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

ragedsparrow
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.

0 Karma

lakshman239
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?

ragedsparrow
SplunkTrust
SplunkTrust

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

0 Karma

Vijeta
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

ragedsparrow
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...