Monday, 6 June 2016

Export Group Membership From Active Directory Using Power Query

If you need to export a list of staff from an Active Directory Group, follow these steps.  Allows you to gather all the names & usernames of staff in a particular group and save the information into Excel.

Step By Step How To:
  • Open up Excel > Power Query Tab
  • Click From Other Sources > From Active Directory:
  • (In Excel 2016 this is found under: Data Tab > New Query > From Other Sources > From Active Directory)
screenshots from Excel 2016 on left, Excel 2010 w/ Power Query Add-on right
  • A popup will ask you to enter your domain name.  It should already be populated with the correct information but if not, input your companies domain name & Click OK:

  • In the Navigator that opens up, expand your domain tree and double click group:


  • You should now see a list of all groups in Active Directory in the Power Query Editor:

  • In order to find & filter for a specific Group, Click the Down-Arrow for the 'distinguishedName' column > Text Filters > Contains...

  • Type the name of your Group & hit OK (Text box is case sensitive!):
Here I am looking for users of the group 'Enable_EB_Screensaver'.

  • Now we want to list all of the staff that are members of the group.  To do this, Expand the group column and unselect everything except the 'member' column:

  • Now expand the group.member column:

You should now have a row for every member inside the group:

Right-Click the group.member column and select 'Unpivot Columns'

  • You should now see a 'Value' column at the end of your spreadsheet.  Click the expand button and select the following columns: displayName, sAMAccountName
  • Now you should have a nice list of all staff that are members of your Active Directory Group!  Click 'Close & Load' to load the data into Excel!


Quick Method How To:

If you're already a bit of a wiz with Power Query, simply copy this code into the Advanced Editor and replace anything highlighted in RED with your own organisational information.

let
    Source = ActiveDirectory.Domains("[DomainName]"),
    [DomainName] = Source{[Domain="[DomainName]"]}[#"Object Categories"],
    group1 = [DomainName]{[Category="group"]}[Objects],
    #"Filtered Rows" = Table.SelectRows(group1, each Text.Contains([distinguishedName], "[ActiveDirectoryGroupName]")),
    #"Expanded group" = Table.ExpandRecordColumn(#"Filtered Rows", "group", {"member"}, {"group.member"}),
    #"Expanded group.member" = Table.ExpandListColumn(#"Expanded group", "group.member"),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded group.member", {"displayName", "top", "posixGroup", "msExchIMRecipient", "msExchBaseClass", "msExchCustomAttributes", "mailRecipient", "securityPrincipal", "distinguishedName"}, "Attribute", "Value"),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Unpivoted Columns", "Value", {"displayName", "sAMAccountName"}, {"Value.displayName", "Value.sAMAccountName"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Value",{"displayName", "top", "posixGroup", "msExchIMRecipient", "msExchBaseClass", "msExchCustomAttributes", "mailRecipient", "securityPrincipal", "distinguishedName", "Attribute"})
in
    #"Removed Columns"


Notes

Feedback
How did you go?  Loving it?  If so, check out my other posts on Power Query:

7 comments:

  1. Thanks very much mate. Just what I was looking for. Clear, concise and easy to follow.

    ReplyDelete
    Replies
    1. thanks Hoggy appreciate the feedback!

      Delete
  2. hi - is it possible to setup the query to read the domain and the group name from two cells that contain the domain and group name information in tab1 and in tab2 to have the group be expanded with its members?

    ReplyDelete
    Replies
    1. Hi Jose, It sounds like what you're asking is to have two separate spreadsheets and show group name in one spreadsheet tab, and group members in the second spreadsheet tab.

      Should be possible but may need two separate Power Queries.

      Alternatively, you could keep the distinguished name column and possibly create a pivot table to show list of users based on which group you have selected?

      Delete
  3. What if we have a group with nested DL's, can we query for indirect members?

    ReplyDelete