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

Comments

Please log in to comment

Answers

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. 

Answered 04/05/2013 by: chucksteel
Red Belt

  • 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).
    • 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
Please log in to comment
Answer this question or Comment on this question for clarity

Share