NS Query - Find Machines without Software by Collection in Access

Since we installed Altiris here several years ago my management has come to me more and more often for reports about installed software. Last week they came to me with a request to find which machines did not have a particular piece of software. Looking through the canned reports did not reveal anything useful. A quick check with our Altiris engineer revealed that this could be done but only through some very complicated SQL.

I am not a super proficient SQL'er but I did come up with something quick and dirty that satisfied the need and allowed me to create something general purpose for future request.

The prerequisite for this is Microsoft Access and an ODBC connection to your Altiris database. Once you have that make sure the following tables have been linked:

• dbo_CollectionMembership
• dbo_Inv_AeX_AC_Identification
• dbo_Inv_AeX_OS_Add_Remove_Programs
• dbo_vCollection

Create the following 3 queries with these exact names:

1. Find Machines with Software - Add Remove

SELECT DISTINCT dbo_Inv_AeX_AC_Identification.Name, dbo_Inv_AeX_OS_Add_Remove_Programs.Name, dbo_Inv_AeX_AC_Identification.[_ResourceGuid], dbo_Inv_AeX_AC_Identification.[System Type]
FROM dbo_Inv_AeX_AC_Identification INNER JOIN dbo_Inv_AeX_OS_Add_Remove_Programs ON dbo_Inv_AeX_AC_Identification.[_ResourceGuid] = dbo_Inv_AeX_OS_Add_Remove_Programs.[_ResourceGuid]
WHERE (((dbo_Inv_AeX_OS_Add_Remove_Programs.Name) Like [Program Name]) AND ((dbo_Inv_AeX_AC_Identification.[System Type]) Like "win*"));

2. Find Machines without Software - Add Remove

SELECT dbo_Inv_AeX_AC_Identification.Name, dbo_Inv_AeX_AC_Identification.[_ResourceGuid]
FROM dbo_Inv_AeX_AC_Identification LEFT JOIN [Find Machines with Software - Add Remove] ON dbo_Inv_AeX_AC_Identification.[_ResourceGuid] = [Find Machines with Software - Add Remove].[_ResourceGuid]
WHERE ((([Find Machines with Software - Add Remove].[_ResourceGuid]) Is Null) AND ((dbo_Inv_AeX_AC_Identification.[System Type]) Like "Win*"))
ORDER BY dbo_Inv_AeX_AC_Identification.Name;

3. Find Machines without Software by Collection - Add Remove

SELECT DISTINCT [Find Machines without Software - Add Remove].Name, dbo_vCollection.Name AS Collection
FROM dbo_vCollection INNER JOIN ([Find Machines without Software - Add Remove] INNER JOIN dbo_CollectionMembership ON [Find Machines without Software - Add Remove].[_ResourceGuid]=dbo_CollectionMembership.ResourceGuid) ON dbo_vCollection.Guid=dbo_CollectionMembership.CollectionGuid
WHERE (((dbo_vCollection.Name) Like [Collection Name]));

When you run the 3rd one it will query you for the Program Name as it appears in Add\Remove Programs and then the Collection. Both fields will allow for the * to be used as a wildcard.

Enjoy!