Custom report not working after upgrading to 5.4.76847
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")
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.
Comments:
-
That worked flawless thank you very much for the help - demacula 10 years ago
-
No problem. The code came from another member of itninja a while back - Jbr32 10 years ago
-
Great report. That will be very useful. Thanks. - rockhead44 10 years ago
