How can I search for results that share the same Mac address?

[shandman]

I think I'm close. Just need a little help. here is my current search
index=windows sourcetype=dhcpsrvlog | stats dc(raw_mac) as macCount values(raw_mac) as mac by dest_nt_host| eventstats count by raw_mac | where count = 2

I'm trying to get results for any 2 systems sharing the same mac address.

[DalJeanis · Accepted solution]

Okay, two things - First, make sure your hosts have standardized names. Many systems have "host" sometimes in lower case, sometimes upper, and sometimes short host and sometimes FQDN. Those are all a single host, so you have to standardize the format of dest_nt_host before the stats command.

Second, upload your csv into a lookup, with the host in a field named host then run a search like this...

 index=windows sourcetype=dhcpsrvlog 
| dedup raw_mac,dest_nt_host
|  something that formats your host
| dedup raw_mac,dest_nt_host
| stats values(dest_nt_host) as host by raw_mac 
| where mvcount(host) > 1
| mvexpand host
| lookup myhosts.csv host OUTPUT host as foundme

Then this gets the ones that WERE in the lookup table...

| where isnotnull(foundme)

...and this gets the ones that were NOT in the lookup table...

| where isnull(foundme)

[shandman]

Thank you. This is great!

[DalJeanis]

@shandman - Glad we were able to help! If that solved your issue, please "accept" the answer so that folks will know the question is closed.

[harsmarvania57]

Hi @shandman,

This query might help you

index=windows sourcetype=dhcpsrvlog | dedup raw_mac,dest_nt_host| stats count(dest_nt_host) AS hostcount, values(dest_nt_host) by raw_mac | where hostcount > 1

Thanks,
Harshil

[shandman]

The query works. For some reason I'm getting over 1000 results. Multiple hosts with the same mac. Weird. Thank you for the response though. I'll keep tinkering.

[shandman]

I also just created a .csv lookup file that is pulling cmdb data with mac addresses to workstation. How can i append this search (above) to include the lookup .csv and verify that hosts match?