LOL. The first pass through AD finds all possible values of attribute1 and stores them in the hash. The second pass goes through and requeries based on that hash that was built from the first pass.
To put it another way. 1. Run a query against AD of attribute1=* 2. Parse the result set and populate the hash table with keys being the values of attribute1 and the values of the hash being the count of DNs with that specific key value as the value of attribute1. 3. Loop through the hash and generate a list of all attribute1 values that have multiple objects using that value. 4. Loop through the list from 3 and requery AD for each multiply used value with attribute1=somevalue This method would return each record with a duplicated attribute1 twice. However it has a much greater chance of being able to sit in memory while running when it is scaled up. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mulnick, Al Sent: Tuesday, April 05, 2005 3:32 PM To: [email protected] Subject: RE: [ActiveDir] GroupBy type queries in LDAP Maybe I'm missing something. How do you already know that attribute1 has a value of vala ? I mean, if you knew what the duplicate values were, couldn't you just query for them and return all the users that have that exact value specified and just fix it that way? Al -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of joe Sent: Tuesday, April 05, 2005 12:17 PM To: [email protected] Subject: RE: [ActiveDir] GroupBy type queries in LDAP Goal is to find users (by DN) with dupe valued for attribute1 as I understand it. Basically what I described is a two pass setup. You first find which attribute values are duplicated, then go back and get the duped DNs. E.G. AD has Rdn attribute1 Cn=someuser1 vala Cn=someuser2 vala Cn=someuser3 valc Cn=someuser4 valf Cn=someuser5 valc Cn=someuser6 vala Cn=someuser7 vald Cn=someuser8 valz You would have a resulting hash of vala 3 valc 2 vald 1 valf 1 Valz 1 You then go back and do a query of attribute1=3 and return DNs. Etc etc. That is where the additional network traffic and time come in Not sure where samaccountname came in, but I wouldn't use it as a unique key, I have seen it duped within a single domain and it can definitely be duped if this gets expanded to be forest wide[1]. Of course, if you know the scale and know it fits, pull all of the DNs and store them in one shot. This can be done by having the server sorting the result set or by using some associative array magic either in memory or on disk (I think this is done with perl tie but I haven't tried it). joe [1] Though that is a bad idea, don't dupe samaccountname's in a forest. Make samaccountname's unique within a forest, if not, it will bite you later. Even better, make samaccountname's unique in an org. Have one single authority granting them and keep them unique forever. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mulnick, Al Sent: Tuesday, April 05, 2005 11:54 AM To: [email protected] Subject: RE: [ActiveDir] GroupBy type queries in LDAP "...that value and insert into the hash using it as the key..." I think that would not work Joe. The reason being is that the original query was to ascertain which objects had duplicate values in attribute1. If your key were to have duplicates, that could be a problem. As for the SQL table, why go back and get the DN's (now that I reread it again?) Why not populate the table with attribute1 and DN's vs. samaccountname in the first place? Admittedly, it should be a much smaller subset of the population that you're reading that time through, but is it necessary? Just thinking of efficiency. Al -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of joe Sent: Tuesday, April 05, 2005 11:37 AM To: [email protected] Subject: RE: [ActiveDir] GroupBy type queries in LDAP I thought of a way last night that would do this ok[1] with hash objects unless the attribute was rather large though number of objects shouldn't be as big a factor. Basically it is more scaleable but not infinitely scaleable which can be said for anything. You would substitute speed and network traffic for reduced memory footprint. Basically you would dump all objects with attribute1=*, then simply pull off that value and insert into the hash using it as the key with the count as the value of that entry. Once you build that, you cycle through looking for any entries that have a count > 0 and reissue a query for that exact attribute value and output those results directly. You could have it watching the amount of data it is holding and once you surpass a specific level, have it use a simple little text file DB that perl does as well. Heck if you don't mind the disk i/o hit you could do that from the start and maintain the DNs as well. Obviously these solutions are stretching so you don't have to buy, and worse yet, set up and maintain, a SQL Server which is just one more security risk. If you already have a SQL Server laying around being maintained, the easiest solution as Al mentions is to use it. Hmm another off the wall solution would be to spin up AD/AM. Set up an object for every new value of attribute1 and then set up a link (FL/BL) relationship with the attribute objects and the user objects. Then when you want to know what users are using what attribute, you just dump the link values. Still not an LDAP query only solution but kept up to date in real time if you are constantly syncing with no additional query time needed. joe [1] Ok being entirely relative. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mulnick, Al Sent: Tuesday, April 05, 2005 9:28 AM To: [email protected] Subject: RE: [ActiveDir] GroupBy type queries in LDAP For millions of records? Easier? Appropriate? "Please note that the directory contains millions of objects and iterating through them will be painful." I wouldn't (could, but I wouldn't.) Why? I'd likely need this information on a repeatable basis maybe as some sort of grooming process for the accounts I manage. I suspect the right tool for the job would be a synchronization tool that syncs, or at least replicates the data to SQL from AD at a regular interval. Some stored query then spits out the report I'm looking for an I could take some sort of action based on that either automated or other. DB's do this type of query very well and I see nothing that would indicate to me that this would be a different kind of app. Like joe (or Joe in this case) I don't like putting things into SQL very often, if for no other reason than the added cost of licensing a SQL server for an application. That licensing needs to be fixed if you buy an app that requires SQL (think MIIS, SMS, MOM, etc), but in the end it comes down to the right tool for the job. A DB is the right tool for the problem stated in my humble opinion. That's me though. I can't script like Deji and joe(Joe). :) -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Monday, April 04, 2005 6:26 PM To: [email protected] Subject: RE: [ActiveDir] GroupBy type queries in LDAP Would putting the output into a dictionary set and then sorting and writing them out not be feasible? Would this not be easier (and on-the-flyish) than dumping it into SQL? Sincerely, D�j� Ak�m�l�f�, MCSE+M MCSA+M MCP+I Microsoft MVP - Dir. Services / Security www.readymaids.com - we know IT www.akomolafe.com Do you now realize that Today is the Tomorrow you were worried about Yesterday? -anon -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gil Kirkpatrick Sent: Monday, April 04, 2005 2:47 PM To: [email protected] Subject: RE: [ActiveDir] GroupBy type queries in LDAP Can't do that in LDAP... About the best you can do is use the LDAP sort control to get a list of entries sorted by Attribute1, but that only gets you halfway to what you want. I suspect Al's strategy is the best way to go. -gil -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mulnick, Al Sent: Monday, April 04, 2005 2:34 PM To: [email protected] Subject: RE: [ActiveDir] GroupBy type queries in LDAP Is it just user objects? (&(objectClass=User)(objectCategory=Person)(Attribute1=*)) Would return all user objects that have a value for Attribute1. If you only wanted all user objects where Attribute1 was a duplicate, I would *think* you have to query based on what's filled in there. i.e. Attribute1=someduplicatevalue or something similar. Might be more productive to bring all of the needed data into a SQL table and then do your query. LDAP isn't going to do that type of logic that I'm aware of. I'd love to hear differently though :) Al -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeremy Palenchar Sent: Monday, April 04, 2005 5:23 PM To: [email protected] Subject: [ActiveDir] GroupBy type queries in LDAP OK, LDAP evangelists, I need to query our customer-facing AD for a list of all the users who share a particular attribute. Let's call that attribute "Attribute1." So, if two people have the same value in Attribute1, I need their DN. The trick is, that I want the results for all possible values of Attribute1. In SQL, I would use group by Attribute1 having count(Attribute1) >1 to get a list of all Attribute1 values where more than one object had the same value. I would then join that back to the table to get a list of all the DN's with those values of Attribute1. Is there a way to do this with an LDAP query. Please note that the directory contains millions of objects and iterating through them will be painful. -Jeremy List info : http://www.activedir.org/List.aspx List FAQ : http://www.activedir.org/ListFAQ.aspx List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/ List info : http://www.activedir.org/List.aspx List FAQ : http://www.activedir.org/ListFAQ.aspx List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/ List info : http://www.activedir.org/List.aspx List FAQ : http://www.activedir.org/ListFAQ.aspx List archive: http://www.mail-archive.com/activedir%40mail.activedir.org/
