Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
CPhelan
Frequent Visitor

PowerBI to Query AD Group Memberships

Hello,

 

I'm relatively new to powerbi.  I came up with a series of powerbi reports to show Active Directory group memberships.  I used powershell to querry a list of users of an AD group then return their full group memberships,  along with SAMaccountname, Mail, UPN, First Name, Last Name, Title, Department, Division, Department Number, Primary Affiliation, and whether the user account is Enabled.  The data from the script is exported to a CSV file then I build my report in powerbi.  This is not a very eligant solution but it works for my first intention which was to report which retired staff still had access across my organization. These powerbi reports allow me to easily querry user group memberships at a glance instead of running a 1:1 script.  The biggest downside, I need to re-run the powershell script then re-import the tables in my report to get the most current data.  I've got 56 different reports so far.  

 

Now what I'd like to do is use the Active Directory data source directly in PowerBI, so I have the most current data and replicate the functionality of the individual reports.  This has proven to be a pretty difficult task.  I've added these Tables from the AD source: Group, inetOrgPerson, and user so far.  

 

my biggest obstacles:

show enabled and disabled user accounts

Connect the Group table to inetOrgPerson in order to return user Group memberships.  

 

I would apreciate any help at all.

 

Charles

1 ACCEPTED SOLUTION
CPhelan
Frequent Visitor

Hi  @v-alq-msft ,

 

There is an active directory connector in Get Data from PowerBI Desktop.  I was able to solve my issues. The first step:  I selected these tables from the AD connector AD: Group, InetorgPerson, User.  The next step was to expand the right columns.

Group Table

expand column Member and select these fields: Display Name, Member Of, Department,  EduPersonPrimary, SamAccountName, User Account Control,

 

InetorgPerson Table

Expand column EduPerson and select this fields: EduPersonPrimaryAffiliation

Expand column SamAccountName and select this field: Security Principal

Expand column  OrganizationalPerson and select these fields: Department, Division, Given Name, Title

 

User Table

Expand column: User and select these fields:  DepartmentNumber,  UserPrincipalName, UserAccountControl.

Expand column: Person and select this field:  SN (this is the surname attribute)

Expand column: SecurityPrincipal and select this field: SamAccountName

 

I linked all the tables: Group, InetorgPerson, and User using SamAccountName

 

My next issue was filtering active and inactive accounts.  I read another post by @niark Solved: AD useraccountcontrol integer conversion - Microsoft Power BI Community to figure out the integer conversion for the user account control. 

CPhelan_2-1610990886620.png

 

I opted to create a spreadsheet with the converted user account integers

 

The last step was to link the user table and my spreadsheet using UACProperties, then group active account and disabled accounts.

I use the UACProperties to filter the active accounts on each report page.

 

In my daily tasks as an enterprise desktop admin, I regularly run powershell applets to get AD user group membership and add or remove users from groups.  PowerBI has allowed me to get a much bigger picture of the users, which groups they belong to and discover issues across my organization.

 

Charles

 

 

View solution in original post

14 REPLIES 14
CPhelan
Frequent Visitor

Hi @santh00 ,

 

Even though the SamAccountName in groups and Users do not match, its important to create the link between them.  Double click on the link and make sure the "Cardinality" is set "Many to Many" and cross filter direction set to both. Hope this helps

 

Charles

 

SAM relationship between Group and User.png

 

 

 

 

CPhelan
Frequent Visitor

Hi  @v-alq-msft ,

 

There is an active directory connector in Get Data from PowerBI Desktop.  I was able to solve my issues. The first step:  I selected these tables from the AD connector AD: Group, InetorgPerson, User.  The next step was to expand the right columns.

Group Table

expand column Member and select these fields: Display Name, Member Of, Department,  EduPersonPrimary, SamAccountName, User Account Control,

 

InetorgPerson Table

Expand column EduPerson and select this fields: EduPersonPrimaryAffiliation

Expand column SamAccountName and select this field: Security Principal

Expand column  OrganizationalPerson and select these fields: Department, Division, Given Name, Title

 

User Table

Expand column: User and select these fields:  DepartmentNumber,  UserPrincipalName, UserAccountControl.

Expand column: Person and select this field:  SN (this is the surname attribute)

Expand column: SecurityPrincipal and select this field: SamAccountName

 

I linked all the tables: Group, InetorgPerson, and User using SamAccountName

 

My next issue was filtering active and inactive accounts.  I read another post by @niark Solved: AD useraccountcontrol integer conversion - Microsoft Power BI Community to figure out the integer conversion for the user account control. 

CPhelan_2-1610990886620.png

 

I opted to create a spreadsheet with the converted user account integers

 

The last step was to link the user table and my spreadsheet using UACProperties, then group active account and disabled accounts.

I use the UACProperties to filter the active accounts on each report page.

 

In my daily tasks as an enterprise desktop admin, I regularly run powershell applets to get AD user group membership and add or remove users from groups.  PowerBI has allowed me to get a much bigger picture of the users, which groups they belong to and discover issues across my organization.

 

Charles

 

 

Several of these columns were missing within the tables mentioned. I am trying to pull this together for a report and am really struggling.

Hi @BayliCapri ,

Once you have selected the tables  from the AD connector AD: Group, InetorgPerson, User.  The next step is to expand the columns you need:

 

Click on Table View on the left side, and click the Home menu, then click "Transform Data"

 

In order to find the attributes you need for the report you'll need to expand Group, Select Member, expand group member

Screenshot 2024-10-16 150845.png

 

The query should look something similar to

= Table.ExpandRecordColumn(#"Expanded group.member", "group.member", {"displayName", "memberOf", "department", "sAMAccountName", "userAccountControl",

Hi we have build a report by using Active Directory we can able to see group type

  1. Mail enabled Security
  2. Distribution

    but we are unable to see

    3. Security and 4. Microsoft 365 group type
    do we have any alternate table or names for this  or it wont support can any one help on this 

Ls,

 

Many Thanks. But I miss some column (the Bold ones) :

 

Group Table

expand column Member and select these fields: Display Name, Member Of, Department,  EduPersonPrimary, SamAccountName, User Account Control,

 

InetorgPerson Table

Expand column EduPerson and select this fields: EduPersonPrimaryAffiliation

Expand column SamAccountName and select this field: Security Principal

Expand column  OrganizationalPerson and select these fields: Department, Division, Given Name, Title

 

User Table

Expand column: User and select these fields:  DepartmentNumber,  UserPrincipalName, UserAccountControl.

Expand column: Person and select this field:  SN (this is the surname attribute)

Expand column: SecurityPrincipal and select this field: SamAccountName

 

What did I do wrong ?

Hi @Ivo_Jansen ,

 

Sorry I'm just repling to you now.  I explained to BayliCapri  that

Once you have selected the tables from the AD connector AD: Group, InetorgPerson, User. The next step is to expand the columns you need:

 

Click on Table View on the left side, and click the Home menu, then click "Transform Data"

 

In order to find the attributes you need for the report you'll need to expand Group, Select Member, expand group member.  My Post to BayliCapri has a few screen shots

None of the mentioned columns other than security was in the inetorgPerson table. I have everything else but the columns I needed there.

Hi @BayliCapri 

 

Here is a screenshot of some of the expanded tables in inetorgPerson.  It took me a while to find the attributes too and what is also a bit maddening is that you may have to format the expanded table as text and sometimes split the collum. 

 

InetOrgPerson_attributes_to_expand.jpg

Hi @CPhelan  SamAccountName in groups and Users are different for me is there any other common column that we can use and also I didn't find any column other than SamAccountName in groups that you mentioned and display name is also empty

 

My requirement is to get user email and which group they belong to in organization using active directory...I get users and group in two tables but didn't able to merge 

This post really helped me today, Charles. Thanks very much for sharing the results of your spelunking with us.

v-alq-msft
Community Support
Community Support

Hi, @CPhelan 

 

Based on my research, there is no Azure Active Directory connector in powerbi desktop currently. You could try howto-use-azure-monitor-workbooks to get azure ad data.

 

Best Regards

Allan

Anonymous
Not applicable

There is an AD connector and you can access groups and users exactly as the commenters above explained. Not sure why you'd send people off to that irrelevant link after saying it's not possible?

I am trying to work through this as well and I believe the AD connector is only for on-prem AD, not Azure AD.

I am also trying to retrieve group membership from Azure AD and have not yet found a direct connection in PowerBI or PowerApps. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.