Is there a way to get list of software packages installed using SQL command line.

Products

• CA Client Automation - IT Client Manager

Issue/Introduction

Is there a way to get list of software packages installed using SQL command line. 

Environment

Client Automation - 14.0, 14.5

Resolution

sqlcmd -S SQLServerName\InstanceName -d mdb -E -Q " SET NOCOUNT ON ; 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 LEFT JOIN usd_actproc P ON A.actproc=P.objectid LEFT JOIN usd_rsw R ON P.rsw=R.objectid LEFT JOIN ca_agent M ON A.target=M.object_uuid WHERE A.installation=a.objectid and A.uninstallstate<>2 and P.itemname<>'delivery proc' and A.status=9 and M.agent_name='AGENT_NAME' ORDER BY [Computer Name], [Install time]" -h-1 -W -s;

Replace  -S SQLServerName\InstanceName by the name and instance of SQL Server
Replace M.agent_name='AGENT_NAME' by the name of computer we want the list
Replace -E by -U ca_itrm -P NOT_changedR11 if logged user has not enough right on SQL Server

This query displays the list like this :

Computername;packagename;packageversion;procedurename;install datetime
AGENT_NAME;CA DSM Agent + Data Transport plugin;14.5.0.153;Install;2021-10-08 09:29:00.000
AGENT_NAME;CA DSM Agent + Remote Control plugin (English only Edition);14.5.0.153;Centrally Managed Complete Agent;2021-10-08 09:29:00.000
AGENT_NAME;CA DSM Manager;14.5.0.153;Detect;2021-10-08 09:29:00.000
AGENT_NAME;CA DSM Agent + Software Delivery plugin (English only Edition);14.5.0.153;Install;2021-10-08 09:29:00.000
AGENT_NAME;CA DSM Agent + Asset Management plugin (English only Edition);14.5.0.153;Install;2021-10-08 09:29:00.000
AGENT_NAME;CA DSM Agent + Basic Inventory plugin (English only Edition);14.5.0.153;Install;2021-10-08 09:29:00.000
AGENT_NAME;CA DSM Explorer;14.5.0.153;* Find SW;2021-10-11 12:34:00.000
AGENT_NAME;CA DSM Scalability Server;14.5.0.153;* Find SW;2021-10-11 12:34:00.000
AGENT_NAME;TEST1 (SXP);1.0/00;Install Package;2021-10-11 15:26:27.000
AGENT_NAME;7-Zip 19.00;19.00.00.0;Local Install;2021-10-13 12:01:55.000

; is the column separator but it could be changed with he parameter -s

Without computername column and | as separator : 

sqlcmd -S SQLServerName\InstanceName -d mdb -E -Q " SET NOCOUNT ON ; SELECT 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 LEFT JOIN usd_actproc P ON A.actproc=P.objectid LEFT JOIN usd_rsw R ON P.rsw=R.objectid LEFT JOIN ca_agent M ON A.target=M.object_uuid WHERE A.installation=a.objectid and A.uninstallstate<>2 and P.itemname<>'delivery proc' and A.status=9 and M.agent_name='AGENT_NAME' ORDER BY [Install time]" -h-1 -W -s^|

CA DSM Agent + Data Transport plugin|14.5.0.153|Install|2021-10-08 09:29:00.000
CA DSM Agent + Remote Control plugin (English only Edition)|14.5.0.153|Centrally Managed Complete Agent|2021-10-08 09:29:00.000
CA DSM Manager|14.5.0.153|Detect|2021-10-08 09:29:00.000
CA DSM Agent + Software Delivery plugin (English only Edition)|14.5.0.153|Install|2021-10-08 09:29:00.000
CA DSM Agent + Asset Management plugin (English only Edition)|14.5.0.153|Install|2021-10-08 09:29:00.000
CA DSM Agent + Basic Inventory plugin (English only Edition)|14.5.0.153|Install|2021-10-08 09:29:00.000
CA DSM Explorer|14.5.0.153|* Find SW|2021-10-11 12:34:00.000
CA DSM Scalability Server|14.5.0.153|* Find SW|2021-10-11 12:34:00.000
TEST1 (SXP)|1.0/00|Install Package|2021-10-11 15:26:27.000
7-Zip 19.00|19.00.00.0|Local Install|2021-10-13 12:01:55.000