Splunk Search
Highlighted

How to edit my search to find duplicate events?

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
Highlighted

Re: How to edit my search to find duplicate events?

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 Macaddress like this search you did and then make a JOIN with the Report.csv to restore the values for the MacAddress selected with duplicated values

0 Karma
Highlighted

Re: How to edit my search to find duplicate events?

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
Highlighted

Re: How to edit my search to find duplicate events?

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

Highlighted

Re: How to edit my search to find duplicate events?

Contributor

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

0 Karma
Highlighted

Re: How to edit my search to find duplicate events?

Contributor

|inputlookup Report.csv
| rename uwifimacaddress as MacAddress
| eventstats count as dupMac by MacAddress
| where dupMac>1
| table Mac
Address serialnumber location.unumber
| sort Mac_Address - 0

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

0 Karma