locked
Accesing userAccountControl attribute when querying Active Directory RRS feed

  • Question

  • Hi

    I've queried active directory but would like to filter the results to show me only enabled or disabled users.  I've done some hunting and found that this information is stored in the userAccountControl attribute in AD, but that this attribute indicates several other things depending on its value. I've found scripts that find disabled accounts using userAccountControl:1.2.840.113556.1.4.803:=2 or something similar.  I've not been able to access this attribute's data in Power Query.  Has anyone had any success with this?

    Best regards

    A

    Friday, July 15, 2016 12:36 AM

Answers

  • Hi Anikke,

    1.2.840.113556.1.4.803 is a bitwise and operation. You can do this in the query editor this way:

    let
        Source = ActiveDirectory.Domains("yourdomain"),
        redmond.corp.microsoft.com = Source{[Domain="yourdomain"]}[#"Object Categories"],
        user1 = redmond.corp.microsoft.com{[Category="user"]}[Objects],
        #"Expanded user" = Table.ExpandRecordColumn(user1, "user", {"userAccountControl"}, {"user.userAccountControl"}),
        #"Filtered Rows" = Table.SelectRows(#"Expanded user", each Number.BitwiseAnd([user.userAccountControl], 2) <> 0)
    in
        #"Filtered Rows"

    Notice the last step calls "Filtered Rows".

    This bitwise and operation is not sent to the domain controller and so it might take a while to return all the users depending on how many users there are in your directory. I would therefore recommend that you do this at the end of your query as much as possible to make sure that other operations are optimized, such as expanding and filtering other fields.

    Tristan

    • Proposed as answer by Tristan St-Cyr Monday, July 18, 2016 5:28 PM
    • Marked as answer by Michael Amadi Saturday, August 13, 2016 9:31 PM
    Monday, July 18, 2016 5:28 PM