July 12, 2004 at 7:26 pm
Is there a way to import Active Directory into a SQL table?
Kris
July 14, 2004 at 12:19 am
Hello Kristen,
It depends on what you mean by importing Active Directory. It is relatively straight forward to query AD using VB, you can then take the output of the AD query and put it wherever you like. I use the following VB form to query user and MS Exchange custom attribute information and dump it into a text file. It should be a relatively simple matter to alter the code to insert the code into a SQL DB. I wrote the code some time ago but I am fairly sure that I borrowed heavily from the examples on Microsoft's web site so you may want to visit the site yourself. The only thing to remember before you run the code is that you will need to add areference to OLE Automation and the Active DS Type Library.
I hope this helps you,
Dave.
VERSION 5.00
Begin VB.Form Form1
Caption = "Get AD User Details"
ClientHeight = 6870
ClientLeft = 60
ClientTop = 345
ClientWidth = 9225
Icon = "GetADUserDetails.frx":0000
LinkTopic = "Form1"
ScaleHeight = 6870
ScaleWidth = 9225
StartUpPosition = 3 'Windows Default
Visible = 0 'False
End
Attribute VB_Name = "Form1"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = False
Attribute VB_PredeclaredId = True
Attribute VB_Exposed = False
Dim iCount As Integer
Dim FileNumber
Private Sub Form_Load()
Dim rootDSE As IADs
iCount = 0
FileNumber = FreeFile
Open "ntlogin.txt" For Output As #FileNumber
PrintHeader
Set rootDSE = GetObject("LDAP://rootDSE")
Set oDomain = GetObject("LDAP://" & rootDSE.Get("DefaultNamingContext"))
EnumOUs oDomain.ADsPath
PrintFooter
Close #FileNumber
Unload Me
End Sub
Sub PrintFooter()
Dim strFooter
strFooter = "REC-CNT=" & iCount
Print #FileNumber, strFooter
End Sub
Sub PrintHeader()
Dim strHeader
strHeader = "DATE="
Select Case Month(Now)
Case 1
strHeader = strHeader & "1231" & Year(Now) - 1
Case 2
strHeader = strHeader & "0131" & Year(Now)
Case 3
strHeader = strHeader & "02"
If Year(Now) Mod 4 = 0 And Year(Now) Mod 400 <> 0 Then
strHeader = strHeader & "29" & Year(Now)
Else
strHeader = strHeader & "28" & Year(Now)
End If
Case 4
strHeader = strHeader & "0331" & Year(Now)
Case 5
strHeader = strHeader & "0430" & Year(Now)
Case 6
strHeader = strHeader & "0531" & Year(Now)
Case 7
strHeader = strHeader & "0630" & Year(Now)
Case 8
strHeader = strHeader & "0731" & Year(Now)
Case 9
strHeader = strHeader & "0831" & Year(Now)
Case 10
strHeader = strHeader & "0930" & Year(Now)
Case 11
strHeader = strHeader & "1031" & Year(Now)
Case 12
strHeader = strHeader & "1130" & Year(Now)
End Select
If Month(Now) < 10 Then
strHeader = strHeader & "0" & Month(Now)
Else
strHeader = strHeader & Month(Now)
End If
If Day(Now) < 10 Then
strHeader = strHeader & "0" & Day(Now)
Else
strHeader = strHeader & Day(Now)
End If
strHeader = strHeader & Year(Now)
If Hour(Now) < 10 Then
strHeader = strHeader & "0" & Hour(Now)
Else
strHeader = strHeader & Hour(Now)
End If
If Minute(Now) < 10 Then
strHeader = strHeader & "0" & Minute(Now)
Else
strHeader = strHeader & Minute(Now)
End If
If Second(Now) < 10 Then
strHeader = strHeader & "0" & Second(Now)
Else
strHeader = strHeader & Second(Now)
End If
strHeader = strHeader & "LONLOG"
Print #FileNumber, strHeader
End Sub
Sub EnumOUs(sADSPath)
Set oContainer = GetObject(sADSPath)
oContainer.Filter = Array("OrganizationalUnit")
For Each oOU In oContainer
If oOU.Name <> "OU=Account Staging" Then
EnumUsers oOU.ADsPath
EnumOUs oOU.ADsPath
End If
Next
End Sub
Sub EnumUsers(sADSPath)
Dim objIADsUser As IADsUser
Dim strCostCentre As String
Dim strOutput As String
Dim strUser As String
Dim strNYID As String
Set oContainer = GetObject(sADSPath)
oContainer.Filter = Array("User")
For Each oADobject In oContainer
If oADobject.Class = "user" Then
Set objIADsUser = oADobject
strCostCentre = GetCostCentre(objIADsUser)
If Len(strCostCentre) <> 0 Then
If UCase(Right(strCostCentre, 2)) <> "GB" And UCase(Right(strCostCentre, 2)) <> "GD" Then
iCount = iCount + 1
If Len(oADobject.SAMAccountName) > 20 Then
strOutput = Left(oADobject.SAMAccountName, 20)
Else
strOutput = oADobject.SAMAccountName & Space(20 - Len(oADobject.SAMAccountName))
End If
If Len(strCostCentre) > 30 Then
strOutput = strOutput & Left(strCostCentre, 30)
Else
strOutput = strOutput & strCostCentre & Space(30 - Len(strCostCentre))
End If
strUser = GetFirstName(objIADsUser) & " " & GetLastName(objIADsUser)
If strUser = " " Then
strUser = oADobject.SAMAccountName & Space(20 - Len(oADobject.SAMAccountName))
End If
If Len(strUser) > 30 Then
strOutput = strOutput & Left(strUser, 31)
Else
strOutput = strOutput & strUser & Space(31 - Len(strUser))
End If
strNYID = GetNYID(objIADsUser)
If strNYID = "SHARED MAILBOX" Then
strNYID = ""
End If
If Len(strNYID) > 20 Then
strOutput = strOutput & Left(strOutput, 20)
Else
strOutput = strOutput & strNYID & Space(20 - Len(strNYID))
End If
Print #FileNumber, strOutput
End If
End If
End If
Next
End Sub
Function GetFirstName(User) As String
On Error GoTo ErrHandler
Dim strFirstName As String
strFirstName = User.FirstName
GetFirstName = strFirstName
ErrHandler:
If Err.Number = &H8000500D Then
strFirstName = ""
End If
End Function
Function GetLastName(User) As String
On Error GoTo ErrHandler
Dim strLastName As String
strLastName = User.LastName
GetLastName = strLastName
ErrHandler:
If Err.Number = &H8000500D Then
strLastName = ""
End If
End Function
Function GetCostCentre(User) As String
On Error GoTo ErrHandler
Dim strCostCentre As String
Dim iPos As Integer
Dim strTemp
Dim strMatch
strMatch = "+"
strCostCentre = User.extensionAttribute1
If strCostCentre = "" Then
strTemp = User.Description
iPos = InStr(strTemp, strMatch)
If iPos <> 0 Then
strCostCentre = Right(strTemp, Len(strTemp) - iPos)
End If
End If
GetCostCentre = strCostCentre
ErrHandler:
If Err.Number = &H8000500D Then
strCostCentre = ""
End If
End Function
Function GetNYID(User) As String
On Error GoTo ErrHandler
Dim strNYID As String
strNYID = User.extensionAttribute3
GetNYID = strNYID
ErrHandler:
If Err.Number = &H8000500D Then
strNYID = ""
End If
End Function
July 14, 2004 at 1:47 am
Wow, that's awsome.....thanks
It may take me a while to go through all of this, but thanks heaps
Kris
July 14, 2004 at 1:05 pm
There is another way. You can query AD and put everything into a table. Here is how I have written an LDAP query before...
EXEC sp_addlinkedserver
'ADSI',
'Active Directory Services 2.5',
'ADSDSOObject',
'adsdatasource'
EXEC sp_addlinkedsrvlogin
'ADSI',
'false',
NULL,
NULL,
NULL
SELECT name As Name, sAMAccountName As UserName, title As Title,
physicalDeliveryOfficeName As Office, telephoneNumber As PhNumber,
mobile As CellPhone, facsimileTelephoneNumber As FaxNumber, mail As Email
FROM OpenQuery(
ADSI,'<;(&(objectCategory=Person">LDAP://CN=Users,DC=XYZ,DC=com>;(&(objectCategory=Person));
name, sAMAccountName, userPrincipalName, title,
physicalDeliveryOfficeName, telephoneNumber, mobile,
facsimileTelephoneNumber, UserAccountControl, mail, adspath;subtree')
Check in MSDN. There are articles that explain an LDAP query. That is where I found out how to do this.
July 14, 2004 at 6:37 pm
Thanks, that worked perfectly, you legend.
Just another question (I'm drawing a blank at the moment) I want to insert this data on a scheduled basis where the sSMAccountName is equal to each other, and I can't remember how to write that in T-SQL.
I've obviously got your script, but how do I write the update statement.
This is what I've got so far and I'm sure it's not what I want.
Update DT_Users
SET givenName = DT_Users.Firstname, sn = DT_Users.LastName,
company = DT_Users.Company, titla = DT_Users.Title, mail = DT_Users.Email
FROM DT_Users
Where sAMAccountName = DT_Users.Login
Select sAMAccountName AS Login, givenName AS FirstName, sn AS LastName,
company AS Company, title AS Title, mail AS Email
FROM OpenQuery(
ADSI,'<LDAP://Server/OU=User Accounts,DC=company,DC=com,DC=au>;(&(objectCategory=Person));
sAMAccountName, givenName, sn, company, title, mail,
adspath;subtree')
P.S if the sSMAccountName don't match I just want SQL to leave it in the table as is.
Thanks in advance for your help
Kris
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply