topic Why is our search on Windows DHCP logs using a MAC address lookup table not producing any results? in Splunk Search

Why is our search on Windows DHCP logs using a MAC address lookup table not producing any results?

pjohnson1 — Wed, 12 Aug 2015 03:23:17 GMT

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.

Re: Why is our search on Windows DHCP logs using a MAC address lookup table not producing any results?

acharlieh — Wed, 12 Aug 2015 04:06:40 GMT

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.

Re: Why is our search on Windows DHCP logs using a MAC address lookup table not producing any results?

pjohnson1 — Wed, 12 Aug 2015 09:30:44 GMT

Thanks for responding. Tried that but no joy...

Re: Why is our search on Windows DHCP logs using a MAC address lookup table not producing any results?

richgalloway — Tue, 29 Sep 2020 06:57:14 GMT

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?

Re: Why is our search on Windows DHCP logs using a MAC address lookup table not producing any results?

maciep — Thu, 13 Aug 2015 01:32:23 GMT

Have you tried adding an asterisk to the vendor_mac values in your lookup file? Like this...

000000*,XEROX CORPORATION

Re: Why is our search on Windows DHCP logs using a MAC address lookup table not producing any results?

pjohnson1 — Fri, 14 Aug 2015 00:14:52 GMT

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.

Re: Why is our search on Windows DHCP logs using a MAC address lookup table not producing any results?

icyfeverr — Tue, 29 Sep 2020 06:58:28 GMT

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.

Re: Why is our search on Windows DHCP logs using a MAC address lookup table not producing any results?

esix_splunk — Fri, 14 Aug 2015 02:39:04 GMT

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?

Re: Why is our search on Windows DHCP logs using a MAC address lookup table not producing any results?

pjohnson1 — Wed, 02 Sep 2015 00:51:18 GMT

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.