/build/static/layout/Breadcrumb_cap_w.png

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?

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
  • Thanks for this, I will give these a try, will this pick up any softwares installed or just ones that are deployed via KACE? - assad.ali 3 years ago

Answers (1)

Posted by: chucksteel 3 years ago
Red Belt
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.



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? - assad.ali 3 years ago
    • It is based on the software inventory, so all titles found on the system. - chucksteel 3 years ago
      • Ah ok is it possible to do it so it qureies what was installed on the client? - assad.ali 3 years ago
      • The software inventory is based on what is installed on the client. Sorry, I thought that was clear. - chucksteel 3 years ago
  • Hi,

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

    Regards - assad.ali 3 years ago
    • Did the new software appear on the device's inventory page? - chucksteel 3 years ago
    • Please post your query. - chucksteel 3 years ago
      • 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 - assad.ali 3 years ago
      • 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 - chucksteel 3 years ago
      • hi that change worked thanks, one final thing if possible, would it be easy to filter out windows updates to not? be included - assad.ali 3 years ago
      • 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'. - chucksteel 3 years ago
      • no problem, thanks for your help much appreciated - assad.ali 3 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

View more:

Share

 
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