Document copyright 2010 Mark Minasi; please see below for info on subscribing, unsubscribing or copying portions of this text.
Hi all —
Some of you may know that NetIQ was kind enough to ask me to put together a short webinar on "Automating AD" this month. It was fun to get a reason to put down on paper some things that have just been in my head to this point, and --no surprise -- I found that I didn't have time for everything that I wanted to cover. One item that I didn't have to cover at all was LDAP queries, a topic that every AD admin should know at least a bit about. It's a must-have basic skill for any "AD mechanic" and I'm eager to get at it but first, a word from our sponsor:
Getting Ready For AD Automation: ADSIEdit and LDAP Query Basics
Recently, I've been doing a lot of teaching and writing on how to manage Active Directories with the various "power tools" out there, including the new PowerShell AD cmdlets that shipped with R2. The toughest part about showing people how to use tools like that, though, is that before we can even get started with them, folks must master a couple of basics. First, they've got to know how to poke around AD's internal data structures, and, second, they must know how to use that information to ask AD questions via AD's native language, "LDAP queries." It's a somewhat geeky but useful skill.
So, fair warning: if you're already a black belt at ADSIEdit and LDAP query syntax, then just skip this newsletter or pass it someone who's not as techie as you, as you probably won't learn anything new. But otherwise, let's take a peek at some of the basics of your AD's structure and learn how to exploit that information to get started building some simple AD queries using the LDAP query syntax.
AD as a Database
As you know, Active Directory is a database, and databases have a structure ("schema" has become the popular phrase in database world in the past couple of decades) in that every record in the AD database, every AD "object" has some number of pre-defined attributes. What that means in simpler terms is that every user account in Active Directory is a record in the AD database, and AD sets aside locations in every user account that you can use to describe that user. For example, every AD user's account contains space for that user's first name, last name, logon name, password, title, description, comment, manager and so on. (I find it sort of interesting that AD includes those attributes and not others -- why no space for height, weight, eye color or the like? As it turns out, Microsoft's choice of AD attributes isn't entirely arbitrary, as we'll see.)
Knowing what attributes AD contains and how it refers to them will help you understand how to query those attributes. You'll find that useful because being able to query Active Directory to find users (or other types of AD objects) that meet some set of criteria is a critical part of being able to automate Active Directory tasks. For example, consider the following examples of jobs that could be pretty time-consuming by hand on an Active Directory, but that might become fairly fast to accomplish with some sort of automation tool:
We All Need Filters
One way to get tasks like this done is to try to extract some sort of generalized approach to them. In this case, we could describe them all like this: "first, filter out a subset of AD users who meet one or more criteria and then do something to those users." "Do something to them" might include moving them, deleting them, making them inactive, modifying one of their attributes, or the like. Again, doing that by hand could involve a lot of clicking, clicking and more clicking. But all that clicking could become just a line of (for example) PowerShell commands if we can
That's it: define the criterion or criteria for the filter, and then for the action and, of course, figure out how to implement the filter and the actions in some tool. Let's leave how to do the "actions" part for a later piece, and focus in this article on how to tell some AD tool, "find all users who meet such-and-such criteria." Now, AD automation tool developers are nothing if not creative, and so a look at eight AD automation tools will probably reveal at least nine methods of specifying which users you want extracted. Many of these filter description methods are quite good, but who wants to have to learn a new way of asking questions of Active Directory every time a new tool comes around? Nobody, which is why the one thing that nearly all AD admin tools have in common is that they all will let you query AD using an vendor-independent standard query language, the "lightweight directory access protocol" or LDAP.
That's my main goal in this newsletter: to get you comfortable with telling your AD how to find the users that you want, using LDAP's query syntax. I warn you, LDAP ain't pretty, but knowing some LDAP syntax will help you get the most out of just about any AD scripting tool, administration GUI, PowerShell cmdlets, you name it. Facility with LDAP queries can make you -- sorry, Seinfeld fans, I can't resist -- the master of your domain.
Here's a quick example. Suppose you wanted to get a list of all of the folks in your Active Directory whose job title was "Professor" and whose last name was "Smith." You could ask any one of at least a half-dozen different AD tools -- very, very different AD tools! -- by telling any one of them to issue this query:
As I said, not pretty -- but universally useful, so let's see how to do it.
Discovering Active Directory Attribute Names
To define the filter, we will usually end up looking at an AD attribute, like a user's name, group membership, description, title or the like, and to do that, we've got to first know what AD calls those attributes internally. Where the fun comes in is in figuring out what LDAP calls those various attributes; for example, I didn't expect you to be able to pick apart that parenthesis-laden LDAP query I just showed you, but it is, I think, easy to note that the query's built on two clauses, "title=professor" and "sn=smith." Clearly, then, the internal AD attribute name for job title is "title," and the internal AD name for a last name is "sn." And "sn" is not the only strangeness to expect from attribute names. For example, the LDAP name for a user's location isn't "office" or "location," it's "physicalDeliveryOfficeName." And the name for the LDAP attributes containing a user's name can be quite confusing, as AD's LDAP structure has attributes called "name," "displayname," "samaccountname," "userprincipalname," "sn" (surname, the user's last name), "givenname," the user's first name, "initials," the user's middle initials if any, and "middleName," to name a few.
How to know what to call AD's bits and pieces? All AD practitioners (and even aspiring AD practitioners) soon learn that ADSIEdit is a great way to poke around AD's innerds, so let's see what it can show us. Here's how.
In this test domain, I've got the usual things in the the root of the domain, like Builtin, Computers, Domain Controllers and the like, and you can see that I've added a few user accounts (Joe, John Doe, and Mark), an OU named TPs (it contains users who are teaching professionals) and some other things. It looks a bit like what you'd see if you ran Active Directory Users and Computers with View/Advanced Features enabled:
The difference is, of course, that ADSIEdit shows everything as folders, where in contrast ADUC uses different icons to distinguish groups, user accounts and the like. The strength of ADSIEdit is that it lets us zero in on any object and find out what AD knows about that object, and what AD calls the attribues of the object. For example, if I right-click "CN=Joe" and choose Properties, I see a list of attributes of Joe and their values, as you see here:
(By the way, if you're sitting at a DC running Server 2008 or later, there's an even quicker way to bring up ADSIEdit's "Properties" dialog for an AD object. From ADUC, ensure that View/Advanced Features is checked, then right-click the object and choose Properties. You'll see a tab labeled "Attribute Editor." Click it and you get the ADSIEdit view of its properties, saving you a bunch of clicks.)
Now, if you try something like this with an AD of your own, you'll see a lot of attributes that lack values. That's common in AD objects, so ADSIEdit has an option to hide the empty attributes. To do that,
Look at the left column and you'll see a few of the names of AD's attributes -- givenName, manager, sAMAccountName, sn and the like. Why on earth, you might wonder, did Microsoft decide to create an attribute named "sn" that holds someone's surname or last name? Actually, they didn't. Back in 1984, the ITU (then the CCITT, which stands for something in French, so I'll skip expanding the acronym), a communications standards body, decided to create a standard description of a directory service ("directory service" is basically just a fancy name for "a database of users and their characteristics") called "X.500" and, as part of that standard, they laid out a sort of "starter kit" for X.500-compatible directory services attributes and attribute names. Developers and implementers could, of course, add their own implementation-specific attributes (so we could have height, weight, eye color and the like if we wanted), but the thought was that hey, everybody's got a name, job title, manager and the like, and business-oriented applications care about those things, so why not put that in the baseline X.500 description? Now, a lot of proposed standards don't end up going that terribly far -- anyone out there using TP4 instead of TCP for their transport layer in their network stack? -- but most directory services built in the past twenty years include at least a nodding X.500-ishness. Ever wondered why Novell and Microsoft describe sub-pieces of their domain/tree as an "organizational unit?" Yup, that came from X.500. (I always found it funny in the late 90s when Novell admins would snicker behind their hands when I explained that the then-upcoming AD would have sort of "sub-domain" things called OUs. "Just Microsoft stealing something else from Novell," they'd assert." "Yes, and what a shame that those X.500 guys stole it from Novell also, back before NDS even existed," I often wanted to respond.)
In any case, X.500's success led to a need for a standard way to query X.500-compatible directory servers, which motivated creating the lightweight directory access protocol (LDAP), which appeared in 1993 as RFC 1487. LDAP's got a bunch of related RFCs, and one of them, RFC 2256, includes a standard set of attributes for X.500-compliant directory services that can be accessed via LDAPv3 (v3, in case you're wondering, was described in RFC 2251, and both RFCs 2251 and 2256 appeared in 1997, before AD). RFC 2256 includes many of the odd-looking, odd-spelled AD attribute names that you'll run across in AD, including sn, physicalDeliveryOfficeName and so on.
So why is all of this useful? Two reasons. First, as I've already observed, AD blesses its users with a bushel of different attributes that are all different, and yet all related somehow to those users' names. For example, consider this partial screen shot from AD Users and Computers. Next to each field, I've annotated the field with its internal AD LDAP attribute name in a small blue box.
Nor is this a complete portrayal of AD's name-related attributes, as it doesn't even include samAccountName (the AD attribute name for the "pre-AD logon name"), nor the userPrincipalName (the AD logon name that looks like an email address). So quick now, which attribute does ADUC use to sort names? (Name.) Which one does Exchange use to identify a user's mailbox? (Displayname.) When you change the "sn" last name, does that change the "name" attribute? (Nope.) Does it change the Displayname attribute? (Nope.) When you change "Display name," does it make a corresponding change to "Name?" (Nope.) My point here is that I would never have figured out stuff like the above picture without ADSIEdit. In fact, to "crack the code" on the names a long time ago, I first created a user in ADUC with attribute values that I set equal to their ADUC labels -- a last name of "lastname," a display name of "displayname," and so on -- and then I fired up ADSIEdit and noted the what ADSIEdit called those attributes. Otherwise I don't think I'd have figured out "sn" very quickly, and seeing that changing "Display name" in ADUC caused no change in the sort order seemed awfully strange until I knew that "name" and "displayname" were two different things.
So now that we know how to use ADSIEdit to discover attributes' internal LDAP names, we're ready to start attacking LDAP queries, using those attribute namea.
LDAP Query Tools: ADUC, LDP, ADFind, CSVDE, and PowerShell
As you've already seen one LDAP query, you'll probably agree that it's going to be sort of tough trying to learn this stuff without something to actually try out a LDAP query on. Fortunately, it is the case (as I've intimated) that there is no shortage of tools around that can process an LDAP query. (All we're doing is querying an AD, not changing it, but it might not be a bad idea to work on a small test AD rather than your production one for this stuff.) If you'd like to create some bogus test AD accounts so that you can do queries like mine, then you really only need populate the "name," "title" and "department" attributes, as they're about all I use in these examples. In any case, the first order of business there is to find something that will let us enter LDAP queries in LDAP query-ese so that we can get a bit of hands-on experience in the area, and it'd be really good if those query tools didn't cost anything.
We'll test out the simplest LDAP query I can think of. It queries the AD for every user whose "name" parameter equals "Mark," and looks like
What tools could allow us to address this query to our Active Directory? Well, offhand I can think of five.
ADUC: The first LDAP query tool you might find useful is Active Directory Users and Computers (ADUC), believe it or not.
As I've got a user named "Mark," I get a result like the following image.
I don't recall how long ADUC has supported these LDAP queries, but I think the Server 2003 version did, as does all of the ADUCs after it.
LDP: ldp.exe is a somewhat ugly but serviceable tool that's been around either in Support Tools or in the OS itself since Windows 2000. KB 224543 explains how to use it to do LDAP queries, so I won't repeat the step-by-steps. Here's a sample result from the (name=mark) query:
CSVDE: a tool built into Support Tools and then the OS itself, CSVDE's main job is to allow you to create comma-separated-value text files with user information in them and then feed those files to CSVDE, which then creates zillions of user accounts in bulk, based on that information. But it can also export AD accounts into comma-separated-value text files, and can be told to export only entries that satisfy an LDAP filter. Tell CSVDE to export its results to the screen and you have an LDAP query tool (albeit an ugly one), as you can see in this example:
PS C:\adtest> csvde -r "(name=mark)" -f CON Connecting to "(null)" Logging in as current user using SSPI Exporting directory to file CON Searching for entries... Writing out entries . Export Completed. Post-processing in progress... DN,objectClass,cn,sn,description,givenName,distinguishedName,instanceType,whenCr eated,whenChanged,displayName,uSNCreated,memberOf,uSNChanged,name,objectGUID,use rAccountControl,badPwdCount,codePage,countryCode,badPasswordTime,lastLogoff,last Logon,pwdLastSet,primaryGroupID,objectSid,adminCount,accountExpires,logonCount,s AMAccountName,sAMAccountType,otherMobile,userPrincipalName,objectCategory,dSCore PropagationData,lastLogonTimestamp,mail,manager
"CN=mark,DC=bigfirm,DC=com",user,mark,Minasi,AD consultant,Mark,"CN=mark,DC=bigf irm,DC=com",4,20091115223614.0Z,20100207171512.0Z,Mark Minasi,16425,"CN=ctest,CN =Users,DC=bigfirm,DC=com;CN=Netmon Users,CN=Users,DC=bigfirm,DC=com;CN=Domain Us ers,CN=Users,DC=bigfirm,DC=com",86497,mark,X'e996f0d72c88fc4d8109dc4b49f6865c',6 6048,0,0,0,129053010231730000,0,129096831449137500,129027981751679843,512,X'0105 00000000000515000000fa9390bc640ae05ae432797652040000',1,9223372036854775807,59,m ark,805306368,3;2,firstname.lastname@example.org,"CN=Person,CN=Schema,CN=Configuration,DC=bigf irm,DC=com",20100127221920.0Z;20091214172523.0Z;20091115223615.0Z;16010101000000 .0Z,129096831449137500,email@example.com,"CN=Mary Tarduno,OU=TPs,DC=bigfirm,DC=com" 1 entries exported
The second big block of data is a dump of various bits of info about my account on that domain. (No, my password's not there.) Just be sure to surround the LDAP query text with double quotes. There's another bulk creation tool in the OS in 2008 and later called "ldifde," and it too accepts LDAP queries.
ADFIND: I've always liked a tool from my fellow DS MVP Joe Richards called "adfind." You can download adfind.exe for free from www.joeware.net. Then just invoke adfind from the command line with the "-f" option and enter our simple LDAP query by typing adfind -f "(name=mark)"; you can also control which attributes you'd like to see adfind output by listing them at the end of the command as in adfind -f "(name=mark)" name,displayname. Here's an example output:
C:\adtest>adfind -f "(name=mark)" name,description AdFind V01.40.00cpp Joe Richards (firstname.lastname@example.org) February 2009 Using server: entr2.bigfirm.com:389 Directory: Windows Server 2008 R2 Base DN: DC=bigfirm,DC=com dn:CN=mark,DC=bigfirm,DC=com >description: AD consultant >name: mark 1 Objects returned
GET-ADUSER: I love adfind, but ever since R2 shipped I'm using the PowerShell AD cmdlets more and more. (And recall that you don't need R2 to get those cmdlets -- any AD DC running 2003 SP2 or later can host them, as I explained in the last newsletter.) On PowerShell, you can type get-aduser -l "ldap query string," and that's a lowercase "L."
Trying out get-aduser, then, I get
PS C:\adtest> get-aduser -l "(name=mark)" DistinguishedName : CN=mark,DC=bigfirm,DC=com Enabled : True GivenName : Mark Name : mark ObjectClass : user ObjectGUID : d7f096e9-882c-4dfc-8109-dc4b49f6865c SamAccountName : mark SID : S-1-5-21-3163591674-1524632164-1987654372-1106 Surname : Minasi UserPrincipalName : email@example.com
That may be a bit more output than we might want, and in that case we can add the "select" cmdlet to reduce the attributes that get-auser shows. For example, to only see Mark's distinguished name (DN), we could type
PS C:\adtest> get-aduser -l "(name=mark)" | select distinguishedname distinguishedname ----------------- CN=mark,DC=bigfirm,DC=com
You also may have noticed that by default, get-aduser omits some potentially interesting AD attributes like description, title, lockedout, manager and the like. That's because Microsoft built the AD PoSH cmdlets with a default set of properties that don't include those attributes, but you can tell get-aduser to add back any or all of those properties by adding the parameter "-properties listofnames," and you can shorten "-properties" to "-pr." Here's a couple of examples:
get-aduser -l (name=mark) -pr *
get-aduser -l (name=mark) -properties title,manager
So if I only wanted to see if Mark's account were locked out, I could combine -properties and select like so:
PS C:\adtest> get-aduser -l "(name=mark)" -pr lockedout | select lockedout lockedout --------- False
With five LDAP query tools close to hand and not a one costing a cent, there's no excuse not to get a bit facile with LDAP queries!
LDAP Queries With Wildcards, and How to Put Quotes in LDAP
We've already met what I've called the simplest LDAP query possible:
But let's make sure that we know what's going on in those parentheses before we start getting a bit fancier. There are three things to know about this and every other LDAP query. First, LDAP queries are built up of one or more clauses, and every clause is surrounded by parentheses. In fact, we'll soon see parentheses inside parentheses and so on. Second, clauses look like (attributename=what-to-match), and in our example, the attribute name is named "name" and we're looking to see if it matches the string "mark." Third, and here's the good news, LDAP clauses are case-insensitive in my experience. If trying to find a user whose last name is "Minasi," you'd search the "sn" attribute, but either (sn=minasi), (SN=minasi), (sN=MiNAsi) or (SN=MINASI) would all work equally fine in getting the job done -- they all accomplish exactly the same thing.
Beyond the simplest LDAP query possible -- (name=mark) -- we can add wildcards. The wildcard character in LDAP is the familiar asterisk ("*") and there isn't a single-character wildcard like "?" or the like. So, for example, to see every user whose "name" attribute starts with "m," we can build a query like "(name=m*)" which, in get-aduser, would look like
get-aduser -l "(name=m*)"
I will spare you the output, as it's lengthy, but take another look at that query string and notice something: the string that you're asking LDAP to search on doesn't need quotes. Yes, PowerShell needs you to surround the entire query with quotes, but the search string that you hand LDAP -- m* -- doesn't need them, which is sort of convenient. The "*" wildcard is also a bit more flexible than some of the wildcards that we're used to from Windows: for example, (name=m*) will match either "Mark" or "Mary," but (name=m*k) will only match Mark. Also, trailing spaces are significant -- "(name=m* )" would not match a name of "Mark." Cool: no case to worry about, smart wildcards and no need for quotes... things to like about LDAP.
Once in a while, though, we'll need to search the directory service for things containing quotes, asterisks, parentheses and the like. In those cases, you can tell LDAP to search for those characters by typing them as a backslash immediately followed by the hex representation of their ASCII value. More specifically,
LDAP escape codes
So, for example, if a previous administrator had whimsically given a number of users nicknames in the middle of their displayname values, like
James "Big Jim" Summerlin
and you wanted to find all of the user accounts with the nicknames so that you could get rid of the nicknames -- HR's all cranky about it and won't stop bothering you until you get them all gone -- then this LDAP query would find them:
get-aduser -l "(displayname=*\22*)"
More Complex Queries: LDAP's AND and OR Operators
Next, let's consider a query that requires two or more clauses. Suppose we wanted to find all of the instructors in the Political Science department, which translates into computer-ese as "let's find all users whose title=Instructor and whose department=Political Science." We can search for all instructors with the query (title=instructor) and all members of the Political Science department with (department=political science). How can we tell LDAP that we only want to see the folks that meet both criteria? With the ampersand "&" and this structure:
To formulate the specific example that I've laid out, we'd type
get-aduser -l "(&(title=instructor)(department=political science))"
Remember how nice I said that it was that we didn't have to worry about quotes and inflexible wildcards in LDAP? Well, as you can see, LDAP repays us with its "AND" syntax... ugh. (It looks to me as if the query language designers were fond of a way of writing things called "postfix," a syntaxic approach designed to make life easier for programmers writing LDAP query resolvers rather than one designed for us carbon-based life forms who are trying to make use of LDAP queries.)
Taking it a step further, if we looked for all users who were instructors in Political Science and had a last name of "Smith," that query would look like
get-aduser -l "(&(title=instructor)(department=political science)(sn=smith))"
If you need a logical OR, then the syntax is the same as the AND, except the operator is a "|" rather than an ampersand. Thus, to find everyone who's either in the Political Science department or the History department, we'd type
get-aduser -l "(|(department=political science)(department=history))"
Of course, you can hook up ANDs and ORs as necessary to create LDAP queries that are as powerful (and ugly) as you'd like. Thus, if we wanted to find all of the instructors who are either in Political Science or History, we'd formulate it (in semi-English LDAP) as looking for all users who are
(title = instructor) AND (department=history OR department=political science)
Now, you and I understand that sort of thing, but LDAP needs things in its own postfixy way, so we've got to a bit of translation. To help that, let's make this a trifle more formal and surround all of the comparisons -- the something=something else parts -- with parentheses of their own. Then it looks like
(title = instructor) AND ((department=history) OR (department=political science))
Of course, where you and I would write "something AND something else," LDAP has its programmer-friendly postfix syntax along the lines of "AND(something, something else). Rewriting it in LDAP's order, the whole thing gets built as
Which, when pumped into get-aduser, looks like
get-aduser -l "(&(title=instructor)(|(department=political science)(department=history)))"
One More Operator: NOT
Veteran users of boolean logical operators (or just anyone who's actually trying to do something with this stuff) will soon notice that while the "=" equals operator is useful, we sometimes need the not equals sign -- for example, how would we formulate a query that asked for everyone who is not in the History department, or one that asked for all users whose title was "Instructor" but who was not in the Political Science department? LDAP fulfills both needs with the "NOT" operator, written "!." LDAP uses it like the & and | operators, so to query everyone whose "department" value is not equal to "History," we'd type this PowerShell command:
get-aduser -l "(!(department=history))"
Similarly, to find all of the instructors who are not from Political Science, we'd build a query that in not-quite-English would be to find all users for whom
NOT(department=political science) AND (title=instructor)
Which, when recast in LDAPese where (A) AND (B) becomes (&(A)(B)) and NOT(B) becomes (!(B)), is
I have by no means covered everything here relevant to LDAP queries -- I've not even touched on the question of performance on LDAP queries, a topic for another day -- but I hope that what I've shown you here makes some of your AD management tasks a bit easier. So pick an LDAP query tool that you like, point it at your nearby AD and get a little LDAP query practice under your belt. Of course, it's reasonable to ask at this point, "how can I use this to automate the litany of requests that I've got to respond to in my AD management work, day to day?," and we'll look at that soon. Thanks for reading!
To Subscribe/Unsubscribe, Read Old Newsletters or Change Your Email Address
To subscribe, visit http://www.minasi.com/nwsreg.htm. To change e-mail or other info, link to http://www.minasi.com/edit-newsletter-record.htm. To unsubscribe, link to http://www.minasi.com/unsubs.htm. Visit the Archives at http://www.minasi.com/archive.htm. Please do not reply to this mail; for comments, please link to http://www.minasi.com/gethelp.
All contents copyright 2010 Mark Minasi. I encourage you to quote this material, SO LONG as you include this entire document; thanks.