In almost all Active Directory (AD) environments, access to resources will be managed via security groups, and group emails handled by distribution groups. While it is also possible to add devices to security groups, and use these to perform some pretty useful functions, it’s mostly all about the users. Managing Active Directory on-premises is an easy process, and one that is likely already in place and locked down with specific user rights and roles, with a process to delegate rights to specific users for specific groups. This gave me an interesting idea: What if I can control application deployments to devices, and Office 365 licensing, by leveraging nothing more than user groups in Active Directory?
The biggest issue with ConfigMgr collections and AD Group discovery, is that it is a one-or-the-other approach: You can typically create a User Collection, and populate that with users; or you can create a Device Collection, and populate that with devices; but you can’t create a collection for one type and include the other type in it. If you were to create a Device Collection, and try to add users to it, you will not return any members (and visa-versa).
In fact, ConfigMgr won’t even let you define selection criteria across types. Obviously, it is entirely possible that you want to just make software available to users, regardless of what device they are currently signed into, and if that is the case then you can just create a normal User Collection and add the Active Directory User Group option when building the criteria.
However, what if you want to limit the devices on which the software can be installed, in order to control the license counts, etc.? Well in that case, you have two choices: You can modify the membership criteria to isolate the installation to the user’s primary device, or you can revert back to a direct membership rule and add just the required devices. But neither of these options is really that useful. Luckily, there is another way.
Query Rules for collections are just WQL, and ConfigMgr WQL supports all the underlying SQL tables and views from the site database: It just can’t represent that graphically. So what do we do? We create a Device Collection that looks for devices, based on the username suffix of the user security group. Yes, that is possible.
Here is where your mileage may vary. When it comes to asset management, every site will likely have their own device naming convention, and methods for linking assets and users. Some sites just accept the automatic name generated by the Windows Setup process; some don’t even bother with tracking the device or user assignment (i.e. hot-desks); some use device serial numbers or other hardware identifiers; and some use locations and/or assigned user names.
Typically, you’ll find most sites recommend against naming devices with something as variable as assigned user, however there are also a number of discovery and management benefits for doing this… with he down-side, obviously, is that the device will need to be renamed if the assigned user ever changes, or if no one user is assigned to the device. For our site, we do name devices after users, and this is the magic that makes this whole project work.
Here’s an example of a WQL query that will suit our needs. We’ll use this later in the article, so feel free to copy it now. The trick here, is that having the suffix of the computer be a user name, all we need to do is strip of the asset prefix in order to match a device to an assigned user. For example, if you have a device named “XT14MURRAYW”, all we need to do is match a substring of the computer name to a user name in order to return a membership value. This type of query cannot currently be represented within ConfigMgr, but can be pasted in via the Query Language editor. Here’s the magic:
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 SUBSTRING(SMS_R_System.Name,<STARTNUMBER>,<TOTALLENGTH>) IN ( SELECT SMS_R_User.Username
FROM SMS_R_User
WHERE SMS_R_User.UserGroupName = "<DOMAIN>\\<GROUPNAME>"
)
The above query contains four (4) variables that I inserted, which must be modified for your own environment. These are as follows:
<STARTNUMBER> – Is the first character of the username, within the substring of the computer name. In our case, we have a 4-digit prefix, so we set this value to “5”.
<TOTALLENGTH> – This value is the maximum number of characters that will be returned with the substring. As you are likely aware, computer names cannot be longer than 15 characters in length, so as we do not have any string after the username, we set this to “11” to return the remainder of the string as the username. If you have the username at the front of the substring, you can change this value to return only the substring you desire.
<DOMAIN> – The domain name for the AD Group that contains your user accounts i.e. “MYDOMAIN”.
<GROUPNAME> – The full name of an AD Group that was added to the ConfigMgr discovery i.e. “O365 Assignment – Power BI”
To make this all work:
- Create a new AD security group i.e. “O365 Assignment – Power BI”, and apply a couple of test users to the group (using AD usernames).
- [Optional] Apply any required permissions and roles to this group, in order to allow helpdesk team management, or as required.
- Ensure that this security group is scoped within the included Azure AD Connect OUs to be synchronised.
- Add this group to Active Directory Group Discovery (Administration > Hierarchy Configuration > Discovery Methods).
- Build a new collection, and name it “Power BI (Available)” or similar.
- For the new collection modify the membership rules and:
- Click Add Rule, and select Query Rule.
- Give the rule a name (i.e. “Select Power BI Users”), then click Edit Query Statement.
- Click Show Query Language (see above for reasons).
- Paste in the query from Figure 1, substituting the variables for the start number, domain, and group name as required.
- Save your changes, then update the collection membership.
- Click Add Rule, and select Query Rule.
- Deploy you app – i.e. the Power BI app – to the new collection as available (or required, if desired).
- Log in to the Azure Active Directory portal, then open the AAD blade – the easiest entry-point, if you’re unsure, is via Admin Centers within the Office 365 Admin portal.
- Click Groups and search for your synchronised AD group – i.e. “O365 Assignment – Power BI”. Note: It will take up to 30 minutes for this to synchronise to AAD from AD.
- Select the desired group, then click “Licenses” from the side menu.
- Click Assign from the top of the new blade, choose the product(s) and Assignment Options for the software i.e. “Power BI (free)”, then click Assign.
This process will:
- Allow you to manage everything from a user-based AD security group
- Which will synchronise members to Azure AD via AAD Connect.
- Which will automatically assign a license using the “Group Based Licensing” mechanism (which does take a bit of time to run).
- And will also update the Device Collection, based on the user names being part of the computer name.
- Which will make the software available (or install it if required).
All this means that licensing and deployment can be automated, via a simple AD security group change: And that is before we even discuss the possibility of using that AD security group to provide access to a share or SharePoint site containing the reports the user’s will need, or even using the group to define firewall exceptions as required to make certain features of the solutions work (if required). It’s pretty powerful.
To set up Azure AD Connect, and extend you local Active Directory to the cloud, read more here: https://docs.microsoft.com/en-us/azure/active-directory/connect/active-directory-aadconnect
To read more about Azure AD Group Based Licensing, read more here: https://docs.microsoft.com/en-us/azure/active-directory/active-directory-licensing-whatis-azure-portal
To integrate the Windows Store for Business with ConfigMgr, in order to deploy Store apps such as my Power BI example above, read more here: https://docs.microsoft.com/en-us/sccm/apps/deploy-use/manage-apps-from-the-windows-store-for-business
For more information on writing Queries, especially WQL queries for collections, read more here: https://technet.microsoft.com/en-au/library/gg712323.aspx
Webber Zone 