How to automatically remove physical applications from PC’s when using Primary Devices.
When you deploy applications to users that are members of a specific Active Directory Application Group but install them machine based, how do you automatically remove the application when a user is no longer a member of the Application Group? Well, before SCCM 2012 this was not possible. There was no way of knowing from which computer to remove the application if a user was no longer a member of the corresponding Application Group. Another problem was that a user could install de application on any PC he logged on to. Possible causing the company to not adhere to the license agreement.
With SCCM 2012 Microsoft introduced the ability to define Primary Devices for users and only allow installation of an application on the user’s primary device. This gives you better a grip on where applications are installed (and the required licenses).
This for me raised the question “Can I automatically remove an application from the primary PC for a user that is not (or no longer) the member of the Application Group for that application?”.
The Answer is “Yes” but its not very easy to do (although much easier now that I have figured out how to do it.) and it is limited. The limitation of this solution is that a change to user device affinity does not trigger an incremental update for collections. This limits the updating of the required collection to the full update schedule.
What I realized is my search for a solution was that my initial question was not exactly right. What I really wanted was to remove an application from any PC that has the application installed and is not a primary PC for any user that is a member of the applications AD group. It was important to get this right because only then was I able to create a correct query to determine from which PC’s the application should be removed.
It seems obvious that you only want to remove an application from PC’s where is is installed, but is was is an important addition because of the limitation of the full update schedule for the collection based on the query I created.
More will be explained later in this article.
If you just want to know the final query click here, for more information about how I build the query and more explanation, read on.
To create the final query I needed various sub queries: (Replace the red text in the queries with your appropriate values)
Q1: All users that are a member of the applications AD group
This is a pretty simple query that can easily be build using the SCCM query builder. The query you end up with is:
select SMS_R_USER.UniqueUserName from SMS_R_User where SMS_R_User.UserGroupName = "<Domain>\\<ADgroup>"
I’ve only selected the UniqueUserName as this is all that is needed for the next query.
Q2: All Devices that are a primary devices for a user in the result of Q1
This was a much more difficult query to build as it couldn’t be build using the query builder only by directly typing the query statement in WQL.
The SCCM table that contains information about user device affinity is the SMS_UserMachineRelationship table. So this is the table that has to be queried. The table contains more rows than there are actual Primary device relationships. It also contains history and (possibly) relationships based on usage. To know if a row represents an actual Primary Device relationship we have to look at the Types column. If this contains the value of 1.00 the row represents a primary Device relationship. The other rows have no value in the Types column. Although the column name Types and the value 1.00 suggest that other values are possible I have not yet seen a different value other than 1.00 or no value. With both user and administrator defined primary device relationships the value is 1.00.
With this information the query can be constructed en will look like this:
select SMS_R_System.ResourceId from SMS_R_System inner join SMS_UserMachineRelationship on SMS_UserMachineRelationship.ResourceID = SMS_R_System.ResourceId where SMS_UserMachineRelationship.Types = 1 AND SMS_UserMachineRelationship.UniqueUserName in (select SMS_R_USER.UniqueUserName from SMS_R_User where SMS_R_User.UserGroupName = "<Domain>\\<ADgroup>")
I’ve only selected the ResourceID as this is all that is needed for the next query. The Subselect query is Q1.
Q3: All Devices that are not in Q2
Now that we have an list of all the primary devices of users in the Application Group we can create a list of all devices that are not in the first list.
This query can again be created using the SCCM query builder.
Create a query selecting the System ResourceID and Name.
Create a criterion for this query of the “SubSelected values” type.
Use the “not in”operator and paste Q2 als the Subselect query.
In WQL the query will then look like this:
select SMS_R_System.ResourceId, SMS_R_System.Name from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System inner join SMS_UserMachineRelationship on SMS_UserMachineRelationship.ResourceID = SMS_R_System.ResourceId where SMS_UserMachineRelationship.Types = 1 AND SMS_UserMachineRelationship.UniqueUserName in (select SMS_R_USER.UniqueUserName from SMS_R_User where SMS_R_User.UserGroupName = "<Domain>\\<ADgroup>"))
This query is enough to create a collection to target the uninstall of your application to. Except there is one issue. The collection will contain even the devices on which the application is not installed and you can not install the application to these devices until the Uninstall job is no longer targeted to it.
To (partially) solve this issue you can limit the query to only list devices that have the application installed as the uninstall job only has to be targeted to those devices.
Q4: All Devices that have the application installed
The query to list all devices that have an application installed can easily be created using the SCCM Query Builder.
Create a query with a simple value criterion.
Select the Installed Applications – Product ID (or if you want to check a 64 bit application use Installed Application (64) – Product ID).
Use “is equal to” as the operator and enter the Applications Product ID (ProductCode GUID) as the value.
If your application is a legacy (non MSI) install and doesn’t have a product ID then you may want to check the Name and possibly Version, but I won’t use this in my example.
Since this query can easily be added as a criterion to Q3 we can now create the final Query
By combining Q3 and Q4 we create the final query. In WQL it will look like this:
select SMS_R_System.ResourceId, SMS_R_System.Name from SMS_R_System 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_R_System.ResourceId not in (select SMS_R_System.ResourceId from SMS_R_System inner join SMS_UserMachineRelationship on SMS_UserMachineRelationship.ResourceID = SMS_R_System.ResourceId where SMS_UserMachineRelationship.Types = 1 AND SMS_UserMachineRelationship.UniqueUserName in (select SMS_R_USER.UniqueUserName from SMS_R_User where SMS_R_User.UserGroupName = "<Domain>\\<ADgroup>")) AND SMS_G_System_ADD_REMOVE_PROGRAMS.ProdID = "<ProductID>"
Setting up the uninstall
Create a collection based on the final query. Set the Full update schedule to once a day. If set higher make sure your environment can handle the load. If set lower it will take longer to before the application can be reinstalled. Deploy the Uninstall of the application to the created collection.
Set Hardware Inventory (which also inventories Installed Applications) to at least once a day. Hardware inventory change will trigger incremental updates so when the application is uninstalled the device will be (more quickly) removed from the uninstall collection.
As long as the uninstall deployment is targeted to a device the application can not be installed on that device (even if it is not installed there anymore).
A device will only be removed from the collection after a full update or an incremental update triggered by a hardware inventory because setting a device as the primary device for a user will not trigger an incremental update of this collection (as this is not supported for the SMS_UserMachineRelationship table). The client will also have to see the update of the collection (Machine Policy Retrieval & Evaluation Cycle). This may take unwanted time, if not properly configured. You could increase the update schedule of the Collection and client, but this may put to much strain on your SCCM environment, especially if you have a lot of applications that you want to uninstall in this way.
Because of the limitation. It may not be desirable to use this method of uninstallation for all applications, but only for application for which uninstall is a high priority, for instance for very large applications or applications with (very) expensive licenses.