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 4 years ago
Answers (1)
Please log in to answer
Posted by:
chucksteel
4 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 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 4 years ago
-
It is based on the software inventory, so all titles found on the system. - chucksteel 4 years ago
-
Ah ok is it possible to do it so it qureies what was installed on the client? - assad.ali 4 years ago
-
The software inventory is based on what is installed on the client. Sorry, I thought that was clear. - chucksteel 4 years ago
-
Hi,
I tested the report by installing a newsoftware but it returned no results could you advise?
Regards - assad.ali 4 years ago-
Did the new software appear on the device's inventory page? - chucksteel 4 years ago
-
Yes - assad.ali 4 years ago
-
Please post your query. - chucksteel 4 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 4 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 4 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 4 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 4 years ago -
no problem, thanks for your help much appreciated - assad.ali 4 years ago
