Configmgr Query : Not Installed Software & Subselect Query’s
Scenario :
You need to make a query where following items need to be taking into consideration :
- Dot.net Framework 3.5SP1 is not installed or visible in add/remove programs
- The machine needs to be a windows XP client
- The machine must have a SCCM client installed
Problem :
Add Remove Programs / Display Name DOES NOT EQUAL "Microsoft .NET Framework 3.5 SP1" , gives me way to much records.
The problem that we are running into is that the query does not properly exclude the clients with .Net 3.5 SP1 because there are other programs on the Add Remove Programs list that aren't .Net Framework 3.5 SP1. It simply does what you have been asking for.
Solution :
In Configmgr, you have to use a sub select query to accomplish this. To get the expected results you have to run one query that selects all computers that DO have .NET 3.5 SP1, then you run another query that gives you all of the computers that are NOT in the first query. This is called a subselect query. In my example we wanted all workstations without .NET 3.5 SP1.
Create a query “All systems with .NET 3.5 SP1” query. This will be used by the subselect query later on.
******************************** Code *****************************************
select distinct SMS_R_System.ResourceId, SMS_R_System.Name from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft .NET Framework 3.5 SP1"
******************************** Code End *****************************************
That query shows all computers that DO have .NET 3.5 SP1 installed. This will be used by the subselect query to not include those systems in the final results.
Create the Subselect Dynamic Collection Query that will filter out only those machine that do not have a .Net 3.5 Framework Sp1 installed
******************************** Code *****************************************
select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select distinct SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Microsoft .NET Framework 3.5 SP1") and SMS_R_System.Client = 1 and SMS_R_System.OperatingSystemNameandVersion = "Microsoft Windows NT Workstation 5.1"
******************************** Code End *****************************************
Hope it Helps ,
Kenny Buntinx