Courion Tip–other users for SQL table: LDIF file creation

The company I work for haven’t yet integrated LDAP account creation into our Courion system. We just haven’t gotten to it yet, but as both a stopgap solution and as a mental exercise, it occurred to me that I could create an *.ldif file from the information in the Courion Database that we use. And that I could probably automate the ldap add process. So, the code below is what I have come up with.

I am working on a modification to this to allow selection of the ldap groups that the user should be made a member of, and will post that later.

Note that I have done a search and replace for all information that might be considered a security risk, or could provide too much information about where I work. Therefore, let me say up front that this worked before I did the search and replace, and I hope it works for you (or at least provides enough information to get you started).

The key steps are to get an *.ldif file to model yours after, and the SQL query commands – note that this runs in a trusted mode – meaning that YOU have rights to access the data…

The usual warnings about copy and pasting from a website apply – the lines probably end in the wrong place, and the quotation marks are probably mangled.

 
'==========================================================================
'
' NAME: LDIF from Courion Profile.vbs
'
'
'==========================================================================
Option Explicit
'On Error Resume Next

Dim objFSO, objFolder, objFile,objShell, DocumentPath, WshShell, network, newuser, SN, GivenName, EmailAddress, Work_Order_Number
Dim LDIF_File, CMD_File, objTextFile, UserObj, tempstring, DateTime

' OpenTextFile Method needs a Const value
' ForAppending = 8 ForReading = 1, ForWriting = 2
Const ForAppending = 8
Const ForWriting = 2

' ==================================================
' Prepare TimeStamp
' ==================================================
DateTime=Year(Now)
If len(Month(Now))=1 Then
DateTime=DateTime&"0"
End If

DateTime=DateTime&Month(Now)
If len(Day(Now))=1 Then
DateTime=DateTime&"0"
End If

DateTime=DateTime&Day(Now)
If len(Hour(Now))=1 Then
DateTime=DateTime&"0"
End If

DateTime=DateTime&Hour(Now)
If len(Minute(Now))=1 Then
DateTime=DateTime&"0"
End If

DateTime=DateTime&Minute(Now)
If len(Second(Now))=1 Then
DateTime=DateTime&"0"
End If

DateTime=DateTime&Second(Now)& "z"

' un comment next line for debugging
'wscript.echo datetime

 

'=============================================
' where to store the files.
Set WshShell = Wscript.CreateObject("Wscript.Shell")
DocumentPath=WshShell.RegRead("HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders\Personal")
' un comment next line for debugging
'    WScript.Echo(DocumentPath)
DocumentPath = DocumentPath+"\Work_Order\"

' Create the File System Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Note If..Exists. Then, Else ... End If construction
If objFSO.FolderExists(DocumentPath) Then
Set objFolder = objFSO.GetFolder(DocumentPath)
'   WScript.Echo DocumentPath & " already created "
Else
Set objFolder = objFSO.CreateFolder(DocumentPath)
WScript.Echo "Just created " & DocumentPath
End If

If err.number = vbEmpty then
Set objShell = CreateObject("WScript.Shell")
Else WScript.echo "VBScript Error: " & err.number
End If

'=============================================
' Prompt for Work_Order_Number
Work_Order_Number = InputBox("Enter the Work_Order_Number for this request")
Work_Order_Number = Trim(Work_Order_Number)
'    WScript.Echo("Processing Work_Order_Number"&Work_Order_Number)

'=============================================
' What user ID
newuser = InputBox("Enter the USER ID that needs added to LDIF_SYSTEM")
newuser = Trim(newuser)
newuser = UCase(newuser)
'    WScript.Echo(newuser)
If Len(newuser)<>7 Then
WScript.Echo("bad user name {wrong length}, exiting")
WScript.Quit()
Else
REM        WScript.Echo("Processing username: "&newuser)
End If

 

REM http://www.scriptinganswers.com/forum2/forum_posts.asp?TID=771
REM Some of the commented out lines below are from this article. I left
REM Them in for reference...
Dim objCN, strConnection
Set objCN = CreateObject("ADODB.Connection")

REM strConnection = "Driver={SQL Server};Server=TRINITY;Database=NorthWind;Trusted_Connection=TRUE"
strConnection = "Driver={SQL Server};Server=SQL_Server_Name;Database=Courion_Database;Trusted_Connection=TRUE"

objCN.Open strConnection

Dim strSQLQuery
strSQLQuery = "Select * from Profile where Profileuid = "&"'"&newuser&"'"
REM     wscript.echo strSQLQuery

'"SELECT * FROM Users"

Dim objRS
Set objRS=CreateObject("ADODB.Recordset")

Set objRS = objCN.Execute(strSQLQuery)

Do Until objRS.EOF
'access columns using objRS("column")
'    WScript.Echo objRS.Fields("ProfileUID") & vbTab & objRS.Fields("Email")
'    WScript.Echo objRS.Fields("ProfileUID").value
GivenName=objRS.Fields("Firstname").value
sn=objRS.Fields("Lastname").value
EmailAddress=objRS.Fields("Email").value
'objRS.Fields("Name") & " [" & Trim(objRS.Fields("SAM")) & "]" & vbTab & objRS.Fields("computer")
objRS.MoveNext
Loop
objRS.Close

objCN.Close

REM Display the information on screen so that we can verify the accuracy..

Wscript.Echo("========== Please verify ==========" _
& vbCrLf & "Work_Order_Number: "&Work_Order_Number _
& vbCrLf & "User Name (profileuid): "&newuser _
& vbCrLf & "First Name (given name): "&GivenName _
& vbCrLf & "Sir Name (last name): "&sn _
& vbCrLf & "Email Address: "&EmailAddress)

'=============================================
' Create the File System Object *.ldif
' Note that this does create a default password, which we do NOT provide to the end user
' we have a "new Target password" workflow to handle this.
LDIF_File = DocumentPath& Work_Order_Number & "_LDIF_SYSTEM_" & newuser & ".LDIF"
Set objFSO = nothing
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(LDIF_File)
Set objFile = nothing
Set objFile = objFSO.OpenTextFile(LDIF_File, ForAppending, True)
objFile.WriteLine("dn: cn="&newuser&",cn=users,dc=location,dc=Company_Name,dc=com")
objFile.WriteLine("userpassword: {MD4}zQdsgmg+io632432ewAR/fMg==")
objFile.WriteLine("orclpassword: {x- orcldbpwd}1.0:EF0324235CFB45A506B44")
objFile.WriteLine("mail: "&EmailAddress)
objFile.WriteLine("uid:"&newuser)
objFile.WriteLine("objectclass: top")
objFile.WriteLine("objectclass: person")
objFile.WriteLine("objectclass: inetorgperson")
objFile.WriteLine("objectclass: organizationalperson")
objFile.WriteLine("objectclass: orcluser")
objFile.WriteLine("objectclass: orcluserv2")
objFile.WriteLine("orclisenabled: ENABLED")
objFile.WriteLine("orclactivestartdate: "&DateTime)
objFile.WriteLine("sn: "&sn)
objFile.WriteLine("GivenName: "&GivenName)
objFile.WriteLine("cn: "&newuser)
objFile.Close

'=============================================
' The following code is up to you to deciede. This creates a *.cmd file
' with a hard coded reference to IBM's ldap software (available on the internet on IBM's site)
' You need to choose to use it or not, I cannot vouch for the licensing.
' In addition, you may not want to create this, but instead use a tool like softera ldap admin to import the file.
' Create the File System Object *.cmd
CMD_File = DocumentPath& Work_Order_Number & "_LDIF_SYSTEM_" & newuser & ".cmd"
Set objFSO = nothing
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(CMD_File)
Set objFile = nothing
Set objFile = objFSO.OpenTextFile(CMD_File, ForAppending, True)

tempstring ="cd "&"""C:\Program Files\IBM\LDAP\V6.2\bin\"""
' wscript.echo tempstring
objFile.WriteLine(tempstring)
tempstring = "ldapadd -h LDIF_SYSTEM -p 389 -D cn=ADMIN_USER,cn=Users,dc=location,dc=Company_Name,dc=com -w ADMIN_PASSWORD -v -f "
tempstring = tempstring & CHR(34) & LDIF_File & CHR(34)
wscript.echo tempstring
objFile.WriteLine(tempstring)
objFile.WriteLine("pause")
objFile.WriteLine("exit")
objFile.Close

'=============================================
'Open up location where *.cmd file was created
If err.number = vbEmpty Then
Set objShell = CreateObject("WScript.Shell")
objShell.run ("Explorer" &" " & DocumentPath)
Else WScript.echo "VBScript Error: " & err.number
End If

WScript.Quit

Advertisements

Posted November 26, 2010 by mmdmurphy in Courion Tip

%d bloggers like this: