Client Automation - SQL Query on Enterprise Job Distribution Container

Products

• CA Client Automation - IT Client Manager

Issue/Introduction

How to get the details about SD Job Distribution Containers on Enterprise database with a SQL Query ?

 

Environment

Client Automation - All Version.

Resolution

Here is a SQL Query to retrieve details about SD Software Job Distribution Container for Staging of Software Package to Domain Managers on Enterprise Database

 

 

SELECT C.name 'Container Name', R.itemname 'Software Name', R.itemversion 'Software Version', M.label 'Domain Name',
CASE CC.diststatus
WHEN 240 THEN 'Pending'
WHEN 241 THEN 'Active'
WHEN 242 THEN 'OK'
WHEN 243 THEN 'Error'
WHEN 244 THEN 'Timeout'
WHEN 245 THEN 'Halted'
END 'Status',
dateadd ( ss, C.disttime + datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) 'Distribution Time'
FROM usd_order O
LEFT JOIN usd_cont C ON C.objectid=O.cont
LEFT JOIN usd_rsw R ON R.objectid=O.operand
LEFT JOIN usd_cc CC ON CC.cont=C.objectid
LEFT JOIN ca_manager M ON M.domain_uuid=CC.boundto
WHERE O.type=1
ORDER BY O.cont, M.label, O.sequenceno

 

 

Exemple :

 

 

 

 

 

 

This second SQL Query returns details about Software Package which have been staged successfully on Domain Manager :

 

SELECT R.itemname 'Software Name', R.itemversion 'Software Version', 
CASE R.itemtype
   WHEN 0 THEN 'Generic'
   WHEN 3 THEN 'Generic (Open)'
   WHEN 5 THEN 'Detected'
END 'Package Type',
M.label 'Domain Name',
dateadd ( ss, D.distributiontime + datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) 'Distribution Time'
FROM usd_distsw D
LEFT JOIN usd_rsw R ON D.rsw=R.objectid
LEFT JOIN ca_manager M ON M.domain_uuid=D.localsite
ORDER BY 1,2,3