ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

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


Article ID: 227747


Updated On:


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


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.


Client Automation - All Versions.


Following SQL Query could be used :


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 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
WHERE A.installation=a.objectid and A.uninstallstate<>2 and P.itemname<>'delivery proc'
ORDER BY [Computer Name], [Install time]


Here is an example of output :