Excel – Query Members of an Active Directory Group

active-directorymicrosoft-excel-2010security-groups

So in active directory, there is a group called WebSiteUsers that is being used to permit access to a folder I am hosting via IIS. I was wondering (using DSQuery, ADFind or any freely available tool) how to do the following:

  1. How do I query the distinguished name of WebSiteUsers (let's assume
    it is buried a few OU's deep in AD)?

  2. How do I query WebSiteUsers to produce a list of users (in human
    readable format) that I can compare against another group to make
    sure all of the people who need access to this resource have been
    added? For this example, assume WebSiteUsers has a few thousand
    accounts added to it so visual inspection is not an option. I
    would prefer to use excel to compare the lists of users, so
    exporting a CSV or some sort of text file I can manipulate in excel
    would be ideal.

Best Answer

To find the DN run the command dsquery group -name WebSiteUsers

If you have a domain controller set up for PowerShell (you should; it's awesome) you can run the command $WebSiteUsers = Get-ADUser -Filter {memberOf -RecursiveMatch CN=WebSiteUsers,OU=Lemings,OU=CorporateBranch,DC=example,DC=com' and $WebSiteUsers | Export-CSV to output to a CSV. You could also use the Compare-Object command like so:

$WebSiteUsers = Get-ADUser -Filter {memberOf -RecursiveMatch CN=WebSiteUsers,OU=Lemings,OU=CorporateBranch,DC=example,DC=com'
$OtherGroupUsers = Get-ADUser -Filter {memberOf -RecursiveMatch CN=OtherGroups,OU=Lemings,OU=CorporateBranch,DC=example,DC=com'
Compare-Object -ReferenceObject $WebSiteUsers -DifferenceObject $OtherGroupUsers -Property Name

This will kick out a list of names that are left out of one group or another. (Add -IncludeEqual if you want to see everyone.) This will make visual inspection much easier:

Jim Bob                      =>                                                                                               
Suzie Q                      <=                                                                                               
Harold Johnson               <=

If you want to add everyone that's a member of the other group to the WebSiteUsers group:

Compare-Object $OtherGroupUsers $WebSiteUsers | Where {$_.SideIndicator -eq '=>'} | foreach{Add-ADGroupMember -Identity WebSiteUsers -Members $_}

Might not hurt to add a -WhatIf on the Add-ADGroupMember command to double check it's going to do what's intended.

You can also get this list using the Active Directory Users and Computer snap-in. You'll need RSAT installed to do this from your workstation, otherwise you can remote in to a domain controller and open it.

Right click on Saved Queries and select New, Query:

enter image description here

Give it an abitrary name and a short description, then click Define Query:

enter image description here

Under Find: select Custom Search. Click on Field and select User, Member Of

enter image description here

Enter the name of the group you'd like to include and click Add:

enter image description here

Now you can view this list in ADUC. To export it, click the Export List button. This will output to a tab delimited text file.

enter image description here

Related Question