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.
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
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
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


Answered 08/12/2014 by: chucksteel
Red Belt

Please log in to comment
0
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))...
Answered 08/12/2014 by: grayematter
Fourth Degree Black Belt

Please log in to comment
Answer this question or Comment on this question for clarity

Share