Forum Discussion
PowerBI to Query AD Group Memberships
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.
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
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.
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
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 Anonymous ,
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.
