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

[jacqu3sy]

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.

[somesoni2 · Accepted solution]

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

[jacqu3sy]

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

[somesoni2]

It's taking the first 6 character of field dest_mac and appending an asterisk to it. This way the format of value matches the lookup values and you can get the vendor value.

[jacqu3sy]

Thats awesome. Thanks.

[muebel]

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!

[jacqu3sy]

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

Thanks tho.

[gcusello]

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

[somesoni2]

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.

[jacqu3sy]

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.

[gcusello]

Add a column (called e.g. search_mac) to your lookup with unauthorised_mac_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

[jacqu3sy]

That worked perfectly. Thank you.