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/ 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/
