#Requires -Module ActiveDirectory
# Utility functions
function Get-CommandParameter {
# .SYNOPSIS
# Get a set of parameters from a specific parameter set.
# .DESCRIPTION
# Get a set of parameters from a specific parameter set.
# .INPUTS
# System.String
# .OUTPUTS
# System.Management.Automation.ParameterMetadata
# .EXAMPLE
# Get-CommandParameter Set-ADUser -ParameterSetName Identity
[OutputType([System.Management.Automation.ParameterMetadata])]
param (
# Retrieve parameters for the specified command.
[Parameter(Mandatory = $true)]
[String]$CommandName,
# Limit results to parameters from the specified parameter set.
[String]$ParameterSetName = '__AllParameterSets',
# Return the results as a hashtable, using the parameter name as a key.
[Switch]$AsHashtable
)
try {
$hashtable = @{}
$command = Get-Command -Name $CommandName -ErrorAction Stop
$command.Parameters.Values |
Where-Object { $_.ParameterSets.Keys -contains $ParameterSetName } |
ForEach-Object {
if ($AsHashtable) {
$hashtable.Add($_.Name, $_)
} else {
$_
}
}
if ($AsHashtable) {
$hashtable
}
} catch {
throw
}
}
# Generic functions
function Invoke-SqlQuery {
# .SYNOPSIS
# Invoke an SQL query.
# .DESCRIPTION
# Invoke an SQL query against a server described by a connection string.
# .INPUTS
# System.String
# .OUTPUTS
# System.Management.Automation.PSObject
# .EXAMPLE
# Invoke-SqlQuery -Query "SELECT * FROM Table" -ConnectionString "Server=server\instance;Database=db;Trusted_Connection=yes;"
[OutputType([System.Management.Automation.PSObject])]
param (
# An SQL query to execute.
[Parameter(Mandatory = $true)]
[String]$Query,
# The connection string to use. A connection will be created prior to executing the query, then removed afterwards.
[Parameter(Mandatory = $true)]
[String]$ConnectionString
)
# Execute the SQL query and return an object representing each row.
try {
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $ConnectionString
$SqlConnection.Open()
$SqlCommand = $SqlConnection.CreateCommand()
$SqlCommand.CommandText = $Query
$reader = $SqlCommand.ExecuteReader()
if ($reader.HasRows) {
while ($reader.Read()) {
$psObject = New-Object PSObject
for ($i = 0; $i -lt $reader.FieldCount; $i++) {
$name = $reader.GetName($i)
if (-not $psObject.PSObject.Properties.Item($name)) {
$value = $reader.GetValue($i)
if ($value -is [DBNull]) {
$value = $null
}
if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
$value = $value.Trim()
}
$psObject | Add-Member $name $value
}
}
$psObject
}
}
$reader.Close()
} catch {
throw
} finally {
if ($SqlConnection.State -eq 'Opened') {
$SqlConnection.Close()
}
}
}
# Process functions
function NewADUser {
# .SYNOPSIS
# Implements the create action.
# .DESCRIPTION
# NewADUser dynamically binds parameters for the New-ADUser command based on a UserInformation object.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation,
[String]$Path = "OU=TestUser,OU=test,DC=test,DC=com"
)
process {
if ($UserInformation.Action -eq 'create') {
$params = @{
Path = $Path
AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
}
$otherAttributes = @{}
# Create a list of properties (from UserInformation) which will be written to the new user
$propertiesToSet = $UserInformation.PSObject.Properties |
Where-Object {
$_.Name -notin 'Action', 'Password', 'Status' -and
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
try {
$validParameters = Get-CommandParameter New-ADUser -AsHashtable
# Use as many named parameters as possible
foreach ($property in $propertiesToSet) {
if ($validParameters.Contains($property.Name)) {
$params.($property.Name) = $property.Value
} else {
$otherAttributes.Add($property.Name, $property.Value)
}
}
# Load everything else into OtherAttributes
if ($otherAttributes.Count -gt 0) {
$params.OtherAttributes = $otherAttributes
}
if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
$params.Name = $UserInformation.DisplayName
}
Write-Host "Creating user with the following information"
Write-Host ($params | ConvertTo-Json -Depth 3)
New-ADUser @params -ErrorAction Stop
$UserInformation.Status = 'OK'
} catch {
$UserInformation.Status = 'Create failed ({0})' -f $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
function SetADUser {
# .SYNOPSIS
# Implements the update action.
# .DESCRIPTION
# SetADUser dynamically binds parameters for the Set-ADUser command based on a UserInformation object.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation
)
process {
$params = @{
Identity = $_.SamAccountName
}
$replace = @{}
# Create a list of properties (from UserInformation) which will be set on the existing user
$propertiesToSet = $UserInformation.PSObject.Properties |
Where-Object {
$_.Name -notin 'Action', 'Password', 'Status', 'SamAccountName' -and
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
if ($UserInformation.Action -eq 'update') {
$validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable
# Use as many named parameters as possible
foreach ($property in $propertiesToSet) {
if ($validParameters.Contains($property.Name)) {
$params.($property.Name) = $property.Value
} else {
$replace.Add($property.Name, $property.Value)
}
}
# Load everything else into OtherAttributes
if ($replace.Count -gt 0) {
$params.Replace = $replace
}
Write-Host "Updating user with the following information"
Write-Host ($params | ConvertTo-Json -Depth 3)
try {
Set-ADUser @params -ErrorAction Stop
$UserInformation.Status = 'OK'
} catch {
$UserInformation.Status = 'Set failed ({0})' -f $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
function RemoveAction {
# .SYNOPSIS
# Remove the action flag.
# .DESCRIPTION
# Remove the action flag for every object in UserInformation where status is set to 'OK'.
# .INPUTS
# System.Management.Automation.PSObject
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation
)
begin {
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $ConnectionString
$SqlConnection.Open()
}
process {
if ($UserInformation.Status -eq 'OK') {
try {
$SqlCommand = $SqlConnection.CreateCommand()
$SqlCommand.CommandText = "UPDATE dbo.GetActiveDirectoryUsers SET Action = ' ' WHERE SamAccountName = '{0}';" -f $UserInformation.SamAccountName
$SqlCommand.ExecuteNonQuery()
} catch {
Write-Error -ErrorRecord $_
}
}
}
end {
$SqlConnection.Close()
}
}
function NewReport {
# .SYNOPSIS
# Create a report.
# .DESCRIPTION
# This step creates an HTML report based on the UserInformation collection.
# .INPUTS
# System.Management.Automation.PSObject[]
# .OUTPUTS
# System.String
param (
# The UserInformation set which holds the state of each action performed by this script.
[Parameter(Mandatory = $true)]
[AllowNull()]
[PSObject[]]$UserInformation
)
# HTML header
$htmlHead = "<style>
body {
font-family: Arial;
}
table {
width: 100%;
border-collapse: collapse;
border: 1px solid;
}
th {
background-color: #87CEFA;
border: 1px solid;
padding: 1px;
}
td {
border: 1px solid;
padding: 1px;
}
td.Red {
color: Red;
}
td.Green {
color: Green;
}
</style>"
if ($null -ne $UserInformation) {
$UserInformation |
Select-Object SamAccountName, DisplayName, Action, Status |
Sort-Object {
switch -Wildcard ($_.Status) {
'Failed*' { 1 }
'OK' { 2 }
default { 2 }
}
}, DisplayName |
ConvertTo-Html -Head $htmlHead |
ForEach-Object {
# Colour the status cells in.
switch -Regex ($_) {
'<td>Failed' { $_ -replace '<td>Failed', '<td class="Red">Failed'; break }
'<td>OK' { $_ -replace '<td>OK', '<td class="Green">OK'; break }
default { $_ }
}
} | Out-String
}
}
function SendMailReport {
param(
[Parameter(Mandatory = $true)]
[PSObject[]]$UserInformation,
[Parameter(Mandatory = $true)]
[String]$Body,
[String]$To = 'someone@domain.com',
[String]$From = 'someone@domain.com',
[String]$Subject = ('User update: {0}' -f (Get-Date).ToShortDateString()),
[String]$SmtpServer = 'someserver.domain.com'
)
# Export this to add as an attachment.
$UserInformation | Export-Csv UserInformation.csv -NoTypeInformation
$params = @{
To = $To
From = $From
Subject = $Subject
Body = $Body
BodyAsHtml = $true
SmtpServer = $SmtpServer
Attachments = '.\UserInformation.csv'
}
Send-MailMessage @params
}
#
# Main
#
# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"
# Query
$sql = "Select FirstName as GivenName,
LastName as sn,
DisplayName,
samAccountName,
EmailAddress as mail,
City as l,
State as st,
c,
CountryCode as co,
Department,
StreetAddress,
samAccountName+'@test.com' as userPrincipalName,
PostalCode,
Title,
Office as physicalDeliveryOfficeName,
OfficePhone as telephoneNumber,
MobilePhone as mobile,
Action,
AccountisEnabled as Enabled,
'11Passw0rd' as Password
FROM GetActiveDirectoryUsers where Action = 'update' OR Action = 'create'"
# A status field is added to each entry. This is used to persistently record the result of our commands.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
Select-Object *,
@{Name = 'PasswordNeverExpires'; Expression = { $true }},
@{Name = 'Status'; Expression = 'NotProcessed'}
# Update any fields which need modification
$usersToProcess | ForEach-Object {
$_.Enabled = (Get-Variable $_.Enabled).Value
}
# Commit the changes to AD
# NewADUser internally filters usersToProcess on Action = Create
$usersToProcess | NewADUser
# SetADUser internally filters usersToProcess on Action = Update
$usersToProcess | SetADUser
# Update database
# RemoveAction internally filters usersToProcess on Status = OK
$usersToProcess | RemoveAction
# Send job notification
$report = NewReport -UserInformation $usersToProcess
if ($report) {
SendMailReport -UserInformation $usersToProcess -Body $report
}
# $CheckIfUserAccountExists = Get-MsolUser -UserPrincipalName $UserName -ErrorAction SilentlyContinue
#Check If User Account Exists
$checkmailbox = get-mailbox -identity $UserName -ErrorAction SilentlyContinue
# Loop till the user account is created
do
{
$checkmailbox = get-mailbox -identity $UserName -ErrorAction SilentlyContinue
Write-Host "Checking if account has been created yet"
Sleep 15
}
# when the user account is created
While ($checkmailbox -eq $Null)
# Set Language, DateFormat & TimeFormat
if ($LOCATION -eq "CH") { Set-MailboxRegionalConfiguration -identity $UserName -Language de-CH -TimeZone "W. Europe Standard Time" -DateFormat "dd.MM.yyyy" -timeformat "HH:mm" -LocalizeDefaultFolderName:$true}
elseif ($LOCATION -eq "DE") { Set-MailboxRegionalConfiguration -identity $UserName -Language de-de -TimeZone "W. Europe Standard Time" -DateFormat "dd.MM.yyyy" -timeformat "HH:mm" -LocalizeDefaultFolderName:$true }
# Set Default as reviewer for calendar of the user & use Calendar as Owner
foreach ($mbx in Get-Mailbox | Where-Object { $_.WhenCreated –ge ((Get-Date).Adddays(-1)) } | Get-Mailbox)
{
$mbox = $mbx.alias + ":\calendar"
$test = Get-MailboxFolderPermission -Identity $mbox -erroraction silentlycontinue
if ($test -ne $null)
{
Set-MailboxFolderPermission -Identity $mbox -User Default -AccessRights Reviewer | out-null
Add-MailboxFolderPermission -Identity $mbox -User Calendar -AccessRights Owner | out-null
}
else
{
$mbox = $mbx.alias + ":\kalender"
Set-MailboxFolderPermission -Identity $mbox -User Default -AccessRights Reviewer | out-null
Add-MailboxFolderPermission -Identity $mbox -User Calendar -AccessRights Owner | out-null
}
}
# Enable Services for User
if ($license -eq "reseller-account:ENTERPRISEPACK") { Set-MsolUserLicense -UserprincipalName $UserName -LicenseOptions $Services}
# Show all the configured parameters in Display
Get-MsolUser -UserPrincipalName $UserName | fl DisplayName, UserprincipalName, StreetAddress, City, State, Country, MobilePhone, PhoneNumber, Office, Title, Department, Office, UsageLocation
# Display Calendar permissions
Write-Host "Following are the permissions for Calendar"
Get-MailboxFolderPermission -Identity $mbox
}
Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.