Splunk Search
Highlighted

How to search a lookup table for results in field 1, then if positive, return both field 1 and field 2?

Path Finder

Hi, is there a way (I'm sure there is, I'm just not seeing it), whereby I can search a lookup table for results in field 1, then
if positive return both field 1 and field 2.

For example, the following csv;

unauthorised_mac_address, vendor
FCE998*,Apple
FCD848*,Apple
3C5AB4*,Google
B827EB*,RaspberryPi

Search similar to as follows with the vendor field then populated via the lookup;

index=windows sourcetype=Dhcp
[ inputlookup UnauthorisedDevices.csv | fields unauthorised_mac_address | rename unauthorised_mac_address as dest_mac]
| dedup dest_mac
| table Date, Time, dest_mac, vendor 

I have tried playing with OUTPUT, but couldn't get this to work, possibly because the lookup contains effectively a wildcard?

Thanks in advance.

0 Karma
Highlighted

Re: How to search a lookup table for results in field 1, then if positive, return both field 1 and field 2?

Legend

Hi jacqu3sy,
you have to call twice your lookup:

index=windows sourcetype=Dhcp
[ inputlookup UnauthorisedDevices.csv | fields unauthorised_mac_address | rename unauthorised_mac_address as dest_mac]
| dedup dest_mac
| lookup UnauthorisedDevices.csv  unauthorised_mac_address AS dest_mac OUTPUT vendor
| table Date, Time, dest_mac, vendor

Bye.
Giuseppe

0 Karma
Highlighted

Re: How to search a lookup table for results in field 1, then if positive, return both field 1 and field 2?

SplunkTrust
SplunkTrust

The first usage of your lookup using inputlookup command is working as filter, and reducing the events only for specified mac addresses. You would need to use lookup command to enrich your data with additional fields from lookup.

0 Karma
Highlighted

Re: How to search a lookup table for results in field 1, then if positive, return both field 1 and field 2?

Path Finder

I tried this but it just error'd. That said, I dont think this will work because the lookup table contains what is effectively a wildcard i.e. FCE998* for the vendor identification element of a MAC address.

Thanks tho.

0 Karma
Highlighted

Re: How to search a lookup table for results in field 1, then if positive, return both field 1 and field 2?

Legend

Add a column (called e.g. searchmac) to your lookup with unauthorisedmac_address without wildcard (the first 6 characters) and modify your search like this

index=windows sourcetype=Dhcp
 [ inputlookup UnauthorisedDevices.csv | fields unauthorised_mac_address | rename unauthorised_mac_address as dest_mac]
 | dedup dest_mac
 | eval search_mac=substr(dest_mac,1,6)
 | lookup UnauthorisedDevices.csv  search_mac OUTPUT vendor
 | table Date, Time, dest_mac, vendor

Bye.
Giuseppe

0 Karma
Highlighted

Re: How to search a lookup table for results in field 1, then if positive, return both field 1 and field 2?

Path Finder

That worked perfectly. Thank you.

0 Karma
Highlighted

Re: How to search a lookup table for results in field 1, then if positive, return both field 1 and field 2?

SplunkTrust
SplunkTrust

Hi jacqu3sy, checkout out the lookup tables section of the transforms.conf spec. In particular, for this lookup definition you'll want to set match_type = WILDCARD to tell splunk that lookup commands that are run against it should consider the asterisk a wildcard. This will allow you to compare the dest_mac in the events against the wildcarded unauthorised field in the lookup, and OUTPUT the vendor.

See: http://docs.splunk.com/Documentation/Splunk/6.5.2/admin/transformsconf#Lookup_tables

Please let me know if this answers your question!

0 Karma
Highlighted

Re: How to search a lookup table for results in field 1, then if positive, return both field 1 and field 2?

Path Finder

Hi, I dont have access to the transforms.conf file so was trying to do this without making any changes there.

Thanks tho.

0 Karma
Highlighted

Re: How to search a lookup table for results in field 1, then if positive, return both field 1 and field 2?

SplunkTrust
SplunkTrust

You can try this dirty workaround.

index=windows sourcetype=Dhcp
 [ inputlookup UnauthorisedDevices.csv | fields unauthorised_mac_address | rename unauthorised_mac_address as dest_mac]
 | dedup dest_mac | eval temp_dest_mac=substr(dest_mac,1,6)."*".
 | lookup UnauthorisedDevices.csv  unauthorised_mac_address AS temp_dest_mac OUTPUT vendor
 | table Date, Time, dest_mac, vendor

View solution in original post

Highlighted

Re: How to search a lookup table for results in field 1, then if positive, return both field 1 and field 2?

Path Finder

It may be dirty, but it worked like an absolute charm. I think I get it, but would you mind confirming what the eval tempdestmac=substr(dest_mac,1,6)."*" is doing though?

0 Karma