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…
Thanks for tip, however from the performance point of view wouldn’t it be better to use Bitwise And operation on the number rather than calling recursive function and converting the value to string?
Thanks for comment Pavel! You are 100% right. For large lists of data I would prefer something with better performance.
Lack of knowledge lead to not the best advice.
I knew nothing about Bitwise* functions before tweet-reply from https://twitter.com/bassilov and your comment. In addition, official documentation lacks of examples of usage.
Of course, for the task to check user statuses Number.Bitwise* functions must be better.
https://msdn.microsoft.com/en-us/library/mt253301.aspx
Pq has already got a required function: Number.BitwiseAnd
Hi Alexander, thanks for sharing! For further readers I would like to add another link: https://en.wikipedia.org/wiki/Bitwise_operation
But be ready to brain-explosion 🙂