Get Data - Active Directory

[Brian_M]

Hi,

I am trying to query active directory as a data source. I have only Azure AD with my automatic "company1.onmicrosoft.com" and my custom domain "company1.uk"

I am using the following "Get Data"

1. Get Data > Active Directory

I've tried entering both domains and neither seems to be recognised "domain not recognised"

Unable to Connect: We encountered an error while trying to connect. Details: "Active Directory: The active directory domain 'company1.uk' couldn't be found."

My questions are:

1. Can I query Azure Active Directory using Power BI?

2. How should I correctly enter my active directory domain?

3. Which credential should I use. Will it be the Microsoft account used on the Azure Portal (as opposed to my O365 Work Account).

Many thanks for your help 

[Greg_Deckler · Accepted solution]

I would try the graph API: https://msdn.microsoft.com/en-us/library/azure/ad/graph/api/api-catalog

[Brian_M · Accepted solution]

Following @Greg_Deckler's lead I search and found a post which I've ironed out a copy paste issue plus created an additional function and parameterised the lot to try to save any future folk some time.

I'll link to the post that lead me to this solution. Thanks to @curth for the solution and @Evogelpohl for the original question..

My first function calls the users in my domain with MyDomain as an input parameter - it will ask for your Azure AD credentials (NB: my Work Account worked for me as I have Admin rights, didn't need to use Azure Portal Admin credentials or anything like that)

(MyDomain as text) =>

let

    Source = Json.Document(Web.Contents("https://graph.windows.net/" & MyDomain & "/users?api-version=1.6")),
    value = Source[value],
    ConvertedtoTable = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedColumn1 = Table.ExpandRecordColumn(ConvertedtoTable, "Column1", {"userPrincipalName", "userType"}),
    GetUserDetails = Table.AddColumn(ExpandedColumn1, "UserDetails", each if [userType] = "Member" then try fnGetUserDetails([userPrincipalName],MyDomain) otherwise null else null),
    ExpandUserDetails = Table.ExpandTableColumn(GetUserDetails, "UserDetails", {"City", "Country", "Department", "Name", "Email", "Postal Code", "State", "Street Address"}, {"Custom.City", "Custom.Country", "Custom.Department", "Custom.Name", "Custom.Email", "Custom.Postal Code", "Custom.State", "Custom.Street Address"})
in

    ExpandUserDetails

My first function calls a second function fnGetUserDetails which takes the userPrincipalName for all users of type 'Member' (and not guest) and returns user details. The fnGetUserDetails is as follows:

let

  FNLookupAD = (EmailAddress as text,MyDomain as text) =>

    let

      Source = Json.Document(Web.Contents("https://graph.windows.net/" & MyDomain & "/users?api-version=1.5", [
      Query = [#"$filter"="proxyAddresses/any(c:c eq 'smtp:" & EmailAddress & "')"]])),
      value = Source[value],
    #"Converted to Table" = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"odata.type", "objectType", "objectId", "deletionTimestamp", "accountEnabled", "assignedLicenses", "assignedPlans", "city", "companyName", "country", "creationType", "department", "dirSyncEnabled", "displayName", "facsimileTelephoneNumber", "givenName", "immutableId", "jobTitle", "lastDirSyncTime", "mail", "mailNickname", "mobile", "onPremisesSecurityIdentifier", "otherMails", "passwordPolicies", "passwordProfile", "physicalDeliveryOfficeName", "postalCode", "preferredLanguage", "provisionedPlans", "provisioningErrors", "proxyAddresses", "sipProxyAddress", "state", "streetAddress", "surname", "telephoneNumber", "thumbnailPhoto@odata.mediaContentType", "usageLocation", "userPrincipalName", "userType"}, {"Column1.odata.type", "Column1.objectType", "Column1.objectId", "Column1.deletionTimestamp", "Column1.accountEnabled", "Column1.assignedLicenses", "Column1.assignedPlans", "Column1.city", "Column1.companyName", "Column1.country", "Column1.creationType", "Column1.department", "Column1.dirSyncEnabled", "Column1.displayName", "Column1.facsimileTelephoneNumber", "Column1.givenName", "Column1.immutableId", "Column1.jobTitle", "Column1.lastDirSyncTime", "Column1.mail", "Column1.mailNickname", "Column1.mobile", "Column1.onPremisesSecurityIdentifier", "Column1.otherMails", "Column1.passwordPolicies", "Column1.passwordProfile", "Column1.physicalDeliveryOfficeName", "Column1.postalCode", "Column1.preferredLanguage", "Column1.provisionedPlans", "Column1.provisioningErrors", "Column1.proxyAddresses", "Column1.sipProxyAddress", "Column1.state", "Column1.streetAddress", "Column1.surname", "Column1.telephoneNumber", "Column1.thumbnailPhoto@odata.mediaContentType", "Column1.usageLocation", "Column1.userPrincipalName", "Column1.userType"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Column1",{"Column1.odata.type", "Column1.objectType", "Column1.objectId", "Column1.deletionTimestamp"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.accountEnabled", "Account Enabled"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns",{"Column1.assignedLicenses", "Column1.assignedPlans"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns1",{{"Column1.city", "City"}, {"Column1.companyName", "OA-FA"}, {"Column1.country", "Country"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns1",{"Column1.creationType"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"Column1.department", "Department"}}),
    #"Removed Columns3" = Table.RemoveColumns(#"Renamed Columns2",{"Column1.dirSyncEnabled"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns3",{{"Column1.displayName", "Name"}}),
    #"Removed Columns4" = Table.RemoveColumns(#"Renamed Columns3",{"Column1.facsimileTelephoneNumber", "Column1.givenName", "Column1.immutableId"}),
    #"Renamed Columns4" = Table.RenameColumns(#"Removed Columns4",{{"Column1.jobTitle", "Title"}}),
    #"Removed Columns5" = Table.RemoveColumns(#"Renamed Columns4",{"Column1.lastDirSyncTime"}),
    #"Renamed Columns5" = Table.RenameColumns(#"Removed Columns5",{{"Column1.mail", "Email"}}),
    #"Removed Columns6" = Table.RemoveColumns(#"Renamed Columns5",{"Column1.mailNickname", "Column1.mobile", "Column1.onPremisesSecurityIdentifier"}),
    #"Expanded Column1.otherMails" = Table.ExpandListColumn(#"Removed Columns6", "Column1.otherMails"),
    #"Removed Columns7" = Table.RemoveColumns(#"Expanded Column1.otherMails",{"Column1.otherMails", "Column1.passwordPolicies", "Column1.passwordProfile", "Column1.physicalDeliveryOfficeName"}),
    #"Renamed Columns6" = Table.RenameColumns(#"Removed Columns7",{{"Column1.postalCode", "Postal Code"}}),
    #"Removed Columns8" = Table.RemoveColumns(#"Renamed Columns6",{"Column1.preferredLanguage", "Column1.provisionedPlans", "Column1.provisioningErrors"}),
    #"Expanded Column1.proxyAddresses" = Table.ExpandListColumn(#"Removed Columns8", "Column1.proxyAddresses"),
    #"Removed Columns9" = Table.RemoveColumns(#"Expanded Column1.proxyAddresses",{"Column1.sipProxyAddress"}),
    #"Renamed Columns7" = Table.RenameColumns(#"Removed Columns9",{{"Column1.state", "State"}, {"Column1.streetAddress", "Street Address"}}),
    #"Removed Columns10" = Table.RemoveColumns(#"Renamed Columns7",{"Column1.surname", "Column1.telephoneNumber", "Column1.thumbnailPhoto@odata.mediaContentType", "Column1.usageLocation", "Column1.userPrincipalName", "Column1.userType"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns10", {"Name"})
in
    #"Removed Duplicates"
in
    FNLookupAD

Thanks to all for your help. Here's a link to @Evogelpohl's original post

[Anonymous]

Hey @Brian_M 

Thanks for sharing the code. It works nicely but i have an issue, it returns only first 100 users (same issue as @no_one_but_me ). Tried to use parameter $top but its seems it is not accepted in current version of API. Do you maybe any idea how it can be passed or show all users in Tenant (domain)?

@no_one_but_me Maybe you found a solution to this one, if yes can you please explain how you solved it?

@Greg_Deckler do you have any additional advice about parameters that we can pass in this query?

Patryk

[FilipTop]

Is it possible to export info about managers for all users? 

[no_one_but_me]

Hey guys,

I have used the same query to get the user details but that is loading only the top 100 user details I don't know why

Can you help me with this?

[no_one_but_me]

Can I get the hierarchical structure from the Active Directory from power bi with the help of this code

If yes, Please help me 

Thanks in advance

[no_one_but_me]

Please kindly help with this 

or if I can get the "reports to" info from the above query 

[joseabarriga]

Hi Brian:

Is there a way to get the user email from the logged user and passed to your fnGetUserDetails function? With DAX, by using  

USERNAME() or USERPRINCIPALNAME(), you can have the user's logged email but I don´t know how to pass this value as a parameter to your fnGetUserDetails instead of typing it.

Any idea?

[joseabarriga]

Well, I found the answer to my question: If you want to get all the AAD information of the logged user, us the graph interface :

https://graph.windows.net/{domain}/me?api-version=1.6

Here you can find more info https://docs.microsoft.com/en-us/graph/api/user-get?view=graph-rest-1.0&tabs=http . Unfortunately, as usual, though it says that is updated, is not. Is version 1.0

[Johanno]

Hi, thanks to @Brian_M  for his thorough answer. If I copy paste this it works by collecting AD data from the person entered as parameter in Power Query. But how do you get information for the current logged on user? I see that @joseabarriga has solved this and I tried to understand the information in the link but I get stuck.

I want:

1. Get a list of all AD users with e-mail, city and department

or of this can't be done:

2. Get e-mail, city and department from only the logged on user

Thank you in advance!

[joseabarriga]

@Johanno 

What I did was use the Microsoft Graph REST API described in https://docs.microsoft.com/en-us/graph/api/user-get?view=graph-rest-1.0&tabs=http .

If you want all the data from all the users in your domain, the command is:

https://graph.windows.net/< your domain>/users?api-version=1.6

For just the logged user will be 

https://graph.windows.net/< your domain>/me?api-version=1.6

To test it I suggested using PostMan

https://docs.microsoft.com/en-us/graph/use-postman?context=graph%2Fapi%2Fbeta&view=graph-rest-beta

Finally, be aware the there are two API with the same results which are the Azure AD Graph and Microsoft Graph ( Just that) ... take a look at https://docs.microsoft.com/en-us/graph/migrate-azure-ad-graph-resource-differences

[DebbieE]

this to me means that the Active Directory in Get Data is really misleading because it doesnt work. is that the case?

Because I have the same issue and I dont want to use Graph because I should be able to use the connector as stated in power BI?

However, I used the Primary Domain I found in Azure Active Directory. I used 

compname.onmicrosoft.com

And it didnt work. got rid of onmicrosoft.com.... and it still didnt work. If this connector doesnt work then surely it should be removed. If it does work what am I doing wrong?

[Anonymous]

Hi @Brian_M,

I am trying to connect to AAD from Power BI Desktop, but am hoping this can be done through the Desktop UI as I have no expertise in making calls to the Graph API.

I am getting the same message you were "The active directory domain x couldn't be found" (where x is the onmicrosoft domain you are given when Power BI is the only thing you're running off AAD). (See attached.) I've tried other domains we have, but definitely the one I would expect to work is not found.

Any suggestions?

Thanks,

Ben

[jhaller]

Hi,

I'm looking for the same thing i.e. get data from Azure AD. For business needed, I've build a report with data from our timesheeting tool. A big plus would be to be able to create a filter on departments. As this notion of departments already exist in AAD, I would like to use the AAD data to avoid having a static table in my report that would need updates each time a person leave or join us.
Do you have any info on how to get data from AAD and store them in a datawarehouse ?

Thanks,
Julien.

[TePe]

Hi,

did you somehow solve this issue? I'm looking for  exporting Users, Groups and their Memberships for some custom security scenario. 

The solution might be a combination of the M code provided above together with Data Flows storing the data in a text format in the CDM folders as part of your Data Lake. For me that would be just perfect...

Thanks,

Thomas

[Greg_Deckler · Accepted solution]

I would try the graph API: https://msdn.microsoft.com/en-us/library/azure/ad/graph/api/api-catalog