joeware - never stop exploring... :)

Today I ran into an issue where a syncing tool blew up because it encountered an object that had multiple values in an attribute that “normally” has a single value in it and it didn’t know how to properly sync that object. Well obviously whomever set up the syncing tool kind of made a mistake because the attribute in AD was multivalued so there was always a chance this would happen so they should have accounted for it in the configuration of the syncing tool. But they didn’t and so I had to go figure out what was going on. Looking at it I quickly realized the problem and was able to point out that an object had multiple values for destinationIndicator and the company standard was to only have a single value. So this raised the question for me, are there any other objects out there in the same boat? I.E. They had multiple values for that attribute and would eventually cause me pain in the syncing tool…

So the generic question was, how do I quickly ascertain which objects had multiple values for a given attribute… Initially I thought, this will be a bit of a pain, I will have to output all objects with that attribute populated and then parse the attribute out with a script… Then I thought… Well I don’t actually care about the various values, just whether or not there are multiple values at all… Then a flash of EUREKA! AdFind to the rescue… (again).

Side Bar: I love when I come up with new ways to use my utilities that I never thought of before. It just encourages me more and more to have flexibility at the core of the utilities because I never know what I or someone else may end up doing with them.

I realized that all I had to do was be able to pick out objects that had multiple values for a single attribute so I typed up a command line like

adfind -h domainname -default -f “&(objectcategory=person)(destinationindicator=*)” destinationindicator -csv

and then added one simple switch that made all the difference in my ability to quickly scan through and find multiple values…

-csvmvdelim ####

So the whole command looked like

adfind -h domainname -default -f “&(objectcategory=person)(destinationindicator=*)” destinationindicator -csv -csvmvdelim ####

Now you may be sitting there thinking… “Well how the heck does that help anything???”. It doesn’t by itself, but when you combine that with grep or find or findstr or whatever and you pipe the output of that command into one of those search tools looking for the string #### it will only output those lines of the CSV output that match… I.E. Objects with multiple values for that attribute. So in its entirety it looks like

adfind -h domainname -default -f “&(objectcategory=person)(destinationindicator=*)” destinationindicator -csv -csvmvdelim #### | grep ####

I don’t know about anyone else but I just think that is pretty cool. 🙂

 

    joe

Recently I was searching for some specific OU’s in Active Directory that had a specific substring within the name. i.e. it didn’t start with a specific substring, the substring I wanted was buried in the main string like for example the substring Crab in the string Joe’s Crab Shack which would be represented as *crab* or in this specific case the LDAP filter looked like OU=*something.  This is called a medial search.

I knew that although I had more than a half a million objects in the directory I only had about 25 OU’s to search through so even though medial searches can be slower than normal searches, with only a handful of OU’s this really shouldn’t be too much of an issue. Surprise… It was. The query took a considerable amount of time to run and it really really shocked me. I could have output the relatively few OU’s and manually found the ones I wanted faster as it took over a minute to find the OU’s I needed.

This really caught my curiosity so I started digging into it. The very first step is to validate that the OU attribute was indexed, I was pretty confident it was but you need to start somewhere:

G:>adfind -sc s:ou searchflags

AdFind V01.40.00cpp **BETA** Joe Richards (joe@joeware.net) February 2009

Using server: r2dc1.test.loc:389
Directory: Windows Server 2003
Base DN: CN=Schema,CN=Configuration,DC=test,DC=loc

dn:CN=Organizational-Unit-Name,CN=Schema,CN=Configuration,DC=test,DC=loc
>searchFlags: 1 [INDEX(1)]

1 Objects returned

 

So yes, it was indexed. Next step is to look at the STATS of the query and make sure the index was being used…

G:\>adfind -default -f ou=*something -stats+only

AdFind V01.40.00cpp **BETA** Joe Richards (joe@joeware.net) February 2009

Using server: r2dc1.test.loc:389
Directory: Windows Server 2003
Base DN: DC=test,DC=loc

Statistics
=================================
Elapsed Time: 89679 (ms)
Returned 0 entries of 714377 visited – (0.00%)

Used Filter:
(ou=*something)

Used Indices:
DNT_index:485112:N

Analysis
———————————
Hit Rate of 0.00% is Inefficient

No dedicated indices used for search, this is inefficient.

Indices used:

Index Name  : DNT_index
Record Count: 485112  (estimate)
Index Type  : Normal Attribute Index

Filter Breakdown:

(
(ou=*something)
)

WHOA! It isn’t using the OU attribute index, it is using the DNT index which is everything… Look at that 714377 entries visited… Now that doesn’t make much sense… Let’s take a step back, what is used if we use OU=*?

G:\>adfind -default -f ou=* -stats+only

AdFind V01.40.00cpp **BETA** Joe Richards (joe@joeware.net) February 2009

Using server: r2dc1.test.loc:389
Directory: Windows Server 2003
Base DN: DC=test,DC=loc

Statistics
=================================
Elapsed Time: 150 (ms)
Returned 22 entries of 23 visited – (95.65%)

Used Filter:
(ou=*)

Used Indices:
idx_ou:23:N

Analysis
———————————
Hit Rate of 95.65% is Efficient

Indices used:

Index Name  : idx_ou
Record Count: 23  (estimate)
Index Type  : Normal Attribute Index

Filter Breakdown:

(
(ou=*)
)

 

Now that is more like it… 23 entries.

But what is happening, that makes seriously no sense. The logic of the Query Processor should be such that if you are searching for an attribute, even via a medial search, if there is a regular index… Use it, don’t use the DNT index. Even if it only cut it down by 10% that is still a nice reduction, in this case it would cut it from more than half a million objects to 23… That is a serious reduction.

So I tested some more and it seems that any time I tried to run a medial search against an attribute that had a normal index (searchFlags & 1) the Query Processor would fail to utilize the index even if the number of choices that had to be looked at was substantially reduced from the DNT index. The only time this worked ok was when an actual tuple (or medial) index was defined for the attribute like so.

G:\>adfind -default -f ou=*something -stats+only

AdFind V01.40.00cpp **BETA** Joe Richards (joe@joeware.net) February 2009

Using server: r2dc1.test.loc:389
Directory: Windows Server 2003
Base DN: DC=test,DC=loc

Statistics
=================================
Elapsed Time: 60 (ms)
Returned 0 entries of 0 visited – (0.00%)

Used Filter:
(ou=*something)

Used Indices:
idx_ou:0:T

Analysis
———————————
Hit Rate of 0.00% is Inefficient

Indices used:

Index Name  : idx_ou
Record Count: 0  (estimate)
Index Type  : Tuple Index (medial substring index)

Filter Breakdown:

(
(ou=*something)
)

Note the index type… tuple. This of course is a workaround for this problem but you don’t generally want to go around making a bunch of tuple indexes as it breaks up the strings into chunks of 3 characters for comparison which could rather dramatically impact DIT size not to mention insertion of new instances of values for that attribute.

 

I tested this problem against Windows Server 2003 and Windows Server 2008 and decided to ping some friends of mine in the production groups at Microsoft in Redmond to see what they thought. Rather quickly Dmitri came back to me and said it looked like this was an area of improvement and to get it into the change request system to get it looked at. He gave me specific details and pointed out where I could find it in the source code to see what it was doing as well. Not only that, he gave me a second workaround that can used until such a time that Microsoft corrects the issue.

There are two solutions to this issue.

1. The first is as we saw above, define a tuple index. That requires a schema change and if you want to practice in your lab you can quickly do this like so…

adfind -sc s:ou searchflags -adcsv | admod searchflags::{{.:SET:32}}

and to clear it you use

adfind -sc s:ou searchflags -adcsv | admod searchflags::{{.:CLR:32}}

Then the attribute will look like

G:\>adfind -sc s:ou searchflags

AdFind V01.40.00cpp **BETA** Joe Richards (joe@joeware.net) February 2009

Using server: r2dc1.test.loc:389
Directory: Windows Server 2003
Base DN: CN=Schema,CN=Configuration,DC=test,DC=loc

dn:CN=Organizational-Unit-Name,CN=Schema,CN=Configuration,DC=test,DC=loc
>searchFlags: 33 [INDEX(1);TUPLE INDEX(32)]

1 Objects returned

 

2. The second workaround is what Dmitri clued me on to… Using what he termed as an “Index Hint”. Basically you add one more piece to the query that forces the Query Processor to look at the indexes again and actually chose correctly. It kind of sucks that it needs to be done but it is nice that it can be done at least. Changing the query to be “&(ou=*something)(ou=*)” will perform as you would expect (ou=*something) should perform all on its own. The (ou=*) is the hint that the Query Processor needs…

G:\>adfind -default -f “&(ou=*something)(ou=*)” -stats+only

AdFind V01.40.00cpp **BETA** Joe Richards (joe@joeware.net) February 2009

Using server: r2dc1.test.loc:389
Directory: Windows Server 2003
Base DN: DC=test,DC=loc

Statistics
=================================
Elapsed Time: 20 (ms)
Returned 0 entries of 23 visited – (0.00%)

Used Filter:
( &  (ou=*something)  (ou=*) )

Used Indices:
idx_ou:23:N

Analysis
———————————
Hit Rate of 0.00% is Inefficient

Indices used:

Index Name  : idx_ou
Record Count: 23  (estimate)
Index Type  : Normal Attribute Index

Filter Breakdown:

(
(&
   (ou=*something)
   (ou=*)
)
)

Still not as efficient as a Tuple index but it doesn’t have the overhead.

I have been working with AD for almost 10 years now, I had no clue about this until just this week when I ran into it. I have always known medial searches to be a bit more slow than your normal searches but never had the time to really dig into it like this and I am glad I did. Definitely keep an eye open on this and if it is something that impacts your company, please be sure to do what I am going to do, report this as an issue to Microsoft for correction. The more people who report it, the more likely it will be fixed in some timely fashion. So call your TAMs up and say, hey I have a bug I want fixed… Here it is… Oh and at the same time, don’t forget to order your TAM (male or female) a nice new Joeware Thong (http://www.cafepress.com/joewarenet). They love em!!! I have had several TAMs email me laughing about having received them. I wouldn’t mind to see it happen more and more. 🙂

 

BTW, Dmitri Gavrilov is one of the speakers (http://www.tec2009.com/vegas/agenda/directory/speaker_bios.php) at The Experts Conference in Henderson (Green Valley Resort) this year and he will be talking about Query Performance. I intend to be in the room when he is presenting because I am sure there are other things I will learn from him as well. That has been my relationship with Dmitri from the beginning, he speaks and I learn things. 🙂

 

   joe

Back on Jan 21 I tipped my hand on a new capability in AdFind which for many people is extremely exciting based on the feedback in my inbox. To refresh your memory, that was the ability for AdFind to take in a list of Base DN’s to execute queries against. For short we will say piping AdFind into AdFind though you could, if for whatever reason gripped you, pipe dsquery into AdFind.

Well in a follow up conversation with my friend and co-Author Brian Desmond, he asked “How do I pipe DNs from AdFind into AdFind and then get counts for the number of users under each of those DN’s?” My response was… well you can’t. The whole counting mechanism is based on the number of objects AdFind returns period… But then I thought, I hacked in CSV when I didn’t think I could… then I hacked in the piping in multiple DNs when I didn’t think I could, let me give this a try before totally saying no… So voila, a new switch because you just know there aren’t enough switches in AdFind yet…. The new switch is -ic… where -c stands for count, -ic stands for intermediate count… That lets you do something like….

G:\>adfind -default -f ou=* -dsq | adfind -sc adobjcnt:user

AdFind V01.40.00cpp **BETA** Joe Richards (joe@joeware.net) February 2009

Using server: r2dc1.test.loc:3268
Directory: Windows Server 2003

BaseDN: OU=CharTests,OU=TestOU,DC=test,DC=loc
1 intermediate objects returned

BaseDN: OU=createtest,OU=TestOU,DC=test,DC=loc
10 intermediate objects returned

BaseDN: OU=Deleted,OU=XXXTest,DC=test,DC=loc
0 intermediate objects returned

BaseDN: OU=XXXTest,DC=test,DC=loc
0 intermediate objects returned

BaseDN: OU=Domain Controllers,DC=test,DC=loc
0 intermediate objects returned

BaseDN: OU=Email,OU=My,DC=test,DC=loc
1 intermediate objects returned

BaseDN: OU=GPOTest,OU=TestOU,DC=test,DC=loc
0 intermediate objects returned

BaseDN: OU=Groups,OU=My,DC=test,DC=loc
0 intermediate objects returned

BaseDN: OU=Groups,OU=TestOU,DC=test,DC=loc
0 intermediate objects returned

BaseDN: OU=HideTest,OU=TestOU,DC=test,DC=loc
0 intermediate objects returned

BaseDN: OU=joeperm,OU=TestOU,DC=test,DC=loc
0 intermediate objects returned

BaseDN: OU=My,DC=test,DC=loc
0 intermediate objects returned

BaseDN: OU=oneleveldown,OU=createtest,OU=TestOU,DC=test,DC=loc
1 intermediate objects returned

BaseDN: OU=Outlook,OU=TestOU,DC=test,DC=loc
0 intermediate objects returned

BaseDN: OU=permtest,OU=TestOU,DC=test,DC=loc
2 intermediate objects returned

BaseDN: OU=PoSTest,DC=test,DC=loc
700001 intermediate objects returned

BaseDN: OU=Skip,OU=TestXXX,OU=XXXTest,DC=test,DC=loc
1 intermediate objects returned

BaseDN: OU=TestXXX,OU=XXXTest,DC=test,DC=loc
4 intermediate objects returned

BaseDN: OU=TestDisable,OU=XXXTest,DC=test,DC=loc
0 intermediate objects returned

BaseDN: OU=TestOU,DC=test,DC=loc
6 intermediate objects returned

BaseDN: OU=Users,OU=My,DC=test,DC=loc
2 intermediate objects returned

BaseDN: OU=Users,OU=TestOU,DC=test,DC=loc
10 intermediate objects returned

700039 Objects returned

Anyone think that is pretty handy??? If so, you can thank Brian, he asked the right question at the right time… I modified the adobjcnt shortcut such that when it detects it is in multi-DN mode it will also insert the -ic switch as well as set the search scope to one-level. If you wanted counts of all of the users in each OU but you wanted the counts to roll up to the higher OU’s as well you would simply add the -s sub or -s subtree switch to your command.

But then I thought, while that is useful, it would be even more useful if I could somehow get that in a CSV format so I could use this more easily from scripts when trying to get a snapshot of an environment… I looked and there was just no way I could get it into the CSV code path. When you do CSV the whole counting section isn’t used and even if it were, it is outside of the location where the CSV code is and in order to try and get it in there would cause me to use some wholly unnatural global variables and other things that just made me go, no, I will not go there, that is too ugly, too inelegant… I know I do some bad things in code, but I don’t want to do THAT bad of things in code.

All hope is not lost however. I decided to add… yes… another switch. I know I know, another one truly isn’t needed but I wanted this functionality and if you don’t like it, just ignore the fact that it is there. This switch is not a very flexible switch, there are no modifiers for it. It is called -ictsv and it simply takes the -ic output and makes it into a TAB Delimited format output. This isn’t going to be tweaked to allow different delimiters or anything like that. It is a hack completely outside the normal CSV routines which have all that flexibility. I chose tab delimited because DNs have commas and it is unlikely (impossible? I don’t know, didn’t test) to see a tab in a DN and to be honest, I like tab delimited output. I usually use TABs for my delimiters for CSV output. Anyway that output looks like…

G:\>adfind -default -f ou=* -dsq | adfind -sc adobjcnt:user -ictsv
OU=CharTests,OU=TestOU,DC=test,DC=loc   1
OU=createtest,OU=TestOU,DC=test,DC=loc  10
OU=Deleted,OU=XXXTest,DC=test,DC=loc 0
OU=XXXTest,DC=test,DC=loc    0
OU=Domain Controllers,DC=test,DC=loc    0
OU=Email,OU=My,DC=test,DC=loc   1
OU=GPOTest,OU=TestOU,DC=test,DC=loc     0
OU=Groups,OU=My,DC=test,DC=loc  0
OU=Groups,OU=TestOU,DC=test,DC=loc      0
OU=HideTest,OU=TestOU,DC=test,DC=loc    0
OU=joeperm,OU=TestOU,DC=test,DC=loc     0
OU=My,DC=test,DC=loc    0
OU=oneleveldown,OU=createtest,OU=TestOU,DC=test,DC=loc  1
OU=Outlook,OU=TestOU,DC=test,DC=loc     0
OU=permtest,OU=TestOU,DC=test,DC=loc    2
OU=PoSTest,DC=test,DC=loc       700001
OU=Skip,OU=TestXXX,OU=XXXTest,DC=test,DC=loc      1
OU=TestXXX,OU=XXXTest,DC=test,DC=loc      4
OU=TestDisable,OU=XXXTest,DC=test,DC=loc     0
OU=TestOU,DC=test,DC=loc        7
OU=Users,OU=My,DC=test,DC=loc   2
OU=Users,OU=TestOU,DC=test,DC=loc       10

Pretty cool huh… Anyone think that is handy? If so… well you are welcome… But something still bothers me about that output… Anyone else bothered by it? It could be just me but I kind of like seeing things that normally have a hierarchical form to be displayed that way. I don’t mean in the white space, but instead, I mean I don’t want to see something like

OU=Outlook,OU=TestOU,DC=test,DC=loc     0
OU=permtest,OU=TestOU,DC=test,DC=loc    2
OU=TestOU,DC=test,DC=loc        7

I want to see the TestOU first and then its sub-OU’s after… I tried modifying my search to see if I could force AD to return the info in that order but quite frankly, AD truly isn’t hierarchical, it just appears that way. It is actually a flat database. The idea of hierarchy is imposed on it for LDAP purposes. So I thought, I really need to do something about this… This will drive me nuts. However, trying to retrieve all of the information and maintain it in memory so I can then sort it is ridiculous, might as well just write this thing in PowerShell or .NET… (count it…) if I do it in such a silly way… Then I thought, wait, I already have what I need in memory to sort it hierarchically after the DNs have been piped in so I added, yes thankyou, another switch called -stdinsort. Why did I do this with a switch? Because I didn’t want to assume someone would want it sorted like I would and I didn’t want to assume I would always want it sorted. Also I wanted to give myself the ability to sort it hierarchically as well as alphabetically both case sensitive and case insensitive, so it is a switch with a default sort order of hierarchical but you can add cialpha or csalpha to get the other types of sort… Now that output looks like

G:\>adfind -default -f ou=* -dsq | adfind -sc adobjcnt:user -ictsv -stdinsort
OU=Domain Controllers,DC=test,DC=loc    0
OU=My,DC=test,DC=loc    0
OU=Email,OU=My,DC=test,DC=loc   1
OU=Groups,OU=My,DC=test,DC=loc  0
OU=Users,OU=My,DC=test,DC=loc   2
OU=PoSTest,DC=test,DC=loc       700001
OU=TestOU,DC=test,DC=loc        7
OU=CharTests,OU=TestOU,DC=test,DC=loc   1
OU=createtest,OU=TestOU,DC=test,DC=loc  10
OU=oneleveldown,OU=createtest,OU=TestOU,DC=test,DC=loc  1
OU=GPOTest,OU=TestOU,DC=test,DC=loc     0
OU=Groups,OU=TestOU,DC=test,DC=loc      0
OU=HideTest,OU=TestOU,DC=test,DC=loc    0
OU=joeperm,OU=TestOU,DC=test,DC=loc     0
OU=Outlook,OU=TestOU,DC=test,DC=loc     0
OU=permtest,OU=TestOU,DC=test,DC=loc    2
OU=Users,OU=TestOU,DC=test,DC=loc       10
OU=XXXTest,DC=test,DC=loc       0
OU=Deleted,OU=XXXTest,DC=test,DC=loc    0
OU=TestDisable,OU=XXXTest,DC=test,DC=loc        0
OU=TestXXX,OU=XXXTest,DC=test,DC=loc    4
OU=Skip,OU=TestXXX,OU=XXXTest,DC=test,DC=loc    1

Much better… ;o)

This and more in the new version of AdFind… AdFind V01.40.00 is expected to emerge from the cocoon on Feb 13, 2009.

     joe