asked on 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
PowershellMicrosoft SQL ServerMicrosoft 365
Last Comment
Ackles
How do you normally kick off the connection to O365? Import a PSSession?
ASKER
### 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
}
ASKER
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 = { '' } }
ASKER
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...
ASKER
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
}
ASKER
can we change License instead of static to pull from SQL?
I have a column named license in SQL
I have a column named license in SQL
ASKER
Sorry, also usage location is same as "c"
Awesome. Just having lunch, no problem on both counts.
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
}
ASKER
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
}
ASKER
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
ASKER
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....
ASKER
the UPN in O365 in this environment would be the EmailAddress from SQL
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
ASKER
I've been only making the changes, let me double check.
Sorry about that....
Sorry about that....
That's okay, it's a (very) long script. Need source control :)
ASKER
where do you want me to insert (Get-Command New-MsolUser).ParameterSet
ASKER
New-ADUser is fine!
Sorry about my mistake... :(
Sorry about my mistake... :(
ASKER
I'm not running 5 :(
I only have V3
I only have V3
(Get-Command New-MsolUser).ParameterSets.Item.Name
ASKER
If i run it in shell, after connecting to MSOL, it says: Item
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
}
ASKER
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
ASKER
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:
ASKER
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
Ohhh scratch that.. need to fix the other error first.
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 $_
}
}
}
}
ASKER
ASKER
should i run the second or not?
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 $_
}
}
}
}
ASKER
& BAM!!!
ASKER
Now, I start the update O365 part ;)
I had it disabled ....
I had it disabled ....
ASKER
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
ASKER
I guess, we have to terminate the connection every time after the script....
LOL
Remove-PSSession at the end I reckon.
Remove-PSSession at the end I reckon.
ASKER
it asks for the id of pssession
ASKER
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....
ASKER
i did get-pssession | remove-pssession
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 $_
}
}
}
ASKER
It's waiting!!!
ASKER
Seems MS went home ;)
Still waiting, but it's a good sign!!!
Still waiting, but it's a good sign!!!
ASKER
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?
ASKER
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?
ASKER
Any thoughts, Chris?
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
}
ASKER
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 :)
ASKER
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?
ASKER
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).
ASKER
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.
ASKER
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
ASKER
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
ASKER
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 $_
}
}
}
}
}
ASKER
this is what I did, but I get the above mentioned error?
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).
ASKER
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
}
}
ASKER
I see you are making it in $mailboxfolder...
Same thing in this one, just a different concatenation style and I skip the variable assignment:
('{0}:\calendar' -f $mailbox.Alias)
heh except I forgot to use the variable properly. Those lines need some work.
ASKER
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.
ASKER
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
}
ASKER
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... :(
ASKER
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
ASKER
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