SQL Query to display CA Patch Me Rollup deployment failed machines .

Products

• CA Client Automation - IT Client Manager

Issue/Introduction

CA Patch Me deployed  and  successfully installed on many machines . Use below SQL query If you want to know on how many machine its installed sucessfully deployment is failed . 

Environment

Release : 1403

Component : Patch Manager

Resolution

Execute the below query in SQL Quary Analyzer to get the list of machine on which CA Patchme deployment failed. Change the V2105 to required month when executing the query. 

SELECT M.agent_name [Computer Name],
dateadd(ss, max(completiontime)+ datediff(ss,getutcdate(),getdate()), convert(datetime,'19700101')) [Last 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
INNER JOIN ca_agent M ON A.target=M.object_uuid
WHERE R.itemname like 'UPM - CA - Patch Me - Security IntelliRollup v2105.00%' and A.status=10 and P.itemname='Silent Install'
and A.target not in 
(SELECT A.objectid
FROM usd_applic A
LEFT JOIN usd_actproc P ON A.actproc=P.objectid
LEFT JOIN usd_rsw R ON P.rsw=R.objectid
INNER JOIN ca_agent M ON A.target=M.object_uuid
WHERE R.itemname like 'UPM - CA - Patch Me - Security IntelliRollup v2105.00%' and A.status=9 and P.itemname='Silent Install' and A.uninstallstate<>2
)
GROUP BY M.agent_name
ORDER BY [Computer Name]

Result will be displayed as below : Result will display only two fields with computer name and Last Install time ( which is failed time ). In DB there is no specific field avaialble with name Failed Time.