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

Products

CA Client Automation - IT Client Manager

Issue/Introduction

Environment

Release : 1402

Component :

Resolution

sqlcmd -S JY-2019-145\TESTJY -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='JY-2019-145' ORDER BY [Computer Name], [Install time]" -h-1 -W -s;
Replace -S JY-2019-145\TESTJY by the name and instance of SQL Server
Replace M.agent_name='JY-2019-145' 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 ilke this :

JY-2019-145;CA DSM Agent + Data Transport plugin;14.5.0.153;Install;2021-10-08 09:29:00.000
JY-2019-145;CA DSM Agent + Remote Control plugin (English only Edition);14.5.0.153;Centrally Managed Complete Agent;2021-10-08 09:29:00.000
JY-2019-145;CA DSM Manager;14.5.0.153;Detect;2021-10-08 09:29:00.000
JY-2019-145;CA DSM Agent + Software Delivery plugin (English only Edition);14.5.0.153;Install;2021-10-08 09:29:00.000
JY-2019-145;CA DSM Agent + Asset Management plugin (English only Edition);14.5.0.153;Install;2021-10-08 09:29:00.000
JY-2019-145;CA DSM Agent + Basic Inventory plugin (English only Edition);14.5.0.153;Install;2021-10-08 09:29:00.000
JY-2019-145;CA DSM Explorer;14.5.0.153;* Find SW;2021-10-11 12:34:00.000
JY-2019-145;CA DSM Scalability Server;14.5.0.153;* Find SW;2021-10-11 12:34:00.000
JY-2019-145;TEST1 (SXP);1.0/00;Install Package;2021-10-11 15:26:27.000
JY-2019-145;7-Zip 19.00;19.00.00.0;Local Install;2021-10-13 12:01:55.000
Computername;packagename;packageversion;procedurename;install datetime
; is the column separator but it could be changed witht he parameter -s
Without computername column and | as separator :

sqlcmd -S JY-2019-145\TESTJY -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='JY-2019-145' ORDER BY [Install time]" -h-1 -W -s^|