Kace reports: Specific software installed within a date range
So being an utter novice to scripting, and still pretty new at KACE in general, I'd like to setup a report that will tell me the system name and logged in user of any system with a specific piece of software. Not to difficult, I can do that part no problem. What I need now is the same report to show me the install date of said piece of software. That's the part I just can't sort out. It doesn't seem there is any reference material for Kace reporting where I could glean this information from. Any ideas?
So far:
SELECT MACHINE.NAME AS SYSTEM_NAME,USER_LOGGED,GROUP_CONCAT(DISTINCT SOFTWARE.DISPLAY_NAME SEPARATOR '\n') AS SOFTWARE_DISPLAY_NAME_GROUPED FROM MACHINE LEFT JOIN MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.MACHINE_ID = MACHINE.ID) LEFT JOIN SOFTWARE ON (SOFTWARE.ID = MACHINE_SOFTWARE_JT.SOFTWARE_ID) WHERE (SOFTWARE.DISPLAY_NAME like '%specificsoftwaretitle01%') GROUP BY MACHINE.ID ORDER BY SYSTEM_NAME,USER_LOGGED
Answers (2)
The registry has an InstallDate value in the Unistall key for the product. You an use a custom inventory for that specific software if you know the full path to the key. For example, for the Dell Kace Agent 5.3.53177, I would use the following custom inventory rule:
RegistryValueReturn(HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\{C80E28A9-0620-48D7-A015-099636FA6FCB}, InstallDate, NUMBER)
On 64 bit Windows this will read from the 32 bit section of the registry (HKLM\Software\Wow6432Node). If you need it to read from the 64 bit version of the registry, use HKEY_LOCAL_MACHINE64, which will still read from the 32 bit registry on 32 bit machines.
All of the ones I have seen so far are in the format YYYYMMDD, if you find one that contains non-numeric values then you will need to change the last argument in the custom inventory rule from NUMBER to TEXT.
Also, different versions of the same software may have different paths to the Uninstall key. For MSI based installs, they key is based on the MSI Product Code, so if a newer version still has the same Product Code, it will be in the same spot.
It doesn't look like Kace inventories the install date. Unless I'm just overlooking it in one of the tables. There's the date created and modified which appears to be when it was created in Kace. There's also an INSTALL_DATE, but that doesn't seem to match actual installed date. If you can find what table that info resides in I can help you create something.
