Trial #33: Querying AD Users Group Membership with PowerBI

Problem:

Group membership can be found quite deep inside the securityPrincipal record of an AD user. There are two lists msds-tokenGroupNamesGlobalAndUniversal & msds-tokenGroupNamesNoGCAcceptable.

I found these groups lists largely symmetrical but unfortunately but both contained unique values so for completeness, we may need to Union these lists.

Furthermore, this combined list is not particularly useful until it is serialised into a single column or preferably accessed using a related table.

Solution:

First of all, as the lists msds-tokenGroupNamesGlobalAndUniversal and msds-tokenGroupNamesNoGCAcceptable are nullable we need to guard against this case and only return sub-item, such as the Common Name when there are items to iterate over.

Rather than writing the same code out twice for each list, lets make a function.

// GetGroups
= (securityPrincipal as record, itemName as text) as list =>
    if Record.Field(securityPrincipal, itemName) = null
    then {}
    else List.Transform(Record.Field(securityPrincipal, itemName), each _[cn])

We can then use this function to create a custom column combining the results of the union of the transformed lists.

= Table.AddColumn(
    #"Table1", 
    "Custom", 
    each 
       List.Union({
         GetGroups( _[securityPrincipal] ,"msds-tokenGroupNamesGlobalAndUniversal"),
         GetGroups( _[securityPrincipal] ,"msds-tokenGroupNamesNoGCAcceptable")
       })
)

At this point you may either flatten the list by serialising, if you have many columns in the table you want to use, or preferably by expansion, combining a new single purpose table back with your user table in a PowerQuery many to one relationship.

Option 1#

Select the created column and extract values

Choose a delimiter that is unlikely to occur in any of your group names, such as ';'

Option 2#

However I would strongly recommend creating a simple table to use in a relationship. For example create a table with just two columns, a foreign key such as distinguishedName or sAMAcountName. and the expanded list of groups.

Expanding the column, creates duplicate entries in the remaining columns so this method is best attempted with a limited number of columns. Perhaps just a foreign key such as ````distinguishedName```` or ````sAMAcountName.````

This method will give you far greater control of how you select on and display the security group information.

Other Posts in the PowerBI-AD Series:

• Trial #34: AD User Account Status in PowerBI Model
• Trial #33: Querying AD Users Group Membership with PowerBI
• Trial #32: Making OUs More Readable with a Related Table in PowerBI Model
• Trial #31: Transform Active Directory User CN to OU in PowerBI Model
• Trial #30: Accessing Active Directory Users in PowerBI Model