[VB.Net Console] – CSV to Active Directory Importer

access_time Monday, February 24, 2014 10:58 pm
account_circle Andy Boot

‘ Description
‘ ———–

‘ Summary – Import a value from a CSV file and write it to an AD user account

‘ The purpose of this console application is to run as a scheduled task to perform the following:
‘ 1) Load a CSV data file
‘ 2) Make any amendments to the CSV file (should it be required)
‘ 3) Grab a value from the CSV file from each row whilst maintaining a unique identity to link with Add
‘ 4) Write the value to the AD user account based upon the given AD attribute

Imports System.DirectoryServices
Imports System.Security.Principal
Imports System.Threading
Imports System.Globalization
Imports System.Data.SqlClient

Module mCSV2AD

‘******************************************
‘*** App Settings ****
‘******************************************
‘Local Log File Save Path
‘@@Save a file called log.csv to the same directory as the exe
Dim LOG_FILE_NAME As String = System.AppDomain.CurrentDomain.BaseDirectory & “log.csv”

‘Initialise all Database Variables
Dim strSQLServerName = “ServerNameLocation” ‘Holds the name of the SQL Server (This is the name/location or IP address of the SQL Server)
Dim strSQLDBUserName = “Username” ‘Holds the user name (for SQL Server Authentication)
Dim strSQLDBPassword = “P4$$w0rd” ‘Holds the password (for SQL Server Authentication)
Dim strSQLDBName = “DatabaseName” ‘Holds the name of the database

‘Tell the app where to find the Active Directory.
‘ On my domain, I don’t need to authenticate, however you may wish to do so by defining a username and password.
Dim AD_LDAP_Path As String = “LDAP://DC=domain;DC=local”
Dim AD_LDAP_Username As String = Nothing
Dim AD_LDAP_Password As String = Nothing

‘******************************************
‘*** Open Database Connection ****
‘******************************************
‘Initialise the DB Connection String And Objects
Dim strCon = “Server=” & strSQLServerName & “;User ID=” & strSQLDBUserName & “;Password=” & strSQLDBPassword & “;Database=” & strSQLDBName & “;”
Dim DBConn As New SqlConnection(strCon)
Dim DBCmd As New SqlCommand
‘ SetADProperty() Helper method that sets property values for AD user accounts.

‘ ()[de] DirectoryEntry to use
‘ ()[pName] Property name to set
‘ ()[pValue] Value of property to set
Public Sub SetADProperty(ByVal de As DirectoryEntry, ByVal pName As String, ByVal pValue As String)
‘First make sure the property value isn’t “nothing”
If Not pValue Is Nothing Then
‘Check to see if the DirectoryEntry contains this property already
If de.Properties.Contains(pName) Then ‘The DE contains this property
‘Update the properties value
de.Properties(pName)(0) = pValue
Else ‘Property doesn’t exist
‘Add the property and set it’s value
de.Properties(pName).Add(pValue)
End If
End If
End Sub

‘ GetDirectoryEntry() Method used to create an entry to the AD.

‘ <>output<> A DirectoryEntry
Public Function GetDirectoryEntry() As DirectoryEntry
Dim dirEntry As DirectoryEntry = New DirectoryEntry()
dirEntry.Path = AD_LDAP_Path
dirEntry.Username = AD_LDAP_Username
dirEntry.Password = AD_LDAP_Password
dirEntry.AuthenticationType = AuthenticationTypes.Secure
Return dirEntry
End Function

‘ ExtractUserName() Function to extract just the USERNAME from the provided string

‘ ()[path] Full AD login of the user (given in the format DOMAINUSERNAME)

‘ <>output<> The login with the “DOMAIN” stripped/replaced
Public Function ExtractUserName(ByVal path As String) As String
Dim userPath As String() = path.Split(New Char() {“”c})
Return userPath((userPath.Length – 1))
End Function

‘ IsValidADLogin() Function to search the Active Directory and ensure the Login provided
‘ in Agent Process is a valid one. The search is performed to see if the
‘ login provided exists for the first and last name of the user being queried

‘ ()[loginName] Login of the associate to search for
‘ ()[givenName] First name of the associate being added
‘ ()[surName] Last name of the associate being added

‘ <>output<> True or False depending if the login provided is a valid one
Public Function IsValidADLogin(ByVal loginName As String, ByVal givenName As String, ByVal surName As String) As Boolean
Try
Dim search As New DirectorySearcher()
search.Filter = String.Format(“(&(SAMAccountName={0}) givenName={1})(sn={2}))”, ExtractUserName(loginName), givenName, surName)
search.PropertiesToLoad.Add(“cn”)
search.PropertiesToLoad.Add(“SAMAccountName”) ‘Users login name
search.PropertiesToLoad.Add(“givenName”) ‘Users first name
search.PropertiesToLoad.Add(“sn”) ‘Users last name
‘Use the .FindOne() Method to stop as soon as a match is found
Dim result As SearchResult = search.FindOne()
If result Is Nothing Then
Return False
Else
Return True
End If
Catch ex As Exception
Console.Write(“Active Directory Error” & ex.Message)
End Try
End Function
‘ SetCultureAndIdentity() Establish identity (principal) and culture for a thread.
Public Sub SetCultureAndIdentity()
AppDomain.CurrentDomain.SetPrincipalPolicy(PrincipalPolicy.WindowsPrincipal)
Dim principal As WindowsPrincipal = CType(Thread.CurrentPrincipal, WindowsPrincipal)
Dim identity As WindowsIdentity = CType(principal.Identity, WindowsIdentity)
System.Threading.Thread.CurrentThread.CurrentCulture = New CultureInfo(“en-US”)
End Sub

‘ Main() – Execute the main Console application
Sub Main()

Dim settDS As DataSet
Dim ds As DataSet
Dim Count As Integer
Dim Count2 As Integer
Dim Count3 As Integer
Dim settPath, settFile, settNameID, settNameType, settADfield, settADvalueID, settPreValueAdd, settLeftNameCharsRemove, settNoRowsToDelete
Dim CSVname, CSVvalue

‘Delete Any Previous Logs From Our Local Log File
System.IO.File.Delete(LOG_FILE_NAME)

‘Open Connection to the SQL Server
DBConn.Open()

‘Delete Any Previous Logs From SQL
Try
DBCmd = New SqlCommand(“DELETE FROM Table”, DBConn)
DBCmd.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try

‘Import Settings from a CSV file named settings.csv located in the same path as the exe
‘Then store the values as a variable.
settDS = CSV2DS(System.AppDomain.CurrentDomain.BaseDirectory, “settings.csv”)
For Count = 0 To settDS.Tables(0).Rows.Count – 1
‘# Example settings.csv file format:
‘# csvPath, csvFile, NameIDColumn, NameType, Adfield, AdvalueColumn
‘# \servershare, test.txt, 2, fullname, ipPhone, 0
‘# \servershare, test2.txt, 2, fullname, ipPhone, 0

settPath = settDS.Tables(0).Rows(Count)(0) ‘ Path to our CSV data file (no trailing backslash)
settFile = settDS.Tables(0).Rows(Count)(1) ‘ Name of our CSV data file (no starting backslash)
settNameID = settDS.Tables(0).Rows(Count)(2) ‘ Column ID within the CSV data file to use as unique ID to compare with an AD account field (below)
settNameType = settDS.Tables(0).Rows(Count)(3) ‘ Field name within the AD account to compare with the CSV ID field (above)
settADfield = settDS.Tables(0).Rows(Count)(4) ‘ Field name within the AD account to populate with our CSV data file value
settADvalueID = settDS.Tables(0).Rows(Count)(5) ‘ Column ID within the CSV data file to use as the value for our AD field to be populated
settPreValueAdd = IIf(IsDBNull(settDS.Tables(0).Rows(Count)(6)), “”, settDS.Tables(0).Rows(Count)(6)) ‘ optional – Add a text string before our
‘ value before saving to the AD field
settLeftNameCharsRemove = IIf(IsDBNull(settDS.Tables(0).Rows(Count)(7)), “0”, settDS.Tables(0).Rows(Count)(7)) ‘ optional – If our data file contains
‘ something at the beginning of the value which
‘ we want to automatically remove, define it here
settNoRowsToDelete = IIf(IsDBNull(settDS.Tables(0).Rows(Count)(8)), “0”, settDS.Tables(0).Rows(Count)(8)) ‘ optional – If our data file contains some bullshit
‘ at the top which we don’t our code to read, define
‘ how many rows at the top to remove before proceeding

‘ If settNoRowsToDelete contains a value above 0, then delete some rows at the beginning of our data file
If CInt(settNoRowsToDelete) > 0 Then
If Not Left(settFile, 3) = “AB_” Then
System.IO.File.Copy(settPath & “” & settFile, settPath & “AB_” & settFile, True)
settFile = “AB_” & settFile

For Count3 = 0 To CInt(settNoRowsToDelete) – 1
DeleteLine(settPath & “” & settFile, Count3)
Next

End If
End If

‘ Put our CSV data file into a dataset
ds = CSV2DS(settPath, settFile)

Count2 = 0

‘ Go through each row of the CSV
For Count2 = 0 To ds.Tables(0).Rows.Count – 1

CSVname = ds.Tables(0).Rows(Count2)(settNameID) ‘ Capture the Unique ID
CSVvalue = ds.Tables(0).Rows(Count2)(settADvalueID) ‘ Capture the Value to submit to AD

‘ Remove any unwanted characters from the beginning of the Unique ID
If Not settLeftNameCharsRemove = “0” Then
CSVname = Right(CSVname, (Len(CSVname) – CInt(settLeftNameCharsRemove)))
End If

‘Do not continue if our Unique ID is blank
If Not IsDBNull(CSVname) Then

‘ Update our AD user account by executing UpdateUserADAccount()
UpdateUserADAccount(settNameType, CSVname, settADfield, CSVvalue, settPreValueAdd)

End If

Next

‘ Wipe our dataset
ds = Nothing
Next

‘Close and Wipe our SQL connection
DBConn.Close()
DBConn = Nothing
End Sub

‘ UpdateUserADAccount() Method that updates user’s properties

‘ [searchField] What AD field to search
‘ [searchValue] What value to search with
‘ [sAttribute] What AD field to modify
‘ [sValue] What value to modify the AD field
‘ [sPreValueAdd] What text string to add before the above value
Public Sub UpdateUserADAccount(ByVal searchField As String, ByVal searchValue As String, _
ByVal sAttribute As String, _
ByVal sValue As String, _
ByVal sPreValueAdd As String)
Dim dirEntry As DirectoryEntry = GetDirectoryEntry()
Dim dirSearcher As DirectorySearcher = New DirectorySearcher(dirEntry)

‘ 1. Search the Active Directory for the speied user
If searchField = “username” Then searchField = “SAMAccountName”
If searchField = “fullname” Then searchField = “displayName”
dirSearcher.Filter = “(&(objectCategory=Person)(objectClass=user)(” & searchField & “=” & searchValue & “*))”
dirSearcher.SearchScope = SearchScope.Subtree

Try
Dim searchResults As SearchResult = dirSearcher.FindOne()
If Not searchResults Is Nothing Then
Dim dirEntryResults As New DirectoryEntry(searchResults.Path)

‘ 2. Set the new property values for the specified user
sValue = sPreValueAdd & sValue
SetADProperty(dirEntryResults, sAttribute, sValue)

‘ 3. Commit the changes
Try
dirEntryResults.CommitChanges()
Console.WriteLine(“Found ” & searchValue & ” – Changed ” & sAttribute & ” to ” & sValue)
LogFile(“Success,” & searchField & “,” & searchValue & “,” & sAttribute & “,” & sValue)
Catch ex As Exception
Console.WriteLine(“Error:- Found: ” & searchValue & ” but errored with ” & ex.Message & “”)
LogFile(“Error,” & searchField & “,” & searchValue & “,” & sAttribute & “,” & sValue & “,” & ex.Message)
End Try

‘ 4. Close & Cleanup
dirEntryResults.Close()

Else
Console.WriteLine(“Could not find ” & searchValue)
LogFile(“NotFound,” & searchField & “,” & searchValue & “,” & sAttribute & “,” & sValue)
AddSQLlog(sValue, sAttribute, searchField, “NotFound”, searchValue, Now())

End If
Catch ex As Exception
Console.WriteLine(“Error:- Could not find ” & searchValue & ” but errored with ” & ex.Message & “”)
End Try
‘ 4a. Close & Cleanup
dirEntry.Close()
End Sub

‘ CSV2DS() Create a Dataset from a CSV file

‘ [sCSVPath] Path to the CSV/Text file
‘ [sCSVfile] Filename of the CSV/Text file

‘<>output<> The dataset
Function CSV2DS(ByVal sCSVPath As String, ByVal sCSVfile As String) As DataSet

‘declare
Dim dt As New DataTable
Dim ds As New DataSet
Dim conn As New OleDb.OleDbConnection
Dim dbCommand As New OleDb.OleDbCommand

Dim ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & sCSVPath & “;Extended Properties=’text;HDR=Yes'”

conn.ConnectionString = ConnectionString

‘Setup the connection and command
Dim CommandText = “select * from ” & sCSVfile
dbCommand = New OleDb.OleDbCommand(CommandText, conn)

Try
‘open connection
conn.Open()
Dim adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(CommandText, conn)

‘set up the dataset
adapter.Fill(dt)
ds.Tables.Add(dt)
conn.Close()

Catch objException As Exception
Console.WriteLine(objException.Message)
Finally
If conn.State = ConnectionState.Open Then
dbCommand.Dispose()
conn.Close()
conn.Dispose()
End If
End Try
Return ds
End Function

‘ LogFile() Log a line of text to the log file after adding a timestamp

‘ [sText] String of text to write to our log file
Sub LogFile(ByVal sText)
Try
Dim objWriter As New System.IO.StreamWriter(LOG_FILE_NAME, True)
objWriter.WriteLine(Now & “,” & sText)
objWriter.Close()
Catch ex As Exception
Console.WriteLine(“LOG FILE ERROR: ” & ex.Message)
End Try

End Sub

‘ DeleteLine() Deletes a line from a text file

‘ [FileAddress] Whole path the file to modify
‘ [line] Line number to delete
Public Sub DeleteLine(ByRef FileAddress As String, ByRef line As Integer)
Dim TheFileLines As New List(Of String)
TheFileLines.AddRange(System.IO.File.ReadAllLines(FileAddress))
‘ if line is beyond end of list the exit sub
If line >= TheFileLines.Count Then Exit Sub
TheFileLines.RemoveAt(line)
System.IO.File.WriteAllLines(FileAddress, TheFileLines.ToArray)
End Sub

‘ AddSQLlog() Log a line of text to the SQL database

‘ [LogText] Text to write
Sub AddSQLlog(ByVal LogText)
Try
‘Add Insert Statement
DBCmd = New SqlCommand(“INSERT INTO Table (LogText) VALUES (@LogText)”, DBConn)

‘Add Database Parameters
DBCmd.Parameters.Add(“@LogText”, SqlDbType.VarChar).Value = LogText

DBCmd.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Sub

End Module