How to get the list of all SD Jobs for all computers with a SQL Query ?

Products

• CA Client Automation - IT Client Manager
• CA Client Automation - Software Delivery
• CA Client Automation

Issue/Introduction

How to get the list of all SD Jobs for all computers with a SQL Query ? The query should indicate the status of the jobs.

Environment

Client Automation - All Versions.

Resolution

Following SQL Queries could be used :

- List of all SD jobs for installed packages

SELECT M.agent_name [Computer Name],
R.itemname [Package Name],
R.itemversion [Package Version],
P.itemname [Procedure Name],
CASE P.type
   WHEN 1 THEN 'Command File'
   WHEN 2 THEN 'Executable File'
   WHEN 3 THEN 'Detected'
   WHEN 4 THEN 'MSI File'
   WHEN 5 THEN 'SXP'
   WHEN 8 THEN 'IPS File'
   ELSE 'N/A'
END Type,
CASE P.task
   WHEN 0 THEN 'Install'
   WHEN 1 THEN 'Uninstall'
   WHEN 2 THEN 'Configure'
   WHEN 3 THEN 'Activate'
   ELSE 'N/A'
END Task,
dateadd(ss, completiontime+ datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) [Install Time],
CASE A.status
   WHEN 1 THEN 'Waiting'
   WHEN 2 THEN 'SW Delivery Ordered'
   WHEN 3 THEN 'SW Delivering'
   WHEN 4 THEN 'SW Delivery OK'
   WHEN 5 THEN 'SW Delivery Error'
   WHEN 6 THEN 'SW Already Delivered'
   WHEN 7 THEN 'Job Execution Ordered'
   WHEN 8 THEN 'Job Executing'
   WHEN 9 THEN 'SW Installed'
   WHEN 10 THEN 'Job Execution Error'
   WHEN 11 THEN 'SW Removal Ordered'
   WHEN 12 THEN 'SW Removing'
   WHEN 13 THEN 'SW Removal OK'
   WHEN 14 THEN 'SW Removal Error'
   WHEN 15 THEN 'SW Already Installed'
   WHEN 16 THEN 'Manipulation not allowed'
   WHEN 17 THEN 'SW Delivery Halt Ordered'
   WHEN 18 THEN 'SW Delivery Halt in Progress'
   WHEN 19 THEN 'SW Delivery Halted'
   WHEN 20 THEN 'SW Delivery Resume Ordered'
   WHEN 21 THEN 'SW Delivery Resume in Progress'
   WHEN 22 THEN 'SW Undelivery Halt Ordered'
   WHEN 23 THEN 'SW Undelivery Halt in Progress'
   WHEN 24 THEN 'SW Undelivery Halted'
   WHEN 25 THEN 'SW Undelivery Resume Ordered'
   WHEN 26 THEN 'SW Undelivery Resume in Progress'
   WHEN 27 THEN 'Execution Order Staged'
   WHEN 28 THEN 'Job Properties'
   WHEN 29 THEN 'SW Already Installed (NonSD)'
   ELSE 'Unknown' 
END Status,
A.administrator 'Installed By'
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 in ('delivery proc','undelivery proc')
ORDER BY [Computer Name], [Install time]

 

Here is an example of output :

 

- List of all SD jobs :

SELECT M.agent_name [Computer Name],
R.itemname [Package Name],
R.itemversion [Package Version],
P.itemname [Procedure Name],
CASE P.type
   WHEN 1 THEN 'Command File'
   WHEN 2 THEN 'Executable File'
   WHEN 3 THEN 'Detected'
   WHEN 4 THEN 'MSI File'
   WHEN 5 THEN 'SXP'
   WHEN 8 THEN 'IPS File'
   ELSE 'N/A'
END Type,
CASE P.task
   WHEN 0 THEN 'Install'
   WHEN 1 THEN 'Uninstall'
   WHEN 2 THEN 'Configure'
   WHEN 3 THEN 'Activate'
   ELSE 'N/A'
END Task,
dateadd(ss, completiontime+ datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) [Install Time],
CASE A.status
   WHEN 1 THEN 'Waiting'
   WHEN 2 THEN 'SW Delivery Ordered'
   WHEN 3 THEN 'SW Delivering'
   WHEN 4 THEN 'SW Delivery OK'
   WHEN 5 THEN 'SW Delivery Error'
   WHEN 6 THEN 'SW Already Delivered'
   WHEN 7 THEN 'Job Execution Ordered'
   WHEN 8 THEN 'Job Executing'
   WHEN 9 THEN 'OK'
   WHEN 10 THEN 'Job Execution Error'
   WHEN 11 THEN 'SW Removal Ordered'
   WHEN 12 THEN 'SW Removing'
   WHEN 13 THEN 'SW Removal OK'
   WHEN 14 THEN 'SW Removal Error'
   WHEN 15 THEN 'SW Already Installed'
   WHEN 16 THEN 'Manipulation not allowed'
   WHEN 17 THEN 'SW Delivery Halt Ordered'
   WHEN 18 THEN 'SW Delivery Halt in Progress'
   WHEN 19 THEN 'SW Delivery Halted'
   WHEN 20 THEN 'SW Delivery Resume Ordered'
   WHEN 21 THEN 'SW Delivery Resume in Progress'
   WHEN 22 THEN 'SW Undelivery Halt Ordered'
   WHEN 23 THEN 'SW Undelivery Halt in Progress'
   WHEN 24 THEN 'SW Undelivery Halted'
   WHEN 25 THEN 'SW Undelivery Resume Ordered'
   WHEN 26 THEN 'SW Undelivery Resume in Progress'
   WHEN 27 THEN 'Execution Order Staged'
   WHEN 28 THEN 'Job Properties'
   WHEN 29 THEN 'SW Already Installed (NonSD)'
   ELSE 'Unknown' 
END Status,
A.errorcause, A.errorparam, A.uninstallstate,
IIF(A.status=9 AND A.uninstallstate=0 and P.Task=0, 'INSTALLED', '') 'Install State'
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 P.itemname not in ('delivery proc' , 'undelivery proc')
ORDER BY [Computer Name], [Install time]