Windows Updates Installed On date
I've duplicated and altered one of the report scripts under patching and I cannot seem to find one field in the DB. I thought it would have been the "SOFTWARE.INSTALL_DATE" that would tell me when an update was installed on a particular computer, but the contents do not match what "Add Remove Programs" > "View Installed Updates" shows for an "Installed On" date. Could someone point me in the direction of where to find this field and what table it's in? I've posted my code below if anyone wants to see it. We did find some of the info under Asset_History, but that doesn't tell me if a particular update is installed on the computer.
SELECT MACHINE.NAME, MACHINE.LAST_USER, SOFTWARE.DISPLAY_NAME, SOFTWARE.PUBLISHER, SOFTWARE.INSTALL_DATE
,str_to_date(SOFTWARE.INSTALL_DATE,'%m/%d/%Y') as InstDate
FROM ORG1.SOFTWARE
JOIN MACHINE_SOFTWARE_JT on MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
JOIN MACHINE on MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
WHERE IS_PATCH = 1
and MACHINE.NAME = "NCPC06TVHS"
ORDER BY InstDate DESC
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
8 years ago
The software table isn't machine specific, so there isn't a date in that table that would show when a title was installed on a particular computer. You might be able to use the ASSET_HISTORY table to figure this out, but depending on how long you keep that data it won't be accurate. If you are using the patching module then the tables there should include when patches were installed. Even if you aren't deploying patches with the module you should still be able to run a detect cycle to gather the information.
Comments:
-
The ASSET_HISTORY table does not seem to show ALL of the items installed on a system. For example, I went directly to the machine I used in the MACHINE_NAME field and opened the "View Installed Updates from programs and Features. Scrolled down the list and found KB3074450 and searched in the ASSET_HISTORY, but it was not there. BUT!!!!! if I look under the Inventory for that system, it shows that particular KB installed on that system. So if the software update is showing up in the inventory, why wouldn't KACE be pulling the "Installed On" field from Windows? - joseph.belanger@nichols.edu 8 years ago
-
FYI: the Asset History table has settings for how long records are kept, located in Settings > History > Assets (under "Subscriptions"). So if your AH records are set to expire after a year, you can't report on anything before that. However, changing retention to "Forever" can cause problems of its own. - JasonEgg 8 years ago
-
Two questions -
1. Are there any settings that need to be changed to read the Installed On date from the computer?
2. What table(s) should the data be stored in when Inventory is detected from the computer? We cannot find it anywhere, and you would think this field would be important. - joseph.belanger@nichols.edu 8 years ago-
1. I don't know where Windows gets the information for "Add/Remove Programs" but I do know there is a registry entry for "InstallDate" in HKLM\Software\Microsoft\Windows\CurrentVersion\Uninstall\[[program id/name]]
2. A lot of different places, and KACE might not even poll the computer for information on when software was installed. I agree it seems odd that this information is not easily available, so I suspect this was a deliberate decision to avoid problems caused by erroneous or missing dates.
That being said, I think Chucksteel's idea of using Patching would get you the closest to your desired functionality. - JasonEgg 8 years ago
