locate duplicate MAC addresses in dhcp logs

[EricPartington]

Should be simple to solve, but i'm drawing a blank.

i have three fields i wnat to look at in dhcp logs mac hostname datetime

i'd like to show the mac address and the hostnames that the dhcp logs contain relating to it. There should be only one mac per hostname but where there isnt i'd like to have alist of all the hostnames that have the duplicate.

how best to search for that?

sourcetype=dhcpd eventtype=dhcpack | top mac 

should give me a count of all macs that show up in the logs i'd like to see the hostnames that refer to that mac (should be 1 to 1 relationship).

thanks

[David]

Assuming you have mac and hostname extracted, I would do the following search:

sourcetype=dhcpd eventtype=dhcpack 
        | table mac hostname
        | dedup mac, hostname
        | mvcombine hostname
        | eventstats distinct_count(hostname) as HostCount by mac 
        | where HostCount > 1

Edit: Beaten to the punch with a better search by Nick. Ain't that always the way.

[sideview · Accepted solution]

<your search> | stats dc(mac) as macCount values(mac) as mac by hostname | search macCount>1

It says for each hostname, give me the number of distinct values of mac and call it 'macCount', and give me the distinct values of mac and call it 'mac'. Coming out of the stats clause the 'mac' field will now be a multivalue field.

If you want to expand the list of offenders back out into a result set that has one row per mac-address, I think this will do it:

<your search> | stats dc(mac) as macCount values(mac) as mac by hostname 
| search macCount>1 | stats count by hostname mac

[EricPartington]

Thanks for the solutions everyone, nice to know there are many ways to solve the problem.