One more ADfind query

I was just looking at the ActiveDir mailing list and noticed a discussion a guy started with the following requirements:

He wants an ADFind command that lists:

  • all users that are active (not disabled)
  • have their user name
  • have the groups they are member of
  • have their creation date
  • dump them into a csv file

 Well, that is a tough list but the ActiveDir mailing list members are a pretty smart crowed. The resultant ADfind command should go something like this:

adfind -default -csv -bit -f “&(objectClass=user)(objectCategory=person)(!userAccountControl:AND:=2)” -nodn samaccountname memberof whencreated
Let me break that down for you:

First, we need to have a look at the filter: the combination of &(objectClass=user)(objectCategory=person) makes sure we only get user objects as search results. A filter like (objectClass=user) wouldn’t be sufficient as that catches all computer accounts, too. We also have a third filter requirement: (userAccountControl:AND:=2) – that might be a tough one. UserAccountControl is an attribute that stores all sorts of user properties. It is actually a bitmask – every bit in the mask enables/disables a certain feature/option on the account. Looking at http://support.microsoft.com/kb/305144, you can see that Bit #2 with decimal value 2 triggers account enable/disable. That’s the bit we need to look at. Since we can’t predict what other bits in the bitmask are flipped, we can’t just query for userAccountControl=2 — if other options are enabled, the attribute’s value most likely won’t match 2 and we’re getting false results.

We still need to look for Bit#2 – if it’s flipped, we know the account is disabled – so we’re using a special thing called “Control” that tells the LDAP server (Active Directory here) to do a bit wise AND operation on the bitmask and check whether Bit #2 (still decimal value 2) is flipped. The control’s ID that we need to use here would normally be 1.2.840.113556.1.4.803 – but since no one of us would be able to remember that ID, joe put a “variable” into ADFind we could use: “AND”.

To make ADFind aware of our variable usage in our filter, we need to use the -bit switch. It basically maps our “:AND:” bit check to :1.2.840.113556.1.4.803: which triggers the binary and control on the LDAP server.

We’re using the “NOT” statement (notice the ! at the beginning of the filter element) to make sure that we DON’T get users with bit #2 flipped (=disabled) as we only want enabled users: (!userAccountControl:AND:=2).

As all three filter elements must be met at the same time, we can put them together with an ampersand, &.Â

Next, we need the logon name – that’s simple as it’s stored in sAMAccountName. The whole list of group membership can be retrieved from “memberOf”. It’s a linked attribute that has a direct connection to the corresponding groups. “whenCreated” is the attribute we use to find out when an Active Directory object was created. The attribute is written on object creation and is never altered after that.

Finally, there’s the -csv switch that makes ADfind format its output in CSV friendly format so that you can redirect the output into a .csv file.

2 Comments so far

  1. Mike Kline on September 9th, 2009

    Another great entry Florian!

    You can also add the -tdcgt switch here to decode the whencreated time (I know you already know that one)

    I wish there was a way to output the group names and not their entire DN. I understand why it happens…just had people ask me when I answer questions on the boards.

    Talk to you later

    Mike

  2. Tomek on September 11th, 2009

    Great post … what is important to add I think is that this query will return only *direct* user group membership, not including any group in which user is a member through nesting etc.