Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sascha_holland
Contributor II
Contributor II

Breakup Active Directory userAccountControl value

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:

ACCOUNTDISABLE0x00022
HOMEDIR_REQUIRED0x00088
LOCKOUT0x001016
PASSWD_NOTREQD0x002032
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.
0x004064
ENCRYPTED_TEXT_PWD_ALLOWED0x0080128
TEMP_DUPLICATE_ACCOUNT0x0100256
NORMAL_ACCOUNT0x0200512

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!

1 Solution

Accepted Solutions
sascha_holland
Contributor II
Contributor II
Author

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

;

View solution in original post

1 Reply
sascha_holland
Contributor II
Contributor II
Author

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

;