Active Directory

[Balkrishnan]

I am looking for a report where I can see active users, inactive disabled users etc

[v-xicai · Accepted solution]

Hi @Balkrishnan ,

You can connect to Active Directory from Power  BI Desktop following the instructions in this blog, load user table into Desktop.

As per this similar blog and similar thread, user account status are controlled by the userAccountControl attribute, you may expand userAccountControl column from user table in Power BI Desktop, then check the values.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Balkrishnan]

 please help me to get
a. Number of disabled users
b. Number of enabled but stale users
c. Number of enabled but locked-out users
d. Number of looped nested groups
e. Number of users with passwords that never expire
f. Number of users with removal recommendations

[jkljoijojoijoij]

I connected, but get null values for some disabled userAccountControl. When I look at the attribute in AD it's 514. Why does it show null in PowerBI. This is happening for a majority of disabled users.

[Anonymous]

514 is a bit flag for the account. 514 is a combination of bit flags (512 + 0x2). The 0x2 is means it is DISABLED.

To filter for DISABLED accounts, use the Number.BitwiseAnd() function, like this:

= Table.Select( PreviousStep , each (Number.BitwiseAnd([userAccountControl], 0x2) = 0x2 ) )

Number.BitwiseAnd will return the value you are filtering for (0x2) if it is present. If it does not exist, 0x0 is returned.