Report - Duplicate machines in inventory
When I try to create the 2nd report mentioned in this article, I get a mysql error "mysql error: [1054: Unknown column 'DESCRIPTION' in 'field list'] in EXECUTE( ...". I was hoping someone could help me to fix it so I can use it.
http://www.kace.com/support/resources/kb/article/understanding-and-dealing-with-duplicate-machines-in-inventory
select MACHINE.ID AS MACHINE_RECORD, MACHINE.NAME as CURRENTLY_REGISTERED_TO, MACHINE.KUID AS CONTENDED_KUID, ASSET_HISTORY.TIME AS TIME_OF_CHANGE, CASE WHEN LOCATE('IP Changed from',DESCRIPTION)>0 AND LOCATE('Name Changed from',LEFT(DESCRIPTION,30))>0 THEN '1. Mac, IP and Name' WHEN LOCATE('IP Changed from',DESCRIPTION)>0 THEN '2. IP and MAC Only' WHEN LOCATE('Name Changed from',LEFT(DESCRIPTION,30))>0 THEN '3. Name and Mac Only' ELSE '4. Mac Only' END CHANGE_DETECTED, IF(LOCATE('IP Changed from',DESCRIPTION)>0, SUBSTRING(DESCRIPTION,(LOCATE('IP Changed from',DESCRIPTION)+17),17) ,'no change detected') OLD_IP, IF(LOCATE('Name Changed from',LEFT(DESCRIPTION,30))>0,SUBSTRING(DESCRIPTION,(LOCATE('Name Changed from',DESCRIPTION)+19),17) ,'no change detected') OLD_NAME, SUBSTRING(DESCRIPTION,(LOCATE('Mac Changed from',DESCRIPTION)+18),17) OLD_MAC, DESCRIPTION FROM ASSET_HISTORY,ASSET, MACHINE WHERE ASSET.ID=ASSET_HISTORY.ASSET_ID and ASSET.MAPPED_ID=MACHINE.ID and ASSET.ASSET_TYPE_ID=5 /*and DESCRIPTION like '%IP Changed%'*/ and DESCRIPTION like '%Mac Changed%'/**/ GROUP BY OLD_MAC ORDER BY CHANGE_DETECTED, MACHINE_RECORD,OLD_IP,OLD_NAME,OLD_MAC
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
11 years ago
It looks like this article hasn't been updated to reflect the changes to the ASSET_HISTORY table that were implemented with 5.4. That table no longer includes a description field.
Comments:
-
Well it was worth a shot.
Anyone have something similar they would not mind posting, I did find a couple but nothing specific to duplicate entries with same mac address (different computer names). - erush 11 years ago-
Here's a simple report for duplicate MAC addresses:
SELECT MACHINE.KUID,MACHINE.NAME AS SYSTEM_NAME,MACHINE.IP,MACHINE.MAC,MACHINE.OS_NAME,MACHINE.LAST_SYNC
FROM (MACHINE ,( select COUNT(ID) CT, MACHINE.MAC FROM MACHINE GROUP BY MACHINE.MAC )M2 )
WHERE M2.CT>1 and MACHINE.MAC=M2.MAC AND MACHINE.MAC != ""
ORDER BY MACHINE.MAC - chucksteel 11 years ago