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