Report that shows uninstalls but not updates?
I wrote the following SQL script to show me the un-installs in my system:
SELECT MACHINE.USER_FULLNAME, MACHINE.User, ASSET_HISTORY.NAME, ASSET_HISTORY.TIME, ASSET_HISTORY.VALUE1 as Software, ASSET_HISTORY.VALUE2 as Version, ASSET_HISTORY.CHANGE_TYPE
FROM MACHINE JOIN ASSET_HISTORY ON MACHINE.Name =ASSET_HISTORY.NAME
WHERE FIELD_NAME = 'SOFTWARE'
AND CHANGE_TYPE = 'No longer detected'
AND TIME > DATE_SUB(CURDATE(), INTERVAL 7 DAY)
ORDER BY ASSET_HISTORY.VALUE1 ASC;
but the problem with that is that most of the software that appears there is software that was updated, even minor updates. Any idea how to modify it so it doesen't show updates?
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
SMal.tmcc
5 years ago
create a CIR and report on that
ShellCommandTextReturn(powershell Get-eventlog -logname application -InstanceID 1034|select Timegenerated,message)
or
ShellCommandTextReturn(powershell Get-eventlog -logname application -InstanceID 1034|select Timegenerated,message|format-list)
Comments:
-
How can I report on CIRs ? Which table are they under? I don't see them in the MACHINE table.... - marcoeangeli 5 years ago
-
you can use the wizard to create the report and select the CIR on at the bottom of page 2
if you want to do it in SQL here is a example for from my sma that reads a cir into a report
SELECT MACHINE.NAME AS SYSTEM_NAME, (SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE, '>', '>'), '<', '<'), '&', '&'), ''', '\''), '"', '"') FROM MACHINE_CUSTOM_INVENTORY WHERE MACHINE_CUSTOM_INVENTORY.ID=MACHINE.ID AND MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID=28837) AS MACHINE_CUSTOM_INVENTORY_0_28837 FROM MACHINE ORDER BY SYSTEM_NAME - SMal.tmcc 5 years ago -
Note: CIR's run with the inventory cycle, so if you create one it will not populate until inventory runs.
You can also do a Null filter to see which devices did not get the command to run the CIR yet.
I usually filter "if the CIR is null" to not get those machines the inventory has not yet run on. If you use "CIR is null" you will see which ones that it has not run on yet. - SMal.tmcc 5 years ago
