Forum Discussion

Balkrishnan's avatar
Balkrishnan
Helper II
6 years ago
Solved

Active Directory

I am looking for a report where I can see active users, inactive disabled users etc
Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    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.

v-xicai's avatar
v-xicai
Community Support
6 years ago

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's avatar
    Balkrishnan
    Helper II
    6 years ago

     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's avatar
      jkljoijojoijoij
      New Member
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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.