Solved: How to search a lookup table for results in field ... - Page 2

jacqu3sy · ‎03-06-2017

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 · ‎03-06-2017

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

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

.conf24 | Registration Open!

ICYMI - Check out the latest releases of Splunk Edge Processor

Introducing the 2024 SplunkTrust!