cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Brian_M
Responsive Resident
Responsive Resident

Get Data - Active Directory

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 

 

2 ACCEPTED SOLUTIONS
Greg_Deckler
Super User IV
Super User IV

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

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Brian_M
Responsive Resident
Responsive Resident

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

View solution in original post

13 REPLIES 13
Brian_M
Responsive Resident
Responsive Resident

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

View solution in original post

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?

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

Please kindly help with this 

 

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

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?
 
 

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
Responsive Resident
Responsive Resident

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!

@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

 

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?

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

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.

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
Super User IV
Super User IV

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

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors