Report on recently installed/updated/modified Software
I am trying to find a way to create a report on recently installed/updated/modified software list in our infrastructure. It seems like there is no canned report that can be created by KACE. I prefer to have a reporting within given time period (such as within 7 days from today).
Has anyone successfully create this report?
Has anyone successfully create this report?
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
7 years ago
Here is an example:
SELECT ASSET_HISTORY.ASSET_ID,
ASSET_HISTORY.TIME,
ASSET_HISTORY.VALUE1,
ASSET_HISTORY.VALUE2,
ASSET_HISTORY.USER_TEXT,
MACHINE.NAME
FROM ASSET_HISTORY
JOIN ASSET ON ASSET.ID = ASSET_HISTORY.ASSET_ID
JOIN MACHINE ON ASSET.NAME = MACHINE.BIOS_SERIAL_NUMBER
WHERE CHANGE_TYPE = "Detected"
AND FIELD_NAME = "SOFTWARE"
AND DATE(ASSET_HISTORY.TIME)> DATE_SUB(NOW(), INTERVAL 7 day)
AND MACHINE.NAME LIKE 'lib-ic-%'
Comments:
-
Thank you Chucksteel. Since I am fairly new to this script, can you explain to me how this script works? What do I need to modify? - HAJCP2016 7 years ago
-
Sure.
The K1000 stores historical information in the ASSET_HISTORY table (side note, this data is only retained for a certain period, check your settings to find out how long). The query starts off by selecting columns from that table (ID, TIME, VALUE1, VALUE2, USER_TEXT) and one column from the MACHINE table (NAME).
Since I started with the ASSET_HISTORY table I need to define the relationship with the MACHINE table, that happens by joining first to the ASSET table and then to the MACHINE table.
Our K1000 is configured to match computer assets to inventory based on the serial number. You can check this in your environment in Assets, Asset Types, Computer.
Once the relationships are in place I filter the results from those tables to return only the results from the ASSET_HISTORY table that are software detections (there is a lot of other data in there depending on your settings) and in the past seven days, INTERVAL 7 day. I also limit the results to just computers that begin with lib-ic- (the naming convention for one of my labs).
So things you might want to change:
Columns in the report go after the SELECT and before the FROM.
Date range, change the interval setting.
Machine names, change the lib-ic-%. MySQL uses % as a wildcard.
I hope that helps. - chucksteel 7 years ago
