Main Page
 The gatekeeper of reality is
 quantified imagination.

Stay notified when site changes by adding your email address:

Your Email:

Bookmark and Share
Email Notification
Project Powershell CSV
Purpose
The purpose of this page is to show how to use Powershell with Quest Software - ActiveRoles Management Shell for Active Directory in order to (1) create Active Directory (AD) user accounts from a CSV along with some advanced AD user attributes you may or may not need, (2) enable disabled AD user accounts from a CSV, and, (3) change AD user account passwords.

Create AD User Accounts From CSV
This powershell code may have more functionality that what you might require, but seeing several inter-related concepts at work will hopefully be beneficial.

<#
.SYNOPSIS
The purpose of this script is to:
- Create an AD User, specifically for the Employees OU
- Add the AD User to the AD groups "Group1" and "Group2"

.CSV-FORMAT (an example could be to save an Excel file as "MS-DOS csv")
Row 0 - Name,Logon,Description,Pwd
Row ... - Joe Smith,joe,Description of this user,joespassword
#>

## Miscellaneous Help Functions
Function Test-QADObject {
## Determine if target OU exists
param($Identity)
(Get-QADObject $Identity -DontUseDefaultIncludedProperties `
-WarningAction SilentlyContinue -ErrorAction SilentlyContinue `
-SizeLimit 1) -ne $null
}
Function Test-QADUser {
## Determine if target USER exists in the domain
param($Identity)
(Get-QADUser -SamAccountName $Identity `
-WarningAction SilentlyContinue -ErrorAction SilentlyContinue `
-SizeLimit 1) -ne $null
}

## Miscellaneous Variables
$recordCount = 0
$adUsersAlreadyExisted = 0
$adUsersAlreadyExisted_Data = ""

## Set OU User Account Gets Created In
$userOU = "ou=Employees,ou=Users,ou=_SomeFolder,dc=Some-Domain,dc=com"

## Define AD Groups To Add New User Accounts To
$adGroups = @()
$adGroups = $adGroups + "Group1"
$adGroups = $adGroups + "Group2"

## Set Email Address
$userEmail = "@your-smtp-mail.com"

## Set the UPN Address
$userUPN = "@Some-Domain.com"

## Set The User Profile Path
$userProfileLocation = "\\Profile-Server\Profiles\%GenericProfile%"

## Get the CSV file with headers "Name", "Logon", "Description", "Pwd"
$csvDataFile = import-csv "C:\tmp-folder\ad-accounts-to-create.csv"

## Let's Begin
Start-Transcript "C:\tmp-folder\createAdAccountsResults.txt"

## Ensure the Target OU Exists
if (Test-QADObject $userOU) {
## Iterate through the rows of users
Foreach($row in $csvDataFile) {
## Get data of the current row
$userFullName = $row.Name
$userLogon = $row.Logon
$userDescription = $row.Description
$userPassword = $row.Pwd

## Create email address
$userEmailAddress = $userLogon + $userEmail
## Set UPN (under AD Users & Computers of user account -> Account Tab -> "User logon name:")
$userUPNAddress = $userLogon + $userUPN
## Get first name
$userFirstName = $userFullName.Split(" ")[0]
## Get Last Name
$userLastName = $userFullName.Split(" ")[$userFullName.Split(" ").GetUpperBound(0)]

## If the user exists, mark that row and do not attempt to create
if (Test-QADUser $userLogon) {
$adUsersAlreadyExisted++
$adUsersAlreadyExisted_Data = $adUsersAlreadyExisted_Data + "`r`n" + $row
}
else {
## User account not found, create it
New-QADUser -Name $userLogon `
-Displayname $userFullName `
-FirstName $userFirstName `
-LastName $userLastName `
-UserPrincipalName $userUPNAddress `
-SamAccountName $userLogon `
-Description $userDescription `
-Email $userEmailAddress `
-UserPassword $userPassword `
-ProfilePath $userProfileLocation `
-ParentContainer $userOU
## Set some user account attributes that cannot be set at creation
Set-QADUser $userLogon -PasswordNeverExpires $true `
-UserMustChangePassword $false `
## Add user account to applicable groups
ForEach($group in $adGroups) {
Add-QADGroupMember -identity $group -member $userLogon
} ## End ForEach
} ## End Else
## Update Record Count
$recordCount++
} ## End ForEach

## Final Output
"Number of Records Found In CSV: $recordCount"
"Number of User Accounts Not Created Because They Already Existed: $adUsersAlreadyExisted"
if ($adUsersAlreadyExisted_Data) {
":::The Following User Accounts Already Existed:::"
"-------------------------------------------------"
"$adUsersAlreadyExisted_Data"
}
} ## End if
else { "ERROR: The OU specified does not exist." }

## We're All Done
Stop-Transcript


Enable Disabled AD user Accounts From CSV
## Get the CSV file with headers "Name", "Logon", "Description", "Pwd"
$csvDataFile = import-csv "C:\tmp-folder\enable-ad-accounts.csv"

## Iterate through the rows of users
Foreach($row in $csvDataFile) {
## Get data of the current row
$userLogon = $row.Logon
Set-QADUser $userLogon -ObjectAttributes @{userAccountControl=512}
}


Update Passwords of AD user Accounts From CSV
## Get the CSV file with headers "Name", "Logon", "Description", "Pwd"
$csvDataFile = import-csv "C:\tmp-folder\update-ad-account-passwords.csv"

## Iterate through the rows of users
Foreach($row in $csvDataFile) {
## Get data of the current row
$userLogon = $row.Logon
$userPassword = $row.Pwd
Set-QADUser $userLogon -UserPassword $userPassword
}


Check To See If AD User Accounts Are In AD Groups
This checks to see if a CSV supplied list of AD user accounts are in the AD Groups specified.

## Miscellaneous Variables
$recordCount = 0
$adUsersNotInGroups = 0
$adUsersNotInGroups_Data = ""

## Define AD Groups To See If AD Account Is A Member Of
$adGroups = @()
$adGroups = $adGroups + "Group1"
$adGroups = $adGroups + "Group2"

## Get the CSV file with headers "Logon"
$csvDataFile = import-csv "C:\tmp-folder\ad-accounts-to-check.csv"
## Let's Begin
Start-Transcript "c:\tmp-folder\adGroupCheckResults.txt"

## Iterate through the rows of users
Foreach($row in $csvDataFile) {
## Get data of the current row
$userLogon = $row.Logon
## Go through AD groups
$missing = 0
ForEach($group in $adGroups) {
$userInGroup = 0
$userCollection = Get-QADUser -SamAccountName $userLogon
$userGroupsRaw = $userCollection.memberOf -split(",")

## Is user account in the AD group?
ForEach($dataFound in $userGroupsRaw) {
$name = $dataFound.Split("=")[0]
$value = $dataFound.Split("=")[1]
if ($name -eq "CN") {
if ($value -eq $group) { $userInGroup = 1 }
}
}

## Not in the AD group
if ($userInGroup -ne 1) {
## Record user account was not in AD group
$adUsersNotInGroups++
$adUsersNotInGroups_Data = $adUsersNotInGroups_Data + "`r`n" + $userLogon + " was not a member of the AD group " + $group
}
}

## Update Record Count
$recordCount++
} ## End ForEach

## Final Output
"Number of Records Found In CSV: $recordCount"
"Number of User Accounts Not In AD Group(s) Specified: $adUsersNotInGroups"
if ($adUsersNotInGroups_Data) {
":::The Following User Accounts Were Not Group Members:::"
"--------------------------------------------------------"
"$adUsersNotInGroups_Data"
}

## We're All Done
Stop-Transcript


About Joe