Determine Who Is within the Domain Admins Group Using ConfigMgr

During my presentation at the Midwest Management Summit in the Mall of America (MMSMOA for short), I demonstrated how to determine who is within the Active Directory Domain Admins group by using ConfigMgr.

At first, this might seem like something odd to know but think about the number of times ConfigMgr Admins are asked by the service desk (help desk) to troubleshoot why an application won’t install on a particular end-user’s computer. Then you’re left to discover that the user in question is not a member of a security group.

Wouldn’t it be nice to create a report that your service desk can use to look up these security group details for themselves, particularly when they are at the end-user’s desk?

This blog post will show you all of the queries you’ll need to create this report!

The hardest part about designing any report is creating the queries. This might not seem like a big deal, but validating the query results and checking the query performance is also an important part of any report design. Since I already validated these queries, you can go ahead and use them for your report!

First, you’ll need the query to determine who is within the domain admins group.

The query for Users within Domain Admins Security Group

Select
U.Unique_User_Name0 as ‘UserID’,
U.Full_User_Name0 as ‘Full User Name’
from
dbo.v_R_User as U
join dbo.v_RA_User_UserGroupName as UUGN on U.ResourceID = UUGN.ResourceID
Where
UUGN.User_Group_Name0 = ‘gartekdomain admins’
order by
U.Unique_User_Name0

This is a good query, but as you can see, it is hardcoded to domain admins. Its functionality is limited. Do you really want to create one report for each security group?

The query for Users within a Security Group Using a Variable

What you really want to be able to do is to query for any security group. For this, you will need to add a variable to the previous query.

Select
U.Unique_User_Name0 as ‘UserID’,
U.Full_User_Name0 as ‘Full User Name’
from
dbo.v_R_User as U
join dbo.v_RA_User_UserGroupName as UUGN on U.ResourceID = UUGN.ResourceID
Where
UUGN.User_Group_Name0 = @SecGroup
order by
U.Unique_User_Name0

This query will prompt you for the name of the security group, and it will also allow you to see all of a security group’s members.

This is great, but do you really want the service desk or yourself having to manually type in the security group’s name? Probably not, so you will need to create a drop-down list for your prompt. For that, you will need another query.

The query for a List of Available Security Groups

This query will provide you with a list of available security groups. You would use this to populate the variable that was added to the query above.

Select Distinct
UUGN.User_Group_Name0 as ‘SecGroupName’
from
dbo.v_RA_User_UserGroupName as UUGN
order by
UUGN.User_Group_Name0

With the last two queries, you can now create a report that will allow you to see if a user is a member of a particular AD security group.

Keep in mind that this assumes that both AD User Discovery and AD Group Discovery are enabled. Also, remember that there will be some lag time between when a user is added to a group and when ConfigMgr discovers and adds these details to the ConfigMgr database.

I hope that you have found this information useful and if you have any questions, please feel free to contact me at @GarthMJ.

Do you have an idea for a blog post about a ConfigMgr query or reporting topic? Let me know. Your idea might become the focus of my next blog post!