How to get list of installed SD Packages per machine using SQL Query ?

Document ID : KB000098940
Last Modified Date : 31/05/2018
Show Technical Document Details
Introduction:

Using a SQL Query how to get the list of Installed SD Packages on the computers ?

This list could be created using DSM Reporter but how to get it using a SQL Query ?

 
Instructions:

Following SQL Query returns the list of Installed SD Packages on the computers :
 
use mdb

SELECT M.agent_name [Computer Name], R.itemname [Package Name], R.itemversion [Package Version], P.itemname [Procedure Name], 
dateadd(ss, completiontime+ datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) [Install Time]
FROM usd_applic A, usd_actproc P, usd_rsw R, ca_agent M
WHERE A.actproc=P.objectid and P.rsw=R.objectid and A.target=M.object_uuid and A.uninstallstate<>2 and A.status=9 and P.task<>1
ORDER BY 1, [Install time]

Example :

User-added image

The query returns 5 columns :

Computer Name
Package Name
Package Version
Procedure Name
Installation Date/Time