We have created a MAC lookup table, but we get no hits in our searches.
Our aim is to lookup the Manufacturer's name from MAC addresses in the Windows DHCP logs.
Lookup Table
[root@lab ~]# head /opt/splunk/etc/system/lookups/ieee-mac-oui.csv
Vendor_MAC,Manufacturer
000000,XEROX CORPORATION
000001,XEROX CORPORATION
000002,XEROX CORPORATION
000003,XEROX CORPORATION
Search
index=windhcp | lookup ieee-mac-oui.csv Vendor_MAC OUTPUT Manufacturer | table hostname, mac, src_ip, Manufacturer
Since the lookup table consists of the the first 6 digits of the MAC address, we specified match_type = WILDCARD in transforms.conf.
transforms.conf
[ieee-mac-oui]
filename = ieee-mac-oui.csv
match_type = WILDCARD
As we were not looking for an exact match, only the first 6 digits of the MAC address.
couple of things:
First, acharlieh is correct, you will want to have the transforms.conf file setup with the following:
[ieee-mac-oui]
filename = ieee-mac-oui.csv
match_type = WILDCARD(Vendor_MAC)
The next part referenced by mcaiep is correct, you will want the values in your lookup table stored with the '' at the end of the values (ex. below):
Vendor_MAC,Manufacturer
000000,XEROX CORPORATION
000001*,XEROX CORPORATION
000002*,XEROX CORPORATION
000003*,XEROX CORPORATION
Now, the last part is to reference the stanza name (ieee-mac-oui) in your lookup.
Current Search:
index=windhcp | lookup ieee-mac-oui.csv Vendor_MAC OUTPUT Manufacturer | table hostname, mac, src_ip, Manufacturer
Corrected Search using Stanza:
index=windhcp | lookup ieee-mac-oui Vendor_MAC OUTPUT Manufacturer | table hostname, mac, src_ip, Manufacturer
Notice the slight difference above, this tells the search to use the stanza in the transforms.conf file instead of just referencing the csv directly without the wildcards.
couple of things:
First, acharlieh is correct, you will want to have the transforms.conf file setup with the following:
[ieee-mac-oui]
filename = ieee-mac-oui.csv
match_type = WILDCARD(Vendor_MAC)
The next part referenced by mcaiep is correct, you will want the values in your lookup table stored with the '' at the end of the values (ex. below):
Vendor_MAC,Manufacturer
000000,XEROX CORPORATION
000001*,XEROX CORPORATION
000002*,XEROX CORPORATION
000003*,XEROX CORPORATION
Now, the last part is to reference the stanza name (ieee-mac-oui) in your lookup.
Current Search:
index=windhcp | lookup ieee-mac-oui.csv Vendor_MAC OUTPUT Manufacturer | table hostname, mac, src_ip, Manufacturer
Corrected Search using Stanza:
index=windhcp | lookup ieee-mac-oui Vendor_MAC OUTPUT Manufacturer | table hostname, mac, src_ip, Manufacturer
Notice the slight difference above, this tells the search to use the stanza in the transforms.conf file instead of just referencing the csv directly without the wildcards.
I've finally got round to re-test this. Thanks for the comment.
I have updated the transforms and .csv file but the lookup is still not happening. The query completes but no loookups in the Manufacturers column.
Transforms
[splunk@lab local]$ more transforms.conf
[ieee-mac-oui]
filename = ieee-mac-oui.csv
match_type = WILDCARD(Vendor_MAC)
ieee-mac-oui.csv
[splunk@lab lookups]$ head ieee-mac-oui.csv
Vendor_MAC,Manufacturer
000000,XEROX CORPORATION
000001*,XEROX CORPORATION
000002*,XEROX CORPORATION
000003*,XEROX CORPORATION
000004*,XEROX CORPORATION
000005*,XEROX CORPORATION
000006*,XEROX CORPORATION
000007*,XEROX CORPORATION
000008*,XEROX CORPORATION
Query
The MAC address field is mac
index=windhcp | lookup ieee-mac-oui Vendor_MAC as mac OUTPUT Manufacturer | table hostname, mac, src_ip, Manufacturer
This query now works but some entries does not show the Manufacturer. Will need to double-check the ieee-mac-oui.csv. Thanks for all the help. I will mark this as answered.
Have you tried adding an asterisk to the vendor_mac values in your lookup file? Like this...
000000*,XEROX CORPORATION
Could there be a typo in your posting? The lookup command references a Vendor_MAC field, but the table command uses 'mac'. I would expect both commands to use the same field. Perhaps the lookup command should be lookup ieee-mac-oui.csv Vendor_MAC AS mac OUTPUT Manufacturer
?
Do you have a props.conf entry to go with your transforms.conf stanza?
Tried this but not hits under Manufacturer...
index=windhcp | lookup ieee-mac-oui.csv Vendor_MAC AS mac OUTPUT Manufacturer | dedup mac | table hostname, mac, src_ip, Manufacturer
Apparently, no entry for this ieee-mac-oui lookup in props.conf. Is one needed, as there isnt one for another lookup which we have (but that lookup only has 1 field).
Thanks.
If I'm reading the transforms doc correctly, I'd think you want match_type = WILDCARD(Vendor_MAC)
but I'll admit I haven't played with such so don't know if this is correct / the only trouble.
Thanks for responding. Tried that but no joy...
You do need to specify the WILDCARD(Vendor_MAC) in transforms.conf. This is required for wildcard searches to match correctly.
Readd this and restart. Can you also provide an example of your dataset?