/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Hi, I need help creating a SQL report that shows any software installed on a group of clients in the past 7 days which will out machine name, software installed and the date it was installed?

07/01/2020 278 views

Hi, I need help creating a SQL report that shows any software installed on a group of clients in the past 7 days which will out machine name, software installed and the date it was installed?

1 Comment   [ + ] Show comment

Comments

  • Thanks for this, I will give these a try, will this pick up any softwares installed or just ones that are deployed via KACE?

All Answers

0
SELECT  MACHINE.NAME,
ASSET_HISTORY.TIME, 
ASSET_HISTORY.VALUE1 as 'Software Title',
ASSET_HISTORY.VALUE2 as 'Software Version'
FROM  ASSET_HISTORY
JOIN ASSET ON ASSET.ID = ASSET_HISTORY.ASSET_ID
JOIN MACHINE ON MACHINE.ID = ASSET.MAPPED_ID
JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
WHERE CHANGE_TYPE = "Detected"
AND FIELD_NAME = "SOFTWARE"
AND DATE(ASSET_HISTORY.TIME)> DATE_SUB(NOW(), INTERVAL 7 day)
AND LABEL.NAME = 'User Services'
ORDER BY MACHINE.NAME

Adjust the LABEL.NAME = 'User Services' to match the label applied to the machines you want to include.

If you aren't using a label but the machines match a naming pattern, you can use this instead:

SELECT  MACHINE.NAME,
ASSET_HISTORY.TIME, 
ASSET_HISTORY.VALUE1 as 'Software Title',
ASSET_HISTORY.VALUE2 as 'Software Version'
FROM  ASSET_HISTORY
JOIN ASSET ON ASSET.ID = ASSET_HISTORY.ASSET_ID
JOIN MACHINE ON MACHINE.ID = ASSET.MAPPED_ID
WHERE CHANGE_TYPE = "Detected"
AND FIELD_NAME = "SOFTWARE"
AND DATE(ASSET_HISTORY.TIME)> DATE_SUB(NOW(), INTERVAL 7 day)
AND MACHINE.NAME like 'lib-%'
ORDER BY MACHINE.NAME

Adjust the MACHINE.NAME like 'lib-%' to match the naming pattern you need.


Answered 07/01/2020 by: chucksteel
Red Belt

  • Thanks for this, I will give these a try, will this pick up any softwares installed or just ones that are deployed via KACE?
    • It is based on the software inventory, so all titles found on the system.
      • Ah ok is it possible to do it so it qureies what was installed on the client?
      • The software inventory is based on what is installed on the client. Sorry, I thought that was clear.
  • Hi,

    I tested the report by installing a newsoftware but it returned no results could you advise?

    Regards
    • Did the new software appear on the device's inventory page?
    • Please post your query.
      • SELECT MACHINE.NAME,
        ASSET_HISTORY.TIME,
        ASSET_HISTORY.VALUE1 as 'Software Title',
        ASSET_HISTORY.VALUE2 as 'Software Version'
        FROM ASSET_HISTORY
        JOIN ASSET ON ASSET.ID = ASSET_HISTORY.ASSET_ID
        JOIN MACHINE ON ASSET.NAME = MACHINE.BIOS_SERIAL_NUMBER
        JOIN MACHINE_LABEL_JT on MACHINE_LABEL_JT.MACHINE_ID = MACHINE.ID
        JOIN LABEL on LABEL.ID = MACHINE_LABEL_JT.LABEL_ID
        WHERE CHANGE_TYPE = "Detected"
        AND FIELD_NAME = "SOFTWARE"
        AND DATE(ASSET_HISTORY.TIME)> DATE_SUB(NOW(), INTERVAL 7 day)
        AND LABEL.NAME = 'DomainControllers'
        ORDER BY MACHINE.NAME
      • I updated the original queries to change the join to the MACHINE table. I realized that I was making that join based on the asset name matching the machine's serial number, which may not be how your appliance is configured. This line:
        JOIN MACHINE ON ASSET.NAME = MACHINE.BIOS_SERIAL_NUMBER
        should be changed to:
        JOIN MACHINE ON MACHINE.ID = ASSET.MAPPED_ID
      • hi that change worked thanks, one final thing if possible, would it be easy to filter out windows updates to not? be included
      • The only way to exclude updates would be to use a text match in the title. So you could add a line like:
        AND ASSET_HISTORY.VALUE1 not like '%Update%'
        before the ORDER BY line. That probably wouldn't exclude all updates, but it may also exclude other things, e.g. 'Adobe Updater'.
      • no problem, thanks for your help much appreciated
 
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