/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Report on recently installed/updated/modified Software

06/12/2017 930 views
    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?
0 Comments   [ + ] Show comments

Comments


All Answers

1
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-%'

Answered 06/13/2017 by: chucksteel
Red Belt

  • 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?
    • 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.
    • This content is currently hidden from public view.
      Reason: Spam For more information, visit our FAQ's.
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ