- Splunk Answers
- :
- Using Splunk
- :
- Splunk Search
- :
- Re: How can I search for results that share the sa...
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark Topic
- Subscribe to Topic
- Mute Topic
- Printer Friendly Page
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you. This is great!
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
Threat Hunting Unlocked: How to Uplevel Your Threat Hunting With the PEAK Framework ...
Splunk APM: New Product Features + Community Office Hours Recap!
Index This | Forward, I’m heavy; backward, I’m not. What am I?
