Posted by rcamarda on 12/21/05 23:00

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]