Recently my colleague had to identify inactive accounts in Active Directory and use this data in automated report on regular basis, to quickly react on new switched off users.
There are plenty of VBA solutions or Vbscripts that work with Active Directory and pull data from it.
However, for automated reports developed in Excel or Power BI it will be more convenient to use Power Query (Get & Transform) to get data directly from AD. In addition this helps to reduce mirroring of data.
Information about user status is stored in field UserAccountControl.
According to documentation on MSDN, field value is not very user friendly
“To disable a user’s account, set the UserAccountControl attribute to 0x0202 (0x002 + 0x0200). In decimal, this is 514 (2 + 512).”
Resulting value is a sum of different values of multiple properties.
E.g. what 2146 could mean?
Not easy to say when number is in decimal notation.
However, it is much easier if value of UserAccountControl is represented in binary string – of 0 and 1.
2146 = 2048 + 64 + 32 + 2 = > 100001100010
For example, if we need to check property ACCOUNTDISABLE, we only need to check second digit (from right).
Unfortunately, there is no standard function in Power Query that converts decimal number to binary notation, so I had to create own function.
As usually, it is available on Github: https://github.com/IvanBond/pquery/blob/master/Number.ToBinaryString.m
// Number.ToBinaryString( 1026 )
// result: 10000000010
Function code is quite short, although it is recursive
When number is in binary notation, we can use Text.End, Text.Start function to get needed digit.
Just an idea. Can be used with Power BI alerts to unblock self-blocked users even before they call to IT, for audit, or with Microsoft Flow somehow…