Looking for a report that returns duplicate serial numbers
I am looking to create a report that returns machines with duplicate serial numbers on our K1000. We started a new naming scheme for machines in our organization, and I'd like to weed out those machines that may have two records under two different names.
0 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
That's exactly what I'm looking for: SELECT MACHINE.KUID,MACHINE.BIOS_SERIAL_NUMBER, MACHINE.NAME AS SYSTEM_NAME,IP,MAC,OS_NAME,LAST_SYNC,CLIENT_VERSION FROM (MACHINE ,( select COUNT(ID) CT, MACHINE.BIOS_SERIAL_NUMBER FROM MACHINE GROUP BY MACHINE.BIOS_SERIAL_NUMBER )M2 ) LEFT JOIN ASSET ON MAPPED_ID=MACHINE.ID WHERE M2.CT>1 and MACHINE.BIOS_SERIAL_NUMBER=M2.BIOS_SERIAL_NUMBER ORDER BY BIOS_SERIAL_NUMBER
That's exactly what I'm looking for: SELECT MACHINE.KUID,MACHINE.BIOS_SERIAL_NUMBER, MACHINE.NAME AS SYSTEM_NAME,IP,MAC,OS_NAME,LAST_SYNC,CLIENT_VERSION FROM (MACHINE ,( select COUNT(ID) CT, MACHINE.BIOS_SERIAL_NUMBER FROM MACHINE GROUP BY MACHINE.BIOS_SERIAL_NUMBER )M2 ) LEFT JOIN ASSET ON MAPPED_ID=MACHINE.ID WHERE M2.CT>1 and MACHINE.BIOS_SERIAL_NUMBER=M2.BIOS_SERIAL_NUMBER ORDER BY BIOS_SERIAL_NUMBER
Please log in to answer
Posted by:
chucksteel
9 years ago
This query works for me:
SELECT MACHINE.KUID,MACHINE.BIOS_SERIAL_NUMBER, MACHINE.NAME AS SYSTEM_NAME,IP,MAC,OS_NAME,LAST_SYNC,CLIENT_VERSION
FROM (MACHINE ,( select COUNT(ID) CT, MACHINE.BIOS_SERIAL_NUMBER FROM MACHINE GROUP BY MACHINE.BIOS_SERIAL_NUMBER )M2 ) LEFT JOIN ASSET ON MAPPED_ID=MACHINE.ID
WHERE M2.CT>1 and MACHINE.BIOS_SERIAL_NUMBER=M2.BIOS_SERIAL_NUMBER
ORDER BY BIOS_SERIAL_NUMBER
Posted by:
grayematter
9 years ago
If you need additional fields, you can create a report that pulls the desired fields from the Inventory and Asset tables with the report wizard, be sure to include "BIOS Serial Number" under "Manufacturer and BIOS Info" section of fields to display. Then edit the SQL to add the criteria for duplicates. This is what we have as part of the WHERE clause in our report.
...WHERE (BIOS_SERIAL_NUMBER in (select b.BIOS_SERIAL_NUMBER from MACHINE b group by b.BIOS_SERIAL_NUMBER having count(b.BIOS_SERIAL_NUMBER) > 1))...
