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