Forum Discussion
Active Directory useraccountcontrol bit mask decode function
Everyone,
I’m working through AD useraccountcontrol integer conversion post:
https://community.powerbi.com/t5/Desktop/AD-useraccountcontrol-integer-conversion/td-p/437105
There is this function (Power BI M language) in this message and it appears below:
// 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
This does not work if the input is blank or non numeric.
For some of user rows, my AD returns blank in the User Account Control.
Questions
- Does anyone know how to exit the function if the input is blank?
I’ve searched the internet but cannot find how to exit a function.
- Should I check the input as blank via empty string “” or Null?
When I look at the table for this field using filter it says it is blank.
- I should also check if the input is not numeric. How do I do this?
Thanks for any help
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.
4 Replies
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 DirectoryI did
Get data->Blank QueryPick the user table
Go to Applied Steps section on the right
Pick last instruction.
Right ClickPick “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!
