We had a great custom report that I got from the site that some one had written to report newly installed software on our systems.  We had this report run every night at 7 to help moniter any unathorized sofware installs during the day.  This was working great and was a wonderful asset tell we upgraded to 5.4.76847. 

Here is the script for the report that was working:

SELECT CONCAT(S.DISPLAY_NAME, ' ', S.DISPLAY_VERSION) AS 'Software',

A.NAME as 'Computer', AH.TIME as 'Installed'

FROM ASSET_HISTORY AH, ASSET A, ASSET_TYPE AST, SOFTWARE S

WHERE AST.ASSET_CATEGORY = "Computer"

and A.ID = AH.ASSET_ID

and AH.DESCRIPTION LIKE CONCAT('%%Found software item ', S.DISPLAY_NAME,

' ', S.DISPLAY_VERSION, '%%')

and DATE(AH.TIME) > DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))

ORDER BY Installed, Computer


This is the out put we are recieving now from the report:

mysql error: [1054: Unknown column 'AH.DESCRIPTION' in 'where clause'] in EXECUTE("SELECT CONCAT(S.DISPLAY_NAME, ' ', S.DISPLAY_VERSION) AS 'Software',

A.NAME as 'Computer', AH.TIME as 'Installed'

FROM ASSET_HISTORY AH, ASSET A, ASSET_TYPE AST, SOFTWARE S

WHERE AST.ASSET_CATEGORY = "Computer"

and A.ID = AH.ASSET_ID

and AH.DESCRIPTION LIKE CONCAT('%%Found software item ', S.DISPLAY_NAME,

' ', S.DISPLAY_VERSION, '%%')

and DATE(AH.TIME) > DATE(DATE_SUB(NOW(), INTERVAL 1 DAY))

ORDER BY Installed, Computer")

 
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

When I ran your query in FlySpeed SQL it indicated a similiar issue as reported above.  

Within the Asset_History table there is no description field.  The field you want is Change_Type and the value you should use is "Detected"

****However - this query is crazy mysql intensive, spiking my mysql thread to 100% CPU utulization.  I suspect that

and AH.DESCRIPTION LIKE CONCAT('%%Found software item ', S.DISPLAY_NAME,

' ', S.DISPLAY_VERSION, '%%')

Is very CPU untensive.  

 

I also have a smiliar report that uses this SQL and does not kill my KaceBox: 

SELECT DISTINCT NAME, VALUE1 AS 'Software Display Name', VALUE2 AS 'Software Version'

FROM ASSET_HISTORY A

WHERE CHANGE_TYPE = 'DETECTED'

AND FIELD_NAME = 'SOFTWARE'

AND TIME > DATE_SUB(NOW(), INTERVAL 1 DAY)

ORDER BY NAME, VALUE1

 

Maybe the above will work for you.  

Answered 08/22/2013 by: Jbr32
Tenth Degree Black Belt

  • That worked flawless thank you very much for the help
  • No problem. The code came from another member of itninja a while back
  • Great report. That will be very useful. Thanks.
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share