Forum Discussion
Active Directory useraccountcontrol bit mask decode function
In the custom function, input is expected to be of number type. If you don't want to keep non-numeric values in userAccountControl column, you can change this column into Decimal Number type. Blank values will become null and non-numeric values will become Error. Replace Errors with null.
Then when you add the custom function column, check whether the input value is null. When it's not null, invoke the custom function.
= Table.AddColumn(#"Replaced Errors", "UAC", each if [userAccountControl] = null then null else fnConvertUAC([userAccountControl], UACRef, "decimal", "|"))If you want to keep non-numeric values in userAccountControl column, you need to keep this column of Text type. Then extract decimal number to invoke custom function when you add the column.
= Table.AddColumn(#"Changed Type", "UAC", each let __number = Number.From([userAccountControl]) in if (try Value.Is(__number, type number) otherwise false) then fnConvertUAC(__number, UACRef, "decimal", "|") else null)Hope it helps!
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Thanks for the reply.
user.userAccountControl is already numeric when I setup the connection from my Active Directory
I did
Get data->Blank Query
Pick the user table
Go to Applied Steps section on the right
Pick last instruction.
Right Click
Pick “Insert Step After”
Add this code
Table.AddColumn(#"Expanded user", "UAC_flag", each if [user.userAccountControl] = null then null else fnConvertUAC([user.userAccountControl], flaghexadecimaldecimal, "Dec", "|"))
Pick Close and Apply
UAC_Flag is still empty even when user.userAccountControl is not
Why is that? How do I fix this?
Hi,
I've found out why the column is blank in that there is a bug in the fnConvertUAC.
I ran the code as given against the number 512 (fuction invoke) and it returned a blank.
So I recoded the entire function so there is now no table lookup. All bit operations are done by explicit code. It runs much faster as well.
(n as number) as text =>
let
result2 = Number.BitwiseAnd(n,1),
val2=if result2 > 0 then "SCRIPT" else "",
result3 = Number.BitwiseAnd(n,2),
val3=if result3 > 0 then "ACCOUNTDISABLE" else "",
result5 = Number.BitwiseAnd(n,8),
val5=if result5 > 0 then "HOMEDIR_REQUIRED" else "",
result6 = Number.BitwiseAnd(n,16),
val6=if result6 > 0 then "LOCKOUT" else "",
result7 = Number.BitwiseAnd(n,32),
val7=if result7 > 0 then "PASSWD_NOTREQD" else "",
result8 = Number.BitwiseAnd(n,64),
val8=if result8 > 0 then "PASSWD_CANT_CHANGE" else "",
result9 = Number.BitwiseAnd(n,128),
val9=if result9 > 0 then "ENCRYPTED_TEXT_PWD_ALLOWED" else "",
result10 = Number.BitwiseAnd(n,256),
val10=if result10 > 0 then "TEMP_DUPLICATE_ACCOUNT" else "",
result11 = Number.BitwiseAnd(n,512),
val11=if result11 > 0 then "NORMAL_ACCOUNT" else "",
result13 = Number.BitwiseAnd(n,2048),
val13=if result13 > 0 then "INTERDOMAIN_TRUST_ACCOUNT" else "",
result14 = Number.BitwiseAnd(n,4096),
val14=if result14 > 0 then "WORKSTATION_TRUST_ACCOUNT" else "",
result15 = Number.BitwiseAnd(n,8192),
val15=if result15 > 0 then "SERVER_TRUST_ACCOUNT" else "",
result18 = Number.BitwiseAnd(n,65536),
val18=if result18 > 0 then "DONT_EXPIRE_PASSWORD" else "",
result19 = Number.BitwiseAnd(n,131072),
val19=if result19 > 0 then "MNS_LOGON_ACCOUNT" else "",
result20 = Number.BitwiseAnd(n,262144),
val20=if result20 > 0 then "SMARTCARD_REQUIRED" else "",
result21 = Number.BitwiseAnd(n,524288),
val21=if result21 > 0 then "TRUSTED_FOR_DELEGATION" else "",
result22 = Number.BitwiseAnd(n,1048576),
val22=if result22 > 0 then "NOT_DELEGATED" else "",
result23 = Number.BitwiseAnd(n,2097152),
val23=if result23 > 0 then "USE_DES_KEY_ONLY" else "",
result24 = Number.BitwiseAnd(n,4194304),
val24=if result24 > 0 then "DONT_REQ_PREAUTH" else "",
result25 = Number.BitwiseAnd(n,8388608),
val25=if result25 > 0 then "PASSWORD_EXPIRED" else "",
result26 = Number.BitwiseAnd(n,16777216),
val26=if result26 > 0 then "TRUSTED_TO_AUTH_FOR_DELEGATION" else "",
result28 = Number.BitwiseAnd(n,67108864),
val28=if result28 > 0 then "PARTIAL_SECRETS_ACCOUNT" else "",
mylist={val2,val3,val5,val6,val7,val8,val9,val10,val11,val13,val14,val15,val18,val19,val20,val21,val22,val23,val24,val25,val26,val28},
mylist_filtered = List.Select(mylist, each _ <> ""),
myflag=Text.Combine(mylist_filtered,"|")
in
myflagps. the code was generated from a powershell program so I coded just one mask and the rest were generated by powershell
All good now!
Wow it's great! You can mark an appropriate reply as Solution to let other people find it quickly!
Best regards,
Jing