Splunk Search

How to edit my search to find duplicate events?

JoshuaJohn
Contributor

I want to be able to see all duplicate macs with their respective location and store. There are duplicate macs with different values for location and store fields, but my search is only giving me the macs listed 1 time.

| inputlookup Report.csv
| rename u_wifi_mac_address as Mac_Address 
| stats count as dupMac by Mac_Address 
| where dupMac>1
| table location.u_number serial_number Mac_Address
0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try like this

| inputlookup Report.csv
 | eventstats count as dupMac by u_wifi_mac_address 
 | where dupMac>1 
 | rename u_wifi_mac_address as Mac_Address 
 | table location.u_number serial_number Mac_Address

OR

| inputlookup Report.csv
 | rename u_wifi_mac_address as Mac_Address 
 | stats values("location.u_number") as "location.u_number" values(serial_number) as serial_number by u_wifi_mac_address 
 | where mvcount('location.u_number')>1 OR mvcount(serial_number)>1
 | rename u_wifi_mac_address as Mac_Address 
 | table location.u_number serial_number Mac_Address

View solution in original post

JoshuaJohn
Contributor

|inputlookup Report.csv
| rename u_wifi_mac_address as Mac_Address
| eventstats count as dupMac by Mac_Address
| where dupMac>1
| table Mac_Address serial_number location.u_number
| sort Mac_Address - 0

This did the job for me, eventstats instead of stats.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try like this

| inputlookup Report.csv
 | eventstats count as dupMac by u_wifi_mac_address 
 | where dupMac>1 
 | rename u_wifi_mac_address as Mac_Address 
 | table location.u_number serial_number Mac_Address

OR

| inputlookup Report.csv
 | rename u_wifi_mac_address as Mac_Address 
 | stats values("location.u_number") as "location.u_number" values(serial_number) as serial_number by u_wifi_mac_address 
 | where mvcount('location.u_number')>1 OR mvcount(serial_number)>1
 | rename u_wifi_mac_address as Mac_Address 
 | table location.u_number serial_number Mac_Address

JoshuaJohn
Contributor

You posted it right after I got it, but exact same answer so ill accept yours! Thank you.

0 Karma

thiagodede
Explorer

Your search are listing all the macs that have duplicated, but since you used the "stats" they are not looking anymore to events, but to statistical values, and your "group" clause on the stats have only the Mac_Address.

You can first recover the Mac_address like this search you did and then make a JOIN with the Report.csv to restore the values for the Mac_Address selected with duplicated values

0 Karma

JoshuaJohn
Contributor

I take it I am still doing something wrong:

| inputlookup Report.csv 
| rename u_wifi_mac_address as Mac_Address 
| stats count as dupMac by Mac_Address 
| where dupMac>1
| join
    [| inputlookup Report.csv 
    | rename u_wifi_mac_address as Mac_Address
    | fields Mac_Address serial_number location.u_number]

So this gives me macs, the dupMac #, serial for one of the duplicates and a location for one of the duplicates.

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...