Splunk Search

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

shandman
Path Finder

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.

0 Karma
1 Solution

DalJeanis
Legend

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)

View solution in original post

DalJeanis
Legend

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
Path Finder

Thank you. This is great!

DalJeanis
Legend

@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.

0 Karma

harsmarvania57
Ultra Champion

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
Path Finder

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.

0 Karma

shandman
Path Finder

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?

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...