ConfigMgr Collection Membership based on NOT existance
Every now and then the need for an inverted membership is required for a collection. Such can be when deploying a new software to servers, one might want to filter out all servers that already has the software or servers with a different software. In this case at a customer, we needed to find all servers without a specific software installed.
While one can use the following query rule to find all systems WITH a specific software, one must create a slightly more complex query rule to find all of those that doesn’t have the software.
select * 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 SSM_G_System_ADD_REMOVE_PROGRAMS.ProdID = “Microsoft Security Client”
So, the above query finds all systems where the software is installed. Why can’t we just change the = to != showing that it shouldn’t be equal to? Because the servers will probably have more than one software installed and thus will the other software return true for that query and therefore including it in the query result.
What we need to do is to select all systems that doesn’t exist in the result of all systems that have the software installed. We do this with a subselect query. First we select all systems with the software, then we select all the systems that are not in that list. Easy, right?
This is the query we use
select * from SMS_R_SYSTEM where SMS_R_System.ResourceID no 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.ProdID = “Microsoft Security Client”) and SMS_R_System.Client = “1”
This works perfectly fine for other queries as well where we look for something other than installed software such as installed server roles. Btw, the condition for a client is added just to make sure that we doesn’t get all systems that hasn’t reported in any inventory data just yet.
Just to make it a bit more complex, we also need to take into consideration that the software could be installed on a 64bit system, we need to check in SMS_G_System_ADD_REMOVE_PROGRAMS64 as well.
This is the final query and the one that we actually use in production.
select * 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_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.ProdID = “Microsoft Security Client”) and 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.ProdID = “Microsoft Security Client”)) and SMS_R_System.Client = “1”