Posted by John Bell on 11/11/09 11:35

Hi

Instead you may want to check out:
http://www.rlmueller.net/List%20User%20Groups.htm

John

"rcamarda" <rcamarda@cablespeed.com> wrote in message
news:1135198823.474342.97500@g43g2000cwa.googlegroups.com...
> This is a tuffie, but I think I'll learn new techniques in SQL.
> I wish to put data from MS Active Directory and put it into a table.
> Specificly I want user information (first name, last name and so forth)
> and the groups that they belong into a SQL table.
> LDIFDE is a utility that can create a csv file from an AD server. This
> is a sample output:
>
> dn: CN=rob camarda,OU=Corporate,OU=Geographic
> Locations,DC=strayer,DC=edu
> changetype: add
> objectClass: top
> objectClass: person
> objectClass: organizationalPerson
> objectClass: user
> cn: rob camarda
> givenName: rob
> memberOf: CN=Arlington Admin,OU=Campus Domain Admin,DC=strayer,DC=edu
> memberOf:
> CN=Arlington,OU=Arlington,OU=Region2,OU=Geographic
> Locations,DC=strayer,DC=edu
> memberOf: CN=RN Report Consumers,OU=Cognos ReportNet,DC=strayer,DC=edu
> sAMAccountName: rob.camarda
>
> dn: CN=Robert A. Camarda,OU=TechnologyGroup,DC=strayer,DC=edu
> changetype: add
> objectClass: top
> objectClass: person
> objectClass: organizationalPerson
> objectClass: user
> cn: Robert A. Camarda
> givenName: Robert
> memberOf: CN=Role Regional Director,OU=Roles,DC=strayer,DC=edu
> memberOf: CN=Role Campus Director,OU=Roles,DC=strayer,DC=edu
> memberOf: CN=TLSAdmin,OU=Talisma-Users,DC=strayer,DC=edu
> memberOf: CN=ASPTestReports,OU=Roles,DC=strayer,DC=edu
> memberOf: CN=IT Report Authors,OU=Roles,DC=strayer,DC=edu
> memberOf: CN=Developers,OU=TechnologyGroup,DC=strayer,DC=edu
> memberOf: CN=SQL Backup Admin,OU=TechnologyGroup,DC=strayer,DC=edu
> memberOf: CN=RN Report MetaData Modelers,OU=Cognos
> ReportNet,DC=strayer,DC=edu
> memberOf:
> CN=RN Corporate,OU=Corporate,OU=Region2,OU=Geographic
> Locations,DC=strayer,DC=
> edu
> memberOf:
> CN=Arlington,OU=Arlington,OU=Region2,OU=Geographic
> Locations,DC=strayer,DC=edu
> memberOf: CN=RN Administrator System,OU=Cognos
> ReportNet,DC=strayer,DC=edu
> memberOf: CN=RN Administrator Server,OU=Cognos
> ReportNet,DC=strayer,DC=edu
> memberOf: CN=RN Report Authors,OU=Cognos ReportNet,DC=strayer,DC=edu
> memberOf: CN=Backup Operators,CN=Builtin,DC=strayer,DC=edu
> memberOf: CN=Domain Admins,CN=Users,DC=strayer,DC=edu
> memberOf: CN=Administrators,CN=Builtin,DC=strayer,DC=edu
> sAMAccountName: robert.camarda
>
> In this output, each user is separated by a blank line. sAMAccountName
> is the user's login ID to ADS. Lines starting with memberOf: shows the
> path for each group the user belongs.
>
> My thought is to load the text data into a SQL table with the PK being
> the line number. This way the data will stay together. The second
> column would be just text, varchar(100).
> I'd like to end up with a table something like
> USER_ID, GROUP_MEMBERSHIP, GIVENNAME
> In the example of robert.camarda, that user belongs to 7 groups, so
> there would be 7 records, one for each group. I think once I have this
> part, I can build my final table with PK's an all the good-house
> keeping of a SQL Table.
>
> Now the part that I have no idea how to solve:
> How do I convert the data from unfriendly for databases, to something I
> can use?
> 1. I know I have a new user when I find dn:
> 2. I know I am done with the user when I get a blank (null) line.
> 3. I know what I want to populate rows with name and the contents once
> I find rows starting with memberOf:
> 4. It appears there is a max line length that LDIFDE will export, and
> starts a new line. So, it will be necessary to join lines.
> I would think this is a combo of CURSORS, a do/while loop and other
> assorted magic.
>
> If someone can help me get started, I would have something to reseach
> or model from. As of now, im staring and a blank page and not sure how
> to start. Maybe someone knows of a simular problem that can share the
> SQL.
> TIA
> Rob
>

[Back to original message]