List of SD Packages not installed to Any Agent

Products

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

Issue/Introduction

How can we retrieve the following lists using SQL Query on mdb database :

- List of SD Packages not installed to Any Agent

- List of SD Packages not installed to Any Agent and not staged to any scalability servers

- List of SD Packages not installed to Any Agent and staged to at least one scalability server.

- Details about where are staged the SD Packages not installed to Any Agent and staged to at least one scalability server.

Environment

Client Automation - Any versions.

Resolution

Here are the SQL Queries to he 4 lists :

 

IF OBJECT_ID ('tempdb.dbo.#TMP_RSW1','U') IS NOT NULL DROP TABLE #TMP_RSW1
IF OBJECT_ID ('tempdb.dbo.#TMP_RSW2','U') IS NOT NULL DROP TABLE #TMP_RSW2
IF OBJECT_ID ('tempdb.dbo.#TMP_RSW3','U') IS NOT NULL DROP TABLE #TMP_RSW3

SELECT distinct R.objectid INTO #TMP_RSW1
FROM usd_applic A
INNER JOIN usd_actproc P ON A.actproc=P.objectid
INNER JOIN usd_rsw R ON P.rsw=R.objectid 
WHERE A.uninstallstate<>2 and A.status=9 and P.task<>1

SELECT objectid INTO #TMP_RSW2 FROM usd_rsw WHERE itemtype<>5 and objectid not in (select objectid from #TMP_RSW1)

SELECT r.objectid INTO #TMP_RSW3
FROM usd_applic a
INNER JOIN usd_actproc p ON p.objectid=a.actproc
INNER JOIN usd_rsw r ON r.objectid=p.rsw
WHERE a.status=4 and a.uninstallstate<>2

PRINT 'SD Packages not installed to any agents'
SELECT itemname, itemversion FROM usd_rsw WHERE itemtype<>5 and objectid in (select objectid from #TMP_RSW2)
ORDER BY itemname, itemversion


PRINT 'SD Packages not installed to any agents and not staged to any scalability Servers'
SELECT itemname, itemversion FROM usd_rsw WHERE itemtype<>5 and objectid in (select objectid from #TMP_RSW2)
and objectid not in (SELECT objectid FROM #TMP_RSW3)
ORDER BY itemname, itemversion

PRINT 'SD Packages not installed to any agents and staged to one or more scalability Servers'
SELECT itemname, itemversion FROM usd_rsw WHERE itemtype<>5 and objectid in (select objectid from #TMP_RSW2)
and objectid in (SELECT objectid FROM #TMP_RSW3)
ORDER BY itemname, itemversion

PRINT 'SD Packages not installed to any agents but staged to some Scalability Servers'
SELECT r.itemname, r.itemversion, m.agent_name  FROM usd_applic a
INNER JOIN usd_actproc p ON p.objectid=a.actproc
INNER JOIN usd_rsw r ON r.objectid=p.rsw
INNER JOIN ca_agent m ON m.object_uuid=a.target
where r.objectid in (select objectid from #TMP_RSW2)
and r.objectid in (select objectid from #TMP_RSW3)
order by r.itemname, r.itemversion, m.agent_name