/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: chucksteel 11 years ago
Red Belt
0

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

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ