Splunk Search

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

pjohnson1
Path Finder

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.

0 Karma
1 Solution

icyfeverr
Path Finder

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.

View solution in original post

icyfeverr
Path Finder

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.

pjohnson1
Path Finder

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.

0 Karma

maciep
Champion

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

000000*,XEROX CORPORATION

richgalloway
SplunkTrust
SplunkTrust

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?

---
If this reply helps you, Karma would be appreciated.

pjohnson1
Path Finder

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.

0 Karma

acharlieh
Influencer

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.

pjohnson1
Path Finder

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

0 Karma

esix_splunk
Splunk Employee
Splunk Employee

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?

Get Updates on the Splunk Community!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...