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!
Comments
I CAN HAZ CHEZBURGER
The good news is: yes you can go nuts on SQL and do it that way.
The bad news is: you could have done it easily with regular collections.
Step 1) create collection of all computers with Software X
Step 2) create collection with ALL computers and exclude the collection made in step 1
Ta-da!
-Geo
It becomes a timing issue at that point
I always the best way was to create two collections on the NS and exclude one over the other, but then that means the NS must update two collections and there's a possibility you could be out of sync on one collection. The easiest SQL query would be like this:
SELECT GUID FROM vResource r
LEFT JOIN
(SELECT _ResourceGUID FROM Inv_AeX_OS_Add_Remove_Programs p
WHERE p.[Name] LIKE 'Symantec Antivirus%') T
ON T._ResourceGUID = r.GUID
WHERE T._ResourceGUID IS NULL
AND IsManaged = 1
AND ResourceTypeGUID in
(
-- Select PC's & VM's
select [ResourceTypeGUID]
from [ResourceTypeHierarchy]
where [BaseResourceTypeGuid]='493435f7-3b17-4c4c-b07f-c23e7ab7781f'
)
You can change "'Symantec Antivirus%' to whatever application you want. One query, one place to keep track of :)
I like your query! Thanks!
I like your query! Thanks!
-Geo
does it works with all SQL series?
does it works with all SQL series?
Would you like to reply?
Login or Register to post your comment.