Reset User Passwords and Add Missing Users to Active Directory via PowerShell from Google Accounts

By | 08/01/2019

MySQL SQL Code

SELECT
SUBSTRING_INDEX(Google.primaryEmail , '@' , 1) AS 'SamAccountName' ,
Google.name.givenName AS 'GivenName' ,
Google.name.familyName AS 'Surname' ,
Google.name.fullName AS 'DisplayName' ,
CASE CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(Google.orgUnitPath , '/' , - 1), ' ', -1),UNSIGNED INTEGER)
WHEN 6 THEN "OU=7,OU=Middle,OU=Students,OU=Users,OU=Thornlie Accounts,DC=tcc,DC=wa,DC=edu,DC=au"
WHEN 7 THEN "OU=8,OU=Middle,OU=Students,OU=Users,OU=Thornlie Accounts,DC=tcc,DC=wa,DC=edu,DC=au"
WHEN 8 THEN "OU=9,OU=Middle,OU=Students,OU=Users,OU=Thornlie Accounts,DC=tcc,DC=wa,DC=edu,DC=au"
WHEN 9 THEN "OU=10,OU=Senior,OU=Students,OU=Users,OU=Thornlie Accounts,DC=tcc,DC=wa,DC=edu,DC=au"
WHEN 10 THEN "OU=11,OU=Senior,OU=Students,OU=Users,OU=Thornlie Accounts,DC=tcc,DC=wa,DC=edu,DC=au"
WHEN 11 THEN "OU=12,OU=Senior,OU=Students,OU=Users,OU=Thornlie Accounts,DC=tcc,DC=wa,DC=edu,DC=au"
END AS 'Path' ,
"" AS 'AccountPassword' ,
Google.primaryEmail AS 'EmailAddress' ,
CASE Google.externalIds.0.value
WHEN "" THEN UPPER(SUBSTRING_INDEX(Google.primaryEmail , '@' , 1))
ELSE Google.externalIds.0.value
END AS 'EmployeeID'
FROM
Google
WHERE
(
Google.orgUnitPath = '/Students/Upper Primary/Year 6'
OR Google.orgUnitPath LIKE '/Students/Middle%'
OR Google.orgUnitPath LIKE '/Students/Senior%'
)
AND
Google.orgUnitPath != '/Students/Senior School/Year 12' AND Google.primaryEmail NOT LIKE '%test%'
ORDER BY
CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(Google.orgUnitPath , '/' , - 1), ' ', -1),UNSIGNED INTEGER) ASC,
Google.name.familyName ASC,
Google.name.givenName ASC

Generating Passwords with pwgen

$ pwgen -1 -c -n -y -B 12 10

gie9eiC]oh)W
ohCho/e4xeer
ixohY9Ag@aim
Aes3zeeph,ie
ooH9eingah*k
che!id3uy<ie
Phoo!Kee7iem
mai4ze#u7The
Phah(b7adoe;
eu4osh4Fee)b

PowerShell Script

$Import =Import-CSV "c:\Test\import.csv"
ForEach ($user in $Import)
{
$Error.Clear()
try
{
Set-ADAccountPassword –Identity $user.SamAccountName –Reset –NewPassword (ConvertTo-SecureString -AsPlainText $user.AccountPassword -Force)
Write-Output "Password for $($user.SamAccountName) changed to $($user.AccountPassword)."
}
catch
{
$password = $user.AccountPassword | ConvertTo-SecureString -AsPlainText -Force
New-ADUser -Name $user.SamAccountName -SamAccountName $user.SamAccountName -GivenName $user.GivenName -Surname $user.Surname -DisplayName $user.DisplayName -EmailAddress $user.EmailAddress -EmployeeID $user.EmployeeID -Path $user.Path -AccountPassword $Password -ChangePasswordAtLogon $True -Enabled $True
Write-Output "Created account for $($user.SamAccountName) and set the password to $($user.AccountPassword)."
}
}

One thought on “Reset User Passwords and Add Missing Users to Active Directory via PowerShell from Google Accounts

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.