Breakup Active Directory userAccountControl value

[sascha_holland]

Hello,

I want to break up a cumulated value in a database field.

The data record of a user account in Active Directory has the field "userAccountControl" which presents a cumulated value of individual flags like this table from microsoft:

ACCOUNTDISABLE	0x0002	2
HOMEDIR_REQUIRED	0x0008	8
LOCKOUT	0x0010	16
PASSWD_NOTREQD	0x0020	32
PASSWD_CANT_CHANGE Note  You cannot assign this permission by directly modifying the UserAccountControl attribute. For information about how to set the permission programmatically, see the "Property flag descriptions" section.	0x0040	64
ENCRYPTED_TEXT_PWD_ALLOWED	0x0080	128
TEMP_DUPLICATE_ACCOUNT	0x0100	256
NORMAL_ACCOUNT	0x0200	512

That means for instance a "normal account" has a value of 512; a "normal account, disabled" has a value of 514 (512 + 2). A "normal account, locked out" account has a value of 528 (512+16) and so on. I want to create a value list or better a table of these single flags of each user, so that if I select "User 1", in the "Users" listbox, it shows me each flag of the selected user(s) in a second "flags" listbox. On the other hand, I want to select flags from the "flags" listbox and want to see which users has these flags.

Has anyone an idea for this?

Thanks!

[sascha_holland · Accepted solution]

Finally, I found a solution for myself splitting up the userAccountControl value into single flags. Here is the part of my script, if someone needs it (the referred table ADUsers is created by this AD Script of Rob Wunderlich):

tmp:

LOAD

    Value_dec as UAC_flag_dec,

    [Property flag] as UAC_text

FROM

[.\AD_Valuemap.xlsx]

(ooxml, embedded labels, table is UserAccountControl)

//order by Value_dec

;

NoConcatenate

UAC_States:

load

    *

Resident tmp Order By UAC_flag_dec

;

drop tables tmp;

//alle vorkommenden distinkten UAC-Kombinationen aus der Tabelle ADUsers laden

    NoConcatenate

    tmpUAC_Values:

    load Distinct

        UAC as UAC_sum

    resident ADUsers where Exists (UAC) order by UAC;

//Schleife, die jeden Datensatz in tmpUAC_Values bis Ende der Tabelle durchgeht

FOR cnt = 1 to NoOfRows('tmpUAC_Values') step 1

    //Aus dem Datensatz mit dem aktuellen Zähler (gesetzt oben in der Schleife) die Variablen vUAC_sum und vUAC_Rest auf den selben Wert setzen

    let vUAC_sum = peek('UAC_sum',$(cnt)-1,'tmpUAC_Values');

    let vUAC_Rest = peek('UAC_sum',$(cnt)-1,'tmpUAC_Values');

    //Schleife, die den maximalen Wert <= vUAC_Rest aus der Tabelle UAC_States sucht und die Variablen subtrahiert, so dass ein neues Restwert errechnet wird

    do

        MaxRecNo:

        Load Max(UAC_flag_dec) as MaxRecNo Resident UAC_States where (UAC_flag_dec <= $(vUAC_Rest));

        Let vRecNo = peek('MaxRecNo', -1, 'MaxRecNo');

        drop Tables MaxRecNo;

        let vUAC_Rest = $(vUAC_Rest) - $(vRecNo);

        //Schreibt die Variablenwerte in die temporäre Tabelle tmpUAC_Mapping

        tmpUAC_Mapping:

        load

            $(cnt) as cnt

            ,$(vUAC_sum) as mUAC_sum

            ,$(vRecNo) as mUAC_flag

        AutoGenerate 1

        ;

    loop while $(vUAC_Rest)>0

next cnt

//Daten aufbereiten

NoConcatenate

UAC_Mapping:

load distinct

    mUAC_sum as UAC

    ,mUAC_flag as UAC_flag_dec

resident tmpUAC_Mapping order by mUAC_sum, mUAC_flag

;

drop tables

    tmpUAC_Values

    ,tmpUAC_Mapping

;