How to edit my search to filter and only return duplicate DHCP MAC addresses?

[crossap]

Hi,

I am trying to create a search that will output any duplicate mac address for potential MAC spoofing

so far I am able to build my table of MACs

sourcetype=dhcp eventtype="nix-all-logs" | table dhcp_mac dhcp_ip dhcp_hostname

This returns all the MACS with some duplicates appearing, but the issue I am having is the filtering part - I just want see the duplicates.

I have tried the search terms suggested here

http://answers.splunk.com/answers/12239/locate-duplicate-mac-addresses-in-dhcp-logs.html

I have played around with the search term changing (mac) to (dhcp_mac) for example as my field is not called mac (guessing this had to be done)

thanks

Paul

[jeffland · Accepted solution]

For your data, the search should be

... | stats dc(dhcp_mac) as macCount values(dhcp_mac) as mac by dhcp_hostname | search macCount>1

You could also rename your fields:

... | rename dhcp_mac as mac | rename dhcp_hostname as hostname

and then use the exact search provided in the answer you mentioned.

[crossap]

Got it

sourcetype=dhcp eventtype="nix-all-logs" | table dhcp_mac dhcp_ip dhcp_hostname | stats dc(dhcp_ip) as IPCount values(dhcp_ip) as IP by dhcp_mac dhcp_hostname | search IPCount>1

Would this show more than 1 hostname if there were multiple?

[jeffland]

Yes. You are calculating your stats by dhcp_hostname, so every hostname there is will get his own row.

[crossap]

Hi,

thanks for the responses

the 2nd | stats dc(dhcp_mac) as macCount values(dhcp_mac) as mac by dhcp_hostname | search macCount>1 is the closest to what I am looking for but MAC rather than hostname if that makes sense

eg

MAC | IP COUNT | IP | HOSTNAME

I will have a play with the search provided as I am sure I just need to slightly amend to meet my needs

Thank you both for your very fast reply 🙂

[bjoernjensen]

Hi!

How does this work?
sourcetype=dhcp eventtype="nix-all-logs" | table dhcp_mac dhcp_ip dhcp_hostname | stats count by dhcp_mac dhcp_ip | where count > 1