How to edit my search to find duplicate events?

[JoshuaJohn]

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

[JoshuaJohn]

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

[somesoni2 · Accepted solution]

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]

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

[thiagodede]

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

[JoshuaJohn]

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.