#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
}
#Requires -Module ActiveDirectory, MSOnline
# 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 = 'Failed (Create)'
$UserInformation.StatusMessage = $_.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 = 'Failed (Update)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
function ConnectO365 {
$ErrorActionPreference = 'Stop'
try {
$emailusername = "abc@test.com"
$encrypted = Get-Content c:\encrypted_password1.txt | ConvertTo-SecureString
$Credential = New-Object PSCredential($emailusername, $encrypted)
$params = @{
ConfigurationName = 'Microsoft.Exchange'
ConnectionUri = 'https://ps.outlook.com/powershell'
Credential = $Credential
Authentication = 'Basic'
AllowRedirection = $true
}
$PSSession = New-PSSession @params
Import-PSSession $PSSession -AllowClobber
Connect-MsolService –Credential $Credential
return $true
} catch {
Write-Error -ErrorRecord $_
return $false
}
}
function UpdateO365 {
# .SYNOPSIS
# Update information held in Office 365.
# .DESCRIPTION
# Update information held in Office 365.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation,
[String]$License = 'reseller-account:ENTERPRISEPACK',
[String[]]$Service
)
process {
$ErrorActionPreference = 'Stop'
try {
# Loop till the user account is created
while (-not ($mailbox = Get-Mailbox -Identity $UserInformation.SamAccountName -ErrorAction SilentlyContinue)) {
Start-Sleep -Seconds 15
}
# These params can be moved down into the Switch statement if any differ
$params = @{
Identity = $UserInformation.SamAccountName
TimeZone = 'W. Europe Standard Time'
DateFormat = 'dd.MM.yyyy'
TimeFormat = 'HH:mm'
LocalizeDefaultFolderName = $true
}
switch ($UserInformation.c) {
'CH' {
$params.Language = 'de-CH'
}
'DE' {
$params.Language = 'de-DE'
}
}
Set-MailboxRegionalConfiguration @params
$mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\calendar' -f $mailbox.Alias) -ErrorAction SilentlyContinue
if (-not $mailboxFolder) {
$mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\kalender' -f $mailbox.Alias) -ErrorAction SilentlyContinue
}
if ($mailboxFolder) {
Set-MailboxFolderPermission -Identity $mailbox.Identity -User Default -AccessRights Reviewer | Out-Null
Add-MailboxFolderPermission -Identity $mailbox.Identity -User Calendar -AccessRights Owner | Out-Null
}
# Enable Services for User
if ($License -eq "reseller-account:ENTERPRISEPACK" -and $null -ne $Services) {
Set-MsolUserLicense -UserprincipalName $UserInformation.UserPrincipalName -LicenseOptions $Services
}
} catch {
$UserInformation.Status = 'Failed (O365)'
$UserInformation.StatusMessage = $_.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, StatusMessage |
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'},
@{Name = 'StatusMessage'; Expression = '' }
# 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
if (ConnectO365) {
$usersToProcess | UpdateO365
} else {
# What would you like to do if the O365 part fails?
}
# 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
}
New-MsolUser -DisplayName $DisplayName -UserPrinciPalName $UserName -FirstName $FirstName -LastName $LastName -Country $Country -City $city -State $state -Title $title -Department $department -Office $office -PostalCode $Zip -PhoneNumber $WorkPhone -MobilePhone $MobilePhone -StreetAddress $Street -UsageLocation $LOCATION -LicenseAssignment $license -Password $Password -ForceChangePassword $True
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
Select-Object *,
@{Name = 'PasswordNeverExpires'; Expression = { $true }},
@{Name = 'Status'; Expression = 'NotProcessed'},
@{Name = 'StatusMessage'; Expression = { '' } }
If the second error happens again, is it possible the Enabled value (from SQL) to be empty?
#Requires -Module ActiveDirectory, MSOnline
# 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"
)
begin {
$validParameters = Get-CommandParameter New-ADUser -AsHashtable
}
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', 'StatusMessage' -and
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
try {
# 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 = 'Failed (Create)'
$UserInformation.StatusMessage = $_.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
)
begin {
$validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable
}
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', 'StatusMessage', 'SamAccountName' -and
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
if ($UserInformation.Action -eq 'update') {
# 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 = 'Failed (Update)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
function ConnectO365 {
$ErrorActionPreference = 'Stop'
try {
$emailusername = "abc@test.com"
$encrypted = Get-Content c:\encrypted_password1.txt | ConvertTo-SecureString
$Credential = New-Object PSCredential($emailusername, $encrypted)
$params = @{
ConfigurationName = 'Microsoft.Exchange'
ConnectionUri = 'https://ps.outlook.com/powershell'
Credential = $Credential
Authentication = 'Basic'
AllowRedirection = $true
}
$PSSession = New-PSSession @params
Import-PSSession $PSSession -AllowClobber
Connect-MsolService –Credential $Credential
return $true
} catch {
Write-Error -ErrorRecord $_
return $false
}
}
function NewO365User {
# .SYNOPSIS
# Create a user in Office 365.
# .DESCRIPTION
# Create a user in Office 365.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation,
[String]$License = 'reseller-account:ENTERPRISEPACK'
)
begin {
$validParameters = Get-CommandParameter New-MsolUser -AsHashtable
}
process {
if ($UserInformation.Action -eq 'create') {
$params = @{
LicenseAssignment = $License
UsageLocation = '??'
ForceChangePassword = $true
}
# Rewrite the property set to support the O365 commands
$o365UserInformation = $UserInformation | Select-Object *,
@{Name = 'FirstName'; Expression = { $_.GivenName }},
@{Name = 'LastName'; Expression = { $_.sn }},
@{Name = 'Country'; Expression = { $_.c }},
@{Name = 'City'; Expression = { $_.l }},
@{Name = 'State'; Experssion = { $_.st }},
@{Name = 'Office'; Expression = { $_.physicalDeliveryOfficeName }},
@{Name = 'PhoneNumber'; Expression = { $_.telephoneNumber }},
@{Name = 'MobilePhone'; Experssion = { $_.mobile }}
# Create a list of properties (from UserInformation) which will be written to the new user
$propertiesToSet = $o365UserInformation.PSObject.Properties |
Where-Object {
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
try {
# Use as many named parameters as possible
foreach ($property in $propertiesToSet) {
if ($validParameters.Contains($property.Name)) {
$params.($property.Name) = $property.Value
}
}
Write-Host "Creating O365 user with the following information"
Write-Host ($params | ConvertTo-Json -Depth 3)
New-MsolUser @params -ErrorAction Stop
$UserInformation.Status = 'OK'
} catch {
$UserInformation.Status = 'Failed (Create O365)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
function UpdateO365User {
# .SYNOPSIS
# Update information held in Office 365.
# .DESCRIPTION
# Update information held in Office 365.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation,
[String]$License = 'reseller-account:ENTERPRISEPACK',
[String[]]$Service
)
process {
$ErrorActionPreference = 'Stop'
try {
# Loop till the user account is created
while (-not ($mailbox = Get-Mailbox -Identity $UserInformation.SamAccountName -ErrorAction SilentlyContinue)) {
Start-Sleep -Seconds 15
}
# These params can be moved down into the Switch statement if any differ
$params = @{
Identity = $UserInformation.SamAccountName
TimeZone = 'W. Europe Standard Time'
DateFormat = 'dd.MM.yyyy'
TimeFormat = 'HH:mm'
LocalizeDefaultFolderName = $true
}
switch ($UserInformation.c) {
'CH' {
$params.Language = 'de-CH'
}
'DE' {
$params.Language = 'de-DE'
}
}
Set-MailboxRegionalConfiguration @params
$mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\calendar' -f $mailbox.Alias) -ErrorAction SilentlyContinue
if (-not $mailboxFolder) {
$mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\kalender' -f $mailbox.Alias) -ErrorAction SilentlyContinue
}
if ($mailboxFolder) {
Set-MailboxFolderPermission -Identity $mailbox.Identity -User Default -AccessRights Reviewer | Out-Null
Add-MailboxFolderPermission -Identity $mailbox.Identity -User Calendar -AccessRights Owner | Out-Null
}
# Enable Services for User
if ($License -eq "reseller-account:ENTERPRISEPACK" -and $null -ne $Services) {
Set-MsolUserLicense -UserprincipalName $UserInformation.UserPrincipalName -LicenseOptions $Services
}
} catch {
$UserInformation.Status = 'Failed (O365)'
$UserInformation.StatusMessage = $_.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, StatusMessage |
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'},
@{Name = 'StatusMessage'; Expression = { '' }}
# 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
if (ConnectO365) {
$usersToProcess | NewO365User
$usersToProcess | UpdateO365User
} else {
# What would you like to do if the O365 part fails?
}
# 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
}
#Requires -Module ActiveDirectory, MSOnline
# 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",
[String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment')
)
begin {
$validParameters = Get-CommandParameter New-ADUser -AsHashtable
}
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 $ExcludeProperties -and
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
try {
# 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 = 'Failed (Create)'
$UserInformation.StatusMessage = $_.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,
[String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment', 'SamAccountName')
)
begin {
$validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable
}
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 $ExcludeProperties -and
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
if ($UserInformation.Action -eq 'update') {
# 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 = 'Failed (Update)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
function ConnectO365 {
# .SYNOPSIS
# Create a connection to Office 365.
#
$ErrorActionPreference = 'Stop'
try {
$emailusername = "abc@test.com"
$encrypted = Get-Content c:\encrypted_password1.txt | ConvertTo-SecureString
$Credential = New-Object PSCredential($emailusername, $encrypted)
$params = @{
ConfigurationName = 'Microsoft.Exchange'
ConnectionUri = 'https://ps.outlook.com/powershell'
Credential = $Credential
Authentication = 'Basic'
AllowRedirection = $true
}
$PSSession = New-PSSession @params
Import-PSSession $PSSession -AllowClobber
Connect-MsolService –Credential $Credential
return $true
} catch {
Write-Error -ErrorRecord $_
return $false
}
}
function NewO365User {
# .SYNOPSIS
# Create a user in Office 365.
# .DESCRIPTION
# Create a user in Office 365.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation,
[String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'SamAccountName')
)
begin {
$validParameters = Get-CommandParameter New-MsolUser -AsHashtable
}
process {
if ($UserInformation.Action -eq 'create') {
$params = @{
ForceChangePassword = $true
}
# Rewrite the property set to support the O365 commands
$o365UserInformation = $UserInformation | Select-Object *,
@{Name = 'FirstName'; Expression = { $_.GivenName }},
@{Name = 'LastName'; Expression = { $_.sn }},
@{Name = 'Country'; Expression = { $_.c }},
@{Name = 'City'; Expression = { $_.l }},
@{Name = 'State'; Experssion = { $_.st }},
@{Name = 'Office'; Expression = { $_.physicalDeliveryOfficeName }},
@{Name = 'PhoneNumber'; Expression = { $_.telephoneNumber }},
@{Name = 'MobilePhone'; Expression = { $_.mobile }},
@{Name = 'UsageLocation'; Expression = { $_.c }}
# Create a list of properties (from UserInformation) which will be written to the new user
$propertiesToSet = $o365UserInformation.PSObject.Properties |
Where-Object {
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
try {
# Use as many named parameters as possible
foreach ($property in $propertiesToSet) {
if ($validParameters.Contains($property.Name)) {
$params.($property.Name) = $property.Value
}
}
Write-Host "Creating O365 user with the following information"
Write-Host ($params | ConvertTo-Json -Depth 3)
New-MsolUser @params -ErrorAction Stop
$UserInformation.Status = 'OK'
} catch {
$UserInformation.Status = 'Failed (Create O365)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
function UpdateO365User {
# .SYNOPSIS
# Update information held in Office 365.
# .DESCRIPTION
# Update information held in Office 365.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation,
[String[]]$Services
)
process {
$ErrorActionPreference = 'Stop'
try {
# Loop till the user account is created
while (-not ($mailbox = Get-Mailbox -Identity $UserInformation.SamAccountName -ErrorAction SilentlyContinue)) {
Start-Sleep -Seconds 15
}
# These params can be moved down into the Switch statement if any differ
$params = @{
Identity = $UserInformation.SamAccountName
TimeZone = 'W. Europe Standard Time'
DateFormat = 'dd.MM.yyyy'
TimeFormat = 'HH:mm'
LocalizeDefaultFolderName = $true
}
switch ($UserInformation.c) {
'CH' {
$params.Language = 'de-CH'
}
'DE' {
$params.Language = 'de-DE'
}
}
Set-MailboxRegionalConfiguration @params
$mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\calendar' -f $mailbox.Alias) -ErrorAction SilentlyContinue
if (-not $mailboxFolder) {
$mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\kalender' -f $mailbox.Alias) -ErrorAction SilentlyContinue
}
if ($mailboxFolder) {
Set-MailboxFolderPermission -Identity $mailbox.Identity -User Default -AccessRights Reviewer | Out-Null
Add-MailboxFolderPermission -Identity $mailbox.Identity -User Calendar -AccessRights Owner | Out-Null
}
# Enable Services for User
if ($UserInformation.LicenseAssignment -eq "reseller-account:ENTERPRISEPACK" -and $null -ne $Services) {
Set-MsolUserLicense -UserprincipalName $UserInformation.UserPrincipalName -LicenseOptions $Services
}
} catch {
$UserInformation.Status = 'Failed (O365)'
$UserInformation.StatusMessage = $_.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, StatusMessage |
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,
License as LicenseAssignment
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'},
@{Name = 'StatusMessage'; Expression = { '' }}
# 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
if (ConnectO365) {
$usersToProcess | NewO365User
$usersToProcess | UpdateO365User
} else {
# What would you like to do if the O365 part fails?
}
# 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
}
#Requires -Module ActiveDirectory, MSOnline
# 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",
[String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment')
)
begin {
$validParameters = Get-CommandParameter New-ADUser -AsHashtable
}
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 $ExcludeProperties -and
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
try {
# 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 = 'Failed (Create)'
$UserInformation.StatusMessage = $_.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,
[String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment', 'SamAccountName')
)
begin {
$validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable
}
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 $ExcludeProperties -and
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
if ($UserInformation.Action -eq 'update') {
# 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 = 'Failed (Update)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
function ConnectO365 {
# .SYNOPSIS
# Create a connection to Office 365.
#
$ErrorActionPreference = 'Stop'
try {
$emailusername = "abc@test.com"
$encrypted = Get-Content c:\encrypted_password1.txt | ConvertTo-SecureString
$Credential = New-Object PSCredential($emailusername, $encrypted)
$params = @{
ConfigurationName = 'Microsoft.Exchange'
ConnectionUri = 'https://ps.outlook.com/powershell'
Credential = $Credential
Authentication = 'Basic'
AllowRedirection = $true
}
$PSSession = New-PSSession @params
Import-PSSession $PSSession -AllowClobber
Connect-MsolService –Credential $Credential
return $true
} catch {
Write-Error -ErrorRecord $_
return $false
}
}
function NewO365User {
# .SYNOPSIS
# Create a user in Office 365.
# .DESCRIPTION
# Create a user in Office 365.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation,
[String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'SamAccountName')
)
begin {
$validParameters = Get-CommandParameter New-MsolUser -AsHashtable
}
process {
if ($UserInformation.Action -eq 'create') {
$params = @{
ForceChangePassword = $true
}
# Rewrite the property set to support the O365 commands
$o365UserInformation = $UserInformation | Select-Object *,
@{Name = 'FirstName'; Expression = { $_.GivenName }},
@{Name = 'LastName'; Expression = { $_.sn }},
@{Name = 'Country'; Expression = { $_.c }},
@{Name = 'City'; Expression = { $_.l }},
@{Name = 'State'; Expression = { $_.st }},
@{Name = 'Office'; Expression = { $_.physicalDeliveryOfficeName }},
@{Name = 'PhoneNumber'; Expression = { $_.telephoneNumber }},
@{Name = 'MobilePhone'; Expression = { $_.mobile }},
@{Name = 'UsageLocation'; Expression = { $_.c }}
# Create a list of properties (from UserInformation) which will be written to the new user
$propertiesToSet = $o365UserInformation.PSObject.Properties |
Where-Object {
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
try {
# Use as many named parameters as possible
foreach ($property in $propertiesToSet) {
if ($validParameters.Contains($property.Name)) {
$params.($property.Name) = $property.Value
}
}
Write-Host "Creating O365 user with the following information"
Write-Host ($params | ConvertTo-Json -Depth 3)
New-MsolUser @params -ErrorAction Stop
$UserInformation.Status = 'OK'
} catch {
$UserInformation.Status = 'Failed (Create O365)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
function UpdateO365User {
# .SYNOPSIS
# Update information held in Office 365.
# .DESCRIPTION
# Update information held in Office 365.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation,
[String[]]$Services
)
process {
$ErrorActionPreference = 'Stop'
try {
# Loop till the user account is created
while (-not ($mailbox = Get-Mailbox -Identity $UserInformation.SamAccountName -ErrorAction SilentlyContinue)) {
Start-Sleep -Seconds 15
}
# These params can be moved down into the Switch statement if any differ
$params = @{
Identity = $UserInformation.SamAccountName
TimeZone = 'W. Europe Standard Time'
DateFormat = 'dd.MM.yyyy'
TimeFormat = 'HH:mm'
LocalizeDefaultFolderName = $true
}
switch ($UserInformation.c) {
'CH' {
$params.Language = 'de-CH'
}
'DE' {
$params.Language = 'de-DE'
}
}
Set-MailboxRegionalConfiguration @params
$mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\calendar' -f $mailbox.Alias) -ErrorAction SilentlyContinue
if (-not $mailboxFolder) {
$mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\kalender' -f $mailbox.Alias) -ErrorAction SilentlyContinue
}
if ($mailboxFolder) {
Set-MailboxFolderPermission -Identity $mailbox.Identity -User Default -AccessRights Reviewer | Out-Null
Add-MailboxFolderPermission -Identity $mailbox.Identity -User Calendar -AccessRights Owner | Out-Null
}
# Enable Services for User
if ($UserInformation.LicenseAssignment -eq "reseller-account:ENTERPRISEPACK" -and $null -ne $Services) {
Set-MsolUserLicense -UserprincipalName $UserInformation.UserPrincipalName -LicenseOptions $Services
}
} catch {
$UserInformation.Status = 'Failed (O365)'
$UserInformation.StatusMessage = $_.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, StatusMessage |
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,
License as LicenseAssignment
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'},
@{Name = 'StatusMessage'; Expression = { '' }}
# 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
if (ConnectO365) {
$usersToProcess | NewO365User
$usersToProcess | UpdateO365User
} else {
# What would you like to do if the O365 part fails?
}
# 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
}
[String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment')
And this: $propertiesToSet = $UserInformation.PSObject.Properties |
Where-Object {
$_.Name -notin $ExcludeProperties -and
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
Which should be working, if I haven't broken something.(Get-Command New-MsolUser).ParameterSets.Item.Name
If you're feeling really brave, and you're running PowerShell 5 you might create a partial copy of the O365 module for me? I have tools for this :)
(Get-Command New-MsolUser).ParameterSets.Item.Name
I just need the output from that (posted here) to update the script. As long as you have the O365 module installed it'll tell me what I need.
#Requires -Module ActiveDirectory, MSOnline
# 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",
[String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment')
)
begin {
$validParameters = Get-CommandParameter New-ADUser -AsHashtable
}
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 $ExcludeProperties -and
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
try {
# 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 = 'Failed (Create)'
$UserInformation.StatusMessage = $_.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,
[String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment', 'SamAccountName')
)
begin {
$validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable
}
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 $ExcludeProperties -and
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
if ($UserInformation.Action -eq 'update') {
# 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 = 'Failed (Update)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
function ConnectO365 {
# .SYNOPSIS
# Create a connection to Office 365.
#
$ErrorActionPreference = 'Stop'
try {
$emailusername = "abc@test.com"
$encrypted = Get-Content c:\encrypted_password1.txt | ConvertTo-SecureString
$Credential = New-Object PSCredential($emailusername, $encrypted)
$params = @{
ConfigurationName = 'Microsoft.Exchange'
ConnectionUri = 'https://ps.outlook.com/powershell'
Credential = $Credential
Authentication = 'Basic'
AllowRedirection = $true
}
$PSSession = New-PSSession @params
Import-PSSession $PSSession -AllowClobber
Connect-MsolService –Credential $Credential
return $true
} catch {
Write-Error -ErrorRecord $_
return $false
}
}
function NewO365User {
# .SYNOPSIS
# Create a user in Office 365.
# .DESCRIPTION
# Create a user in Office 365.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation
)
begin {
$validParameters = Get-CommandParameter New-MsolUser -ParameterSetName Item -AsHashtable
}
process {
if ($UserInformation.Action -eq 'create') {
$params = @{
ForceChangePassword = $true
}
# Rewrite the property set to support the O365 commands
$o365UserInformation = $UserInformation | Select-Object *,
@{Name = 'FirstName'; Expression = { $_.GivenName }},
@{Name = 'LastName'; Expression = { $_.sn }},
@{Name = 'UserPrincipalName'; Expression = { $_.mail }},
@{Name = 'Country'; Expression = { $_.c }},
@{Name = 'City'; Expression = { $_.l }},
@{Name = 'State'; Expression = { $_.st }},
@{Name = 'Office'; Expression = { $_.physicalDeliveryOfficeName }},
@{Name = 'PhoneNumber'; Expression = { $_.telephoneNumber }},
@{Name = 'MobilePhone'; Expression = { $_.mobile }},
@{Name = 'UsageLocation'; Expression = { $_.c }}
# Create a list of properties (from UserInformation) which will be written to the new user
$propertiesToSet = $o365UserInformation.PSObject.Properties |
Where-Object {
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
try {
# Use as many named parameters as possible
foreach ($property in $propertiesToSet) {
if ($validParameters.Contains($property.Name)) {
$params.($property.Name) = $property.Value
}
}
Write-Host "Creating O365 user with the following information"
Write-Host ($params | ConvertTo-Json -Depth 3)
New-MsolUser @params -ErrorAction Stop
$UserInformation.Status = 'OK'
} catch {
$UserInformation.Status = 'Failed (Create O365)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
function UpdateO365User {
# .SYNOPSIS
# Update information held in Office 365.
# .DESCRIPTION
# Update information held in Office 365.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation,
[String[]]$Services
)
process {
$ErrorActionPreference = 'Stop'
try {
# Loop till the user account is created
while (-not ($mailbox = Get-Mailbox -Identity $UserInformation.SamAccountName -ErrorAction SilentlyContinue)) {
Start-Sleep -Seconds 15
}
# These params can be moved down into the Switch statement if any differ
$params = @{
Identity = $UserInformation.SamAccountName
TimeZone = 'W. Europe Standard Time'
DateFormat = 'dd.MM.yyyy'
TimeFormat = 'HH:mm'
LocalizeDefaultFolderName = $true
}
switch ($UserInformation.c) {
'CH' {
$params.Language = 'de-CH'
}
'DE' {
$params.Language = 'de-DE'
}
}
Set-MailboxRegionalConfiguration @params
$mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\calendar' -f $mailbox.Alias) -ErrorAction SilentlyContinue
if (-not $mailboxFolder) {
$mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\kalender' -f $mailbox.Alias) -ErrorAction SilentlyContinue
}
if ($mailboxFolder) {
Set-MailboxFolderPermission -Identity $mailbox.Identity -User Default -AccessRights Reviewer | Out-Null
Add-MailboxFolderPermission -Identity $mailbox.Identity -User Calendar -AccessRights Owner | Out-Null
}
# Enable Services for User
if ($UserInformation.LicenseAssignment -eq "reseller-account:ENTERPRISEPACK" -and $null -ne $Services) {
Set-MsolUserLicense -UserprincipalName $UserInformation.UserPrincipalName -LicenseOptions $Services
}
} catch {
$UserInformation.Status = 'Failed (O365)'
$UserInformation.StatusMessage = $_.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, StatusMessage |
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,
License as LicenseAssignment
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'},
@{Name = 'StatusMessage'; Expression = { '' }}
# 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
if (ConnectO365) {
$usersToProcess | NewO365User
$usersToProcess | UpdateO365User
} else {
# What would you like to do if the O365 part fails?
}
# 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
}
(Get-Command New-MsolUser).ParameterSets
And: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
}
}
Get-CommandParameter -CommandName New-MsolUser -ParameterSetName Item
function NewO365User {
# .SYNOPSIS
# Create a user in Office 365.
# .DESCRIPTION
# Create a user in Office 365.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation
)
begin {
$validParameters = Get-CommandParameter New-MsolUser -ParameterSetName Item -AsHashtable
}
process {
if ($UserInformation.Action -eq 'create') {
$params = @{
ForceChangePassword = $true
}
# Rewrite the property set to support the O365 commands
$o365UserInformation = $UserInformation | Select-Object -ExcludeProperty UserPrincipalName -Property *,
@{Name = 'FirstName'; Expression = { $_.GivenName }},
@{Name = 'LastName'; Expression = { $_.sn }},
@{Name = 'UserPrincipalName'; Expression = { $_.mail }},
@{Name = 'Country'; Expression = { $_.c }},
@{Name = 'City'; Expression = { $_.l }},
@{Name = 'State'; Expression = { $_.st }},
@{Name = 'Office'; Expression = { $_.physicalDeliveryOfficeName }},
@{Name = 'PhoneNumber'; Expression = { $_.telephoneNumber }},
@{Name = 'MobilePhone'; Expression = { $_.mobile }},
@{Name = 'UsageLocation'; Expression = { $_.c }}
# Create a list of properties (from UserInformation) which will be written to the new user
$propertiesToSet = $o365UserInformation.PSObject.Properties |
Where-Object {
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
try {
# Use as many named parameters as possible
foreach ($property in $propertiesToSet) {
if ($validParameters.Contains($property.Name)) {
$params.($property.Name) = $property.Value
}
}
Write-Host "Creating O365 user with the following information"
Write-Host ($params | ConvertTo-Json -Depth 3)
New-MsolUser @params -ErrorAction Stop
$UserInformation.Status = 'OK'
} catch {
$UserInformation.Status = 'Failed (Create O365)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
function NewO365User {
# .SYNOPSIS
# Create a user in Office 365.
# .DESCRIPTION
# Create a user in Office 365.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation
)
begin {
$validParameters = Get-CommandParameter New-MsolUser -ParameterSetName "AddUser__0" -AsHashtable
}
process {
if ($UserInformation.Action -eq 'create') {
$params = @{
ForceChangePassword = $true
}
# Rewrite the property set to support the O365 commands
$o365UserInformation = $UserInformation | Select-Object -ExcludeProperty UserPrincipalName -Property *,
@{Name = 'FirstName'; Expression = { $_.GivenName }},
@{Name = 'LastName'; Expression = { $_.sn }},
@{Name = 'UserPrincipalName'; Expression = { $_.mail }},
@{Name = 'Country'; Expression = { $_.c }},
@{Name = 'City'; Expression = { $_.l }},
@{Name = 'State'; Expression = { $_.st }},
@{Name = 'Office'; Expression = { $_.physicalDeliveryOfficeName }},
@{Name = 'PhoneNumber'; Expression = { $_.telephoneNumber }},
@{Name = 'MobilePhone'; Expression = { $_.mobile }},
@{Name = 'UsageLocation'; Expression = { $_.c }}
# Create a list of properties (from UserInformation) which will be written to the new user
$propertiesToSet = $o365UserInformation.PSObject.Properties |
Where-Object {
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
try {
# Use as many named parameters as possible
foreach ($property in $propertiesToSet) {
if ($validParameters.Contains($property.Name)) {
$params.($property.Name) = $property.Value
}
}
Write-Host "Creating O365 user with the following information"
Write-Host ($params | ConvertTo-Json -Depth 3)
New-MsolUser @params -ErrorAction Stop
$UserInformation.Status = 'OK'
} catch {
$UserInformation.Status = 'Failed (Create O365)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
function ConnectO365 {
# .SYNOPSIS
# Create a connection to Office 365.
# .DESCRIPTION
# Create a connection to Office 365.
$ErrorActionPreference = 'Stop'
try {
$emailusername = "abc@test.com"
$encrypted = Get-Content c:\encrypted_password1.txt | ConvertTo-SecureString
$Credential = New-Object PSCredential($emailusername, $encrypted)
$params = @{
ConfigurationName = 'Microsoft.Exchange'
ConnectionUri = 'https://ps.outlook.com/powershell'
Credential = $Credential
Authentication = 'Basic'
AllowRedirection = $true
}
$PSSession = New-PSSession @params
Import-PSSession $PSSession -AllowClobber
Connect-MsolService –Credential $Credential
return $PSSession
} catch {
Write-Error -ErrorRecord $_
}
}
And:if ($PSSession = ConnectO365) {
$usersToProcess | NewO365User
$usersToProcess | UpdateO365User
Remove-PSSession -Session $PSSession
} else {
# What would you like to do if the O365 part fails?
}
The code is supposed to be waiting for the mailbox to be created. Let's debug that.function UpdateO365User {
# .SYNOPSIS
# Update information held in Office 365.
# .DESCRIPTION
# Update information held in Office 365.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation,
[String[]]$Services
)
process {
$ErrorActionPreference = 'Stop'
try {
# Loop till the user account is created
while (-not ($mailbox = Get-Mailbox -Identity $UserInformation.SamAccountName -ErrorAction SilentlyContinue)) {
Write-Host "Waiting for mailbox"
Start-Sleep -Seconds 15
}
if (-not $mailbox) {
Write-Host "Yeah, that didn't work."
}
# These params can be moved down into the Switch statement if any differ
$params = @{
Identity = $UserInformation.SamAccountName
TimeZone = 'W. Europe Standard Time'
DateFormat = 'dd.MM.yyyy'
TimeFormat = 'HH:mm'
LocalizeDefaultFolderName = $true
}
switch ($UserInformation.c) {
'CH' {
$params.Language = 'de-CH'
}
'DE' {
$params.Language = 'de-DE'
}
}
Set-MailboxRegionalConfiguration @params
$mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\calendar' -f $mailbox.Alias) -ErrorAction SilentlyContinue
if (-not $mailboxFolder) {
$mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\kalender' -f $mailbox.Alias) -ErrorAction SilentlyContinue
}
if ($mailboxFolder) {
Set-MailboxFolderPermission -Identity $mailbox.Identity -User Default -AccessRights Reviewer | Out-Null
Add-MailboxFolderPermission -Identity $mailbox.Identity -User Calendar -AccessRights Owner | Out-Null
}
# Enable Services for User
if ($UserInformation.LicenseAssignment -eq "reseller-account:ENTERPRISEPACK" -and $null -ne $Services) {
Set-MsolUserLicense -UserprincipalName $UserInformation.mail -LicenseOptions $Services
}
} catch {
$UserInformation.Status = 'Failed (O365)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
#Requires -Module ActiveDirectory, MSOnline
# 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 ConnectO365 {
# .SYNOPSIS
# Create a connection to Office 365.
# .DESCRIPTION
# Create a connection to Office 365.
$ErrorActionPreference = 'Stop'
try {
$emailusername = "abc@test.com"
$encrypted = Get-Content c:\encrypted_password1.txt | ConvertTo-SecureString
$Credential = New-Object PSCredential($emailusername, $encrypted)
$params = @{
ConfigurationName = 'Microsoft.Exchange'
ConnectionUri = 'https://ps.outlook.com/powershell'
Credential = $Credential
Authentication = 'Basic'
AllowRedirection = $true
}
$PSSession = New-PSSession @params
Import-PSSession $PSSession -AllowClobber
Connect-MsolService –Credential $Credential
return $PSSession
} catch {
Write-Error -ErrorRecord $_
}
}
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 (workflow) 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",
[String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment')
)
begin {
$validParameters = Get-CommandParameter New-ADUser -AsHashtable
}
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 $ExcludeProperties -and
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
try {
# 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'
$UserInformation.Action = 'create-mail'
} catch {
$UserInformation.Status = 'Failed (Create)'
$UserInformation.StatusMessage = $_.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,
[String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment', 'SamAccountName')
)
begin {
$validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable
}
process {
if ($UserInformation.Action -eq 'update') {
$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 $ExcludeProperties -and
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
if ($UserInformation.Action -eq 'update') {
# 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
# End of workflow
$UserInformation.Action = ''
$UserInformation.Status = 'OK'
} catch {
$UserInformation.Status = 'Failed (Update)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
}
function NewO365User {
# .SYNOPSIS
# Create a user in Office 365.
# .DESCRIPTION
# Create a user in Office 365.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation
)
begin {
$validParameters = Get-CommandParameter New-MsolUser -ParameterSetName "AddUser__0" -AsHashtable
}
process {
if ($UserInformation.Action -eq 'create-mail') {
$params = @{
ForceChangePassword = $true
}
# Rewrite the property set to support the O365 commands
$o365UserInformation = $UserInformation | Select-Object -ExcludeProperty UserPrincipalName -Property *,
@{Name = 'FirstName'; Expression = { $_.GivenName }},
@{Name = 'LastName'; Expression = { $_.sn }},
@{Name = 'UserPrincipalName'; Expression = { $_.mail }},
@{Name = 'Country'; Expression = { $_.c }},
@{Name = 'City'; Expression = { $_.l }},
@{Name = 'State'; Expression = { $_.st }},
@{Name = 'Office'; Expression = { $_.physicalDeliveryOfficeName }},
@{Name = 'PhoneNumber'; Expression = { $_.telephoneNumber }},
@{Name = 'MobilePhone'; Expression = { $_.mobile }},
@{Name = 'UsageLocation'; Expression = { $_.c }}
# Create a list of properties (from UserInformation) which will be written to the new user
$propertiesToSet = $o365UserInformation.PSObject.Properties |
Where-Object {
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
try {
# Use as many named parameters as possible
foreach ($property in $propertiesToSet) {
if ($validParameters.Contains($property.Name)) {
$params.($property.Name) = $property.Value
}
}
Write-Host "Creating O365 user with the following information"
Write-Host ($params | ConvertTo-Json -Depth 3)
New-MsolUser @params -ErrorAction Stop
$UserInformation.Action = 'update-mail'
$UserInformation.Status = 'Not complete'
} catch {
$UserInformation.Status = 'Failed (Create O365)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
function UpdateO365User {
# .SYNOPSIS
# Update information held in Office 365.
# .DESCRIPTION
# Update information held in Office 365.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation,
[String[]]$Services
)
begin {
$ErrorActionPreference = 'Stop'
}
process {
if ($UserInformation.Action -eq 'update-mail') {
$mailbox = Get-Mailbox -Identity $UserInformation.SamAccountName -ErrorAction SilentlyContinue
if ($mailbox) {
try {
# These params can be moved down into the Switch statement if any differ
$params = @{
Identity = $UserInformation.SamAccountName
TimeZone = 'W. Europe Standard Time'
DateFormat = 'dd.MM.yyyy'
TimeFormat = 'HH:mm'
LocalizeDefaultFolderName = $true
}
switch ($UserInformation.c) {
'CH' {
$params.Language = 'de-CH'
}
'DE' {
$params.Language = 'de-DE'
}
}
Set-MailboxRegionalConfiguration @params
$mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\calendar' -f $mailbox.Alias) -ErrorAction SilentlyContinue
if (-not $mailboxFolder) {
$mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\kalender' -f $mailbox.Alias) -ErrorAction SilentlyContinue
}
if ($mailboxFolder) {
Set-MailboxFolderPermission -Identity $mailbox.Identity -User Default -AccessRights Reviewer | Out-Null
Add-MailboxFolderPermission -Identity $mailbox.Identity -User Calendar -AccessRights Owner | Out-Null
}
# Enable Services for User
if ($UserInformation.LicenseAssignment -eq "reseller-account:ENTERPRISEPACK" -and $null -ne $Services) {
Set-MsolUserLicense -UserprincipalName $UserInformation.mail -LicenseOptions $Services
}
# End of workflow
$UserInformation.Action = ''
$UserInformation.Status = 'OK'
} catch {
$UserInformation.Status = 'Failed (O365)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
}
function UpdateAction {
# .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 {
try {
$SqlCommand = $SqlConnection.CreateCommand()
$SqlCommand.CommandText = "UPDATE dbo.GetActiveDirectoryUsers SET Action = '{1}' WHERE SamAccountName = '{0}';" -f $UserInformation.SamAccountName, $UserInformation.Action
$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.Orange {
color: Orange;
}
td.Green {
color: Green;
}
</style>"
if ($null -ne $UserInformation) {
$UserInformation |
Select-Object SamAccountName, DisplayName, Action, Status, StatusMessage |
Sort-Object {
switch -Wildcard ($_.Status) {
'Failed*' { 1 }
'Not complete' { 2 }
'OK' { 3 }
default { 3 }
}
}, 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>Not complete' { $_ -replace '<td>Not complete', '<td class="Orange">Not complete'; 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,
License as LicenseAssignment
FROM GetActiveDirectoryUsers where Action LIKE 'create%' OR Action LIKE 'update%'"
# 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'},
@{Name = 'StatusMessage'; Expression = { '' }}
# 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
if ($PSSession = ConnectO365) {
$usersToProcess | NewO365User
$usersToProcess | UpdateO365User
Remove-PSSession -Session $PSSession
} else {
# What would you like to do if the O365 part fails?
}
# Update database
# RemoveAction internally filters usersToProcess on Status = OK
$usersToProcess | UpdateAction
# Send job notification
$report = NewReport -UserInformation $usersToProcess
if ($report) {
SendMailReport -UserInformation $usersToProcess -Body $report
}
# 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'},
@{Name = 'StatusMessage'; Expression = { '' }}
if (($usersToProcess | Measure-Object).Count -gt 0) {
# Do the rest
One thing to debug when it's not going quite right, one reporting channel, one place to maintain state. These things make me happy :)function ConnectO365 {
# .SYNOPSIS
# Create a connection to Office 365.
# .DESCRIPTION
# Create a connection to Office 365.
$ErrorActionPreference = 'Stop'
try {
$emailusername = "abc@test.com"
$encrypted = Get-Content c:\encrypted_password1.txt | ConvertTo-SecureString
$Credential = New-Object PSCredential($emailusername, $encrypted)
$params = @{
ConfigurationName = 'Microsoft.Exchange'
ConnectionUri = 'https://ps.outlook.com/powershell'
Credential = $Credential
Authentication = 'Basic'
AllowRedirection = $true
}
$PSSession = New-PSSession @params
$null = Import-PSSession $PSSession -AllowClobber
$null = Connect-MsolService –Credential $Credential
return $PSSession
} catch {
Write-Error -ErrorRecord $_
}
}
$mailbox = Get-Mailbox -Identity $UserInformation.SamAccountName -ErrorAction SilentlyContinue
That should be mail, right? $mailbox = Get-Mailbox -Identity $UserInformation.mail -ErrorAction SilentlyContinue
function UpdateO365User {
# .SYNOPSIS
# Update information held in Office 365.
# .DESCRIPTION
# Update information held in Office 365.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation,
[String[]]$Services
)
begin {
$ErrorActionPreference = 'Stop'
}
process {
if ($UserInformation.Action -eq 'update-mail') {
$mailbox = Get-Mailbox -Identity $UserInformation.mail -ErrorAction SilentlyContinue
if ($mailbox) {
try {
# These params can be moved down into the Switch statement if any differ
$params = @{
Identity = $mailbox.Identity
TimeZone = 'W. Europe Standard Time'
DateFormat = 'dd.MM.yyyy'
TimeFormat = 'HH:mm'
LocalizeDefaultFolderName = $true
}
switch ($UserInformation.c) {
'CH' {
$params.Language = 'de-CH'
}
'DE' {
$params.Language = 'de-DE'
}
}
Set-MailboxRegionalConfiguration @params
$mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\calendar' -f $mailbox.Alias) -ErrorAction SilentlyContinue
if (-not $mailboxFolder) {
$mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\kalender' -f $mailbox.Alias) -ErrorAction SilentlyContinue
}
if ($mailboxFolder) {
Set-MailboxFolderPermission -Identity $mailbox.Identity -User Default -AccessRights Reviewer | Out-Null
Add-MailboxFolderPermission -Identity $mailbox.Identity -User Calendar -AccessRights Owner | Out-Null
}
# Enable Services for User
if ($UserInformation.LicenseAssignment -eq "reseller-account:ENTERPRISEPACK" -and $null -ne $Services) {
Set-MsolUserLicense -UserprincipalName $UserInformation.mail -LicenseOptions $Services
}
# End of workflow
$UserInformation.Action = ''
$UserInformation.Status = 'OK'
} catch {
$UserInformation.Status = 'Failed (O365)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
}
$ErrorActionPreference = 'Stop'
In the last thread I knew exactly what would kill or or not so I didn't use it. This time around there's lots of command calls to go wrong.('{0}:\calendar' -f $mailbox.Alias)
It does appear to be finding it, but the right is already present so an error is thrown.
function UpdateO365User {
# .SYNOPSIS
# Update information held in Office 365.
# .DESCRIPTION
# Update information held in Office 365.
# .INPUTS
# System.Management.Automation.PSObject
# System.String
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation,
[String[]]$Services
)
begin {
$ErrorActionPreference = 'Stop'
}
process {
if ($UserInformation.Action -eq 'update-mail') {
$mailbox = Get-Mailbox -Identity $UserInformation.mail -ErrorAction SilentlyContinue
if ($mailbox) {
try {
# These params can be moved down into the Switch statement if any differ
$params = @{
Identity = $mailbox.Identity
TimeZone = 'W. Europe Standard Time'
DateFormat = 'dd.MM.yyyy'
TimeFormat = 'HH:mm'
LocalizeDefaultFolderName = $true
}
switch ($UserInformation.c) {
'CH' {
$params.Language = 'de-CH'
}
'DE' {
$params.Language = 'de-DE'
}
}
Set-MailboxRegionalConfiguration @params
$folderPath = '{0}:\calendar' -f $mailbox.Alias
$mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
if (-not $mailboxFolder) {
$mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
}
if ($mailboxFolder) {
Set-MailboxFolderPermission -Identity $folderPath -User Default -AccessRights Reviewer | Out-Null
Add-MailboxFolderPermission -Identity $folderPath -User Calendar -AccessRights Owner | Out-Null
}
# Enable Services for User
if ($UserInformation.LicenseAssignment -eq "reseller-account:ENTERPRISEPACK" -and $null -ne $Services) {
Set-MsolUserLicense -UserprincipalName $UserInformation.mail -LicenseOptions $Services
}
# End of workflow
$UserInformation.Action = ''
$UserInformation.Status = 'OK'
} catch {
$UserInformation.Status = 'Failed (O365)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
}
$folderPath = '{0}:\calendar' -f $mailbox.Alias
$mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
if (-not $mailboxFolder) {
$folderPath = '{0}:\kalender' -f $mailbox.Alias
$mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
}
Set-MailboxFolderPermission -Identity $folderPath -User Default -AccessRights Reviewer | Out-Null
Add-MailboxFolderPermission -Identity $folderPath -User Calendar -AccessRights Owner | Out-Null