Techie Pie

If you have Altiris, at some point you have no doubt wanted to create a collection based on a program that may be installed on the desktop.  For me, this program was the administrative nightmare known as Deep Freeze, however you can modify the query to reflect whatever program you want to hunt for.

It is important to note that the SQL to build the collection for this is NOT the same as the SQL used to build a report for this, and if you start with a report to find the software and then try to create the collection, you won’t get all the data.  To create the collection statement, I poked around at a SQL level first until I got the desired result set.  At that point I was able to use the query to create the computer collection.

The bold items represent what you would swap out for your own query.

select Guid from vResource where Guid in (select ResourceGuid as Guid from (SELECT DISTINCT
i.[Name],
csw.KnownAs [Product Name],
csw.ProductVersion [Version],
csw.Manufacturer,
i.[Guid] [ResourceGuid]
FROM dbo.vComputer i
JOIN dbo.Inv_AeX_SW_Audit_Software_spt t1
ON t1.[_ResourceGuid] = i.Guid
JOIN dbo.Cmn_SW_Common csw
ON csw.[_KeyHash] = t1.[_KeyHash]
JOIN dbo.Inv_AeX_AC_Identification d
ON d.[_ResourceGuid] = i.Guid
JOIN dbo.CollectionMembership cm
ON cm.ResourceGuid = d.[_ResourceGuid]
JOIN dbo.vCollection it
ON it.Guid = cm.CollectionGuid
/* WHERE csw.KnownAs = ‘Deep Freeze‘ OR csw.KnownAs = ‘Deep Freeze 5‘ OR csw.KnownAs = ‘Deep Freeze 6‘ */
WHERE csw.KnownAs LIKE ‘Deep Freeze%‘
AND d.[System Type] LIKE ‘Win%’
AND i.[Name] LIKE ‘%’ ) xxx)