Solved

Create User if Current Date is Listed or Before

Posted on 2006-11-17
11
248 Views
Last Modified: 2010-05-18
The field strEnter has a value of DD/MM/YYYY.
This is the date the user has started here. I need it to not only check if user exist but only create the user if strEnter is strCDate or Earlier than strCDate.
The strExit has a value of DD/MM/YYYY HH:MM.
This is the date the user has left here. I need it to delete these users if they exist in AD past strCDate.

I have also posted the script again i had to put some () around some of the Writeline's.

Option Explicit

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'Dont Edit Above
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Const WORKING_DIRECTORY = "C:\"
Const LOG_FILE_PATH = "C:\"

Const EXCHANGE_SERVER = "XX.XX.XX.XX"

Const SEND_EMAIL = "YES"
Const EMAIL_FROM = "UserEmail@Domain.com"
Const EMAIL_TO = "UserEmail@Domain.com"
Const EMAIL_SUBJECT = "New User Added to Network "
Const EMAIL_BODY = "See Attached for New Users and Password Created on "

Const HOME_DRIVE = "H:"

Const YEAR_7 = "Year 7"
Const YEAR_8 = "Year 8"
Const YEAR_9 = "Year 9"

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'Dont Edit Below
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

'
' Functions
'

Function UserExists(strUserName)
     ' Searching for User ID. We use this method because you can't duplicate the SAMAccountName within the
     ' domain without upsetting it.
     
     Dim objNetwork, objUser
     Dim booUserExists

     booUserExists = False

     On Error Resume Next : Err.Clear

     Set objNetwork = CreateObject("WScript.Network")
     Set objUser = GetObject("WinNT://" & objNetwork.UserDomain & "/" & strUserName & ", user")
     If Err.Number = 0 Then
          MsgBox "User Found " & strUserID
          objLogFile.WriteLine "User Already Exists " & strUserID
          booUserExists = True
     End If
     On Error Goto 0
     Set objUser = Nothing
     Set objNetwork = Nothing
     
     UserExists = booUserExists
End Function

Function FindObject(strObjectName, strDN)
     ' Finds an OU Object within AD by Name

     Dim objRootDSE, objOU, objSubOU
     Dim strResult
     
     If strDN = "" Then
          Set objRootDSE = GetObject("LDAP://RootDSE")
          Set objOU = GetObject("LDAP://" & objRootDSE.Get("defaultNamingContext"))
          Set objRootDSE = Nothing
     Else
          Set objOU = GetObject("LDAP://" & strDN)
     End If
     
     objOU.Filter = Array("organizationalUnit")

     For Each objSubOU in objOU
          If objSubOU.Get("name") = strObjectName Then
               strResult = objSubOU.Get("distinguishedName")
          ElseIf strResult = "" Then
               strResult = FindObject(strObjectName, objSubOU.Get("distinguishedName"))
          End If
     Next
     FindObject = strResult
End Function

'
' Subroutines
'

Sub EmailUser
     ' Sends out an Email

     Dim objMail

     Set objMail = CreateObject("CDO.Message")

     objMail.From = EMAIL_FROM
     objMail.To = EMAIL_TO
     objMail.Subject = EMAIL_SUBJECT & strCDate
     objMail.TextBody = EMAIL_BODY & strCDate

     'An attachment can be included.
     objMail.AddAttachment strPasswordExport

     objMail.Configuration.Fields.Item _
          ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
     objMail.Configuration.Fields.Item _
          ("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
          = EXCHANGE_SERVER
     objMail.Configuration.Fields.Item _
          ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") _
          = 25

     objMail.Configuration.Fields.Update

     objMail.Send
     Set objMail = Nothing
End Sub

'
' Main Code
'

' Global Variables

Dim objFileSys, objLogFile, objInputFile, objFile, objRootDSE, objOU, objUser
Dim strCDate, strLogFile, strExportFromCases, strPasswordExport, strData
Dim strUserID, strFirstName, strSurname, strGroup, strYear, strHomeFolder
Dim strEnter, strExit, strPassword, strDN, strDomain
Dim arrInputData, arrData

' Global Objects

Set objFileSys = CreateObject("Scripting.FileSystemObject")

' Generate and Store all Filenames - Saves repetition

strCDate = Day(Date) & "-" & Month(Date) & "-" & Year(Date)
strLogFile = WORKING_DIRECTORY & strCDate & " AutoUserImport.log"
strExportFromCases = WORKING_DIRECTORY & strCDate & " ExportFromCases.csv"
strPasswordExport = WORKING_DIRECTORY & strCDate & " ExportWithPasswords.csv"

' Create the LogFile Object

Set objLogFile = objFileSys.CreateTextFile(strLogFile, True)
objLogFile.WriteLine "Log File Created " & strLogFile

' Connect to the Import File and read everything into the Array InputData

objLogFile.WriteLine "Opening Cases Original Export " & strExportFromCases

Set objInputFile = objFileSys.OpenTextFile(strExportFromCases)
arrInputData = Split(objInputFile.ReadAll, vbNewline)
Set objInputFile = Nothing

' Create the new Export File with Passwords added

Set objFile = objFileSys.CreateTextFile(strPasswordExport, True)
On Error Resume Next
For Each strData In arrInputData
     objFile.writeline(strData & "," & Right("00000" & Int(Rnd()*1000000), 6))
     
     strUserID = UCase(Split(strData, ",")(0))

     objLogFile.WriteLine ("Generating Password For " &  strUserID & " Saving Data to " (strPasswordExport))
Next
objFile.Close
Set objFile = Nothing

' Reattach to the Password File for Reading

Set objInputFile = objFileSys.OpenTextFile(strPasswordExport)
arrInputData = Split(objInputFile.ReadAll, vbNewline)

For each strData In arrInputData
     objLog.WriteLine ("Reading User Information " & strUserID & " From " & strPasswordExport)

     ' The following are not currently used:
     ' strGroup, strEnter, strExit

     arrData = Split(strData, ",")

     strUserID = Ucase(arrData(0))
     strFirstname = Lcase(arrData(1))
     strFirstname = UCase(Left(strFirstname, 1)) & Mid(strFirstname, 2, Len(strFirstname))
     strSurname = LCase(arrData(2))
     strSurname = UCase(Left(strSurname, 1)) & Mid(strSurname, 2, Len(strSurname))
     strSurname = Replace(strSurname, "'", "")
     strGroup = UCase(arrData(3))
     strYear = LCase (arrData(4))
     strHomeFolder = LCase(arrData(4))
     strEnter = LCase(arrData(5))
     strExit = LCase (Split(arrData(6)))
     strPassword = Lcase(arrData(7))
         
     ' Select is just neater and shorter for this. : is used to replace a normal line break in the script to keep it neat.
     ' strYear needs to be populated with one of these values before this is called.
     
     Select Case strYear
          Case "7" : strYear = YEAR_7
          Case "8" : strYear = YEAR_8
          Case "9" : strYear = YEAR_9
     End Select
     
     ' Find the OU - Calls the FindObject subroutine above - That deals with connecting to the current domain.
     ' It uses Recursion to find it. strDN will be blank if it can't be found. There is one limitation with the
     ' current version, it will not find OUs under the default Users folder (as this is Container class not
     ' organizationalUnit).
     
     strDN = FindObject(strYear, "")

     ' Only do the next bit if the User doesn't exist and we've managed to find the OU it goes into
     
     If UserExists(strUserID) = False And strDN <> "" Then

          ' Connect to the OU and Create a User - This demonstrates filling in a few other bits of information.
          ' Everything is based around the Username at the moment, it's all flexible though and depends on what
          ' you have in the CSV.
         
          Set objRootDSE = GetObject("LDAP://RootDSE")
          strDomain = Replace(objRootDSE.Get("defaultNamingContext"), ",DC=", ".")
          strDomain = Replace(strDomain, "DC=", "")
          Set objRootDSE = Nothing
         
          ' The Users container name must begin with CN=. Generated from the GivenName and Surname.
         
          Set objOU = GetObject("LDAP://" & strDN)
          Set objUser = objOU.Create("user", "CN=" & strFirstName & " " & strSurname)
          objUser.SetInfo
         
          ' Setup all the name entries
         
          objUser.Put "sAMAccountName", strUserID
          objUser.Put "givenName", strFirstName
          objUser.Put "sN", strSurname
          objUser.Put "displayName", strFirstName & " " & strSurname
          objUser.Put "userPrincipalName", strUserID & "@" & strDomain
          objUser.SetInfo
         
          ' Setup the Home Directory information
         
          objUser.Put "homeDrive", HOME_DRIVE
          objUser.Put "homeDirectory", strHomeFolder
          objUser.SetInfo
         
          ' Set the new password
         
          objUser.SetPassword strPassword
         
          ' Get rid of the Objects we have - especially important as we're looping and creating multiple users.
         
          Set objUser = Nothing
          Set objOU = Nothing
     End If
Next

' Send out an Email with the new users and their passwords

If SEND_EMAIL = "YES" Then
     objLogFile.WriteLine ("Sending Email to " & strEmailTo & " With Attachment " (strPasswordExport))
     EmailUser
End If

' Log File and FileSystemObject

objLogFile.WriteLine "Log File Close and Save " & strLogFile
objLogFile.Close

Set objLogFile = Nothing
Set objFileSys = Nothing
0
Comment
Question by:dion_p1
  • 6
  • 5
11 Comments
 
LVL 70

Expert Comment

by:Chris Dent
ID: 17965282

You don't need the () around the statements in WriteLine, but it's pretty unimportant in the scheme of things. To compare Dates you need strCDate back in Date format, then you can convert the date you read in from the CSV.

strCDate just contains the current date, so we can just use the Date function to get that again instead.

For safety I've commented out the line that Deletes the User (objOU.Delete "user", strUserCN), so at present it will just log what it's going to do. If you're happy it's effecting the right users then remove the comment character from that line.



Option Explicit

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'Dont Edit Above
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Const WORKING_DIRECTORY = "C:\"
Const LOG_FILE_PATH = "C:\"

Const EXCHANGE_SERVER = "XX.XX.XX.XX"

Const SEND_EMAIL = "YES"
Const EMAIL_FROM = "UserEmail@Domain.com"
Const EMAIL_TO = "UserEmail@Domain.com"
Const EMAIL_SUBJECT = "New User Added to Network "
Const EMAIL_BODY = "See Attached for New Users and Password Created on "

Const HOME_DRIVE = "H:"

Const YEAR_7 = "Year 7"
Const YEAR_8 = "Year 8"
Const YEAR_9 = "Year 9"

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'Dont Edit Below
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

'
' Functions
'

Function UserExists(strUserName)
      ' Searching for User ID. We use this method because you can't duplicate the SAMAccountName within the
      ' domain without upsetting it.
      
      Dim objNetwork, objUser
      Dim booUserExists

      booUserExists = False

      On Error Resume Next : Err.Clear

      Set objNetwork = CreateObject("WScript.Network")
      Set objUser = GetObject("WinNT://" & objNetwork.UserDomain & "/" & strUserName & ", user")
      If Err.Number = 0 Then
            MsgBox "User Found " & strUserID
            objLogFile.WriteLine "User Already Exists " & strUserID
            booUserExists = True
      End If
      On Error Goto 0
      Set objUser = Nothing
      Set objNetwork = Nothing
      
      UserExists = booUserExists
End Function

Function FindObject(strObjectName, strDN)
      ' Finds an OU Object within AD by Name

      Dim objRootDSE, objOU, objSubOU
      Dim strResult
      
      If strDN = "" Then
            Set objRootDSE = GetObject("LDAP://RootDSE")
            Set objOU = GetObject("LDAP://" & objRootDSE.Get("defaultNamingContext"))
            Set objRootDSE = Nothing
      Else
            Set objOU = GetObject("LDAP://" & strDN)
      End If
      
      objOU.Filter = Array("organizationalUnit")

      For Each objSubOU in objOU
            If objSubOU.Get("name") = strObjectName Then
                  strResult = objSubOU.Get("distinguishedName")
            ElseIf strResult = "" Then
                  strResult = FindObject(strObjectName, objSubOU.Get("distinguishedName"))
            End If
      Next
      FindObject = strResult
End Function

Function GetObjectDN(strObject)
      ' Finds the DistinguishedName of an Object

      Const ADS_NAME_INITTYPE_GC = 3
      Const ADS_NAME_TYPE_1779 = 1
      Const ADS_NAME_TYPE_NT4 = 3

      Dim objNameTranslate, objNetwork
      Dim strDomain, strDN

      On Error Resume Next

      Set objNameTranslate = CreateObject("NameTranslate")
      Set objNetwork = CreateObject("WScript.Network")

      strDomain = objNetwork.UserDomain

      objNameTranslate.Init ADS_NAME_INITTYPE_GC, ""
      objNameTranslate.Set ADS_NAME_TYPE_NT4, strDomain & "\" & strObject
      strDN = objNameTranslate.Get(ADS_NAME_TYPE_1779)

      Set objNameTranslate = Nothing
      Set objNetwork = Nothing

      On Error Goto 0

      GetObjectDN = strDN
End Function

'
' Subroutines
'

Sub EmailUser
      ' Sends out an Email

      Dim objMail

      Set objMail = CreateObject("CDO.Message")

      objMail.From = EMAIL_FROM
      objMail.To = EMAIL_TO
      objMail.Subject = EMAIL_SUBJECT & strCDate
      objMail.TextBody = EMAIL_BODY & strCDate

      'An attachment can be included.
      objMail.AddAttachment strPasswordExport

      objMail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
      objMail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
            = EXCHANGE_SERVER
      objMail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") _
            = 25

      objMail.Configuration.Fields.Update

      objMail.Send
      Set objMail = Nothing
End Sub

'
' Main Code
'

' Global Variables

Dim objFileSys, objLogFile, objInputFile, objFile, objRootDSE, objOU, objUser
Dim strCDate, strLogFile, strExportFromCases, strPasswordExport, strData
Dim strUserID, strFirstName, strSurname, strGroup, strYear, strHomeFolder
Dim strEnter, strExit, strPassword, strDN, strDomain, strUserDN, strUserCN
Dim arrInputData, arrData
Dim dtmEnter, dtmExit

' Global Objects

Set objFileSys = CreateObject("Scripting.FileSystemObject")

' Generate and Store all Filenames - Saves repetition

strCDate = Day(Date) & "-" & Month(Date) & "-" & Year(Date)
strLogFile = WORKING_DIRECTORY & strCDate & " AutoUserImport.log"
strExportFromCases = WORKING_DIRECTORY & strCDate & " ExportFromCases.csv"
strPasswordExport = WORKING_DIRECTORY & strCDate & " ExportWithPasswords.csv"

' Create the LogFile Object

Set objLogFile = objFileSys.CreateTextFile(strLogFile, True)
objLogFile.WriteLine "Log File Created " & strLogFile

' Connect to the Import File and read everything into the Array InputData

objLogFile.WriteLine "Opening Cases Original Export " & strExportFromCases

Set objInputFile = objFileSys.OpenTextFile(strExportFromCases)
arrInputData = Split(objInputFile.ReadAll, vbNewline)
Set objInputFile = Nothing

' Create the new Export File with Passwords added

Set objFile = objFileSys.CreateTextFile(strPasswordExport, True)
On Error Resume Next
For Each strData In arrInputData
      objFile.writeline(strData & "," & Right("00000" & Int(Rnd() * 1000000), 6))
      
      strUserID = UCase(Split(strData, ",")(0))

      objLogFile.WriteLine ("Generating Password For " &  strUserID & " Saving Data to " (strPasswordExport))
Next
objFile.Close
Set objFile = Nothing

' Reattach to the Password File for Reading

Set objInputFile = objFileSys.OpenTextFile(strPasswordExport)
arrInputData = Split(objInputFile.ReadAll, vbNewline)

For each strData In arrInputData
      objLog.WriteLine ("Reading User Information " & strUserID & " From " & strPasswordExport)

      ' The following are not currently used:
      ' strGroup

      arrData = Split(strData, ",")

      strUserID = Ucase(arrData(0))
      strFirstname = Lcase(arrData(1))
      strFirstname = UCase(Left(strFirstname, 1)) & Mid(strFirstname, 2, Len(strFirstname))
      strSurname = LCase(arrData(2))
      strSurname = UCase(Left(strSurname, 1)) & Mid(strSurname, 2, Len(strSurname))
      strSurname = Replace(strSurname, "'", "")
      strGroup = UCase(arrData(3))
      strYear = LCase (arrData(4))
      strHomeFolder = LCase(arrData(4))
      strEnter = LCase(arrData(5))
      strExit = LCase (Split(arrData(6)))
      strPassword = Lcase(arrData(7))

      ' Convert the values in strEnter and strExit to Date.

      dtmEnter = CDate(strEnter)
      dtmExit = CDate(strExit)

      ' Select is just neater and shorter for this. : is used to replace a normal line break in the script to keep it neat.
      ' strYear needs to be populated with one of these values before this is called.
      
      Select Case strYear
            Case "7" : strYear = YEAR_7
            Case "8" : strYear = YEAR_8
            Case "9" : strYear = YEAR_9
      End Select
      
      ' Find the OU - Calls the FindObject subroutine above - That deals with connecting to the current domain.
      ' It uses Recursion to find it. strDN will be blank if it can't be found. There is one limitation with the
      ' current version, it will not find OUs under the default Users folder (as this is Container class not
      ' organizationalUnit).
      
      strDN = FindObject(strYear, "")

      ' Only do the next bit if the User doesn't exist and we've managed to find the OU it goes into
      
      If UserExists(strUserID) = False And (strDN <> "") And (dtmEnter <= Date) Then

            ' Connect to the OU and Create a User - This demonstrates filling in a few other bits of information.
            ' Everything is based around the Username at the moment, it's all flexible though and depends on what
            ' you have in the CSV.
          
            Set objRootDSE = GetObject("LDAP://RootDSE")
            strDomain = Replace(objRootDSE.Get("defaultNamingContext"), ",DC=", ".")
            strDomain = Replace(strDomain, "DC=", "")
            Set objRootDSE = Nothing
          
            ' The Users container name must begin with CN=. Generated from the GivenName and Surname.
          
            Set objOU = GetObject("LDAP://" & strDN)
            Set objUser = objOU.Create("user", "CN=" & strFirstName & " " & strSurname)
            objUser.SetInfo
          
            ' Setup all the name entries
          
            objUser.Put "sAMAccountName", strUserID
            objUser.Put "givenName", strFirstName
            objUser.Put "sN", strSurname
            objUser.Put "displayName", strFirstName & " " & strSurname
            objUser.Put "userPrincipalName", strUserID & "@" & strDomain
            objUser.SetInfo
          
            ' Setup the Home Directory information
          
            objUser.Put "homeDrive", HOME_DRIVE
            objUser.Put "homeDirectory", strHomeFolder
            objUser.SetInfo
          
            ' Set the new password
          
            objUser.SetPassword strPassword
          
            ' Get rid of the Objects we have - especially important as we're looping and creating multiple users.
          
            Set objUser = Nothing
            Set objOU = Nothing
            
      ElseIf dtmExit < Date And strDN <> "" Then

            ' Deletes the User Account from the OU. Uses GetObjectDN to retrieve the DistinguishedName for the User.
            ' This is required to get the Container Name for the user.
            
            Set objOU = GetObject("LDAP://" & strDN)
            strUserDN = GetObjectDN(strUserID)

            If strUserDN <> "" Then
                  Set objUser = GetObject("LDAP://" & strUserDN)
                  strUserCN = objUser.Name
            
                  objLogFile.WriteLine "Deleting User " & objUser.Get("displayName") & ": " & strUserID
            
                  Set objUser = Nothing
            
                  ' This line is responsible for deleting the User
            
                  ' objOU.Delete "user", strUserCN
            End If
      
            Set objOU = Nothing
      End If
Next

' Send out an Email with the new users and their passwords

If SEND_EMAIL = "YES" Then
      objLogFile.WriteLine ("Sending Email to " & strEmailTo & " With Attachment " (strPasswordExport))
      EmailUser
End If

' Log File and FileSystemObject

objLogFile.WriteLine "Log File Close and Save " & strLogFile
objLogFile.Close

Set objLogFile = Nothing
Set objFileSys = Nothing
0
 

Author Comment

by:dion_p1
ID: 17969425
I forgot to mention that sometimes the field strExit will be empty, if the field is emtpy the users need to still exist in Domain.

Im not sure why but, the first time i ran it it created users that strExit was before the Current Date. <---- Every second time from there im assuming it would recreate the user if it doesn't exist or is deleted as its supposed to be.
The second time i ran it it deleted those users! <---- As Expected

I think it needs to do something like this

If user exist and strExit is not Null and strExit is Before The Current Date then Delete User.
If user doesn't exist and strEnter is Before the Curren Date or Equal to and strExit is Null and strExit is After Current Date then Create User.

0
 

Author Comment

by:dion_p1
ID: 17969448
Sorry Reposted last Bit

If user exist and strExit is not Null and strExit is Before The Current Date then Delete User.
If user doesn't exist and strEnter is Before the Curren Date or Equal to and strExit is Null and/or strExit is After Current Date then Create User.
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 18006971

Sorry for the delay, bit of a bad week.

Hopefully this revision addresses the problems above:



Option Explicit

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'Dont Edit Above
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Const WORKING_DIRECTORY = "C:\"
Const LOG_FILE_PATH = "C:\"

Const EXCHANGE_SERVER = "XX.XX.XX.XX"

Const SEND_EMAIL = "YES"
Const EMAIL_FROM = "UserEmail@Domain.com"
Const EMAIL_TO = "UserEmail@Domain.com"
Const EMAIL_SUBJECT = "New User Added to Network "
Const EMAIL_BODY = "See Attached for New Users and Password Created on "

Const HOME_DRIVE = "H:"

Const YEAR_7 = "Year 7"
Const YEAR_8 = "Year 8"
Const YEAR_9 = "Year 9"

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'Dont Edit Below
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

'
' Functions
'

Function UserExists(strUserName)
      ' Searching for User ID. We use this method because you can't duplicate the SAMAccountName within the
      ' domain without upsetting it.
      
      Dim objNetwork, objUser
      Dim booUserExists

      booUserExists = False

      On Error Resume Next : Err.Clear

      Set objNetwork = CreateObject("WScript.Network")
      Set objUser = GetObject("WinNT://" & objNetwork.UserDomain & "/" & strUserName & ", user")
      If Err.Number = 0 Then
            MsgBox "User Found " & strUserID
            objLogFile.WriteLine "User Already Exists " & strUserID
            booUserExists = True
      End If
      On Error Goto 0
      Set objUser = Nothing
      Set objNetwork = Nothing
      
      UserExists = booUserExists
End Function

Function FindObject(strObjectName, strDN)
      ' Finds an OU Object within AD by Name

      Dim objRootDSE, objOU, objSubOU
      Dim strResult
      
      If strDN = "" Then
            Set objRootDSE = GetObject("LDAP://RootDSE")
            Set objOU = GetObject("LDAP://" & objRootDSE.Get("defaultNamingContext"))
            Set objRootDSE = Nothing
      Else
            Set objOU = GetObject("LDAP://" & strDN)
      End If
      
      objOU.Filter = Array("organizationalUnit")

      For Each objSubOU in objOU
            If objSubOU.Get("name") = strObjectName Then
                  strResult = objSubOU.Get("distinguishedName")
            ElseIf strResult = "" Then
                  strResult = FindObject(strObjectName, objSubOU.Get("distinguishedName"))
            End If
      Next
      FindObject = strResult
End Function

Function GetObjectDN(strObject)
      ' Finds the DistinguishedName of an Object

      Const ADS_NAME_INITTYPE_GC = 3
      Const ADS_NAME_TYPE_1779 = 1
      Const ADS_NAME_TYPE_NT4 = 3

      Dim objNameTranslate, objNetwork
      Dim strDomain, strDN

      On Error Resume Next

      Set objNameTranslate = CreateObject("NameTranslate")
      Set objNetwork = CreateObject("WScript.Network")

      strDomain = objNetwork.UserDomain

      objNameTranslate.Init ADS_NAME_INITTYPE_GC, ""
      objNameTranslate.Set ADS_NAME_TYPE_NT4, strDomain & "\" & strObject
      strDN = objNameTranslate.Get(ADS_NAME_TYPE_1779)

      Set objNameTranslate = Nothing
      Set objNetwork = Nothing

      On Error Goto 0

      GetObjectDN = strDN
End Function

'
' Subroutines
'

Sub EmailUser
      ' Sends out an Email

      Dim objMail

      Set objMail = CreateObject("CDO.Message")

      objMail.From = EMAIL_FROM
      objMail.To = EMAIL_TO
      objMail.Subject = EMAIL_SUBJECT & strCDate
      objMail.TextBody = EMAIL_BODY & strCDate

      'An attachment can be included.
      objMail.AddAttachment strPasswordExport

      objMail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
      objMail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
            = EXCHANGE_SERVER
      objMail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") _
            = 25

      objMail.Configuration.Fields.Update

      objMail.Send
      Set objMail = Nothing
End Sub

'
' Main Code
'

' Global Variables

Dim objFileSys, objLogFile, objInputFile, objFile, objRootDSE, objOU, objUser
Dim strCDate, strLogFile, strExportFromCases, strPasswordExport, strData
Dim strUserID, strFirstName, strSurname, strGroup, strYear, strHomeFolder
Dim strEnter, strExit, strPassword, strDN, strDomain, strUserDN, strUserCN
Dim arrInputData, arrData
Dim dtmEnter, dtmExit
Dim booCreateUser, booDeleteUser

' Global Objects

Set objFileSys = CreateObject("Scripting.FileSystemObject")

' Generate and Store all Filenames - Saves repetition

strCDate = Day(Date) & "-" & Month(Date) & "-" & Year(Date)
strLogFile = WORKING_DIRECTORY & strCDate & " AutoUserImport.log"
strExportFromCases = WORKING_DIRECTORY & strCDate & " ExportFromCases.csv"
strPasswordExport = WORKING_DIRECTORY & strCDate & " ExportWithPasswords.csv"

' Create the LogFile Object

Set objLogFile = objFileSys.CreateTextFile(strLogFile, True)
objLogFile.WriteLine "Log File Created " & strLogFile

' Connect to the Import File and read everything into the Array InputData

objLogFile.WriteLine "Opening Cases Original Export " & strExportFromCases

Set objInputFile = objFileSys.OpenTextFile(strExportFromCases)
arrInputData = Split(objInputFile.ReadAll, vbNewline)
Set objInputFile = Nothing

' Create the new Export File with Passwords added

Set objFile = objFileSys.CreateTextFile(strPasswordExport, True)
On Error Resume Next
For Each strData In arrInputData
      objFile.writeline(strData & "," & Right("00000" & Int(Rnd() * 1000000), 6))
      
      strUserID = UCase(Split(strData, ",")(0))

      objLogFile.WriteLine ("Generating Password For " &  strUserID & " Saving Data to " (strPasswordExport))
Next
objFile.Close
Set objFile = Nothing

' Reattach to the Password File for Reading

Set objInputFile = objFileSys.OpenTextFile(strPasswordExport)
arrInputData = Split(objInputFile.ReadAll, vbNewline)

For each strData In arrInputData
      objLog.WriteLine ("Reading User Information " & strUserID & " From " & strPasswordExport)

      ' The following are not currently used:
      ' strGroup

      arrData = Split(strData, ",")

      strUserID = Ucase(arrData(0))
      strFirstname = Lcase(arrData(1))
      strFirstname = UCase(Left(strFirstname, 1)) & Mid(strFirstname, 2, Len(strFirstname))
      strSurname = LCase(arrData(2))
      strSurname = UCase(Left(strSurname, 1)) & Mid(strSurname, 2, Len(strSurname))
      strSurname = Replace(strSurname, "'", "")
      strGroup = UCase(arrData(3))
      strYear = LCase (arrData(4))
      strHomeFolder = LCase(arrData(4))
      strEnter = LCase(arrData(5))
      strExit = LCase (Split(arrData(6)))
      strPassword = Lcase(arrData(7))

      ' Convert the values in strEnter and strExit to Date.

      dtmEnter = CDate(strEnter)
      If strExit <> "" Then
            dtmExit = CDate(strExit)
      End If

      ' Select is just neater and shorter for this. : is used to replace a normal line break in the script to keep it neat.
      ' strYear needs to be populated with one of these values before this is called.
      
      Select Case strYear
            Case "7" : strYear = YEAR_7
            Case "8" : strYear = YEAR_8
            Case "9" : strYear = YEAR_9
      End Select
      
      ' Find the OU - Calls the FindObject subroutine above - That deals with connecting to the current domain.
      ' It uses Recursion to find it. strDN will be blank if it can't be found. There is one limitation with the
      ' current version, it will not find OUs under the default Users folder (as this is Container class not
      ' organizationalUnit).
      
      strDN = FindObject(strYear, "")

      ' Check to see if the User should be Created

      booCreateUser = False
      If UserExists(strUserID) = False And strDN <> "" And dtmEnter <= Date Then
            If strExit <> "" Then
                  If dtmExit < Date Then
                        booCreateUser = True
                  End If
            Else
                  booCreateUser = True
            End If
      End If

      ' Check to see if the User should be Deleted

      booDeleteUser = False
      If UserExists(strUserID) = True And strDN <> "" Then
            If strExit <> "" Then
                  If dtmExit > Date Then
                        booDeleteUser = True
                  End If
            End If
      End If

      If booCreateUser = True Then

            ' Connect to the OU and Create a User - This demonstrates filling in a few other bits of information.
            ' Everything is based around the Username at the moment, it's all flexible though and depends on what
            ' you have in the CSV.
          
            Set objRootDSE = GetObject("LDAP://RootDSE")
            strDomain = Replace(objRootDSE.Get("defaultNamingContext"), ",DC=", ".")
            strDomain = Replace(strDomain, "DC=", "")
            Set objRootDSE = Nothing
          
            ' The Users container name must begin with CN=. Generated from the GivenName and Surname.
          
            Set objOU = GetObject("LDAP://" & strDN)
            Set objUser = objOU.Create("user", "CN=" & strFirstName & " " & strSurname)
            objUser.SetInfo
          
            ' Setup all the name entries
          
            objUser.Put "sAMAccountName", strUserID
            objUser.Put "givenName", strFirstName
            objUser.Put "sN", strSurname
            objUser.Put "displayName", strFirstName & " " & strSurname
            objUser.Put "userPrincipalName", strUserID & "@" & strDomain
            objUser.SetInfo
          
            ' Setup the Home Directory information
          
            objUser.Put "homeDrive", HOME_DRIVE
            objUser.Put "homeDirectory", strHomeFolder
            objUser.SetInfo
          
            ' Set the new password
          
            objUser.SetPassword strPassword
          
            ' Get rid of the Objects we have - especially important as we're looping and creating multiple users.
          
            Set objUser = Nothing
            Set objOU = Nothing
            
      ElseIf booDeleteUser = True Then

            ' Deletes the User Account from the OU. Uses GetObjectDN to retrieve the DistinguishedName for the User.
            ' This is required to get the Container Name for the user.
            
            Set objOU = GetObject("LDAP://" & strDN)
            strUserDN = GetObjectDN(strUserID)

            If strUserDN <> "" Then
                  Set objUser = GetObject("LDAP://" & strUserDN)
                  strUserCN = objUser.Name
            
                  objLogFile.WriteLine "Deleting User " & objUser.Get("displayName") & ": " & strUserID
            
                  Set objUser = Nothing
            
                  ' This line is responsible for deleting the User
            
                  ' objOU.Delete "user", strUserCN
            End If
      
            Set objOU = Nothing
      End If
Next
      
' Send out an Email with the new users and their passwords

If SEND_EMAIL = "YES" Then
      objLogFile.WriteLine ("Sending Email to " & strEmailTo & " With Attachment " (strPasswordExport))
      EmailUser
End If

' Log File and FileSystemObject

objLogFile.WriteLine "Log File Close and Save " & strLogFile
objLogFile.Close

Set objLogFile = Nothing
Set objFileSys = Nothing
0
 

Author Comment

by:dion_p1
ID: 18013962
Sot sure whats happening here but.....
CSV File format is - <USERNAME>,<SURNAME>,<FIRSTNAME>,<GROUP>,<YEAR>,<ENTRY>,<EXIT>

PAR0001,PARSONS,Dion,10,10,29/01/2001,8/12/2004 12:25
PAR0002,PARSONS,Dion,10,10,29/01/2001,
PAR0003,PARSONS,Dion,10,10,03/12/2006,
PAR0004,SIVELL,Dion,10,10,29/01/2001,8/12/2004 12:25
PAR0005,SIVELL,Dion,10,10,29/01/2001,
PAR0006,SIVELL,Dion,10,10,03/12/2006,
PAR0007,PARSONS,Dion,10,10,29/01/2001,8/12/2004 12:25
PAR0008,PARSONS,Dion,10,10,29/01/2001,
PAR0009,PARSONS,Dion,10,10,03/12/2006,

This is what its doing

PAR0001 & PAR0004 & PAR0007 - (User still exits in Active Directory, They should have been deleted because Exit Date Exist and has Past)
PAR0002 & PAR0005 & PAR0008 - (User is not created, This user Should be Created becuase Entry Date has Past, User has no Exit Date)
PAR0003 & PAR0006 & PAR0009- (User isn't Created because Current Date is before the User is scheduled to start, This is Correct)

Its also not telling me when its creating a User
And its logging twice for Each User that Exist that the User Exist in the Domain

Sorry after this one i will log another question and post the link to the new question here. On the same script but just another part of it.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 70

Expert Comment

by:Chris Dent
ID: 18045834

Is that the exact file format? It will need a minor alteration to cope with the names being swapped (the script below is changed).

Otherwise this is the fixed version (I hope):




Option Explicit

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'Dont Edit Above
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Const WORKING_DIRECTORY = "C:\"
Const LOG_FILE_PATH = "C:\"

Const EXCHANGE_SERVER = "XX.XX.XX.XX"

Const SEND_EMAIL = "YES"
Const EMAIL_FROM = "UserEmail@Domain.com"
Const EMAIL_TO = "UserEmail@Domain.com"
Const EMAIL_SUBJECT = "New User Added to Network "
Const EMAIL_BODY = "See Attached for New Users and Password Created on "

Const HOME_DRIVE = "H:"

Const YEAR_7 = "Year 7"
Const YEAR_8 = "Year 8"
Const YEAR_9 = "Year 9"

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'Dont Edit Below
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

'
' Functions
'

Function UserExists(strUserName)
      ' Searching for User ID. We use this method because you can't duplicate the SAMAccountName within the
      ' domain without upsetting it.
      
      Dim objNetwork, objUser
      Dim booUserExists

      booUserExists = False

      On Error Resume Next : Err.Clear

      Set objNetwork = CreateObject("WScript.Network")
      Set objUser = GetObject("WinNT://" & objNetwork.UserDomain & "/" & strUserName & ", user")
      If Err.Number = 0 Then
            MsgBox "User Found " & strUserID
            objLogFile.WriteLine "User Already Exists " & strUserID
            booUserExists = True
      End If
      On Error Goto 0
      Set objUser = Nothing
      Set objNetwork = Nothing
      
      UserExists = booUserExists
End Function

Function FindObject(strObjectName, strDN)
      ' Finds an OU Object within AD by Name

      Dim objRootDSE, objOU, objSubOU
      Dim strResult
      
      If strDN = "" Then
            Set objRootDSE = GetObject("LDAP://RootDSE")
            Set objOU = GetObject("LDAP://" & objRootDSE.Get("defaultNamingContext"))
            Set objRootDSE = Nothing
      Else
            Set objOU = GetObject("LDAP://" & strDN)
      End If
      
      objOU.Filter = Array("organizationalUnit")

      For Each objSubOU in objOU
            If objSubOU.Get("name") = strObjectName Then
                  strResult = objSubOU.Get("distinguishedName")
            ElseIf strResult = "" Then
                  strResult = FindObject(strObjectName, objSubOU.Get("distinguishedName"))
            End If
      Next
      FindObject = strResult
End Function

Function GetObjectDN(strObject)
      ' Finds the DistinguishedName of an Object

      Const ADS_NAME_INITTYPE_GC = 3
      Const ADS_NAME_TYPE_1779 = 1
      Const ADS_NAME_TYPE_NT4 = 3

      Dim objNameTranslate, objNetwork
      Dim strDomain, strDN

      On Error Resume Next

      Set objNameTranslate = CreateObject("NameTranslate")
      Set objNetwork = CreateObject("WScript.Network")

      strDomain = objNetwork.UserDomain

      objNameTranslate.Init ADS_NAME_INITTYPE_GC, ""
      objNameTranslate.Set ADS_NAME_TYPE_NT4, strDomain & "\" & strObject
      strDN = objNameTranslate.Get(ADS_NAME_TYPE_1779)

      Set objNameTranslate = Nothing
      Set objNetwork = Nothing

      On Error Goto 0

      GetObjectDN = strDN
End Function

'
' Subroutines
'

Sub EmailUser
      ' Sends out an Email

      Dim objMail

      Set objMail = CreateObject("CDO.Message")

      objMail.From = EMAIL_FROM
      objMail.To = EMAIL_TO
      objMail.Subject = EMAIL_SUBJECT & strCDate
      objMail.TextBody = EMAIL_BODY & strCDate

      'An attachment can be included.
      objMail.AddAttachment strPasswordExport

      objMail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
      objMail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
            = EXCHANGE_SERVER
      objMail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") _
            = 25

      objMail.Configuration.Fields.Update

      objMail.Send
      Set objMail = Nothing
End Sub

'
' Main Code
'

' Global Variables

Dim objFileSys, objLogFile, objInputFile, objFile, objRootDSE, objOU, objUser
Dim strCDate, strLogFile, strExportFromCases, strPasswordExport, strData
Dim strUserID, strFirstName, strSurname, strGroup, strYear, strHomeFolder
Dim strEnter, strExit, strPassword, strDN, strDomain, strUserDN, strUserCN
Dim arrInputData, arrData
Dim dtmEnter, dtmExit
Dim booUserExists, booCreateUser, booDeleteUser

' Global Objects

Set objFileSys = CreateObject("Scripting.FileSystemObject")

' Generate and Store all Filenames - Saves repetition

strCDate = Day(Date) & "-" & Month(Date) & "-" & Year(Date)
strLogFile = WORKING_DIRECTORY & strCDate & " AutoUserImport.log"
strExportFromCases = WORKING_DIRECTORY & strCDate & " ExportFromCases.csv"
strPasswordExport = WORKING_DIRECTORY & strCDate & " ExportWithPasswords.csv"

' Create the LogFile Object

Set objLogFile = objFileSys.CreateTextFile(strLogFile, True)
objLogFile.WriteLine "Log File Created " & strLogFile

' Connect to the Import File and read everything into the Array InputData

objLogFile.WriteLine "Opening Cases Original Export " & strExportFromCases

Set objInputFile = objFileSys.OpenTextFile(strExportFromCases)
arrInputData = Split(objInputFile.ReadAll, vbNewline)
Set objInputFile = Nothing

' Create the new Export File with Passwords added

Set objFile = objFileSys.CreateTextFile(strPasswordExport, True)
On Error Resume Next
For Each strData In arrInputData
      objFile.writeline(strData & "," & Right("00000" & Int(Rnd() * 1000000), 6))
      
      strUserID = UCase(Split(strData, ",")(0))

      objLogFile.WriteLine ("Generating Password For " &  strUserID & " Saving Data to " (strPasswordExport))
Next
objFile.Close
Set objFile = Nothing

' Reattach to the Password File for Reading

Set objInputFile = objFileSys.OpenTextFile(strPasswordExport)
arrInputData = Split(objInputFile.ReadAll, vbNewline)

For each strData In arrInputData
      objLog.WriteLine ("Reading User Information " & strUserID & " From " & strPasswordExport)

      ' The following are not currently used:
      ' strGroup

      arrData = Split(strData, ",")

      strUserID = Ucase(arrData(0))
      strFirstname = Lcase(arrData(1))
      strFirstname = UCase(Left(strFirstname, 1)) & Mid(strFirstname, 2, Len(strFirstname))
      strSurname = LCase(arrData(2))
      strSurname = UCase(Left(strSurname, 1)) & Mid(strSurname, 2, Len(strSurname))
      strSurname = Replace(strSurname, "'", "")
      strGroup = UCase(arrData(3))
      strYear = LCase(arrData(4))
      strHomeFolder = LCase(arrData(4))
      strEnter = LCase(arrData(5))
      strExit = LCase(arrData(6))
      strPassword = Lcase(arrData(7))

      ' Convert the values in strEnter and strExit to Date.

      dtmEnter = CDate(strEnter)
      If strExit <> "" Then
            dtmExit = CDate(strExit)
      End If

      ' Select is just neater and shorter for this. : is used to replace a normal line break in the script to keep it neat.
      ' strYear needs to be populated with one of these values before this is called.
      
      Select Case strYear
            Case "7" : strYear = YEAR_7
            Case "8" : strYear = YEAR_8
            Case "9" : strYear = YEAR_9
      End Select
      
      ' Find the OU - Calls the FindObject subroutine above - That deals with connecting to the current domain.
      ' It uses Recursion to find it. strDN will be blank if it can't be found. There is one limitation with the
      ' current version, it will not find OUs under the default Users folder (as this is Container class not
      ' organizationalUnit).
      
      strDN = FindObject(strYear, "")

      ' Check if the User Exists
      
      booUserExists = UserExists(strUserID)

      ' Check to see if the User should be Created

      booCreateUser = False
      If booUserExists = False And strDN <> "" And dtmEnter <= Date Then
            booCreateUser = True
      End If

      ' Check to see if the User should be Deleted

      booDeleteUser = False
      If booUserExists = True And strDN <> "" Then
            If strExit <> "" Then
                  If dtmExit < Date Then
                        booDeleteUser = True
                  End If
            End If
      End If

      If booCreateUser = True And booDeleteUser = False Then

            ' Connect to the OU and Create a User - This demonstrates filling in a few other bits of information.
            ' Everything is based around the Username at the moment, it's all flexible though and depends on what
            ' you have in the CSV.
          
            Set objRootDSE = GetObject("LDAP://RootDSE")
            strDomain = Replace(objRootDSE.Get("defaultNamingContext"), ",DC=", ".")
            strDomain = Replace(strDomain, "DC=", "")
            Set objRootDSE = Nothing
          
            ' The Users container name must begin with CN=. Generated from the GivenName and Surname.
          
            Set objOU = GetObject("LDAP://" & strDN)
            Set objUser = objOU.Create("user", "CN=" & strFirstName & " " & strSurname)
            objUser.SetInfo
          
            ' Setup all the name entries
          
            objUser.Put "sAMAccountName", strUserID
            objUser.Put "givenName", strFirstName
            objUser.Put "sN", strSurname
            objUser.Put "displayName", strFirstName & " " & strSurname
            objUser.Put "userPrincipalName", strUserID & "@" & strDomain
            objUser.SetInfo
          
            ' Setup the Home Directory information
          
            objUser.Put "homeDrive", HOME_DRIVE
            objUser.Put "homeDirectory", strHomeFolder
            objUser.SetInfo
          
            ' Set the new password
          
            objUser.SetPassword strPassword
          
            ' Get rid of the Objects we have - especially important as we're looping and creating multiple users.
          
            Set objUser = Nothing
            Set objOU = Nothing
            
      ElseIf booDeleteUser = True Then

            ' Deletes the User Account from the OU. Uses GetObjectDN to retrieve the DistinguishedName for the User.
            ' This is required to get the Container Name for the user.
            
            Set objOU = GetObject("LDAP://" & strDN)
            strUserDN = GetObjectDN(strUserID)

            If strUserDN <> "" Then
                  Set objUser = GetObject("LDAP://" & strUserDN)
                  strUserCN = objUser.Name
            
                  objLogFile.WriteLine "Deleting User " & objUser.Get("displayName") & ": " & strUserID
            
                  Set objUser = Nothing
            
                  ' This line is responsible for deleting the User
            
                  ' objOU.Delete "user", strUserCN
            End If
      
            Set objOU = Nothing
      End If
Next
      
' Send out an Email with the new users and their passwords

If SEND_EMAIL = "YES" Then
      objLogFile.WriteLine ("Sending Email to " & strEmailTo & " With Attachment " (strPasswordExport))
      EmailUser
End If

' Log File and FileSystemObject

objLogFile.WriteLine "Log File Close and Save " & strLogFile
objLogFile.Close

Set objLogFile = Nothing
Set objFileSys = Nothing
0
 

Author Comment

by:dion_p1
ID: 18048756
It doesn't create the user now & it still doesnt delete user.

I tested Create User by deleting all users out of AD
I tested Delete user be recreating a user PAR0001. When i created this user and run the script it did msgbox me saying User Found.

Sometimes we might have the same name but different username in the csv file the previous post is almots the exact format it will be.
0
 
LVL 70

Expert Comment

by:Chris Dent
ID: 18052376

There were a few bugs in it, hopefully all of these have been resolved now.



Option Explicit

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'Dont Edit Above
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Const WORKING_DIRECTORY = "C:\"
Const LOG_FILE_PATH = "C:\"

Const EXCHANGE_SERVER = "XX.XX.XX.XX"

Const SEND_EMAIL = "YES"
Const EMAIL_FROM = "UserEmail@Domain.com"
Const EMAIL_TO = "UserEmail@Domain.com"
Const EMAIL_SUBJECT = "New User Added to Network "
Const EMAIL_BODY = "See Attached for New Users and Password Created on "

Const HOME_DRIVE = "H:"

Const YEAR_7 = "Year 7"
Const YEAR_8 = "Year 8"
Const YEAR_9 = "Year 9"

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'Dont Edit Below
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

'
' Functions
'

Function UserExists(strUserName)
      ' Searching for User ID. We use this method because you can't duplicate the SAMAccountName within the
      ' domain without upsetting it.
      
      Dim objNetwork, objUser
      Dim booUserExists

      booUserExists = False

      On Error Resume Next : Err.Clear

      Set objNetwork = CreateObject("WScript.Network")
      Set objUser = GetObject("WinNT://" & objNetwork.UserDomain & "/" & strUserName & ", user")
      If Err.Number = 0 Then
            MsgBox "User Found " & strUserID
            objLogFile.WriteLine "User Already Exists " & strUserID
            booUserExists = True
      End If
      On Error Goto 0
      Set objUser = Nothing
      Set objNetwork = Nothing
      
      UserExists = booUserExists
End Function

Function FindObject(strObjectName, strDN)
      ' Finds an OU Object within AD by Name

      Dim objRootDSE, objOU, objSubOU
      Dim strResult
      
      If strDN = "" Then
            Set objRootDSE = GetObject("LDAP://RootDSE")
            Set objOU = GetObject("LDAP://" & objRootDSE.Get("defaultNamingContext"))
            Set objRootDSE = Nothing
      Else
            Set objOU = GetObject("LDAP://" & strDN)
      End If
      
      objOU.Filter = Array("organizationalUnit")

      For Each objSubOU in objOU
            If objSubOU.Get("name") = strObjectName Then
                  strResult = objSubOU.Get("distinguishedName")
            ElseIf strResult = "" Then
                  strResult = FindObject(strObjectName, objSubOU.Get("distinguishedName"))
            End If
      Next
      FindObject = strResult
End Function

Function GetObjectDN(strObject)
      ' Finds the DistinguishedName of an Object

      Const ADS_NAME_INITTYPE_GC = 3
      Const ADS_NAME_TYPE_1779 = 1
      Const ADS_NAME_TYPE_NT4 = 3

      Dim objNameTranslate, objNetwork
      Dim strDomain, strDN

      On Error Resume Next

      Set objNameTranslate = CreateObject("NameTranslate")
      Set objNetwork = CreateObject("WScript.Network")

      strDomain = objNetwork.UserDomain

      objNameTranslate.Init ADS_NAME_INITTYPE_GC, ""
      objNameTranslate.Set ADS_NAME_TYPE_NT4, strDomain & "\" & strObject
      strDN = objNameTranslate.Get(ADS_NAME_TYPE_1779)

      Set objNameTranslate = Nothing
      Set objNetwork = Nothing

      On Error Goto 0

      GetObjectDN = strDN
End Function

'
' Subroutines
'

Sub EmailUser
      ' Sends out an Email

      Dim objMail

      Set objMail = CreateObject("CDO.Message")

      objMail.From = EMAIL_FROM
      objMail.To = EMAIL_TO
      objMail.Subject = EMAIL_SUBJECT & strCDate
      objMail.TextBody = EMAIL_BODY & strCDate

      'An attachment can be included.
      objMail.AddAttachment strPasswordExport

      objMail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
      objMail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserver") _
            = EXCHANGE_SERVER
      objMail.Configuration.Fields.Item _
            ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") _
            = 25

      objMail.Configuration.Fields.Update

      objMail.Send
      Set objMail = Nothing
End Sub

'
' Main Code
'

' Global Variables

Dim objFileSys, objLogFile, objInputFile, objFile, objRootDSE, objOU, objUser
Dim strCDate, strLogFile, strExportFromCases, strPasswordExport, strData
Dim strUserID, strFirstName, strSurname, strGroup, strYear, strHomeFolder
Dim strEnter, strExit, strPassword, strDN, strDomain, strUserDN, strUserCN
Dim arrInputData, arrData
Dim dtmEnter, dtmExit
Dim booUserExists, booCreateUser, booDeleteUser

' Global Objects

Set objFileSys = CreateObject("Scripting.FileSystemObject")

' Generate and Store all Filenames - Saves repetition

strCDate = Day(Date) & "-" & Month(Date) & "-" & Year(Date)
strLogFile = WORKING_DIRECTORY & strCDate & " AutoUserImport.log"
strExportFromCases = WORKING_DIRECTORY & strCDate & " ExportFromCases.csv"
strPasswordExport = WORKING_DIRECTORY & strCDate & " ExportWithPasswords.csv"

' Create the LogFile Object

Set objLogFile = objFileSys.CreateTextFile(strLogFile, True)
objLogFile.WriteLine "Log File Created " & strLogFile

' Connect to the Import File and read everything into the Array InputData

objLogFile.WriteLine "Opening Cases Original Export " & strExportFromCases

Set objInputFile = objFileSys.OpenTextFile(strExportFromCases)
arrInputData = Split(objInputFile.ReadAll, vbNewline)
Set objInputFile = Nothing

' Create the new Export File with Passwords added

Set objFile = objFileSys.CreateTextFile(strPasswordExport, True)
For Each strData In arrInputData
      If strData <> "" Then
            objFile.writeline(strData & "," & Right("00000" & Int(Rnd() * 1000000), 6))
            strUserID = UCase(Split(strData, ",")(0))
            objLogFile.WriteLine "Generating Password For " &  strUserID & " Saving Data to " & (strPasswordExport)
      End If
Next
objFile.Close
Set objFile = Nothing

' Reattach to the Password File for Reading

Set objInputFile = objFileSys.OpenTextFile(strPasswordExport)
arrInputData = Split(objInputFile.ReadAll, vbNewline)

For each strData In arrInputData
      objLogFile.WriteLine "Reading User Information " & strUserID & " From " & strPasswordExport

      ' The following are not currently used:
      ' strGroup

      arrData = Split(strData, ",")
      If UBound(arrData) = 7 Then
            strUserID = Ucase(arrData(0))
            strFirstname = Lcase(arrData(1))
            strFirstname = UCase(Left(strFirstname, 1)) & Mid(strFirstname, 2, Len(strFirstname))
            strSurname = LCase(arrData(2))
            strSurname = UCase(Left(strSurname, 1)) & Mid(strSurname, 2, Len(strSurname))
            strSurname = Replace(strSurname, "'", "")
            strGroup = UCase(arrData(3))
            strYear = LCase(arrData(4))
            strHomeFolder = LCase(arrData(4))
            strEnter = LCase(arrData(5))
            strExit = LCase(arrData(6))
            strPassword = Lcase(arrData(7))
      
            ' Convert the values in strEnter and strExit to Date.
      
            dtmEnter = CDate(strEnter)
            If strExit <> "" Then
                  dtmExit = CDate(strExit)
            End If
      
            ' Select is just neater and shorter for this. : is used to replace a normal line break in the script to keep it neat.
            ' strYear needs to be populated with one of these values before this is called.
            
            Select Case strYear
                  Case "7" : strYear = YEAR_7
                  Case "8" : strYear = YEAR_8
                  Case "9" : strYear = YEAR_9
            End Select
            
            ' Find the OU - Calls the FindObject subroutine above - That deals with connecting to the current domain.
            ' It uses Recursion to find it. strDN will be blank if it can't be found. There is one limitation with the
            ' current version, it will not find OUs under the default Users folder (as this is Container class not
            ' organizationalUnit).
            
            strDN = FindObject(strYear, "")
            If strDN = "" Then
                  objLogFile.WriteLine "Failed to Get Distinguished Name for " & strYear
            End If
      
            ' Check if the User Exists
            
            booUserExists = UserExists(strUserID)
      
            ' Check to see if the User should be Created
      
            booCreateUser = False
            If booUserExists = False And strDN <> "" And dtmEnter <= Date Then
                  booCreateUser = True
            End If
      
            ' Check to see if the User should be Deleted
      
            booUserExists = True
      
            booDeleteUser = False
            If booUserExists = True And strDN <> "" Then
                  If strExit <> "" Then
                        If dtmExit < Date Then
                              booDeleteUser = True
                        End If
                  End If
            End If
      
            If booCreateUser = True And booDeleteUser = False Then
      
                  ' Connect to the OU and Create a User - This demonstrates filling in a few other bits of information.
                  ' Everything is based around the Username at the moment, it's all flexible though and depends on what
                  ' you have in the CSV.
                  
                  Set objRootDSE = GetObject("LDAP://RootDSE")
                  strDomain = Replace(objRootDSE.Get("defaultNamingContext"), ",DC=", ".")
                  strDomain = Replace(strDomain, "DC=", "")
                  Set objRootDSE = Nothing
               
                  ' The Users container name must begin with CN=. Generated from the GivenName and Surname.
          
                  Set objOU = GetObject("LDAP://" & strDN)
                  Set objUser = objOU.Create("user", "CN=" & strFirstName & " " & strSurname)
                  objUser.SetInfo
               
                  ' Setup all the name entries
               
                  objUser.Put "sAMAccountName", strUserID
                  objUser.Put "givenName", strFirstName
                  objUser.Put "sN", strSurname
                  objUser.Put "displayName", strFirstName & " " & strSurname
                  objUser.Put "userPrincipalName", strUserID & "@" & strDomain
                  objUser.SetInfo
               
                  ' Setup the Home Directory information
               
                  objUser.Put "homeDrive", HOME_DRIVE
                  objUser.Put "homeDirectory", strHomeFolder
                  objUser.SetInfo
               
                  ' Set the new password
               
                  objUser.SetPassword strPassword
               
                  ' Get rid of the Objects we have - especially important as we're looping and creating multiple users.
               
                  Set objUser = Nothing
                  Set objOU = Nothing
                  
            ElseIf booDeleteUser = True Then
      
                  ' Deletes the User Account from the OU. Uses GetObjectDN to retrieve the DistinguishedName for the User.
                  ' This is required to get the Container Name for the user.
                  
                  Set objOU = GetObject("LDAP://" & strDN)
                  strUserDN = GetObjectDN(strUserID)
      
                  If strUserDN <> "" Then
                        Set objUser = GetObject("LDAP://" & strUserDN)
                        strUserCN = objUser.Name
                  
                        objLogFile.WriteLine "Deleting User " & objUser.Get("displayName") & ": " & strUserID
                  
                        Set objUser = Nothing
                  
                        ' This line is responsible for deleting the User
                  
                        objOU.Delete "user", strUserCN
                  End If
      
                  Set objOU = Nothing
            End If
      End If
Next
      
' Send out an Email with the new users and their passwords

If SEND_EMAIL = "YES" Then
      objLogFile.WriteLine "Sending Email to " & EMAIL_TO & " With Attachment " & strPasswordExport
      EmailUser
End If

' Log File and FileSystemObject

objLogFile.WriteLine "Log File Close and Save " & strLogFile
objLogFile.Close

Set objLogFile = Nothing
Set objFileSys = Nothing
0
 

Author Comment

by:dion_p1
ID: 18053418
Almost

It error telling me object already exist i think its checking the First and Last name rather than Username

here is the CSV File

PAR0001,PARSONS,Dion,10,10,29/01/2001,8/12/2004 12:25
PAR0002,PARSONS,Dion,10,10,29/01/2001,8/12/2004 12:25
PAR0003,PARSONS,Dion,10,10,03/12/2006,
PAR0004,SIVELL,Dion,10,10,29/01/2001,8/12/2004 12:25
PAR0005,SIVELL,Dion,10,10,29/01/2001,
PAR0006,SIVELL,Dion,10,10,03/12/2006,
PAR0007,PARSONS,Dion,10,10,29/01/2001,8/12/2004 12:25
PAR0008,PARSONS,Dion,10,10,29/01/2001,
PAR0009,PARSONS,cOLIN,10,10,03/12/2006,
PAR0010,Warrick,Dion,10,10,03/12/2006,
PAR0011,PARSONS,Dion,10,10,29/01/2001,
PAR0012,PARSONS,Dion,10,10,29/01/2001,

PAR0011 Is created but it tells me object aready exist when it gets to PAR0012

I tested this by Changing the Name or PAR0012 to see if it will add and it did with no errors.


0
 
LVL 70

Accepted Solution

by:
Chris Dent earned 500 total points
ID: 18053949

Okay, I see why.

At the moment when we create the user here:

Set objUser = objOU.Create("user", "CN=" & strFirstName & " " & strSurname)

We're creating a user so it appears in AD as "Dion Parsons". Because all the users are the same name it's bumping into problems creating another user with the same AD name.

To combat that we can change that line to:

Set objUser = objOU.Create("user", "CN=" & strUserID)

Which will keep it unique, the only disadvantage is that it won't look as pretty.

Chris
0
 

Author Comment

by:dion_p1
ID: 18057023
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now