AD useraccountcontrol integer conversion

[niark]

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 !

[phillip_from_oz]

I’m trying to do bitwise operation on the userAccountControl field of Active Directory(AD) user table.

I’ve successfully loaded the AD user table.

I’m using this article:

https://community.powerbi.com/t5/Desktop/AD-useraccountcontrol-integer-conversion/td-p/437105

but having trouble doing this step:

This is where I’m at:

But the “Insert Step After” is blanked out.

Please how do I fix this?

[KenSkinner]

Wow - that totally works!  I've never tried working with a custom function before but you've definitely opened a cool door.  Thanks!

[v-yulgu-msft]

Hi @niark,

Please provide more details about source data in Power BI and the result table you want to achieve.

How to Get Your Question Answered Quickly

regards,

Yuliana Gu

[niark]

Hi,

the sources :

tableName = User

content =

displayName	userAccountControl
User1	66082
User2	514
User3	66048
User4	512
User5	66080

tableName = UAC_Flags

content =

Flag	Hex	Dec
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
INTERDOMAIN_TRUST_ACCOUNT	0x0800	2048
WORKSTATION_TRUST_ACCOUNT	0x1000	4096
SERVER_TRUST_ACCOUNT	0x2000	8192
RESERVED
RESERVED
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
PARTIAL_SECRETS_ACCOUNT	0x04000000	67108864
RESERVED
RESERVED
RESERVED
RESERVED
RESERVED

expected result :

displayName	userAccountControl	Flags
User1	66082	ACCOUNTDISABLE | PASSWD_NOTREQD | NORMAL_ACCOUNT | DONT_EXPIRE_PASSWORD
User2	514	ACCOUNTDISABLE | NORMAL_ACCOUNT
User3	66048	NORMAL_ACCOUNT | DONT_EXPIRE_PASSWORD
User4	512	NORMAL_ACCOUNT
User5	66080	PASSWD_NOTREQD | NORMAL_ACCOUNT | DONT_EXPIRE_PASSWORD

[PBInewbie21]

@niark  - Thanks for the solution. I have time understanding the M code but followed all most steps. I'm still not sure how you got table UAC_Flags? Does the code you provided generates it or got it from somewhere else?

[niark · Accepted solution]

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 🙂

[marshgm]

I really like this, but I ams struggling on this. After I create the new blank query and paste the code above. It doesnt give me the option to right click in applied steps. It want's me to enter Parameters. Do you have any screenshots or anything that can help me with this? I would really appreciate it. 

[Anonymous]

Hi,

Just wanted to thank you for the solution you provided to the other person. After much headscratching I worked out how to use your solution. All I have to say is you must be really smart 🙂 I wonder if the Power BI people will integrate your idea in to Power BI AD interface so it can translate the Microsoft hex codes in to human readable format.

Ciao

[Anonymous]

@niark Thanks for following through with the solution. I'm having trouble implementing the last step of your solution within PowerBI. I have the data and columsn in powerBI, but how are you invoking the PS script you developed? Any guidance would be appreciated. 

[niark]

Hi @Anonymous ,

the code has to be used as a function.

please follow these steps :

go to "edit queries"

create a new blank query

open the advanced editor

paste the code as is

name the query as you want (ei:fnConvertUAC)

in the "applied step" pane add a new step (right click) and paste the content of the formula : #"Invoked Custom Function1" = Table.AddColumn(<Previous Step>, <Name of the column to create>, each fnConvertUAC([userAccountControl], UACRef, "decimal", "|"))

where

[userAccountControl] = the column where the data are

UACRef = the table having the references (headers are "flag" "hexadecimal" "decimal") in my previous post

"decimal" = the column to use to compare (as userAccountControl is an [int] the decimal column should be used)

"|" = the separator you want

Hope it's clearer.

and sorry for the delay, the email sent by the forum was under my spams...

Regards

[bagurudeen]

Hi,

I am getting the below error when I invoke the function.

Function:

= Table.AddColumn(user, "UAC", each fnConvertUAC([userAccountControl], UAC_Flags, "decimal", "|"))

Error:

Expression.Error: The column 'decimal' of the table wasn't found.
Details:
decimal

[niark]

Hi bagurudeen,

« decimal » is the name of the column containing the values. Power query is a case sentive language be careful with such mistakes.

Regards

[bagurudeen]

Thank you very much for your quick response.

Now when I run the function, its creating an empty column. 

User table:

displayNameuserAccountControl

User1	66082
User2	514
User3	66048
User4	512
User5	66080

Applied steps

= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLTJU0lEyMzOwMFKK1YGIGAFFTA1N4HxjiAoTC7iICVgFQocp1AwDpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [displayName = _t, userAccountControl = _t])

= Table.TransformColumnTypes(Source,{{"userAccountControl", Int64.Type}})

UAC_Flags table:

FlagHexDec

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		null
INTERDOMAIN_TRUST_ACCOUNT	0x0800	2048
WORKSTATION_TRUST_ACCOUNT	0x1000	4096
SERVER_TRUST_ACCOUNT	0x2000	8192
RESERVED		null
RESERVED		null
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		null
PARTIAL_SECRETS_ACCOUNT	0x04000000	67108864
RESERVED		null
RESERVED		null
RESERVED		null
RESERVED		null
RESERVED		null

Applied steps:

= Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZPBjuMgDIZfpcp5DkBIQo8MeFrUBDLgTKeqRrzGPv4SSNqm073tIRLCP/bn3871WgXlzYjVW0X+EEJoOtDq5+1aSaXcZFGbIN97WOMsHViOewjgv0CvEZ4OPEeObgBtfPTwORl/V4h0EFnRO3Vy01qUkrlomyOjDOGso3WYXq8v2Syo2aNASYtRHaU9rGh8FrWFAKzylxFBR4RvjGN6IPvenW8sIpdkhQZhGKOext4oiRCXxouSklnJmkJnnR9kvyp2RcKypKG/bNnlb740FsFrN0hjI/op4FMKUaoQXoDOzp8CSjTuSZ7VCWlWc7IvULmef5WXFaWg+y3bbgP3+la7ZDB8j2mAMXvuvN7d6mevm6YuBIMNsXeHRPurfPa5pqQrBGGQHpX0erscfFGyllFeRpjbSQP8cD5q6OGQ7SiJxSJvGGdCLJPBVbbkzJi5fLK16QrpFCDJQjzBJTrbXx5AywT2HW3Y3YBEGUcPcsLjHTS7T/e8Jvy+ksmexS79AJntr4Voidh0hS7OOZ+6e+Auv0TXdWz5MV7u1ZjcNGkhAygPGLary2+J2o6SxMD/nei/X/78BQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Flag = _t, Hex = _t, Dec = _t])

= Table.TransformColumnTypes(Source,{{"Flag", type text}, {"Hex", type text}, {"Dec", Int64.Type}})

= Table.TransformColumnTypes(#"Changed Type",{{"Dec", type number}})

Your code:

= (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

Invoked fucntion:

= Table.AddColumn(user, "UAC", each fnConvertUAC([userAccountControl], UAC_Flags, "Dec", "|"))

Result:

displayNameuserAccountControlUAC

User1	66082
User2	514
User3	66048
User4	512
User5	66080

Dont know what I am doing wrong, appreciate you help. Thank you again.

[CyberPro]

If you are still having trouble, a small tweak to the reference table.  Make the column names (i.e. Flag, Hex, Dec) all lower case | flag hex dec - this will fix the error!