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
1  3735 
Hi
Instead you may want to check out: http://www.rlmueller.net/List%20User%20Groups.htm
John
"rcamarda" <rc******@cablespeed.com> wrote in message
news:11*********************@g43g2000cwa.googlegro ups.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Torsten Mohr |
last post by:
Hi,
is there some description available to overwrite the import
hook? By googling i found out so far that i need to overwrite
__builtins__.__import__ with something else.
Can i also do this...
|
by: Jon |
last post by:
It appears that (windows) python searches in the current working
directory before looking in the local site-packages directory, or that
'.' comes first in sys.path? The problem arises when I made...
|
by: Jeremy Sanders |
last post by:
Hi - Is it possible to override the import process so that if in my program
I do
import foo.bar
Python will look for bar in a directory which isn't called foo?
I want my module/program to...
|
by: Quique |
last post by:
Hello,
I've got a problem importing a worksheet Excel into a temporary table in
access. All the information is imported but the table is not ordered as it
is originally in excel.
I'm using a...
|
by: =?Utf-8?B?am9uaWdy?= |
last post by:
I wrote a simple VB.NET application that imports and edits CSV files.
Now I’d like to “lock” the raw (pre-import) CSV files so these cannot be
opened separately. It is not high-sensitive...
|
by: spohle |
last post by:
hi,
i have written a small project for myself all in seperate classes and
each of the classes lives in a seperate file. now i am looking for an
import structure something like import wx, and...
|
by: thread |
last post by:
Hi All
i'm building a database in access and i want to restrict permissions.
from the access i can just limit the posiblity to unhide an hidden
table and in this way i can preventing the users...
|
by: Eonasdan |
last post by:
Greetings! I hope some can help with out with this. I am trying to write a scripting using bcp to import large amounts of data from a text file that is tab delimited. The problems I have run into is...
|
by: RachH |
last post by:
I import tables from one Access 2003 database into another 1x/day (for various reasons, linking tables does not meet the need). The database I am importing from is password protected at the general...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development projectplanning, coding, testing,...
| |
