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!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...