Powershell help for creating accounts
Hello Experts,
I need help with the below :
The code mentioned lookup in SQL & creates & updates Accounts in AD, which is in-house.
Based on the same information I want the script to Create, Update & Delete Accounts.
So when in SQL, the value of the field
Action = create, the account has to be created.
Action = update, the account has to be updated.
At the end of the creation there are some tasks to be performed, as O365 doesn't allow you to perform this tasks during the creation.
I have this being performed in another script, which actually is independent of SQL.
I want this all to be run as same script.
Please see below the tasks:
However, in case of Action = update, when the field AccountIsEnabled = False, the account has to be deleted in O365, but the impact on AD remains as in the script currently.
I want to figure out how to initiate the connection to O365 in the same script, & run the jobs?
Thanks in advance for your help
Regards,
A
I need help with the below :
#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
}
The code mentioned lookup in SQL & creates & updates Accounts in AD, which is in-house.
Based on the same information I want the script to Create, Update & Delete Accounts.
So when in SQL, the value of the field
Action = create, the account has to be created.
Action = update, the account has to be updated.
At the end of the creation there are some tasks to be performed, as O365 doesn't allow you to perform this tasks during the creation.
I have this being performed in another script, which actually is independent of SQL.
I want this all to be run as same script.
Please see below the tasks:
# $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
}
However, in case of Action = update, when the field AccountIsEnabled = False, the account has to be deleted in O365, but the impact on AD remains as in the script currently.
I want to figure out how to initiate the connection to O365 in the same script, & run the jobs?
Thanks in advance for your help
Regards,
A
### Connect to Office 365
Set-ExecutionPolicy RemoteSigned
Import-Module MSOnline
$emailusername = "abc@test.com"
$encrypted = Get-Content c:\encrypted_password1.txt
$O365Cred = New-Object System.Management.Automati
$O365Session = New-PSSession –ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Credential $O365Cred -Authentication Basic -AllowRedirection
Import-PSSession $O365Session -AllowClobber
Connect-MsolService –Credential $O365Cred
Set-ExecutionPolicy RemoteSigned
Import-Module MSOnline
$emailusername = "abc@test.com"
$encrypted = Get-Content c:\encrypted_password1.txt
$O365Cred = New-Object System.Management.Automati
$O365Session = New-PSSession –ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Credential $O365Cred -Authentication Basic -AllowRedirection
Import-PSSession $O365Session -AllowClobber
Connect-MsolService –Credential $O365Cred
First attempt then.
On a call, I want to have a chat about workflows.
On a call, I want to have a chat about workflows.
#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
}
Thanks Chris,
I wrote the stuff in the question as to what all I want after the account is created....
I guess I mislead ... sorry about that
The part I gave was for updating the existing user, however first part is to create the user, below is the code what works for creating the user:
as you can see, these will be the actual variables pulled out of SQL for account creation, once the account is created, the script will go to pause as O365 needs time to stage mailbox, once they are checked, then the rest of the code kicks in.
Good News is that the connection works as desired, I can see that the script first goes to AD, then O365 part kicks in, however for the AD part it throws this error:
ERROR: Select-Object : Key "expression" cannot have an empty string value.
TestO365.ps1 (587, 1): ERROR: At Line: 587 char: 1
ERROR: + Select-Object *,
ERROR: + ~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidArgument: (:) [Select-Object], NotSupportedException
ERROR: + FullyQualifiedErrorId : ExpressionEmptyString1,Mic
ERROR: Get-Variable : Cannot validate argument on parameter 'Name'. The argument is null. Supply a non-null argument and try the command again.
TestO365.ps1 (594, 29): ERROR: At Line: 594 char: 29
ERROR: + $_.Enabled = (Get-Variable $_.Enabled).Value
ERROR: + ~~~~~~~~~~
ERROR: + CategoryInfo : InvalidData: (:) [Get-Variable], ParameterBindingValidation
ERROR: + FullyQualifiedErrorId : ParameterArgumentValidatio
ERROR:
I wrote the stuff in the question as to what all I want after the account is created....
I guess I mislead ... sorry about that
The part I gave was for updating the existing user, however first part is to create the user, below is the code what works for creating the user:
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
as you can see, these will be the actual variables pulled out of SQL for account creation, once the account is created, the script will go to pause as O365 needs time to stage mailbox, once they are checked, then the rest of the code kicks in.
Good News is that the connection works as desired, I can see that the script first goes to AD, then O365 part kicks in, however for the AD part it throws this error:
ERROR: Select-Object : Key "expression" cannot have an empty string value.
TestO365.ps1 (587, 1): ERROR: At Line: 587 char: 1
ERROR: + Select-Object *,
ERROR: + ~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidArgument: (:) [Select-Object], NotSupportedException
ERROR: + FullyQualifiedErrorId : ExpressionEmptyString1,Mic
ERROR: Get-Variable : Cannot validate argument on parameter 'Name'. The argument is null. Supply a non-null argument and try the command again.
TestO365.ps1 (594, 29): ERROR: At Line: 594 char: 29
ERROR: + $_.Enabled = (Get-Variable $_.Enabled).Value
ERROR: + ~~~~~~~~~~
ERROR: + CategoryInfo : InvalidData: (:) [Get-Variable], ParameterBindingValidation
ERROR: + FullyQualifiedErrorId : ParameterArgumentValidatio
ERROR:
Too much to hope for that Select-Object would like that. It needs this updated section.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
Select-Object *,
@{Name = 'PasswordNeverExpires'; Expression = { $true }},
@{Name = 'Status'; Expression = 'NotProcessed'},
@{Name = 'StatusMessage'; Expression = { '' } }
It didn't throw any error, but the weird thing is that the script didn't even finish.....
Chris,
Can you please make first the function for creating new user on O365?
I would prefer to play with Update on it...
Chris,
Can you please make first the function for creating new user on O365?
I would prefer to play with Update on it...
Just so that you know the whole picture:
I have a web form which is tied up with SQL, which has the possibility to retrieve & create record from SQL
When I want to change something for an existing user, I query the web form & make changes, which in turn reflect on SQL
Same way when I create a new user, the changes are reflected on SQL
After that the script comes in action, since the AD is in house & mails are on O365, the script will do the needful on both, but from Administration point of view, I only interact with web form.
Same will be handed over to HR who will fill the form when the new Hire, Employee leaving or some change is to be done.
I will just have to approve & with a click multiple tasks can be performed at once.
That's why 2 things are done, create & change & for both AD & O365.
I have a web form which is tied up with SQL, which has the possibility to retrieve & create record from SQL
When I want to change something for an existing user, I query the web form & make changes, which in turn reflect on SQL
Same way when I create a new user, the changes are reflected on SQL
After that the script comes in action, since the AD is in house & mails are on O365, the script will do the needful on both, but from Administration point of view, I only interact with web form.
Same will be handed over to HR who will fill the form when the new Hire, Employee leaving or some change is to be done.
I will just have to approve & with a click multiple tasks can be performed at once.
That's why 2 things are done, create & change & for both AD & O365.
I think this. There was only one value I wasn't sure about. The UsageLocation parameter for the O365 command. Taken from SQL? Something else? It's set to "??" at the moment in the script below.
#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
}
Okay, I think we have them all. Please check I have right name for the field from SQL (in the main query). The only thing we haven't filled in now is "$Services" used when updating the O365 user with a specific license type.
#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
}
So, first thing:
New-ADUser doesn't like;ERROR: NewADUser : The specified directory service attribute or value does not exist
New-O365....
Doesn't like:
ERROR: Select-Object : Illegal key Experssion
TestO365.ps1 (362, 46): ERROR: At Line: 362 char: 46
ERROR: + $o365UserInformation = $UserInformation | Select-Object *,
New-ADUser doesn't like;ERROR: NewADUser : The specified directory service attribute or value does not exist
New-O365....
Doesn't like:
ERROR: Select-Object : Illegal key Experssion
TestO365.ps1 (362, 46): ERROR: At Line: 362 char: 46
ERROR: + $o365UserInformation = $UserInformation | Select-Object *,
Second bug is fixed below.
For the first, I must have messed up field cleaning. I (try to) remove things to stop them feeding otherAttributes. Clearly I've broken one. Please can you paste the user information for the one that's failing?
For the first, I must have messed up field cleaning. I (try to) remove things to stop them feeding otherAttributes. Clearly I've broken one. Please can you paste the user information for the one that's failing?
#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
}
No worries, here :
Creating user with the following information
{
"AccountPassword": {
"Length": 10
},
"userPrincipalName": "tel@test.com",
"DisplayName": "Test Last",
"samAccountName": "tel",
"Name": "Test Last",
"Department": "Team Z",
"GivenName": "Test",
"OtherAttributes": {
"st": "Zürich",
"co": "756",
"mail": "test.last@test.com",
"l": "Zürich",
"c": "CH",
"LicenseAssignment": "reseller-account:EXCHANGE
"sn": "Last"
},
"PostalCode": "8001",
"PasswordNeverExpires": true,
"StreetAddress": "5",
"Path": "OU=TestUser,OU=test,DC=te
"Enabled": true
}
ERROR: NewADUser : The specified directory service attribute or value does not exist
ERROR: Parameter name: LicenseAssignment
TestO365.ps1 (679, 19): ERROR: At Line: 679 char: 19
ERROR: + $usersToProcess | NewADUser
Creating user with the following information
{
"AccountPassword": {
"Length": 10
},
"userPrincipalName": "tel@test.com",
"DisplayName": "Test Last",
"samAccountName": "tel",
"Name": "Test Last",
"Department": "Team Z",
"GivenName": "Test",
"OtherAttributes": {
"st": "Zürich",
"co": "756",
"mail": "test.last@test.com",
"l": "Zürich",
"c": "CH",
"LicenseAssignment": "reseller-account:EXCHANGE
"sn": "Last"
},
"PostalCode": "8001",
"PasswordNeverExpires": true,
"StreetAddress": "5",
"Path": "OU=TestUser,OU=test,DC=te
"Enabled": true
}
ERROR: NewADUser : The specified directory service attribute or value does not exist
ERROR: Parameter name: LicenseAssignment
TestO365.ps1 (679, 19): ERROR: At Line: 679 char: 19
ERROR: + $usersToProcess | NewADUser
There is an issue with new o365
Creating O365 user with the following information
{
"ForceChangePassword": true
}
ERROR: NewO365User : You must provide a required property: Parameter name: UserPrincipalName
It seems, that the required parameter is missing....
Creating O365 user with the following information
{
"ForceChangePassword": true
}
ERROR: NewO365User : You must provide a required property: Parameter name: UserPrincipalName
It seems, that the required parameter is missing....
When you've been updating, entire script? Or just the bits that are changing? There have been a few incremental updates to NewADUser and SetADUser which we'll need. One of those updates attempts to exclude the LicenseAssignment property.
That's done with this:
Ah, I know what's breaking the O365 bit. I need the parameter set name. It seems to have only one, but MS are doing the wonderful inconsistent thing. Please can you run this and let me know what it says:
That's done with this:
[String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment')
$propertiesToSet = $UserInformation.PSObject.Properties |
Where-Object {
$_.Name -notin $ExcludeProperties -and
$null -ne $_.Value -and
($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
}
Ah, I know what's breaking the O365 bit. I need the parameter set name. It seems to have only one, but MS are doing the wonderful inconsistent thing. Please can you run this and let me know what it says:
(Get-Command New-MsolUser).ParameterSets.Item.Name
(Get-Command New-MsolUser).ParameterSets.Item.Name
Lovely, what a daft name. Thanks MS.
Let's see if this fixes the O365 part then.
Let's see if this fixes the O365 part then.
#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
}
Chris,
Can you please tell me where all you made changes?
So I can just replace the parts....
my guess is only in: function NewO365User
Can you please tell me where all you made changes?
So I can just replace the parts....
my guess is only in: function NewO365User
on only changing the new o365 i get the following:
ERROR: + $o365UserInformation = $UserInformation | Select-Object *,
ERROR: + ~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (@{GivenName=Tes...already
ERROR: + FullyQualifiedErrorId : AlreadyExistingUserSpecifi
ERROR:
Creating O365 user with the following information
{
"ForceChangePassword": true
}
ERROR: NewO365User : You must provide a required property: Parameter name: UserPrincipalName
TestO365.ps1 (686, 20): ERROR: At Line: 686 char: 20
ERROR: + $usersToProcess | NewO365User
ERROR: + ~~~~~~~~~~~
ERROR: + CategoryInfo : OperationStopped: (:) [Write-Error], MicrosoftOnlineException
ERROR: + FullyQualifiedErrorId : Microsoft.Online.Administr
ERROR:
ERROR: + $o365UserInformation = $UserInformation | Select-Object *,
ERROR: + ~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (@{GivenName=Tes...already
ERROR: + FullyQualifiedErrorId : AlreadyExistingUserSpecifi
ERROR:
Creating O365 user with the following information
{
"ForceChangePassword": true
}
ERROR: NewO365User : You must provide a required property: Parameter name: UserPrincipalName
TestO365.ps1 (686, 20): ERROR: At Line: 686 char: 20
ERROR: + $usersToProcess | NewO365User
ERROR: + ~~~~~~~~~~~
ERROR: + CategoryInfo : OperationStopped: (:) [Write-Error], MicrosoftOnlineException
ERROR: + FullyQualifiedErrorId : Microsoft.Online.Administr
ERROR:
sorry, this didn't get pasted
RROR: Select-Object : Property cannot be processed because property "UserPrincipalName" already exists.
TestO365.ps1 (362, 46): ERROR: At Line: 362 char: 46
ERROR: + $o365UserInformation = $UserInformation | Select-Object *,
ERROR: + ~~~~~~~~~~~~~~~~
RROR: Select-Object : Property cannot be processed because property "UserPrincipalName" already exists.
TestO365.ps1 (362, 46): ERROR: At Line: 362 char: 46
ERROR: + $o365UserInformation = $UserInformation | Select-Object *,
ERROR: + ~~~~~~~~~~~~~~~~
Dammit, that was the point of getting the parameter set name. Please can you run these, I need the output. the first will be long, the second too if it works...
(Get-Command New-MsolUser).ParameterSets
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
And again...
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 $_
}
}
}
}
Nah, this fixes, I hope.
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 $_
}
}
}
}
ha ha ha
Command execution stopped because the preference variable "ErrorActionPreference" or common parameter is set to Stop: [ps.outlook.com] Processing data from remote server ps.outlook.com failed with the following error message: [ClientAccessServer=AM5PR0
Command execution stopped because the preference variable "ErrorActionPreference" or common parameter is set to Stop: [ps.outlook.com] Processing data from remote server ps.outlook.com failed with the following error message: [ClientAccessServer=AM5PR0
so the account is made in o365, but the problem is that the script doesn't wait for the mailbox to be created & because of that the setting doesn't work....
This is a bit of a safer fix.
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 $_
}
}
if ($PSSession = ConnectO365) {
$usersToProcess | NewO365User
$usersToProcess | UpdateO365User
Remove-PSSession -Session $PSSession
} else {
# What would you like to do if the O365 part fails?
}
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 $_
}
}
}
Chris,
I have to leave, It's still showing that the mailbox is not ready
I will check later & keep you posted.
Thanks,
A
I have to leave, It's still showing that the mailbox is not ready
I will check later & keep you posted.
Thanks,
A
We likely need some kind of timeout on that part or it'll never finish.
This brings me back to an earlier thought.
If we reset Action (in your DB) after each stage we can unbundle these activities to an extent. You might still run the entire script, but it doesn't actually much matter if it missing an "Update" in O365 if Action is updated to reflect that's still pending, and the script runs sufficiently often to ensure it's not a problem.
Let's say, for example, the script runs once an hour to process pending changes. Then it doesn't matter if the mailbox doesn't exist on the first pass after creating a new user, it'll catch it an hour later. That way we can lose the logic which tells it to wait for a mailbox to exist.
What do you think?
This brings me back to an earlier thought.
If we reset Action (in your DB) after each stage we can unbundle these activities to an extent. You might still run the entire script, but it doesn't actually much matter if it missing an "Update" in O365 if Action is updated to reflect that's still pending, and the script runs sufficiently often to ensure it's not a problem.
Let's say, for example, the script runs once an hour to process pending changes. Then it doesn't matter if the mailbox doesn't exist on the first pass after creating a new user, it'll catch it an hour later. That way we can lose the logic which tells it to wait for a mailbox to exist.
What do you think?
I guess you are right, because now after running overnight it still hasn't done the job.
The only requirement is that I don't want to restart the script manually, if it can trigger on it's own after let's say an hour, then I'm fine with it.
Also, I guess it would make sense to somehow bind the Notification after every task is completed, that way we will know when the task was completed & what we should expect to come?
What do you think?
The only requirement is that I don't want to restart the script manually, if it can trigger on it's own after let's say an hour, then I'm fine with it.
Also, I guess it would make sense to somehow bind the Notification after every task is completed, that way we will know when the task was completed & what we should expect to come?
What do you think?
Good morning,
To implement the workflow approach we rather need to define the possible states following each of the steps. Essentially we have two parallel workflows, create and update.
If the mailbox on O365 doesn't exist right away, the account will be left with an "update-mail" action. The next time the script runs it picks up the user and carries out the missing actions before clearing state completely.
In the update below I've made create consist of:
create
create-mail
update-mail
And the update workflow:
update
Obviously these can be tweaked to fit properly.
When an action is successful it is updated to match the next step in the workflow. The SQL query has been modified to, hopefully, return accounts in any state.
If a step fails to complete (error raised) the workflow will not advance for that user.
How do you plan to handle problems when the account already exists (and create is called)?
To implement the workflow approach we rather need to define the possible states following each of the steps. Essentially we have two parallel workflows, create and update.
If the mailbox on O365 doesn't exist right away, the account will be left with an "update-mail" action. The next time the script runs it picks up the user and carries out the missing actions before clearing state completely.
In the update below I've made create consist of:
create
create-mail
update-mail
And the update workflow:
update
Obviously these can be tweaked to fit properly.
When an action is successful it is updated to match the next step in the workflow. The SQL query has been modified to, hopefully, return accounts in any state.
If a step fails to complete (error raised) the workflow will not advance for that user.
How do you plan to handle problems when the account already exists (and create is called)?
#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
}
Morning Chris,
Thanks for the complete scenario, however on a second thought...
Since it's always the update in O365 which is causing the issue, I would suggest that we separate it completely.
Then I create a schedule on my another system, which actually is causing the trigger from web-page to SQL, to run this separated script at a given interval that is twice or three times a day.....
That way we can have notification sent once the job is done. I mean that only when the script finds something to be done, then only sends a notification that the job has been performed?
What do you think?
Thanks for the complete scenario, however on a second thought...
Since it's always the update in O365 which is causing the issue, I would suggest that we separate it completely.
Then I create a schedule on my another system, which actually is causing the trigger from web-page to SQL, to run this separated script at a given interval that is twice or three times a day.....
That way we can have notification sent once the job is done. I mean that only when the script finds something to be done, then only sends a notification that the job has been performed?
What do you think?
It's up to you, of course. I would keep it in the same process because it has a huge benefit of being a complete process. Using the action field to track progress fits rather nicely in my mind.
Debugging things later (sometimes years later) is that much harder if you have to figure out where the different bits of a thing might be.
But you have to do what you think best, it's yours :)
Debugging things later (sometimes years later) is that much harder if you have to figure out where the different bits of a thing might be.
But you have to do what you think best, it's yours :)
Oh sorry, I didn't mean not to have it in the same process, I meant along with this.
Instead of running it next time the script is called, I want to have it separated which I can run on schedule.
The practical implication is, we don't make users every day, it's only once or twice a month, max.
So if we have to wait that long, then it defeats the purpose...
What do you say?
Instead of running it next time the script is called, I want to have it separated which I can run on schedule.
The practical implication is, we don't make users every day, it's only once or twice a month, max.
So if we have to wait that long, then it defeats the purpose...
What do you say?
well, actually on a second thought,
if we timeout the update O365 part & since we are putting in DB to update-mail, then I can actually call the same script without creating a new one?
if we timeout the update O365 part & since we are putting in DB to update-mail, then I can actually call the same script without creating a new one?
There's no reason you can't arrange it like that. It certainly makes sense to do so.
It could depend how often you expect to run the script in this thread against how quickly the update-mail action must be executed.
That is, even if a new user was only an occasional thing I'd probably still set quite a fast schedule so it will respond quickly. I might even run it twice with an offset. For example, if I run it every night at 7pm, and again at 9pm, that should be enough time for the mailboxes to create and complete everything.
If that were the approach, I'd omit "Not complete" users from the report so it only reports either success or failure.
A quick check like the one below will prevent the script doing any work if there's nothing to do (which from what you've said will be the case most of the time).
All that said, the actions are pretty well defined and you should feel free to run them in whatever manner you need :)
It could depend how often you expect to run the script in this thread against how quickly the update-mail action must be executed.
That is, even if a new user was only an occasional thing I'd probably still set quite a fast schedule so it will respond quickly. I might even run it twice with an offset. For example, if I run it every night at 7pm, and again at 9pm, that should be enough time for the mailboxes to create and complete everything.
If that were the approach, I'd omit "Not complete" users from the report so it only reports either success or failure.
A quick check like the one below will prevent the script doing any work if there's nothing to do (which from what you've said will be the case most of the time).
# 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
All that said, the actions are pretty well defined and you should feel free to run them in whatever manner you need :)
> then I can actually call the same script without creating a new one?
Yes, exactly. I went through and made sure it correctly reacted to and tagged "Action" throughout. Once it's finished the first pass the database will be updated with an "update-mail" action for newly created mailboxes. Second time the script runs it picks up from there (and does not repeat earlier actions).
Yes, exactly. I went through and made sure it correctly reacted to and tagged "Action" throughout. Once it's finished the first pass the database will be updated with an "update-mail" action for newly created mailboxes. Second time the script runs it picks up from there (and does not repeat earlier actions).
Thanks Chris,
I ran & the first errors I got:
ERROR: Remove-PSSession : Cannot bind parameter 'Session'. Cannot convert the "tmp_wry2ujs2.g5n" value of type "System.Management.Automat
ERROR: "System.Management.Automat
TestO365.ps1 (717, 28): ERROR: At Line: 717 char: 28
ERROR: + Remove-PSSession -Session $PSSession
ERROR: + ~~~~~~~~~~
ERROR: + CategoryInfo : InvalidArgument: (:) [Remove-PSSession], ParameterBindingException
ERROR: + FullyQualifiedErrorId : CannotConvertArgumentNoMes
ERROR:
1
ERROR: Send-MailMessage : The operation has timed out.
TestO365.ps1 (660, 2): ERROR: At Line: 660 char: 2
ERROR: + Send-MailMessage @params
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (System.Net.Mail.SmtpClien
ERROR: + FullyQualifiedErrorId : SmtpException,Microsoft.Po
ERROR:
the only thing I added in Mail was UseSSL
$params = @{
To = $To
From = $From
Subject = $Subject
Body = $Body
BodyAsHtml = $true
UseSSl = $true
SmtpServer = $SmtpServer
Attachments = '.\UserInformation.csv'
}
I ran & the first errors I got:
ERROR: Remove-PSSession : Cannot bind parameter 'Session'. Cannot convert the "tmp_wry2ujs2.g5n" value of type "System.Management.Automat
ERROR: "System.Management.Automat
TestO365.ps1 (717, 28): ERROR: At Line: 717 char: 28
ERROR: + Remove-PSSession -Session $PSSession
ERROR: + ~~~~~~~~~~
ERROR: + CategoryInfo : InvalidArgument: (:) [Remove-PSSession], ParameterBindingException
ERROR: + FullyQualifiedErrorId : CannotConvertArgumentNoMes
ERROR:
1
ERROR: Send-MailMessage : The operation has timed out.
TestO365.ps1 (660, 2): ERROR: At Line: 660 char: 2
ERROR: + Send-MailMessage @params
ERROR: + ~~~~~~~~~~~~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (System.Net.Mail.SmtpClien
ERROR: + FullyQualifiedErrorId : SmtpException,Microsoft.Po
ERROR:
the only thing I added in Mail was UseSSL
$params = @{
To = $To
From = $From
Subject = $Subject
Body = $Body
BodyAsHtml = $true
UseSSl = $true
SmtpServer = $SmtpServer
Attachments = '.\UserInformation.csv'
}
I think the connection function needs a little update.
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 $_
}
}
For Send-MailMessage, you might need to define a Port if you're using SSL. I can't do much about a network timeout unfortunately.
Alright, the mail is working!!!
The account is made in O365, however when i run the script again, it doesn't update the calendar settings
I have a question, with what property are we looking for the user in O365 at Update stage?
The mail address mentioned in the SQL will be the get-mailbox -identity
The account is made in O365, however when i run the script again, it doesn't update the calendar settings
I have a question, with what property are we looking for the user in O365 at Update stage?
The mail address mentioned in the SQL will be the get-mailbox -identity
So, I changed it from samaccount name to mail & seems to find it correctly.
However, it's not setting the permissions for calendar...
However, it's not setting the permissions for calendar...
Ah yes, I see the mailbox locator for that is a bit wrong.
$mailbox = Get-Mailbox -Identity $UserInformation.SamAccountName -ErrorAction SilentlyContinue
$mailbox = Get-Mailbox -Identity $UserInformation.mail -ErrorAction SilentlyContinue
yes that's what I did, however I get this error:
ERROR: An existing permission entry was found for user: Calendar.
ERROR: + CategoryInfo : NotSpecified: (:) [Add-MailboxFolderPermissi
ERROR: + FullyQualifiedErrorId : [Server=DB5PR0401MB2006,Re
ERROR: erAlreadyExistsInPermissio
ERROR: + PSComputerName : ps.outlook.com
ERROR:
ERROR: An existing permission entry was found for user: Calendar.
ERROR: + CategoryInfo : NotSpecified: (:) [Add-MailboxFolderPermissi
ERROR: + FullyQualifiedErrorId : [Server=DB5PR0401MB2006,Re
ERROR: erAlreadyExistsInPermissio
ERROR: + PSComputerName : ps.outlook.com
ERROR:
Two of them to update. This has more fixes.
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 $_
}
}
}
}
}
No, that's raised by the bit that sets / adds mailbox folder rights. I can't advise on the best way to deal with that. Presumably it either needs to overwrite or force, or something else.
It's dying there because I have this one in the function:
Possibly approaches are:
1. Ignore the error and get on with it.
2. Avoid the error completely (check first, add only if not present).
3. Handle it as a failure (current approach).
It's dying there because I have this one in the function:
$ErrorActionPreference = 'Stop'
Possibly approaches are:
1. Ignore the error and get on with it.
2. Avoid the error completely (check first, add only if not present).
3. Handle it as a failure (current approach).
I guess we have to change the variable to alias for calendar to take impact, see in my previous script I had the code as below,
If you see, it first creates $mbox, which is $mbx.alias
I think without alias it won't find calendar or kalender
foreach ($mbx in Get-Mailbox | Where-Object { $_.WhenCreated –ge ((Get-Date).Adddays(-1)) } | Get-Mailbox)
{
$mbox = $mbx.alias + ":\calendar"
$test = Get-MailboxFolderPermissio
if ($test -ne $null)
{
Set-MailboxFolderPermissio
Add-MailboxFolderPermissio
}
else
{
$mbox = $mbx.alias + ":\kalender"
Set-MailboxFolderPermissio
Add-MailboxFolderPermissio
}
}
If you see, it first creates $mbox, which is $mbx.alias
I think without alias it won't find calendar or kalender
foreach ($mbx in Get-Mailbox | Where-Object { $_.WhenCreated –ge ((Get-Date).Adddays(-1)) } | Get-Mailbox)
{
$mbox = $mbx.alias + ":\calendar"
$test = Get-MailboxFolderPermissio
if ($test -ne $null)
{
Set-MailboxFolderPermissio
Add-MailboxFolderPermissio
}
else
{
$mbox = $mbx.alias + ":\kalender"
Set-MailboxFolderPermissio
Add-MailboxFolderPermissio
}
}
Same thing in this one, just a different concatenation style and I skip the variable assignment:
('{0}:\calendar' -f $mailbox.Alias)
Chris,
When the error is thrown, the Action get's updated in SQL, is it possible that we don't update SQL till the time no error is thrown?
When the error is thrown, the Action get's updated in SQL, is it possible that we don't update SQL till the time no error is thrown?
Trying again.
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 $_
}
}
}
}
}
> When the error is thrown, the Action get's updated in SQL, is it possible that we don't update SQL till the time no error is thrown?
It really shouldn't. It should be dropping out at that point and leaving Action alone for that user.
It really shouldn't. It should be dropping out at that point and leaving Action alone for that user.
hmmmm, one thing i noticed in the last update you sent, the line:
$folderPath = '{0}:\calendar' -f $mailbox.Alias
looks for the folder "calendar" how about if it's "kalender" ?
$folderPath = '{0}:\calendar' -f $mailbox.Alias
looks for the folder "calendar" how about if it's "kalender" ?
Missed a copy. I meant to add that too.
$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
}
unfortunately, it doesn't update the permissions, it doesn't throw error anymore after the latest update.
Also, when it throws error, it updates SQL... :(
Also, when it throws error, it updates SQL... :(
WARNING: The command completed successfully but no permissions of 'test.last:\' have been modified.
ERROR: An existing permission entry was found for user: Calendar.
ERROR: + CategoryInfo : NotSpecified: (:) [Add-MailboxFolderPermissi
ERROR: + FullyQualifiedErrorId : [Server=DB5PR0401MB2006,Re
ERROR: erAlreadyExistsInPermissio
ERROR: + PSComputerName : ps.outlook.com
ERROR:
I guess, it's because of the warning the SQL changes.... however the error still persists....
ERROR: An existing permission entry was found for user: Calendar.
ERROR: + CategoryInfo : NotSpecified: (:) [Add-MailboxFolderPermissi
ERROR: + FullyQualifiedErrorId : [Server=DB5PR0401MB2006,Re
ERROR: erAlreadyExistsInPermissio
ERROR: + PSComputerName : ps.outlook.com
ERROR:
I guess, it's because of the warning the SQL changes.... however the error still persists....
It looks like it's not trying to work against the calendar folder at the moment, or at least that's what the error message suggests.
The warning is from those commands as well, nothing to do with SQL.
There's a limited amount I can do to debug these commands, I don't have them here to test syntax with.
It perhaps needs these running independently to verify that the values are indeed correct.
The warning is from those commands as well, nothing to do with SQL.
There's a limited amount I can do to debug these commands, I don't have them here to test syntax with.
It perhaps needs these running independently to verify that the values are indeed correct.
Set-MailboxFolderPermission -Identity $folderPath -User Default -AccessRights Reviewer | Out-Null
Add-MailboxFolderPermission -Identity $folderPath -User Calendar -AccessRights Owner | Out-Null
Morning Chris,
On further testing, I have realised that it's not only the permissions on calendar, but also the services assignment for corresponding license not working...
I am now of opinion that we split the Update O365 part & put in another script, that way I will run it on schedule.
I suggest that we use the line:
foreach ($mbx in Get-Mailbox | Where-Object { $_.WhenCreated –ge ((Get-Date).Adddays(-1)) } | Get-Mailbox)
that way we will have the variable for all the mailboxes made in last 1 day & we can run it on all of them.
Another benefit would be that all future changes for updates (which of course will keep on coming), will be done in this script, that way it will be the only part to be touched....
What do you think?
A
On further testing, I have realised that it's not only the permissions on calendar, but also the services assignment for corresponding license not working...
I am now of opinion that we split the Update O365 part & put in another script, that way I will run it on schedule.
I suggest that we use the line:
foreach ($mbx in Get-Mailbox | Where-Object { $_.WhenCreated –ge ((Get-Date).Adddays(-1)) } | Get-Mailbox)
that way we will have the variable for all the mailboxes made in last 1 day & we can run it on all of them.
Another benefit would be that all future changes for updates (which of course will keep on coming), will be done in this script, that way it will be the only part to be touched....
What do you think?
A
Services isn't working because the list is empty. I did not that when I introduced the variable. It doesn't feed from SQL (that I know of), so you'd have to manually set values.
The permissions on the calendars will work, if the commands are made right, it's so, so close.
The permissions on the calendars will work, if the commands are made right, it's so, so close.
That puts a smile on my face ;)
I also really want that it works this way, after so much work....
Let's see it for a while , otherwise we take the other option....
I also really want that it works this way, after so much work....
Let's see it for a while , otherwise we take the other option....
This is the variable I had in services earlier:
$Services = New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRI
$Services = New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRI
Services, if it's a fixed list it just needs adding to the UpdateO365User function:
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,
$Services = (New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE)
)
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 $_
}
}
}
}
}
That leaves debugging setting permissions. This has a couple of lines to start to debug it a bit. Just Write-Host. When this is debugged those might be changed to Write-Debug instead. Write-Host is a bit evil.
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,
$Services = (New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE)
)
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) {
$folderPath = '{0}:\calendar' -f $mailbox.Alias
$mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
}
if ($mailboxFolder) {
Write-Host "Setting rights for $folderPath (Default: Reviewer)"
Set-MailboxFolderPermission -Identity $folderPath -User Default -AccessRights Reviewer | Out-Null
Write-Host "Adding rights for $folderPath (Calendar: Owner)"
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 $_
}
}
}
}
}
Forgot to update the second version to Kalender.
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,
$Services = (New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE)
)
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) {
$folderPath = '{0}:\kalender' -f $mailbox.Alias
$mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
}
if ($mailboxFolder) {
Write-Host "Setting rights for $folderPath (Default: Reviewer)"
Set-MailboxFolderPermission -Identity $folderPath -User Default -AccessRights Reviewer | Out-Null
Write-Host "Adding rights for $folderPath (Calendar: Owner)"
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 $_
}
}
}
}
}
Thanks, Services worked fine!
Calendar is still having issues...
This was the output:
Setting rights for test.now:\kalender (Default: Reviewer)
Adding rights for test.now:\kalender (Calendar: Owner)
But the rights are still not propagated....
Calendar is still having issues...
This was the output:
Setting rights for test.now:\kalender (Default: Reviewer)
Adding rights for test.now:\kalender (Calendar: Owner)
But the rights are still not propagated....
Chris,
Check this out:
PS C:\Users\sysadm> Get-MailboxFolderPermissio
FolderName User AccessRights
---------- ---- ------------
Oberste Ebene des... Default {Reviewer}
Oberste Ebene des... Anonymous {None}
Oberste Ebene des... Calendar {Owner}
PS C:\Users\sysadm> Get-MailboxFolderPermissio
FolderName User AccessRights
---------- ---- ------------
Oberste Ebene des... Default {Reviewer}
Oberste Ebene des... Anonymous {None}
Oberste Ebene des... Calendar {Owner}
PS C:\Users\sysadm> Get-MailboxFolderPermissio
FolderName User AccessRights
---------- ---- ------------
Kalender Default {Reviewer}
Kalender Anonymous {None}
Kalender Calendar {Owner}
PS C:\Users\sysadm> Get-MailboxFolderPermissio
FolderName User AccessRights
---------- ---- ------------
Kalender Default {AvailabilityOnly}
Kalender Anonymous {None}
Check this out:
PS C:\Users\sysadm> Get-MailboxFolderPermissio
FolderName User AccessRights
---------- ---- ------------
Oberste Ebene des... Default {Reviewer}
Oberste Ebene des... Anonymous {None}
Oberste Ebene des... Calendar {Owner}
PS C:\Users\sysadm> Get-MailboxFolderPermissio
FolderName User AccessRights
---------- ---- ------------
Oberste Ebene des... Default {Reviewer}
Oberste Ebene des... Anonymous {None}
Oberste Ebene des... Calendar {Owner}
PS C:\Users\sysadm> Get-MailboxFolderPermissio
FolderName User AccessRights
---------- ---- ------------
Kalender Default {Reviewer}
Kalender Anonymous {None}
Kalender Calendar {Owner}
PS C:\Users\sysadm> Get-MailboxFolderPermissio
FolderName User AccessRights
---------- ---- ------------
Kalender Default {AvailabilityOnly}
Kalender Anonymous {None}
Sorry,
But DefaultFolderNameMatching user language is not getting propagated:
When I check, it shows the value as False
Can you please see....
But DefaultFolderNameMatching user language is not getting propagated:
When I check, it shows the value as False
Can you please see....
It needs this bit testing independently.
$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
Chris,
It still shows False, however the folders are named correctly, so I guess we let it be for the moment.
I'm sorry, but I want to have this command built in:
get-mailbox -identity $UserInformation.mail | Set-Mailbox -LitigationHoldEnabled $true -LitigationHoldDuration 2555 -RetentionHoldEnabled $true
How would you place it in Update O365 part?
It still shows False, however the folders are named correctly, so I guess we let it be for the moment.
I'm sorry, but I want to have this command built in:
get-mailbox -identity $UserInformation.mail | Set-Mailbox -LitigationHoldEnabled $true -LitigationHoldDuration 2555 -RetentionHoldEnabled $true
How would you place it in Update O365 part?
Yep, that makes sense since it requires the mailbox to exist.
It can slot in anywhere really, for example here it's just after permissions are handled.
It can slot in anywhere really, for example here it's just after permissions are handled.
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,
$Services = (New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE)
)
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) {
$folderPath = '{0}:\kalender' -f $mailbox.Alias
$mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
}
if ($mailboxFolder) {
Write-Host "Setting rights for $folderPath (Default: Reviewer)"
Set-MailboxFolderPermission -Identity $folderPath -User Default -AccessRights Reviewer | Out-Null
Write-Host "Adding rights for $folderPath (Calendar: Owner)"
Add-MailboxFolderPermission -Identity $folderPath -User Calendar -AccessRights Owner | Out-Null
}
$mailbox | Set-Mailbox -LitigationHoldEnabled $true -LitigationHoldDuration 2555 -RetentionHoldEnabled $true
# 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 $_
}
}
}
}
}
What do you think about this:
We are running function UpdateO365User only when the flag is set to update-mail
New-ADUser runs when Action = create & then sets Action = create-mail
Set-ADUser runs when Action = update & then sets Action = ' '
NewO365 runs when Action = create-mail & then sets Action = update-mail
UpdateO365 runs when Action = update-mail & then sets Action = ' '
Now, if we consider the value of Action = Update & are expecting changes to be made in O365 also e.g. we change the Telephone Number, then I don't see anyhow it being pushed to O365....
Please confirm that my understanding is correct?
What I would like to see is that if the update switch is triggered, it should also impact the O365 users, the question will be how to trigger it for Existing users, we can't run New-MsolUser....
What would make more sense, to check in NewO365User function if the user exists, if exists, then update or would you go in UpdateO365User function???
We are running function UpdateO365User only when the flag is set to update-mail
New-ADUser runs when Action = create & then sets Action = create-mail
Set-ADUser runs when Action = update & then sets Action = ' '
NewO365 runs when Action = create-mail & then sets Action = update-mail
UpdateO365 runs when Action = update-mail & then sets Action = ' '
Now, if we consider the value of Action = Update & are expecting changes to be made in O365 also e.g. we change the Telephone Number, then I don't see anyhow it being pushed to O365....
Please confirm that my understanding is correct?
What I would like to see is that if the update switch is triggered, it should also impact the O365 users, the question will be how to trigger it for Existing users, we can't run New-MsolUser....
What would make more sense, to check in NewO365User function if the user exists, if exists, then update or would you go in UpdateO365User function???
It is correct.
I would increase how granular the workflow steps are to handle that kind of event.
That is, the UpdateO365 user function is, at the moment, more of a post-create fix-up task. That can stay in the creation workflow, perhaps with a more meaningful name.
The update workflow might want additional steps though, a step to fix up organisational information. For example:
Create:
NewADUser -> Action: new-mailbox
NewO365Mailbox -> Action: update-newmailbox
UpdateO365NewMailbox -> Action: ' '
Update:
SetADUser -> Action: updateO365Mailbox
UpdateO365Mailbox -> Action: ' '
There are some aspects of this which are going to groan a bit as it gets more complex, for instance the property renaming we do to cater for the NewO365User command is a bit messy. If there's a need to handle user-level updates there too something a bit more flexible might be introduced.
I would increase how granular the workflow steps are to handle that kind of event.
That is, the UpdateO365 user function is, at the moment, more of a post-create fix-up task. That can stay in the creation workflow, perhaps with a more meaningful name.
The update workflow might want additional steps though, a step to fix up organisational information. For example:
Create:
NewADUser -> Action: new-mailbox
NewO365Mailbox -> Action: update-newmailbox
UpdateO365NewMailbox -> Action: ' '
Update:
SetADUser -> Action: updateO365Mailbox
UpdateO365Mailbox -> Action: ' '
There are some aspects of this which are going to groan a bit as it gets more complex, for instance the property renaming we do to cater for the NewO365User command is a bit messy. If there's a need to handle user-level updates there too something a bit more flexible might be introduced.
I agree!
What do you think if we take this approach:
In NewO365Mailbox, we create condition to check if the mailbox exists , if it exists we run set-mailbox instead of new-mailbox.
That way we will not have to rewrite the whole stuff & can make use of existing work?
What do you think if we take this approach:
In NewO365Mailbox, we create condition to check if the mailbox exists , if it exists we run set-mailbox instead of new-mailbox.
That way we will not have to rewrite the whole stuff & can make use of existing work?
Does this make sense:
try
{
# Use as many named parameters as possible
foreach ($property in $propertiesToSet)
{
if ($validParameters.Contains
{
$params.($property.Name) = $property.Value
}
}
# Check if the User Exists
Get-MsolUser -UserPrincipalName $UserInformation.UserPrinc
If (errorVariable -ne $null)
{
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'
}
else {
Set-MsolUser @params -ErrorAction Stop
}
}
catch
{
$UserInformation.Status = 'Failed (Create O365)'
$UserInformation.StatusMes
Write-Error -ErrorRecord $_
}
try
{
# Use as many named parameters as possible
foreach ($property in $propertiesToSet)
{
if ($validParameters.Contains
{
$params.($property.Name) = $property.Value
}
}
# Check if the User Exists
Get-MsolUser -UserPrincipalName $UserInformation.UserPrinc
If (errorVariable -ne $null)
{
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'
}
else {
Set-MsolUser @params -ErrorAction Stop
}
}
catch
{
$UserInformation.Status = 'Failed (Create O365)'
$UserInformation.StatusMes
Write-Error -ErrorRecord $_
}
Getting there, tweaked a bit.
The tricky part is making sure we're passing the right sets of parameters to each command (New vs Set). This doesn't cater for that yet, only for the parameters used by New-MsolUser.
The tricky part is making sure we're passing the right sets of parameters to each command (New vs Set). This doesn't cater for that yet, only for the parameters used by New-MsolUser.
try
{
# Use as many named parameters as possible
foreach ($property in $propertiesToSet)
{
if ($validParameters.Contains($property.Name))
{
$params.($property.Name) = $property.Value
}
}
# Check if the User Exists
$msolUser = Get-MsolUser -UserPrincipalName $UserInformation.mail -ErrorAction SilentlyContinue
if ($msolUser) {
try {
Set-MsolUser @params -ErrorAction Stop
$UserInformation.Action = 'update-mail'
$UserInformation.Status = 'Not complete'
} catch {
$UserInformation.Status = 'Failed (Update O365)'
$UserInformation.StatusMessage = $_.Exception.Message
}
} else {
Write-Host "Creating O365 user with the following information"
Write-Host ($params | ConvertTo-Json -Depth 3)
try {
New-MsolUser @params -ErrorAction Stop
$UserInformation.Action = 'update-mail'
$UserInformation.Status = 'Not complete'
} catch {
$UserInformation.Status = 'Failed (Create O365)'
$UserInformation.StatusMessage = $_.Exception.Message
}
}
}
Oops :)
try
{
# Use as many named parameters as possible
foreach ($property in $propertiesToSet)
{
if ($validParameters.Contains($property.Name))
{
$params.($property.Name) = $property.Value
}
}
# Check if the User Exists
$msolUser = Get-MsolUser -UserPrincipalName $UserInformation.mail -ErrorAction SilentlyContinue
if ($msolUser) {
try {
Set-MsolUser @params -ErrorAction Stop
$UserInformation.Action = 'update-mail'
$UserInformation.Status = 'Not complete'
} catch {
$UserInformation.Status = 'Failed (Update O365)'
$UserInformation.StatusMessage = $_.Exception.Message
}
} else {
Write-Host "Creating O365 user with the following information"
Write-Host ($params | ConvertTo-Json -Depth 3)
try {
New-MsolUser @params -ErrorAction Stop
$UserInformation.Action = 'update-mail'
$UserInformation.Status = 'Not complete'
} catch {
$UserInformation.Status = 'Failed (Create O365)'
$UserInformation.StatusMessage = $_.Exception.Message
}
}
} catch {
# Might be able to remove this soon, it captures things we didn't expect to go wrong.
throw
}
Don't you think, in the SetADUser function we have to put $UserInformation.Action = 'create-mail' ?
That's why whenever the update value in Action is set, it will go to SetADUser function & then it will go to NewO365User function.....
That's why whenever the update value in Action is set, it will go to SetADUser function & then it will go to NewO365User function.....
Somehow, the O365 is not updating....
I will have a thorough look tomorrow, seems we missing some thing....
I will have a thorough look tomorrow, seems we missing some thing....
Almost certainly, I'd be surprised if the parameters for New and Set were exactly the same for instance.
I think we should not set any value for Action in SetADUser.
We should have the condition in NewO365 to trigger if the value is "UPDATE" or "Create-Email"
Whenever we run SetADUser, we MUST run NewO365 because we want to update the O365 also.
Otherwise O365 won't be updated till the next run.
So, either NewADUser or SetADUser both should trigger NewO365 & when SetADUser is not touching Action, it will automatically go to NewO365.
We should have the condition in NewO365 to trigger if the value is "UPDATE" or "Create-Email"
Whenever we run SetADUser, we MUST run NewO365 because we want to update the O365 also.
Otherwise O365 won't be updated till the next run.
So, either NewADUser or SetADUser both should trigger NewO365 & when SetADUser is not touching Action, it will automatically go to NewO365.
Either this:
if ($UserInformation.Action -in 'create-mail', 'update')
if ('create-mail', 'update' -contains $UserInformation.Action)
if ($UserInformation.Action -eq 'create-mail' -or $UserInformation.Action -eq 'update')
This is the error which is not allowing the update:
Cannot convert 'System.String' to the type 'System.Nullable`1[System.
Cannot convert 'System.String' to the type 'System.Nullable`1[System.
Morning Chris,
In the NewO365User we have in the beginning of if statement in Process block ForceChangePassword set to True.
My guess is that is causing the above error.
Can we in the block of update remove it somehow?
In the NewO365User we have in the beginning of if statement in Process block ForceChangePassword set to True.
My guess is that is causing the above error.
Can we in the block of update remove it somehow?
I moved the @parms block inside the else statement to stop impacting update of O365 user.
However there is a logical issue.... (or at least I think)
SetADUser marks Action = Create-Mail
I have this condition in UpdateO365: if ($UserInformation.Action -eq 'create-mail' -or $UserInformation.Action -eq 'update')
However, it seems not to go there?
Also, in the SQL the value in Action is set to create-mail even though I have marked the value to be set to Blank after update is initiated in UpdateO365 function.....
However there is a logical issue.... (or at least I think)
SetADUser marks Action = Create-Mail
I have this condition in UpdateO365: if ($UserInformation.Action -eq 'create-mail' -or $UserInformation.Action -eq 'update')
However, it seems not to go there?
Also, in the SQL the value in Action is set to create-mail even though I have marked the value to be set to Blank after update is initiated in UpdateO365 function.....
Another update....
If in UpdateO365 I don't add action -eq update, it still correctly goes to the if block , finds the user & executes the branch.
This doesn't work if I move the @parms block inside else block.
But if I keep the @params block at original place, then the functions kicks off, however the error is thrown: Cannot convert 'System.String' to the type 'System.Nullable`1[System.
How can we make sure that the password error doesn't happen when set-msoluser is executed?
I guess it will work if this error is removed....
If in UpdateO365 I don't add action -eq update, it still correctly goes to the if block , finds the user & executes the branch.
This doesn't work if I move the @parms block inside else block.
But if I keep the @params block at original place, then the functions kicks off, however the error is thrown: Cannot convert 'System.String' to the type 'System.Nullable`1[System.
How can we make sure that the password error doesn't happen when set-msoluser is executed?
I guess it will work if this error is removed....
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
}
}
# Check if the User Exists
$msolUser = Get-MsolUser -UserPrincipalName $UserInformation.mail -ErrorAction SilentlyContinue
if ($msolUser)
{
try
{
Write-Host "Update MSOLUser initiated"
Set-MsolUser @params -ErrorAction Stop
$UserInformation.Action = ' '
$UserInformation.Status = 'Not complete'
}
catch
{
$UserInformation.Status = 'Failed (Update O365)'
$UserInformation.StatusMessage = $_.Exception.Message
}
}
else
{
Write-Host "Create MSOLUser initiated"
Write-Host "Creating O365 user with the following information"
Write-Host ($params | ConvertTo-Json -Depth 3)
try
{
New-MsolUser @params -ErrorAction Stop
$UserInformation.Action = 'update-mail'
$UserInformation.Status = 'Not complete'
}
catch
{
$UserInformation.Status = 'Failed (Create O365)'
$UserInformation.StatusMessage = $_.Exception.Message
}
}
}
catch
{
# Might be able to remove this soon, it captures things we didn't expect to go wrong
}
}
}
}
Excluded PasswordNeverExpires (in the large Select-Object statement). Allowed it to discover parameters for Set-MsolUser.
There's likely to be a bug. I've guessed at the parameter set name used by Set-MsolUser. Remember we went through this for New-MsolUser? Same thing again, need to make sure we have the right parameter set name or it won't pass anything properly.
There's likely to be a bug. I've guessed at the parameter set name used by Set-MsolUser. Remember we went through this for New-MsolUser? Same thing again, need to make sure we have the right parameter set name or it won't pass anything properly.
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
{
$validSetUserParameters = Get-CommandParameter Set-MsolUser -ParameterSetName "SetUser__0" -AsHashtable
$validNewUserParameters = Get-CommandParameter New-MsolUser -ParameterSetName "AddUser__0" -AsHashtable
}
process
{
if ($UserInformation.Action -eq 'create-mail')
{
# Rewrite the property set to support the O365 commands
$o365UserInformation = $UserInformation | Select-Object -ExcludeProperty UserPrincipalName, PasswordNeverExpires -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 '')
}
$params = @{}
try
{
# Check if the User Exists
$msolUser = Get-MsolUser -UserPrincipalName $UserInformation.mail -ErrorAction SilentlyContinue
if ($msolUser)
{
# Use as many named parameters as possible
foreach ($property in $propertiesToSet)
{
if ($validSetUserParameters.Contains($property.Name))
{
$params.($property.Name) = $property.Value
}
}
try
{
Write-Host "Update MSOLUser initiated"
Set-MsolUser @params -ErrorAction Stop
$UserInformation.Action = ' '
$UserInformation.Status = 'Not complete'
}
catch
{
$UserInformation.Status = 'Failed (Update O365)'
$UserInformation.StatusMessage = $_.Exception.Message
}
}
else
{
$params.ForceChangePassword = $true
# Use as many named parameters as possible
foreach ($property in $propertiesToSet)
{
if ($validNewUserParameters.Contains($property.Name))
{
$params.($property.Name) = $property.Value
}
}
Write-Host "Create MSOLUser initiated"
Write-Host "Creating O365 user with the following information"
Write-Host ($params | ConvertTo-Json -Depth 3)
try
{
New-MsolUser @params -ErrorAction Stop
$UserInformation.Action = 'update-mail'
$UserInformation.Status = 'Not complete'
}
catch
{
$UserInformation.Status = 'Failed (Create O365)'
$UserInformation.StatusMessage = $_.Exception.Message
}
}
}
catch
{
# Might be able to remove this soon, it captures things we didn't expect to go wrong
throw
}
}
}
}
Progress!!
It updated the changed value!
However, if I delete an existing value, like I had Mobile Phone earlier & in the updated it was blank, it didn't remove....
It updated the changed value!
However, if I delete an existing value, like I had Mobile Phone earlier & in the updated it was blank, it didn't remove....
Ah yes, so we explicitly suppress blank values. That means it can't (at the moment) clear values out.
With the AD module there's very specific syntax to use when an attribute is cleared (I think anyway, can't test any more). I've no idea if this is the case or not with Msol.
Please can you let me know if this kind of command works:
With the AD module there's very specific syntax to use when an attribute is cleared (I think anyway, can't test any more). I've no idea if this is the case or not with Msol.
Please can you let me know if this kind of command works:
Set-MsolUser -Identity someone@domain.com -Mobile ''
In that case, let's try letting it have blank values. The filter was applied when the propertiesToSet variable was created, it's been removed from the snippet below.
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
{
$validSetUserParameters = Get-CommandParameter Set-MsolUser -ParameterSetName "AddUser__0" -AsHashtable
$validNewUserParameters = Get-CommandParameter New-MsolUser -ParameterSetName "AddUser__0" -AsHashtable
}
process
{
if ($UserInformation.Action -eq 'create-mail')
{
# Rewrite the property set to support the O365 commands
$o365UserInformation = $UserInformation | Select-Object -ExcludeProperty UserPrincipalName, PasswordNeverExpires -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
$params = @{}
try
{
# Check if the User Exists
$msolUser = Get-MsolUser -UserPrincipalName $UserInformation.mail -ErrorAction SilentlyContinue
if ($msolUser)
{
# Use as many named parameters as possible
foreach ($property in $propertiesToSet)
{
if ($validSetUserParameters.Contains($property.Name))
{
$params.($property.Name) = $property.Value
}
}
try
{
Write-Host "Update MSOLUser initiated"
Set-MsolUser @params -ErrorAction Stop
$UserInformation.Action = ' '
$UserInformation.Status = 'Not complete'
}
catch
{
$UserInformation.Status = 'Failed (Update O365)'
$UserInformation.StatusMessage = $_.Exception.Message
}
}
else
{
$params.ForceChangePassword = $true
# Use as many named parameters as possible
foreach ($property in $propertiesToSet)
{
if ($validNewUserParameters.Contains($property.Name))
{
$params.($property.Name) = $property.Value
}
}
Write-Host "Create MSOLUser initiated"
Write-Host "Creating O365 user with the following information"
Write-Host ($params | ConvertTo-Json -Depth 3)
try
{
New-MsolUser @params -ErrorAction Stop
$UserInformation.Action = 'update-mail'
$UserInformation.Status = 'Not complete'
}
catch
{
$UserInformation.Status = 'Failed (Create O365)'
$UserInformation.StatusMessage = $_.Exception.Message
}
}
}
catch
{
# Might be able to remove this soon, it captures things we didn't expect to go wrong
throw
}
}
}
}
It works for UpdateO365!
Now, in the SetADUser it just leaves the values there....?
Shall we try to see if it works in there also removing the check?
Now, in the SetADUser it just leaves the values there....?
Shall we try to see if it works in there also removing the check?
SetADUser, I think it needs a completely different approach to handling empty values. However, it's worth a try, the limitation may only apply to objectAttributes, those definitely need a different handler.
Anyway, give it a try, see what happens. Same change to propertiesToSet.
Anyway, give it a try, see what happens. Same change to propertiesToSet.
I tried, but the SetADUser needs the value:
It throws error: Cannot validate argument on parameter 'Replace'. The argument is null or an element of the argument collection contains a null value.
:(
It throws error: Cannot validate argument on parameter 'Replace'. The argument is null or an element of the argument collection contains a null value.
:(
Chris,
How about this approach: https://www.experts-exchange.com/questions/28577073/PowerShell-Script-for-Importing-Data-from-CSV-into-Active-Directory.html
If we create Dynamic, then we don't have to worry at any stage?
How about this approach: https://www.experts-exchange.com/questions/28577073/PowerShell-Script-for-Importing-Data-from-CSV-into-Active-Directory.html
If we create Dynamic, then we don't have to worry at any stage?
Figures.
They need to feed the Clear parameter. This attempts to wire that up.
They need to feed the Clear parameter. This attempts to wire that up.
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
}
$clear = @()
$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
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 {
if ($_.Value -is [String] -and $_.Value.Trim() -eq '') {
$clear += $property.Name
} else {
$replace.Add($property.Name, $property.Value)
}
}
}
# Load everything else into OtherAttributes
if ($clear.Count -gt 0) {
$params.Clear = $clear
}
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 $_
}
}
}
}
}
> How about this approach
That's what it's doing already. This just ramps the dynamic nature up a level or two :)
That's what it's doing already. This just ramps the dynamic nature up a level or two :)
Updating user with the following information
{
"PostalCode": "8001",
"Enabled": true,
"Replace": {
"st": "",
"mobile": "",
"co": "756",
"mail": "test.now1@test.com",
"l": "",
"telephoneNumber": "",
"c": "CH",
"physicalDeliveryOfficeNam
"sn": "Now1"
},
"userPrincipalName": "ten@test.com",
"GivenName": "G ",
"PasswordNeverExpires": true,
"Identity": "ten",
"StreetAddress": "",
"Title": "Manager",
"Department": "Team A",
"DisplayName": "Test Now1"
}
ERROR: SetADUser : replace
TestO365.ps1 (768, 19): ERROR: At Line: 768 char: 19
ERROR: + $usersToProcess | SetADUser
ERROR: + ~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (ten:ADUser) [Write-Error], ADInvalidOperationExceptio
ERROR: + FullyQualifiedErrorId : ActiveDirectoryServer:0,Se
{
"PostalCode": "8001",
"Enabled": true,
"Replace": {
"st": "",
"mobile": "",
"co": "756",
"mail": "test.now1@test.com",
"l": "",
"telephoneNumber": "",
"c": "CH",
"physicalDeliveryOfficeNam
"sn": "Now1"
},
"userPrincipalName": "ten@test.com",
"GivenName": "G ",
"PasswordNeverExpires": true,
"Identity": "ten",
"StreetAddress": "",
"Title": "Manager",
"Department": "Team A",
"DisplayName": "Test Now1"
}
ERROR: SetADUser : replace
TestO365.ps1 (768, 19): ERROR: At Line: 768 char: 19
ERROR: + $usersToProcess | SetADUser
ERROR: + ~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (ten:ADUser) [Write-Error], ADInvalidOperationExceptio
ERROR: + FullyQualifiedErrorId : ActiveDirectoryServer:0,Se
Sorry, silly mistake. Fixed here, wrong variable name used.
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
}
$clear = @()
$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
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 {
if ($property.Value.ToString().Trim() -eq '') {
$clear += $property.Name
} else {
$replace.Add($property.Name, $property.Value)
}
}
}
# Load everything else into OtherAttributes
if ($clear.Count -gt 0) {
$params.Clear = $clear
}
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 $_
}
}
}
}
}
still the same error?
Updating user with the following information
{
"PostalCode": "8001",
"Enabled": true,
"Clear": [
"l",
"st",
"telephoneNumber",
"mobile"
],
"Replace": {
"c": "CH",
"physicalDeliveryOfficeNam
"co": "756",
"mail": "test.now1@test.com",
"sn": "Now1"
},
"userPrincipalName": "ten@test.com",
"GivenName": "G",
"PasswordNeverExpires": true,
"Identity": "ten",
"StreetAddress": "",
"Title": "Manager",
"Department": "Team A",
"DisplayName": "Test Now1"
}
ERROR: SetADUser : replace
TestO365.ps1 (768, 19): ERROR: At Line: 768 char: 19
ERROR: + $usersToProcess | SetADUser
ERROR: + ~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (ten:ADUser) [Write-Error], ADInvalidOperationExceptio
ERROR: + FullyQualifiedErrorId : ActiveDirectoryServer:0,Se
ERROR:
Updating user with the following information
{
"PostalCode": "8001",
"Enabled": true,
"Clear": [
"l",
"st",
"telephoneNumber",
"mobile"
],
"Replace": {
"c": "CH",
"physicalDeliveryOfficeNam
"co": "756",
"mail": "test.now1@test.com",
"sn": "Now1"
},
"userPrincipalName": "ten@test.com",
"GivenName": "G",
"PasswordNeverExpires": true,
"Identity": "ten",
"StreetAddress": "",
"Title": "Manager",
"Department": "Team A",
"DisplayName": "Test Now1"
}
ERROR: SetADUser : replace
TestO365.ps1 (768, 19): ERROR: At Line: 768 char: 19
ERROR: + $usersToProcess | SetADUser
ERROR: + ~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (ten:ADUser) [Write-Error], ADInvalidOperationExceptio
ERROR: + FullyQualifiedErrorId : ActiveDirectoryServer:0,Se
ERROR:
I reckon it's falling on streetAddress.
Please can you let me know if this works?
Please can you let me know if this works?
Set-ADUser someone -StreetAddress ""
Damn really? That's rather unfortunate because I can't see anything else wrong with it. Dammit, that takes us back to trimming it down until the offending attribute is identified. Perhaps start with the Clear operation since we just added that in.
Set-ADUser someone -Clear "l", "st", "telephoneNumber", "mobile"
Sorry,
I meant I removed all attributes of the user in AD, street, phone, mail & all & then ran the script & it fails.
I meant I removed all attributes of the user in AD, street, phone, mail & all & then ran the script & it fails.
Yeah, there's a problem with the command, not a problem with the user.
From the error it looks like it's in the replace block, but since Clear is the thing we added it makes sense to test that one first.
From the error it looks like it's in the replace block, but since Clear is the thing we added it makes sense to test that one first.
Build the command up to find the error.
Start with the new stuff, Clear.
Start with the new stuff, Clear.
Set-ADUser someone -Clear "l", "st", "telephoneNumber", "mobile"
shall I run this in a new shell or incorporate in the script?
I have already run it in the shell & after that when I run the script , it fails....
I have already run it in the shell & after that when I run the script , it fails....
New shell, just a one-off to isolate the error. Then we'll add stuff to make sure the error doesn't occur in the real thing.
If the first works, this is the second iteration:
Set-ADUser -Identity "ten" -PostalCode "8001" -Enabled $true -Clear "l", "st", "telephoneNumber", "mobile" -userPrincipalname "ten@test.com" -GivenName "G" -PasswordNeverExpires $true -StreetAddress "" -Title "Manager" -Department "Team A" -Displayname "Test Now1"
And this is the third iteration, this one is expected to fail. If it doesn't fail it will be incredibly annoying.
Set-ADUser -Identity "ten" -PostalCode "8001" -Enabled $true -Clear "l", "st", "telephoneNumber", "mobile" -userPrincipalname "ten@test.com" -GivenName "G" -PasswordNeverExpires $true -StreetAddress "" -Title "Manager" -Department "Team A" -Displayname "Test Now1" -Replace @{
"c" = "CH"
"physicalDeliveryOfficeName" = "2344"
"co" = "756"
"mail" = "test.now1@test.com"
"sn" = "Now1"
}
so the first part I ran:
Set-ADUser -Identity "ten" -PostalCode "8001" -Enabled $true -Clear "l", "st", "telephoneNumber", "mobile" -userPrincipalname "ten@test.com" -GivenName "G" -PasswordNeverExpires $true -StreetAddress "" -Title "Manager" -Department "Team A" -Displayname "Test Now1"
I got this error:
Set-ADUser : replace
At line:1 char:1
+ Set-ADUser -Identity "ten" -PostalCode "8001" -Enabled $true -Clear "l", "st", " ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (ten:ADUser) [Set-ADUser], ADInvalidOperationExceptio
+ FullyQualifiedErrorId : ActiveDirectoryServer:0,Mi
Set-ADUser -Identity "ten" -PostalCode "8001" -Enabled $true -Clear "l", "st", "telephoneNumber", "mobile" -userPrincipalname "ten@test.com" -GivenName "G" -PasswordNeverExpires $true -StreetAddress "" -Title "Manager" -Department "Team A" -Displayname "Test Now1"
I got this error:
Set-ADUser : replace
At line:1 char:1
+ Set-ADUser -Identity "ten" -PostalCode "8001" -Enabled $true -Clear "l", "st", " ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (ten:ADUser) [Set-ADUser], ADInvalidOperationExceptio
+ FullyQualifiedErrorId : ActiveDirectoryServer:0,Mi
Awesome, that helps a lot. It means the error is nothing at all to do with the replace parameter despite the error message.
Does the error go away if we eject the StreetAddress parameter?
Does the error go away if we eject the StreetAddress parameter?
Set-ADUser -Identity "ten" -PostalCode "8001" -Enabled $true -Clear "l", "st", "telephoneNumber", "mobile" -userPrincipalname "ten@test.com" -GivenName "G" -PasswordNeverExpires $true -Title "Manager" -Department "Team A" -Displayname "Test Now1"
What a surprise.
Does this version work?
Does this version work?
Set-ADUser -Identity "ten" -PostalCode "8001" -Enabled $true -Clear "l", "st", "telephoneNumber", "mobile", "streetAddress" -userPrincipalname "ten@test.com" -GivenName "G" -PasswordNeverExpires $true -Title "Manager" -Department "Team A" -Displayname "Test Now1" -Replace @{
"c" = "CH"
"physicalDeliveryOfficeName" = "2344"
"co" = "756"
"mail" = "test.now1@test.com"
"sn" = "Now1"
}
if it does, this is a speculative fix for the function.
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
}
$clear = @()
$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
if ($UserInformation.Action -eq 'update') {
# Use as many named parameters as possible
foreach ($property in $propertiesToSet) {
if ($property.Value.ToString().Trim() -eq '') {
if ($validParameters.Contains($property.Name)) {
$params.($property.Name) = $property.Value
} else {
$replace.Add($property.Name, $property.Value)
}
} else {
$clear += $property.Name
}
}
# Load everything else into OtherAttributes
if ($clear.Count -gt 0) {
$params.Clear = $clear
}
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 $_
}
}
}
}
}
Not a very good speculative fix, logic error. Fixed here.
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
}
$clear = @()
$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
if ($UserInformation.Action -eq 'update') {
# Use as many named parameters as possible
foreach ($property in $propertiesToSet) {
if ($property.Value.ToString().Trim() -ne '') {
if ($validParameters.Contains($property.Name)) {
$params.($property.Name) = $property.Value
} else {
$replace.Add($property.Name, $property.Value)
}
} else {
$clear += $property.Name
}
}
# Load everything else into OtherAttributes
if ($clear.Count -gt 0) {
$params.Clear = $clear
}
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 $_
}
}
}
}
}
Chris,
So far the test is going Great!
If I want to add Alias for newly created user in function UpdateO365User with samaccount name e.g.
ten@abc.com & test.now1@xyz.com
How would I call the variable for samaccount name? As we are not using it anywhere in the O365....
So far the test is going Great!
If I want to add Alias for newly created user in function UpdateO365User with samaccount name e.g.
ten@abc.com & test.now1@xyz.com
How would I call the variable for samaccount name? As we are not using it anywhere in the O365....
It can be added during the massive Select-Object statement.
How do you determine whether it's abc.com or xyz.com? The former is hard-coded in the example below.
How do you determine whether it's abc.com or xyz.com? The former is hard-coded in the example below.
$o365UserInformation = $UserInformation | Select-Object -ExcludeProperty UserPrincipalName, PasswordNeverExpires -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 } },
@{ Name = 'Alias'; Expression = { '{0}@abc.com' -f $_.SamAccountName }}
it's not either or, its always and
so if i have a user test.now who's short is ten , then I need 3 accounts
test.now@test.com
test.now@abc.com
ten@test.com
this is the logic
so if i have a user test.now who's short is ten , then I need 3 accounts
test.now@test.com
test.now@abc.com
ten@test.com
this is the logic
So we need to run New-MsolUser 3 times for each incoming user?
If so, its likely the best way is to generate three aliases up-front and loop through those.
If we do so, what happens with UserPrincipalName? It'll be the same in each case. Same question applies to "mail" since we're using that as a primary identity for the online mailbox.
If so, its likely the best way is to generate three aliases up-front and loop through those.
If we do so, what happens with UserPrincipalName? It'll be the same in each case. Same question applies to "mail" since we're using that as a primary identity for the online mailbox.
How about this:
Add multiple Proxy E-mail addresse
In case that we need to add multiple E-mail addresses, we can separate each E-mail address with a comma.
PowerShell command example
Set-Mailbox Angelina -EmailAddresses @{add="Angelina-Alias11@o3
from: http://o365info.com/manage-email-address-using-powershell/
now, if we translate this to my scenario:
$Variable: ten
$variable2: test.now1
Set-Mailbox Angelina -EmailAddresses @{add="$variable@xyz.com",
How about this?
Add multiple Proxy E-mail addresse
In case that we need to add multiple E-mail addresses, we can separate each E-mail address with a comma.
PowerShell command example
Set-Mailbox Angelina -EmailAddresses @{add="Angelina-Alias11@o3
from: http://o365info.com/manage-email-address-using-powershell/
now, if we translate this to my scenario:
$Variable: ten
$variable2: test.now1
Set-Mailbox Angelina -EmailAddresses @{add="$variable@xyz.com",
How about this?
That's certainly easier. However, it would have to be in the UpdateO365 function, we can't be sure the mailbox will be there until that kicks in.
Perhaps...
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,
$Services = (New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE)
)
begin {
$ErrorActionPreference = 'Stop'
}
process {
if ($UserInformation.Action -eq 'update-mail') {
$mailbox = Get-Mailbox -Identity $UserInformation.mail -ErrorAction SilentlyContinue
if ($mailbox) {
try {
$alias = $UserInformation.mail -replace '@.+$'
$aliases = @(
'{0}@xyz.com' -f $UserInformation.SamAccountName
'{0}@abc.com' -f $alias
'{0}@xyz.com' -f $alias
)
$mailbox | Set-Mailbox -EmailAddresses @{add=$aliases}
# 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) {
$folderPath = '{0}:\kalender' -f $mailbox.Alias
$mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
}
if ($mailboxFolder) {
Write-Host "Setting rights for $folderPath (Default: Reviewer)"
Set-MailboxFolderPermission -Identity $folderPath -User Default -AccessRights Reviewer | Out-Null
Write-Host "Adding rights for $folderPath (Calendar: Owner)"
Add-MailboxFolderPermission -Identity $folderPath -User Calendar -AccessRights Owner | Out-Null
}
$mailbox | Set-Mailbox -LitigationHoldEnabled $true -LitigationHoldDuration 2555 -RetentionHoldEnabled $true
# 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 $_
}
}
}
}
}
This was my test data.
$UserInformation = ConvertFrom-Json '{
"mail": "test.now1@test.com",
"userPrincipalName": "ten@test.com",
"sAMAccountName": "ten",
}'
$alias = $UserInformation.mail -replace '@.+$'
$aliases = @(
'{0}@xyz.com' -f $UserInformation.SamAccountName
'{0}@abc.com' -f $alias
'{0}@xyz.com' -f $alias
)
ten@xyz.com
test.now1@abc.com
test.now1@xyz.com
Hi Chris,
I found a small glitch, when the Email account is created or updated it takes the country as "c" which is CH, whereas the Country should be "country" as Switzerland.
Can you suggest where do I have to make the change?
I found a small glitch, when the Email account is created or updated it takes the country as "c" which is CH, whereas the Country should be "country" as Switzerland.
Can you suggest where do I have to make the change?
The property mapping thing contains this entry:
@{ Name = 'Country'; Expression = { $_.c } },
Thanks,
I'm sorry I don't get what you mean by, if the information is available in $UserInformation...
I'm not sure where $userinformation is being created...
I'm sorry I don't get what you mean by, if the information is available in $UserInformation...
I'm not sure where $userinformation is being created...
Sorry, almost Monday :)
UserInformation is a single record (row) from the SQL database. Each instance of UserInformation has the properties we selected from SQL available. To automatically fill the Country field we'd need to select something named Country in this block:
UserInformation is a single record (row) from the SQL database. Each instance of UserInformation has the properties we selected from SQL available. To automatically fill the Country field we'd need to select something named Country in this block:
$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%'"
Wow!
That was tricky, I created the Country in the block & AD creation started to throw error saying c is already as country.
Then I put Country as test & made the change in msoluser, then again aduser says unknown ....
then I put test in excluded properties & it worked fine!!!
Thanks a lot!!!
Let me find more glitches ;)
That was tricky, I created the Country in the block & AD creation started to throw error saying c is already as country.
Then I put Country as test & made the change in msoluser, then again aduser says unknown ....
then I put test in excluded properties & it worked fine!!!
Thanks a lot!!!
Let me find more glitches ;)
Chris,
Another help... When I change the users license, let's say from
reseller-account:EXCHANGEE
desired behaviour is that the License should be changed in O365, however we don't have this setup or at least it's not working...
Can you please suggest?
My guess is it should be in UpdateO365 in this area
Please suggest...
Another help... When I change the users license, let's say from
reseller-account:EXCHANGEE
desired behaviour is that the License should be changed in O365, however we don't have this setup or at least it's not working...
Can you please suggest?
My guess is it should be in UpdateO365 in this area
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'
}
switch ($UserInformation.c)
{
'CH' {
$params.Language = 'de-CH'
}
'DE' {
$params.Language = 'de-DE'
}
}
Set-MailboxRegionalConfiguration @params -LocalizeDefaultFolderName:$true
This is what I think should be added >>>> Set-MsolUser -UserPrincipalName $UserInformation.mail -AddLicenses $LicenseAssignment
Please suggest...
One thing I noticed is, that if you don'r remove the existing license, it assigns the second license too, that means the first part has to remove the existing license & then assign the changed license.... what the hell MS
So two commands would be:
Set-MsolUserLicense -UserPrincipalName -RemoveLicense "Existing License"
Set-MsolUserLicense -UserPrincipalName -AddLicense "New License"
So two commands would be:
Set-MsolUserLicense -UserPrincipalName -RemoveLicense "Existing License"
Set-MsolUserLicense -UserPrincipalName -AddLicense "New License"
Is there a way to list the existing licenses for an account? That would help determine if Remove needs to be used at all. Or will an account always have exactly 1?
I thought the same, but the problem is that once the SQL has the update value in action at that time it already removes the existing one....
This is getting Ugly... if the license is EnterprisePack, then the service assignment will also have to be triggered at this step only.
We have this in UpdateO365, but since this is for existing user & being triggered in NewO365 part, when the mailbox is existing... it has to trigger here.
We have this in UpdateO365, but since this is for existing user & being triggered in NewO365 part, when the mailbox is existing... it has to trigger here.
I think I have an idea!
We put if statement & reverse there logic, means of the license in SQL is A, that means it should first remove B then assign A!
What do you think?
Only problem is, it will always do that, point of to figure out how to run this only when license changes
We put if statement & reverse there logic, means of the license in SQL is A, that means it should first remove B then assign A!
What do you think?
Only problem is, it will always do that, point of to figure out how to run this only when license changes
Hmm I would suggest this needs a new step. If only because its relatively complex and it'd be nice not to have to think about it in the context of everything else (within an existing step).
How about we make a column in SQL, call it "OldLicense", then the change is only happening on column "License"
Now, we create if statement, which checks if OldLicense = License, if yes nothing happens, if no then we can run this:
Set-MsolUserLicense -UserPrincipalName $upn -AddLicenses $License -RemoveLicenses $oldLicense
& after that we copy the value of License to OldLicense in SQL, that way when the next time no change is happening on License, nothing happens & if the change happens, then this triggers.
What do you think?
Now, we create if statement, which checks if OldLicense = License, if yes nothing happens, if no then we can run this:
Set-MsolUserLicense -UserPrincipalName $upn -AddLicenses $License -RemoveLicenses $oldLicense
& after that we copy the value of License to OldLicense in SQL, that way when the next time no change is happening on License, nothing happens & if the change happens, then this triggers.
What do you think?
It works, another approach is to check the license configuration for each user passing through the system and tag it onto the end of the normal update workflow. The assumption being that it's set correctly when someone is created under the new user workflow.
I'd go with the new function (no changes to SQL).
It's more sure, you're always checking the current licence state against an authoritative source, the database doesn't have to worry about being out of date because someone made a change outside of that process.
It's more sure, you're always checking the current licence state against an authoritative source, the database doesn't have to worry about being out of date because someone made a change outside of that process.
This is far from complete, without the commands I don't have much of a chance of getting this perfectly right. It'll need some experimentation and tweaking.
function UpdateO365License {
# .SYNOPSIS
# Implements the update-license action.
# .DESCRIPTION
# UpdateO365License tests and updates license and service information for a user.
# .INPUTS
# System.Management.Automation.PSObject
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation
)
begin {
$ErrorActionPreference = 'Stop'
}
process {
if ($UserInformation.Action -eq 'update-license') {
try {
# Get the mailbox, should give license information.
$mailbox = Get-MsolUser -UserPrincipalName $UserInformation.mail
# Not sure if this comparison will work at all. Depends how this is exposed, what type of object it is, etc.
if ($mailbox) {
if ($mailbox.Licenses -ne $UserInformation.License) {
# Presumably the services will change depending on the license?
$services = (New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE)
# Looks like you can swap a license in a single step.
Set-MsolUserLicense -UserPrincipalName -AddLicense $UserInformation.License -RemoveLicense $mailbox.Licenses -LicenseOptions $services
}
# End of workflow
$UserInformation.Action = ''
$UserInformation.Status = 'OK'
} else {
$UserInformation.Status = 'Failed (Update)'
$UserInformation.StatusMessage = 'Unable to find mailbox'
}
} catch {
$UserInformation.Status = 'Failed (Update)'
$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
$null = Import-PSSession $PSSession -AllowClobber
$null = 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
#
# Create
#
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 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
{
$validSetUserParameters = Get-CommandParameter Set-MsolUser -ParameterSetName "AddUser__0" -AsHashtable
$validNewUserParameters = Get-CommandParameter New-MsolUser -ParameterSetName "AddUser__0" -AsHashtable
}
process
{
if ($UserInformation.Action -eq 'create-mail')
{
# Rewrite the property set to support the O365 commands
$o365UserInformation = $UserInformation | Select-Object -ExcludeProperty UserPrincipalName, PasswordNeverExpires -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
$params = @{}
try
{
# Check if the User Exists
$msolUser = Get-MsolUser -UserPrincipalName $UserInformation.mail -ErrorAction SilentlyContinue
if ($msolUser)
{
# Use as many named parameters as possible
foreach ($property in $propertiesToSet)
{
if ($validSetUserParameters.Contains($property.Name))
{
$params.($property.Name) = $property.Value
}
}
try
{
Write-Host "Update MSOLUser initiated"
Set-MsolUser @params -ErrorAction Stop
$UserInformation.Action = ' '
$UserInformation.Status = 'Not complete'
}
catch
{
$UserInformation.Status = 'Failed (Update O365)'
$UserInformation.StatusMessage = $_.Exception.Message
}
}
else
{
$params.ForceChangePassword = $true
# Use as many named parameters as possible
foreach ($property in $propertiesToSet)
{
if ($validNewUserParameters.Contains($property.Name))
{
$params.($property.Name) = $property.Value
}
}
Write-Host "Create MSOLUser initiated"
Write-Host "Creating O365 user with the following information"
Write-Host ($params | ConvertTo-Json -Depth 3)
try
{
New-MsolUser @params -ErrorAction Stop
$UserInformation.Action = 'update-mail'
$UserInformation.Status = 'Not complete'
}
catch
{
$UserInformation.Status = 'Failed (Create O365)'
$UserInformation.StatusMessage = $_.Exception.Message
}
}
}
catch
{
# Might be able to remove this soon, it captures things we didn't expect to go wrong
throw
}
}
}
}
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,
$Services = (New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE)
)
begin {
$ErrorActionPreference = 'Stop'
}
process {
if ($UserInformation.Action -eq 'update-mail') {
$mailbox = Get-Mailbox -Identity $UserInformation.mail -ErrorAction SilentlyContinue
if ($mailbox) {
try {
$alias = $UserInformation.mail -replace '@.+$'
$aliases = @(
'{0}@xyz.com' -f $UserInformation.SamAccountName
'{0}@abc.com' -f $alias
'{0}@xyz.com' -f $alias
)
$mailbox | Set-Mailbox -EmailAddresses @{add=$aliases}
# 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) {
$folderPath = '{0}:\kalender' -f $mailbox.Alias
$mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
}
if ($mailboxFolder) {
Write-Host "Setting rights for $folderPath (Default: Reviewer)"
Set-MailboxFolderPermission -Identity $folderPath -User Default -AccessRights Reviewer | Out-Null
Write-Host "Adding rights for $folderPath (Calendar: Owner)"
Add-MailboxFolderPermission -Identity $folderPath -User Calendar -AccessRights Owner | Out-Null
}
$mailbox | Set-Mailbox -LitigationHoldEnabled $true -LitigationHoldDuration 2555 -RetentionHoldEnabled $true
# 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 $_
}
}
}
}
}
#
# Update
#
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
}
$clear = @()
$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
if ($UserInformation.Action -eq 'update') {
# Use as many named parameters as possible
foreach ($property in $propertiesToSet) {
if ($property.Value.ToString().Trim() -ne '') {
if ($validParameters.Contains($property.Name)) {
$params.($property.Name) = $property.Value
} else {
$replace.Add($property.Name, $property.Value)
}
} else {
$clear += $property.Name
}
}
# Load everything else into OtherAttributes
if ($clear.Count -gt 0) {
$params.Clear = $clear
}
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.Action = 'update-license'
$UserInformation.Status = 'OK'
} catch {
$UserInformation.Status = 'Failed (Update)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
}
function UpdateO365License {
# .SYNOPSIS
# Implements the update-license action.
# .DESCRIPTION
# UpdateO365License tests and updates license and service information for a user.
# .INPUTS
# System.Management.Automation.PSObject
# .OUTPUTS
# None
param (
[Parameter(ValueFromPipeline = $true)]
[PSObject]$UserInformation
)
begin {
$ErrorActionPreference = 'Stop'
}
process {
if ($UserInformation.Action -eq 'update-license') {
try {
# Get the mailbox, should give license information.
$mailbox = Get-MsolUser -UserPrincipalName $UserInformation.mail
# Not sure if this comparison will work at all. Depends how this is exposed, what type of object it is, etc.
if ($mailbox) {
if ($mailbox.Licenses -ne $UserInformation.License) {
# Presumably the services will change depending on the license?
$services = (New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE)
# Looks like you can swap a license in a single step.
Set-MsolUserLicense -UserPrincipalName -AddLicense $UserInformation.License -RemoveLicense $mailbox.Licenses -LicenseOptions $services
}
# End of workflow
$UserInformation.Action = ''
$UserInformation.Status = 'OK'
} else {
$UserInformation.Status = 'Failed (Update)'
$UserInformation.StatusMessage = 'Unable to find mailbox'
}
} catch {
$UserInformation.Status = 'Failed (Update)'
$UserInformation.StatusMessage = $_.Exception.Message
Write-Error -ErrorRecord $_
}
}
}
}
#
# Reporting
#
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 = { '' }}
if (($usersToProcess | Measure-Object).Count -gt 0) {
# 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
$usersToProcess | UpdateO365License
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
}
}