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.
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)
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)
Thank you. This is great!
@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.
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
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.
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?