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: