Here again your favorite Random Dude.
Today I got something for the SMA, I have been posting a lot for the SDA but not that much for the SMA (still trying to learn it bit by bit). So I was asked to get a report of when a software was installed on my users computers but also bringing the usual. (name, publisher, version, etc...) So after several trial and failure I got it working with this query.
SELECT DISTINCT
ASSET_HISTORY.NAME,
MACHINE.LAST_USER,
ASSET_HISTORY.TIME,
ASSET_HISTORY.VALUE1 AS 'Software Name',
SOFTWARE.PUBLISHER AS Publisher,
ASSET_HISTORY.VALUE2 AS VERSION
FROM ASSET_HISTORY
LEFT JOIN SOFTWARE ON DISPLAY_NAME = VALUE1
LEFT JOIN MACHINE ON MACHINE.NAME = ASSET_HISTORY.NAME
WHERE FIELD_NAME = 'SOFTWARE'
AND CHANGE_TYPE = 'DETECTED'
AND VALUE1 NOT LIKE '%Security Update%'
AND VALUE1 NOT LIKE '%KB%'
AND SOFTWARE.PUBLISHER like '%Notepad%'
AND ASSET_HISTORY.VALUE2 LIKE '%7.9.5%'
AND TIME > DATE_SUB(NOW(), INTERVAL 365 DAY)
You can modify this 3 lines to get other information, like software from one publisher, or between a set amount of days so the SMA is not pulling that much data.
AND SOFTWARE.PUBLISHER like '%Notepad%'
AND ASSET_HISTORY.VALUE2 LIKE '%7.9.5%'
AND TIME > DATE_SUB(NOW(), INTERVAL 365 DAY)
Here is an example on the results will look like after it runs.
I hope this helps someone. If you have any questions or comments put them down there.
Comments