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

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

Accepted Solutions
Super User
Super User

Re: Get Data - Active Directory

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

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

Highlighted
Brian_M
Advisor

Re: Get Data - Active Directory

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

4 REPLIES 4
Super User
Super User

Re: Get Data - Active Directory

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

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

View solution in original post

Highlighted
Brian_M
Advisor

Re: Get Data - Active Directory

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

benswift Regular Visitor
Regular Visitor

Re: Get Data - Active Directory

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 Occasional Visitor
Occasional Visitor

Re: Get Data - Active Directory

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.

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)