none
Power Query Active Directory - how to pull a list of users email and network ID RRS feed

  • Question

  • I love using Power Query to pull data from Active Directory. Very useful for group management and the like. I'm stuck though when trying to pull a users network ID. I can't seem to find that field. For simplicity, lets say I need to report all users with their email address and network id. Assuming a domain of "MyCo" I need to see:

    john.doe@myco.com     myco\doej1

    jane.doe@myco.com     myco\doej2

    Can someone guide me to where I can find the network id using Power Query with Active Directory as the source???


    -PentaGalDad

    Friday, October 27, 2017 3:03 PM

Answers

  • Just to define some terms, you are looking for:

    email    domain\alias

    You should be able to get all the information from the user object.

    email: You can expand the user column to expose the mail field which is the primary email address.

    alias: You can expand the securityPrincipal column to expose the sAMAccountName

    domain: This is where it gets a little tricky.  The domain name should be the first DC segment of the distinguishedName column.  You can get that value by adding a custom column with this formula
    Text.Middle(List.First(List.Select(Splitter.SplitTextByDelimiter(",")([distinguishedName]), (value) => Text.StartsWith(value, "DC="))), 3))

    Saturday, October 28, 2017 6:45 AM

All replies

  • Do you know how to use regular AD commands? If you can the right command, you could use the technique I cover here. https://exceleratorbi.com.au/power-query-command-screen-output/

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Saturday, October 28, 2017 4:08 AM
  • Just to define some terms, you are looking for:

    email    domain\alias

    You should be able to get all the information from the user object.

    email: You can expand the user column to expose the mail field which is the primary email address.

    alias: You can expand the securityPrincipal column to expose the sAMAccountName

    domain: This is where it gets a little tricky.  The domain name should be the first DC segment of the distinguishedName column.  You can get that value by adding a custom column with this formula
    Text.Middle(List.First(List.Select(Splitter.SplitTextByDelimiter(",")([distinguishedName]), (value) => Text.StartsWith(value, "DC="))), 3))

    Saturday, October 28, 2017 6:45 AM
  • AWESOME!!!

    I have looked for NetworkID many times and always came up short. Seems so easy now. <head slap>


    -PentaGalDad

    Friday, February 9, 2018 5:17 PM