Solved: AD useraccountcontrol integer conversion - Page 2

niark · ‎06-11-2018

Hi all,

I used to use a simple PS code to translate an AD useraccountcontrol attribute value into a human readable format :

Function Translate-UAC {

Param ([int]$UAC)

$PropertyFlags = @(
	"SCRIPT", "ACCOUNTDISABLE", "RESERVED", "HOMEDIR_REQUIRED", "LOCKOUT", "PASSWD_NOTREQD", "PASSWD_CANT_CHANGE", "ENCRYPTED_TEXT_PWD_ALLOWED", "TEMP_DUPLICATE_ACCOUNT", "NORMAL_ACCOUNT", "RESERVED", "INTERDOMAIN_TRUST_ACCOUNT", "WORKSTATION_TRUST_ACCOUNT", "SERVER_TRUST_ACCOUNT", "RESERVED", "RESERVED", "DONT_EXPIRE_PASSWORD", "MNS_LOGON_ACCOUNT", "SMARTCARD_REQUIRED", "TRUSTED_FOR_DELEGATION", "NOT_DELEGATED", "USE_DES_KEY_ONLY", "DONT_REQ_PREAUTH", "PASSWORD_EXPIRED", "TRUSTED_TO_AUTH_FOR_DELEGATION", "RESERVED", "PARTIAL_SECRETS_ACCOUNT" "RESERVED" "RESERVED" "RESERVED" "RESERVED" "RESERVED"
)
#One property per line (commented because I use the second one)
#1..($PropertyFlags.Length) | Where-Object {$UAC -bAnd [math]::Pow(2,$_)} | ForEach-Object {$PropertyFlags[$_]} 

#One line for all properties (suits my script better)
$Attributes = ""
1..($PropertyFlags.Length) | ? {$UAC -bAnd [math]::Pow(2,$_)} | % {
   If ($Attributes.Length -EQ 0) {$Attributes = $PropertyFlags[$_]} 
   Else {$Attributes = $Attributes + " | " + $PropertyFlags[$_]}
}
Return $Attributes
}
Translate-UAC -UAC 66080

this function returns : PASSWD_NOTREQD | NORMAL_ACCOUNT | DONT_EXPIRE_PASSWORD

what I did in PBI, is to create a new table with all values (PASSWD_NOTREQD...) and an index column starting by 1.

the question is, from a useraccountcontrol value in another table (AD_Table) how can I filter the value from TABLE1 if the calculation of the bitwise is 'true' and from that take the 'human readable value' and concatenate them into one cell...

here manual result :

userAccountControl Column1

66082	ACCOUNTDISABLE \| PASSWD_NOTREQD \| NORMAL_ACCOUNT \| DONT_EXPIRE_PASSWORD
514	ACCOUNTDISABLE \| NORMAL_ACCOUNT
66048	NORMAL_ACCOUNT \| DONT_EXPIRE_PASSWORD
512	NORMAL_ACCOUNT
66080	PASSWD_NOTREQD \| NORMAL_ACCOUNT \| DONT_EXPIRE_PASSWORD

Thanks for your help !

niark · ‎06-22-2018

Here is the solution :

// input is the cell with the decimal value ei:512
// tableref is the table referencing the decimal values with the 'human readable' ones
// columnref is the column name of tableref where decimal values are
// delimiter is the character to split by ei:|
// the result is a text ei:PASSWD_NOTREQD|NORMAL_ACCOUNT|DONT_EXPIRE_PASSWORD
// the function can be updated to return a table/list to have it expanded for example
(input as number, tableref as table, columnref as text,delimiter as text) as text =>
let
	//create a list with all potential values. should be 32 for this purpose
	values = {1..Table.RowCount(tableref)},
	//function to have the power of a decimal value
	fnPower = (value as number) => Number.Power(2,value),
	//function to have the bitwise "and" value. this function compare the power value against the input ei: 8/512
	fnBitwise = (value as number, v as number) => Number.BitwiseAnd(value,v),
	//all values are calculated (pow2) and the bitwise and is retrieved
	//an index column is then added to get the position of the values 
	//the result is filtered to get records that are not 0 (0 means the bitwise comparison failed)
	TableResult = Table.SelectRows(
                        Table.AddIndexColumn(
			    Table.FromList(
			        List.Transform(
				    values,
				    each fnBitwise(fnPower(_),input)
				),
				Splitter.SplitByNothing(),
				null,
				null,
				ExtraValues.Error
			    ),
			    "Index",
			    1,
			    1
                        ),
			each ([Column1] <> 0)
		    ),
	//the result is merged with the reference table on the referenced column. the inner join is used to filtered out not required values
	//then the rows are merged into one cell with the positioned delimiter
	Result = Text.Combine(
                    Table.ExpandTableColumn(
                        Table.RemoveColumns(
                            Table.NestedJoin(TableResult,{"Column1"},tableref,{columnref},columnref,JoinKind.Inner),
			    {"Column1", "Index"}
			),
			columnref
			,{"flag"}
			,{"flag"}
		    )[flag],
		    delimiter
                )
in
	Result

the reference table is like this :

flaghexadecimaldecimal

SCRIPT	0x0001	1
ACCOUNTDISABLE	0x0002	2
RESERVED	0x0004	4
HOMEDIR_REQUIRED	0x0008	8
LOCKOUT	0x0010	16
PASSWD_NOTREQD	0x0020	32
PASSWD_CANT_CHANGE	0x0040	64
ENCRYPTED_TEXT_PWD_ALLOWED	0x0080	128
TEMP_DUPLICATE_ACCOUNT	0x0100	256
NORMAL_ACCOUNT	0x0200	512
RESERVED	0x0400	1024
INTERDOMAIN_TRUST_ACCOUNT	0x0800	2048
WORKSTATION_TRUST_ACCOUNT	0x1000	4096
SERVER_TRUST_ACCOUNT	0x2000	8192
RESERVED	0x4000	16384
RESERVED	0x8000	32768
DONT_EXPIRE_PASSWORD	0x10000	65536
MNS_LOGON_ACCOUNT	0x20000	131072
SMARTCARD_REQUIRED	0x40000	262144
TRUSTED_FOR_DELEGATION	0x80000	524288
NOT_DELEGATED	0x100000	1048576
USE_DES_KEY_ONLY	0x200000	2097152
DONT_REQ_PREAUTH	0x400000	4194304
PASSWORD_EXPIRED	0x800000	8388608
TRUSTED_TO_AUTH_FOR_DELEGATION	0x1000000	16777216
RESERVED	0x2000000	33554432
PARTIAL_SECRETS_ACCOUNT	0x4000000	67108864
RESERVED	0x8000000	134217728
RESERVED	0x10000000	268435456
RESERVED	0x20000000	536870912
RESERVED	0x40000000	1073741824
RESERVED	0x80000000	2147483648

and to use it, add a custom function column :

#"Invoked Custom Function1" = Table.AddColumn(#"Removed Columns", "UAC", each fnConvertUAC([userAccountControl], UACRef, "decimal", "|"))

Hope it will help.

if someone comes with a better/quicker/less code way, don't hesitate to share 🙂

View solution in original post

AD useraccountcontrol integer conversion

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly