CA Client Automation

Guys, say i have some nodes with the following in the installed Packages node:

• MySoftware  4.3
• MySoftware  4.4
• MySoftware  4.5

How to write a query that return all nodes with multiple versions of MySoftware  regardless of the versions ?

Hi Shocko,

You could try following queries :

IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMP

SElECT h.host_name, r.itemname, r.itemversion
INTO #TEMP
FROM usd_applic a, usd_actproc p, usd_rsw r, ca_discovered_hardware h
WHERE p.task=0 and r.itemname='MySoftware'
and a.objectid=a.installation and a.status=9 and a.actproc=p.objectid and p.rsw=r.objectid and h.dis_hw_uuid=a.target

SELECT * FROM #TEMP
WHERE host_name in
(SELECT host_name FROM #TEMP
GROUP BY host_name, itemname
HAVING COUNT(*)>1)

Do they return requested result ?

Regards,

Jean-Yves

Thanks Jean-Yves! Apologies for only replying now but uber busy. I'm basic in terms of SQL queries so could you give a quick explanation as to whats going on with this one for us noobs!

Hi Shocko,

This query fills the temp table #TEMP with machines which have at least one package "MySoftware" succesfully installed

SElECT h.host_name, r.itemname, r.itemversion
INTO #TEMP
FROM usd_applic a, usd_actproc p, usd_rsw r, ca_discovered_hardware h
WHERE p.task=0 and r.itemname='MySoftware'
and a.objectid=a.installation and a.status=9 and a.actproc=p.objectid and p.rsw=r.objectid and h.dis_hw_uuid=a.target

This second query returns the list of machines which have 2 or more package "MySoftware" succesfully installed

SELECT * FROM #TEMP
WHERE host_name in
(SELECT host_name FROM #TEMP
GROUP BY host_name, itemname
HAVING COUNT(*)>1)

In order to specify the package name, just replace 'MySoftware' by the name of package you want to research.

Thanks.

Regards,

Jean-Yves