Powershell - Create Active Directory Users From SQL

I need to write a Powershell script to perform the following steps.

1. Pull user information from two separate tables in a SQL database (database1)
    a. Pull FirstName, LastName, Nickname, and GovernmentID fields from Table1
    b. Pull LastTerm and CampusName from Table2
    Note: Table1 and Table2 are related via an EmployeeID field that is the PK in both    tables

2. Create new AD username by concatenating the "FirstName" + "." + "LastName" fields.  
3. Create new AD passwords for each user by concatenating the first 4 characters of "FirstName" + "." + "LastName" + "." + "GovernmentID".


Additional requirements:
1. Only create accounts if an account with the same username does not already exist in AD.
2. Only create accounts if the LastTerm field is less than 1 year ago (measured in standard years)
3. If two accounts exist in SQL that have the same FirstName.LastName, but different EmployeeID's, I need to substitute the Nickname for the FirstName in both username and password generators, using the same First 4 rule in the password
4. Create each account in an OU that bears the name of the CampusName field


What would a template for this type of script look like?
LVL 1
marrjAsked:
Who is Participating?
 
YZlatConnect With a Mentor Commented:
$conn.ConnectionString = "Server=ServerName;Database=DatabaseName;User Id=user;Password=pwd;"

$sql = "SELECT t1.FirstName, t1.LastName, t1.FirstName " '.' + t1.LastName As [UserName],  t1.Nickname, t1.GovernmentID, t2.LastTerm, t2.CampusName FROM Table1 as t1 
INNER JOIN Table2 as t2 ON t1.EmployeeID=t2.EmployeeID"
$conn.Open()

$cmd = New-Object Data.SqlClient.SqlCommand($sql, $conn)
$Reader = $cmd.ExecuteReader()

$dt = New-Object System.Data.DataTable 
$dt.Load($Reader)  

#loop through each row of the datatable
$dt.Rows | foreach {
	#for each username check if it already exists
	$User = Get-ADUser -LDAPFilter "(sAMAccountName=$_.UserName)"
 	if ($User -eq $null) {
		#user does not exist
		#check if LastTerm is less than a year ago
		$datediff = new-TimeSpan $_.LastTerm $(Get-Date);
		if ($datediff -lt 365){
			New-ADUser -Name $_.UserName
		}
		
	}
 
}

Open in new window

0
All Courses

From novice to tech pro — start learning today.