Client Automation - SQL Query on Enterprise Job Distribution Container
search cancel

Client Automation - SQL Query on Enterprise Job Distribution Container


Article ID: 212316


Updated On:


CA Client Automation - IT Client Manager


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



Client Automation - All Version.


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 '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 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