We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Powershell help for creating accounts

Medium Priority
959 Views
1 Endorsement
Last Modified: 2017-06-30
Hello Experts,
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
}

Open in new window


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
		
	}

Open in new window


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
Comment
Watch Question

Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
How do you normally kick off the connection to O365? Import a PSSession?

Author

Commented:
### Connect to Office 365
Set-ExecutionPolicy RemoteSigned
Import-Module MSOnline
$emailusername = "abc@test.com"
$encrypted = Get-Content c:\encrypted_password1.txt | ConvertTo-SecureString
$O365Cred = New-Object System.Management.Automation.PsCredential($emailusername, $encrypted)

$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
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
First attempt then.

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
}

Open in new window

Author

Commented:
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:

		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

Open in new window


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,Microsoft.PowerShell.Commands.SelectObjectCommand
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], ParameterBindingValidationException
ERROR:     + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.PowerShell.Commands.GetVariableCommand
ERROR:
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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 = { '' } }

Open in new window

If the second error happens again, is it possible the Enabled value (from SQL) to be empty?

Author

Commented:
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...

Author

Commented:
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.
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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
}

Open in new window

Author

Commented:
can we change License instead of static to pull from SQL?
I have a column named license in SQL

Author

Commented:
Sorry, also usage location is same as "c"
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Awesome. Just having lunch, no problem on both counts.
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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
}

Open in new window

Author

Commented:
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 *,
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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?

#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
}

Open in new window

Author

Commented:
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:EXCHANGEENTERPRISE",
                            "sn":  "Last"
                        },
    "PostalCode":  "8001",
    "PasswordNeverExpires":  true,
    "StreetAddress":  "5",
    "Path":  "OU=TestUser,OU=test,DC=test,DC=com",
    "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

Author

Commented:
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....

Author

Commented:
the UPN in O365 in this environment would be the EmailAddress from SQL
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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:
        [String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment')

Open in new window

And this:
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object { 
                    $_.Name -notin $ExcludeProperties -and 
                    $null -ne $_.Value -and
                    ($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
                }

Open in new window

Which should be working, if I haven't broken something.

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

Open in new window

If you're feeling really brave, and you're running PowerShell 5 you might create a partial copy of the O365 module for me? I have tools for this :)

Author

Commented:
I've been only making the changes, let me double check.
Sorry about that....
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
That's okay, it's a (very) long script. Need source control :)

Author

Commented:
where do you want me to insert (Get-Command New-MsolUser).ParameterSets.Item.Name

Author

Commented:
New-ADUser is fine!
Sorry about my mistake... :(

Author

Commented:
I'm not running 5 :(
I only have V3
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
(Get-Command New-MsolUser).ParameterSets.Item.Name

Open in new window

I just need the output from that (posted here) to update the script. As long as you have the O365 module installed it'll tell me what I need.

Author

Commented:
If i run it in shell, after connecting to MSOL, it says: Item
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Lovely, what a daft name. Thanks MS.

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
}

Open in new window

Author

Commented:
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

Author

Commented:
on only changing the new o365 i get the following:

ERROR: +             $o365UserInformation = $UserInformation | Select-Object *,
ERROR: +                                                       ~~~~~~~~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidOperation: (@{GivenName=Tes...already exists}:PSObject) [Select-Object], PSArgumentException
ERROR:     + FullyQualifiedErrorId : AlreadyExistingUserSpecifiedPropertyNoExpand,Microsoft.PowerShell.Commands.SelectObjectCommand
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.Administration.Automation.RequiredPropertyNotSetException,NewO365User
ERROR:

Author

Commented:
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: +                                                       ~~~~~~~~~~~~~~~~
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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

Open in new window

And:
function Get-CommandParameter {
    # .SYNOPSIS
    #   Get a set of parameters from a specific parameter set.
    # .DESCRIPTION
    #   Get a set of parameters from a specific parameter set.
    # .INPUTS
    #   System.String
    # .OUTPUTS
    #   System.Management.Automation.ParameterMetadata
    # .EXAMPLE
    #   Get-CommandParameter Set-ADUser -ParameterSetName Identity
    
    [OutputType([System.Management.Automation.ParameterMetadata])]
    param (
        # Retrieve parameters for the specified command.
        [Parameter(Mandatory = $true)]
        [String]$CommandName,

        # Limit results to parameters from the specified parameter set.
        [String]$ParameterSetName = '__AllParameterSets',

        # Return the results as a hashtable, using the parameter name as a key.
        [Switch]$AsHashtable
    )

    try {
        $hashtable = @{}

        $command = Get-Command -Name $CommandName -ErrorAction Stop
        $command.Parameters.Values | 
            Where-Object { $_.ParameterSets.Keys -contains $ParameterSetName } |
            ForEach-Object {
                if ($AsHashtable) {
                    $hashtable.Add($_.Name, $_)
                } else {
                    $_
                }
            }

        if ($AsHashtable) {
            $hashtable
        }
    } catch {
        throw
    }
}
Get-CommandParameter -CommandName New-MsolUser -ParameterSetName Item

Open in new window

Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Ohhh scratch that.. need to fix the other error first.
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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 $_
            }
        }
    }
}

Open in new window

Author

Commented:

Author

Commented:
should i run the second or not?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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 $_
            }
        }
    }
}

Open in new window

Author

Commented:
& BAM!!!

Author

Commented:
Now, I start the update O365 part ;)
I had it disabled ....

Author

Commented:
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=AM5PR0502CA0016,BackEndServer=db5pr0401mb2006.eurprd04.prod.outlook.com,RequestId=c4a4d02e-4441-4097-81db-fe0f0bc5a530,TimeStamp=4/7/2017 3:33:27 PM] [AuthZRequestId=bdb6b9d7-6d45-47db-82ee-4939c8254be1][FailureCategory=AuthZ-AuthorizationException] Fail to create a runspace because you have exceeded the maximum number of connections allowed : 3 for the policy party : MaxConcurrency. Please close existing runspace and try again.

Author

Commented:
I guess, we have to terminate the connection every time after the script....
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
LOL

Remove-PSSession at the end I reckon.

Author

Commented:
it asks for the id of pssession

Author

Commented:
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....

Author

Commented:
i did get-pssession | remove-pssession
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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 $_
    }
}

Open in new window

And:
if ($PSSession = ConnectO365) {
    $usersToProcess | NewO365User
    $usersToProcess | UpdateO365User

    Remove-PSSession -Session $PSSession
} else {
    # What would you like to do if the O365 part fails?
}

Open in new window

The code is supposed to be waiting for the mailbox to be created. Let's debug that.
function UpdateO365User {
    # .SYNOPSIS
    #   Update information held in Office 365.
    # .DESCRIPTION
    #   Update information held in Office 365.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None
    
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String[]]$Services
    )

    process {
        $ErrorActionPreference = 'Stop'
        try {
    		# Loop till the user account is created
            while (-not ($mailbox = Get-Mailbox -Identity $UserInformation.SamAccountName -ErrorAction SilentlyContinue)) {
                Write-Host "Waiting for mailbox"

                Start-Sleep -Seconds 15
            }

            if (-not $mailbox) {
                Write-Host "Yeah, that didn't work."
            }

            # These params can be moved down into the Switch statement if any differ
            $params = @{
                Identity                  = $UserInformation.SamAccountName
                TimeZone                  = 'W. Europe Standard Time'
                DateFormat                = 'dd.MM.yyyy'
                TimeFormat                = 'HH:mm'
                LocalizeDefaultFolderName = $true
            }
            switch ($UserInformation.c) {
                'CH' {
                    $params.Language = 'de-CH'
                }
                'DE' {
                    $params.Language = 'de-DE'
                }
            }
            Set-MailboxRegionalConfiguration @params

            $mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\calendar' -f $mailbox.Alias) -ErrorAction SilentlyContinue
            if (-not $mailboxFolder) {
                $mailboxFolder = Get-MailboxFolderPermission -Identity ('{0}:\kalender' -f $mailbox.Alias) -ErrorAction SilentlyContinue
            }
            if ($mailboxFolder) {
                Set-MailboxFolderPermission -Identity $mailbox.Identity -User Default -AccessRights Reviewer | Out-Null
                Add-MailboxFolderPermission -Identity $mailbox.Identity -User Calendar -AccessRights Owner | Out-Null
            }
            
            # Enable Services for User
            if ($UserInformation.LicenseAssignment -eq "reseller-account:ENTERPRISEPACK" -and $null -ne $Services) {
                Set-MsolUserLicense -UserprincipalName $UserInformation.mail -LicenseOptions $Services
            }
        } catch {
            $UserInformation.Status = 'Failed (O365)'
            $UserInformation.StatusMessage = $_.Exception.Message

            Write-Error -ErrorRecord $_
        }
    }
}

Open in new window

Author

Commented:
It's waiting!!!

Author

Commented:
Seems MS went home ;)
Still waiting, but it's a good sign!!!

Author

Commented:
Chris,
I have to leave, It's still showing that the mailbox is not ready
I will check later & keep you posted.

Thanks,
A
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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?

Author

Commented:
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?

Author

Commented:
Any thoughts, Chris?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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)?
#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
}

Open in new window

Author

Commented:
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?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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 :)

Author

Commented:
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?

Author

Commented:
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?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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).
# 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

Open in new window

One thing to debug when it's not going quite right, one reporting channel, one place to maintain state. These things make me happy :)

All that said, the actions are pretty well defined and you should feel free to run them in whatever manner you need :)
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
> 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).

Author

Commented:
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.Automation.PSModuleInfo" to type
ERROR: "System.Management.Automation.Runspaces.PSSession".
TestO365.ps1 (717, 28): ERROR: At Line: 717 char: 28
ERROR: +     Remove-PSSession -Session $PSSession
ERROR: +                               ~~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidArgument: (:) [Remove-PSSession], ParameterBindingException
ERROR:     + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.RemovePSSessionCommand
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.SmtpClient:SmtpClient) [Send-MailMessage], SmtpException
ERROR:     + FullyQualifiedErrorId : SmtpException,Microsoft.PowerShell.Commands.SendMailMessage
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'
      }
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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 $_
    }
}

Open in new window

Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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.

Author

Commented:
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

Author

Commented:
So, I changed it from samaccount name to mail & seems to find it correctly.
However, it's not setting the permissions for calendar...
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Ah yes, I see the mailbox locator for that is a bit wrong.
            $mailbox = Get-Mailbox -Identity $UserInformation.SamAccountName -ErrorAction SilentlyContinue

Open in new window

That should be mail, right?
            $mailbox = Get-Mailbox -Identity $UserInformation.mail -ErrorAction SilentlyContinue

Open in new window

Author

Commented:
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-MailboxFolderPermission], UserAlreadyExis...nEntryException
ERROR:     + FullyQualifiedErrorId : [Server=DB5PR0401MB2006,RequestId=3e5140fb-3837-450b-8142-30ffcb015571,TimeStamp=11.04.2017 13:27:34] [FailureCategory=Cmdlet-Us
ERROR:    erAlreadyExistsInPermissionEntryException] B957C514,Microsoft.Exchange.Management.StoreTasks.AddMailboxFolderPermission
ERROR:     + PSComputerName        : ps.outlook.com
ERROR:
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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 $_
                }
            }
        }
    }
}

Open in new window

Author

Commented:
this is what I did, but I get the above mentioned error?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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:
        $ErrorActionPreference = 'Stop'

Open in new window

In the last thread I knew exactly what would kill or or not so I didn't use it. This time around there's lots of command calls to go wrong.

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).

Author

Commented:
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-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
                        
                  }
            }

Author

Commented:
I see you are making it in $mailboxfolder...
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Same thing in this one, just a different concatenation style and I skip the variable assignment:
('{0}:\calendar' -f $mailbox.Alias)

Open in new window

It does appear to be finding it, but the right is already present so an error is thrown.
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
heh except I forgot to use the variable properly. Those lines need some work.

Author

Commented:
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?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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 $_
                }
            }
        }
    }
}

Open in new window

Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
> 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.

Author

Commented:
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" ?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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
                    }

Open in new window

Author

Commented:
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... :(

Author

Commented:
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-MailboxFolderPermission], UserAlreadyExis...nEntryException
ERROR:     + FullyQualifiedErrorId : [Server=DB5PR0401MB2006,RequestId=d02fcb4e-d00b-444d-987f-3cdd41f95ff8,TimeStamp=11.04.2017 14:11:13] [FailureCategory=Cmdlet-Us
ERROR:    erAlreadyExistsInPermissionEntryException] B957C514,Microsoft.Exchange.Management.StoreTasks.AddMailboxFolderPermission
ERROR:     + PSComputerName        : ps.outlook.com
ERROR:

I guess, it's because of the warning the SQL changes.... however the error still persists....
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
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.
Set-MailboxFolderPermission -Identity $folderPath -User Default -AccessRights Reviewer | Out-Null
Add-MailboxFolderPermission -Identity $folderPath -User Calendar -AccessRights Owner | Out-Null

Open in new window

Author

Commented:
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
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Services isn't working because the list is empty. I did not that when I introduced the variable. It doesn't feed from SQL (that I know of), so you'd have to manually set values.

The permissions on the calendars will work, if the commands are made right, it's so, so close.

Author

Commented:
That puts a smile on my face ;)
I also really want that it works this way, after so much work....
Let's see it for a while , otherwise we take the other option....

Author

Commented:
This is the variable I had in services earlier:

            $Services = New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE

Author

Commented:
shall i put $services above : #Enable Services for user?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Services, if it's a fixed list it just needs adding to the UpdateO365User function:
function UpdateO365User {
    # .SYNOPSIS
    #   Update information held in Office 365.
    # .DESCRIPTION
    #   Update information held in Office 365.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None
    
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        $Services = (New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE)
    )

    begin {
        $ErrorActionPreference = 'Stop'
    }

    process {
        if ($UserInformation.Action -eq 'update-mail') {
            $mailbox = Get-Mailbox -Identity $UserInformation.mail -ErrorAction SilentlyContinue
            if ($mailbox) {
                try {
                    # These params can be moved down into the Switch statement if any differ
                    $params = @{
                        Identity                  = $mailbox.Identity
                        TimeZone                  = 'W. Europe Standard Time'
                        DateFormat                = 'dd.MM.yyyy'
                        TimeFormat                = 'HH:mm'
                        LocalizeDefaultFolderName = $true
                    }
                    switch ($UserInformation.c) {
                        'CH' {
                            $params.Language = 'de-CH'
                        }
                        'DE' {
                            $params.Language = 'de-DE'
                        }
                    }
                    Set-MailboxRegionalConfiguration @params

                    $folderPath = '{0}:\calendar' -f $mailbox.Alias
                    $mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
                    if (-not $mailboxFolder) {
                        $mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
                    }
                    if ($mailboxFolder) {
                        Set-MailboxFolderPermission -Identity $folderPath -User Default -AccessRights Reviewer | Out-Null
                        Add-MailboxFolderPermission -Identity $folderPath -User Calendar -AccessRights Owner | Out-Null
                    }
                    
                    # Enable Services for User
                    if ($UserInformation.LicenseAssignment -eq "reseller-account:ENTERPRISEPACK" -and $null -ne $Services) {
                        Set-MsolUserLicense -UserprincipalName $UserInformation.mail -LicenseOptions $Services
                    }

                    # End of workflow
                    $UserInformation.Action = ''
                    $UserInformation.Status = 'OK'
                } catch {
                    $UserInformation.Status = 'Failed (O365)'
                    $UserInformation.StatusMessage = $_.Exception.Message

                    Write-Error -ErrorRecord $_
                }
            }
        }
    }
}

Open in new window

Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
That leaves debugging setting permissions. This has a couple of lines to start to debug it a bit. Just Write-Host. When this is debugged those might be changed to Write-Debug instead. Write-Host is a bit evil.
function UpdateO365User {
    # .SYNOPSIS
    #   Update information held in Office 365.
    # .DESCRIPTION
    #   Update information held in Office 365.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None
    
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        $Services = (New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE)
    )

    begin {
        $ErrorActionPreference = 'Stop'
    }

    process {
        if ($UserInformation.Action -eq 'update-mail') {
            $mailbox = Get-Mailbox -Identity $UserInformation.mail -ErrorAction SilentlyContinue
            if ($mailbox) {
                try {
                    # These params can be moved down into the Switch statement if any differ
                    $params = @{
                        Identity                  = $mailbox.Identity
                        TimeZone                  = 'W. Europe Standard Time'
                        DateFormat                = 'dd.MM.yyyy'
                        TimeFormat                = 'HH:mm'
                        LocalizeDefaultFolderName = $true
                    }
                    switch ($UserInformation.c) {
                        'CH' {
                            $params.Language = 'de-CH'
                        }
                        'DE' {
                            $params.Language = 'de-DE'
                        }
                    }
                    Set-MailboxRegionalConfiguration @params

                    $folderPath = '{0}:\calendar' -f $mailbox.Alias
                    $mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
                    if (-not $mailboxFolder) {
                        $folderPath = '{0}:\calendar' -f $mailbox.Alias
                        $mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
                    }
                    if ($mailboxFolder) {
                        Write-Host "Setting rights for $folderPath (Default: Reviewer)"

                        Set-MailboxFolderPermission -Identity $folderPath -User Default -AccessRights Reviewer | Out-Null

                        Write-Host "Adding rights for $folderPath (Calendar: Owner)"

                        Add-MailboxFolderPermission -Identity $folderPath -User Calendar -AccessRights Owner | Out-Null
                    }
                    
                    # Enable Services for User
                    if ($UserInformation.LicenseAssignment -eq "reseller-account:ENTERPRISEPACK" -and $null -ne $Services) {
                        Set-MsolUserLicense -UserPrincipalName $UserInformation.mail -LicenseOptions $Services
                    }

                    # End of workflow
                    $UserInformation.Action = ''
                    $UserInformation.Status = 'OK'
                } catch {
                    $UserInformation.Status = 'Failed (O365)'
                    $UserInformation.StatusMessage = $_.Exception.Message

                    Write-Error -ErrorRecord $_
                }
            }
        }
    }
}

Open in new window

Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Forgot to update the second version to Kalender.
function UpdateO365User {
    # .SYNOPSIS
    #   Update information held in Office 365.
    # .DESCRIPTION
    #   Update information held in Office 365.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None
    
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        $Services = (New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE)
    )

    begin {
        $ErrorActionPreference = 'Stop'
    }

    process {
        if ($UserInformation.Action -eq 'update-mail') {
            $mailbox = Get-Mailbox -Identity $UserInformation.mail -ErrorAction SilentlyContinue
            if ($mailbox) {
                try {
                    # These params can be moved down into the Switch statement if any differ
                    $params = @{
                        Identity                  = $mailbox.Identity
                        TimeZone                  = 'W. Europe Standard Time'
                        DateFormat                = 'dd.MM.yyyy'
                        TimeFormat                = 'HH:mm'
                        LocalizeDefaultFolderName = $true
                    }
                    switch ($UserInformation.c) {
                        'CH' {
                            $params.Language = 'de-CH'
                        }
                        'DE' {
                            $params.Language = 'de-DE'
                        }
                    }
                    Set-MailboxRegionalConfiguration @params

                    $folderPath = '{0}:\calendar' -f $mailbox.Alias
                    $mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
                    if (-not $mailboxFolder) {
                        $folderPath = '{0}:\kalender' -f $mailbox.Alias
                        $mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
                    }
                    if ($mailboxFolder) {
                        Write-Host "Setting rights for $folderPath (Default: Reviewer)"

                        Set-MailboxFolderPermission -Identity $folderPath -User Default -AccessRights Reviewer | Out-Null

                        Write-Host "Adding rights for $folderPath (Calendar: Owner)"

                        Add-MailboxFolderPermission -Identity $folderPath -User Calendar -AccessRights Owner | Out-Null
                    }
                    
                    # Enable Services for User
                    if ($UserInformation.LicenseAssignment -eq "reseller-account:ENTERPRISEPACK" -and $null -ne $Services) {
                        Set-MsolUserLicense -UserPrincipalName $UserInformation.mail -LicenseOptions $Services
                    }

                    # End of workflow
                    $UserInformation.Action = ''
                    $UserInformation.Status = 'OK'
                } catch {
                    $UserInformation.Status = 'Failed (O365)'
                    $UserInformation.StatusMessage = $_.Exception.Message

                    Write-Error -ErrorRecord $_
                }
            }
        }
    }
}

Open in new window

Author

Commented:
Thanks, Services worked fine!
Calendar is still having issues...
This was the output:

Setting rights for test.now:\kalender (Default: Reviewer)
Adding rights for test.now:\kalender (Calendar: Owner)

But the rights are still not propagated....

Author

Commented:
Chris,
Check this out:

PS C:\Users\sysadm> Get-MailboxFolderPermission -Identity test.last

FolderName           User                 AccessRights
----------           ----                 ------------
Oberste Ebene des... Default              {Reviewer}
Oberste Ebene des... Anonymous            {None}
Oberste Ebene des... Calendar             {Owner}


PS C:\Users\sysadm> Get-MailboxFolderPermission -Identity test.now

FolderName           User                 AccessRights
----------           ----                 ------------
Oberste Ebene des... Default              {Reviewer}
Oberste Ebene des... Anonymous            {None}
Oberste Ebene des... Calendar             {Owner}


PS C:\Users\sysadm> Get-MailboxFolderPermission -Identity test.now:\Kalender

FolderName           User                 AccessRights
----------           ----                 ------------
Kalender             Default              {Reviewer}
Kalender             Anonymous            {None}
Kalender             Calendar             {Owner}


PS C:\Users\sysadm> Get-MailboxFolderPermission -Identity test.last:\Kalender

FolderName           User                 AccessRights
----------           ----                 ------------
Kalender             Default              {AvailabilityOnly}
Kalender             Anonymous            {None}
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Still throwing errors?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
I don't understand what I'm looking at. Which one is right / wrong?

Author

Commented:
test.now is right, test.last is wrong.

Let me do a user from scratch & check....

Author

Commented:
Awesome!
On the new user it works!!!

Author

Commented:
i would like to keep it open for a while, will test other things & let you know.
Is that OK?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Of course, please do.

Author

Commented:
Sorry,
But DefaultFolderNameMatching user language is not getting propagated:

When I check, it shows the value as False
Can you please see....
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
How is that set? LocalizeDefaultFolderName?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
How is that value set?

Author

Commented:
we have set in params in update O365
LocalizeDefaultFolderName = $true
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
It needs this bit testing independently.
                    $params = @{
                        Identity                  = $mailbox.Identity
                        TimeZone                  = 'W. Europe Standard Time'
                        DateFormat                = 'dd.MM.yyyy'
                        TimeFormat                = 'HH:mm'
                        LocalizeDefaultFolderName = $true
                    }
                    switch ($UserInformation.c) {
                        'CH' {
                            $params.Language = 'de-CH'
                        }
                        'DE' {
                            $params.Language = 'de-DE'
                        }
                    }
                    Set-MailboxRegionalConfiguration @params

Open in new window

All I did was change how the parameters were fed. I know little or nothing about the command itself, everything here looks sane to me as long as $mailbox.Identity works. $mailbox.Identity comes from Get-Mailbox.

Author

Commented:
Chris,
It still shows False, however the folders are named correctly, so I guess we let it be for the moment.

I'm sorry, but I want to have this command built in:
get-mailbox -identity $UserInformation.mail | Set-Mailbox -LitigationHoldEnabled $true -LitigationHoldDuration 2555 -RetentionHoldEnabled $true

How would you place it in Update O365 part?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Yep, that makes sense since it requires the mailbox to exist.

It can slot in anywhere really, for example here it's just after permissions are handled.
function UpdateO365User {
    # .SYNOPSIS
    #   Update information held in Office 365.
    # .DESCRIPTION
    #   Update information held in Office 365.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None
    
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        $Services = (New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE)
    )

    begin {
        $ErrorActionPreference = 'Stop'
    }

    process {
        if ($UserInformation.Action -eq 'update-mail') {
            $mailbox = Get-Mailbox -Identity $UserInformation.mail -ErrorAction SilentlyContinue
            if ($mailbox) {
                try {
                    # These params can be moved down into the Switch statement if any differ
                    $params = @{
                        Identity                  = $mailbox.Identity
                        TimeZone                  = 'W. Europe Standard Time'
                        DateFormat                = 'dd.MM.yyyy'
                        TimeFormat                = 'HH:mm'
                        LocalizeDefaultFolderName = $true
                    }
                    switch ($UserInformation.c) {
                        'CH' {
                            $params.Language = 'de-CH'
                        }
                        'DE' {
                            $params.Language = 'de-DE'
                        }
                    }
                    Set-MailboxRegionalConfiguration @params

                    $folderPath = '{0}:\calendar' -f $mailbox.Alias
                    $mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
                    if (-not $mailboxFolder) {
                        $folderPath = '{0}:\kalender' -f $mailbox.Alias
                        $mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
                    }
                    if ($mailboxFolder) {
                        Write-Host "Setting rights for $folderPath (Default: Reviewer)"

                        Set-MailboxFolderPermission -Identity $folderPath -User Default -AccessRights Reviewer | Out-Null

                        Write-Host "Adding rights for $folderPath (Calendar: Owner)"

                        Add-MailboxFolderPermission -Identity $folderPath -User Calendar -AccessRights Owner | Out-Null
                    }
                    
                    $mailbox | Set-Mailbox -LitigationHoldEnabled $true -LitigationHoldDuration 2555 -RetentionHoldEnabled $true

                    # Enable Services for User
                    if ($UserInformation.LicenseAssignment -eq "reseller-account:ENTERPRISEPACK" -and $null -ne $Services) {
                        Set-MsolUserLicense -UserPrincipalName $UserInformation.mail -LicenseOptions $Services
                    }

                    # End of workflow
                    $UserInformation.Action = ''
                    $UserInformation.Status = 'OK'
                } catch {
                    $UserInformation.Status = 'Failed (O365)'
                    $UserInformation.StatusMessage = $_.Exception.Message

                    Write-Error -ErrorRecord $_
                }
            }
        }
    }
}

Open in new window

Author

Commented:
I got it working!
Getting there...slowly but surely ;)

Author

Commented:
What do you think about this:
We are running function UpdateO365User only when the flag is set to update-mail

New-ADUser runs when Action = create & then sets Action = create-mail
Set-ADUser runs when Action = update  & then sets Action = ' '
NewO365 runs when Action = create-mail & then sets Action = update-mail
UpdateO365 runs when Action = update-mail & then sets Action = ' '

Now, if we consider the value of Action = Update & are expecting changes to be made in O365 also e.g. we change the Telephone Number, then I don't see anyhow it being pushed to O365....

Please confirm that my understanding is correct?

What I would like to see is that if the update switch is triggered, it should also impact the O365 users, the question will be how to trigger it for Existing users, we can't run New-MsolUser....

What would make more sense, to check in NewO365User function if the user exists, if exists, then update or would you go in UpdateO365User function???
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
It is correct.

I would increase how granular the workflow steps are to handle that kind of event.

That is, the UpdateO365 user function is, at the moment, more of a post-create fix-up task. That can stay in the creation workflow, perhaps with a more meaningful name.

The update workflow might want additional steps though, a step to fix up organisational information. For example:

Create:

NewADUser -> Action: new-mailbox
NewO365Mailbox -> Action: update-newmailbox
UpdateO365NewMailbox -> Action: ' '

Update:

SetADUser -> Action: updateO365Mailbox
UpdateO365Mailbox -> Action: ' '

There are some aspects of this which are going to groan a bit as it gets more complex, for instance the property renaming we do to cater for the NewO365User command is a bit messy. If there's a need to handle user-level updates there too something a bit more flexible might be introduced.

Author

Commented:
I agree!
What do you think if we take this approach:
In NewO365Mailbox, we create condition to check if the mailbox exists , if it exists we run set-mailbox instead of new-mailbox.

That way we will not have to rewrite the whole stuff & can make use of existing work?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
That works, yes. It becomes more of an UpdateMailbox instead of creating everything from scratch.

Author

Commented:
Great,
Let me try to write the condition, if I'm not able to, then I comeback....

Author

Commented:
Does this make sense:

try
                  {
                        # Use as many named parameters as possible
                        foreach ($property in $propertiesToSet)
                        {
                              if ($validParameters.Contains($property.Name))
                              {
                                    $params.($property.Name) = $property.Value
                              }
                        }
                        
                        # Check if the User Exists
                        Get-MsolUser -UserPrincipalName $UserInformation.UserPrincipalName -ErrorAction SilentlyContinue -ErrorVariable errorVariable
                        If (errorVariable -ne $null)
                        {
                              Write-Host "Creating O365 user with the following information"
                              Write-Host ($params | ConvertTo-Json -Depth 3)
                              New-MsolUser @params -ErrorAction Stop
                              $UserInformation.Action = 'update-mail'
                              $UserInformation.Status = 'Not complete'
                        }
                        else {
                              Set-MsolUser @params -ErrorAction Stop
                        }
                  }
                  catch
                  {
                        $UserInformation.Status = 'Failed (Create O365)'
                        $UserInformation.StatusMessage = $_.Exception.Message
                        
                        Write-Error -ErrorRecord $_
                  }
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Getting there, tweaked a bit.

The tricky part is making sure we're passing the right sets of parameters to each command (New vs Set). This doesn't cater for that yet, only for the parameters used by New-MsolUser.
try
{
    # Use as many named parameters as possible
    foreach ($property in $propertiesToSet)
    {
        if ($validParameters.Contains($property.Name))
        {
            $params.($property.Name) = $property.Value
        }
    }

    # Check if the User Exists
    $msolUser = Get-MsolUser -UserPrincipalName $UserInformation.mail -ErrorAction SilentlyContinue
    if ($msolUser) {
        try {
            Set-MsolUser @params -ErrorAction Stop

            $UserInformation.Action = 'update-mail'
            $UserInformation.Status = 'Not complete'                
        } catch {
            $UserInformation.Status = 'Failed (Update O365)'
            $UserInformation.StatusMessage = $_.Exception.Message
        }
    } else {
        Write-Host "Creating O365 user with the following information"
        Write-Host ($params | ConvertTo-Json -Depth 3)

        try {
            New-MsolUser @params -ErrorAction Stop

            $UserInformation.Action = 'update-mail'
            $UserInformation.Status = 'Not complete'
        } catch {
            $UserInformation.Status = 'Failed (Create O365)'
            $UserInformation.StatusMessage = $_.Exception.Message
        }
    }
}

Open in new window

Author

Commented:
I'm getting error, try block is missing it's catch....
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Oops :)
try
{
    # Use as many named parameters as possible
    foreach ($property in $propertiesToSet)
    {
        if ($validParameters.Contains($property.Name))
        {
            $params.($property.Name) = $property.Value
        }
    }

    # Check if the User Exists
    $msolUser = Get-MsolUser -UserPrincipalName $UserInformation.mail -ErrorAction SilentlyContinue
    if ($msolUser) {
        try {
            Set-MsolUser @params -ErrorAction Stop

            $UserInformation.Action = 'update-mail'
            $UserInformation.Status = 'Not complete'                
        } catch {
            $UserInformation.Status = 'Failed (Update O365)'
            $UserInformation.StatusMessage = $_.Exception.Message
        }
    } else {
        Write-Host "Creating O365 user with the following information"
        Write-Host ($params | ConvertTo-Json -Depth 3)

        try {
            New-MsolUser @params -ErrorAction Stop

            $UserInformation.Action = 'update-mail'
            $UserInformation.Status = 'Not complete'
        } catch {
            $UserInformation.Status = 'Failed (Create O365)'
            $UserInformation.StatusMessage = $_.Exception.Message
        }
    }
} catch {
    # Might be able to remove this soon, it captures things we didn't expect to go wrong.
    throw
}

Open in new window

Author

Commented:
Don't you think, in the SetADUser function we have to put $UserInformation.Action = 'create-mail' ?
That's why whenever the update value in Action is set, it will go to SetADUser function & then it will go to NewO365User function.....
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Yep :)

Author

Commented:
Somehow, the O365 is not updating....
I will have a thorough look tomorrow, seems we missing some thing....
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Almost certainly, I'd be surprised if the parameters for New and Set were exactly the same for instance.

Author

Commented:
I think we should not set any value for Action in SetADUser.

We should have the condition in NewO365 to trigger if the value is "UPDATE" or "Create-Email"

Whenever we run SetADUser, we MUST run NewO365 because we want to update the O365 also.
Otherwise O365 won't be updated till the next run.

So, either NewADUser or SetADUser both should trigger NewO365 & when SetADUser is not touching Action, it will automatically go to NewO365.

Author

Commented:
like
if ($UserInformation.Action -eq 'create-mail' -or 'update')

?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Either this:
if ($UserInformation.Action -in 'create-mail', 'update')

Open in new window

Or this:
if ('create-mail', 'update' -contains $UserInformation.Action)

Open in new window

Or this:
if ($UserInformation.Action -eq 'create-mail' -or $UserInformation.Action -eq 'update')

Open in new window

Doesn't matter which, I tend to use the first one.

Author

Commented:
This is the error which is not allowing the update:

Cannot convert 'System.String' to the type 'System.Nullable`1[System.Boolean]' required by parameter 'PasswordNeverExpires'.

Author

Commented:
Morning Chris,
In the NewO365User we have in the beginning of if statement in Process block ForceChangePassword set to True.
My guess is that is causing the above error.

Can we in the block of update remove it somehow?

Author

Commented:
I moved the @parms block inside the else statement to stop impacting update of O365 user.

However there is a logical issue.... (or at least I think)
SetADUser marks Action = Create-Mail

I have this condition in UpdateO365: if ($UserInformation.Action -eq 'create-mail' -or $UserInformation.Action -eq 'update')

However, it seems not to go there?
Also, in the SQL the value in Action is set to create-mail even though I have marked the value to be set to Blank after update is initiated in UpdateO365 function.....

Author

Commented:
Another update....
If in UpdateO365 I don't add action -eq update, it still correctly goes to the if block , finds the user & executes the branch.

This doesn't work if I move the @parms block inside else block.

But if I keep the @params block at original place, then the functions kicks off, however the error is thrown: Cannot convert 'System.String' to the type 'System.Nullable`1[System.Boolean]' required by parameter 'PasswordNeverExpires'.

How can we make sure that the password error doesn't happen when set-msoluser is executed?
I guess it will work if this error is removed....
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Please can you post your updated code?

Author

Commented:
function NewO365User
{
	# .SYNOPSIS
	#   Create a user in Office 365.
	# .DESCRIPTION
	#   Create a user in Office 365.
	# .INPUTS
	#   System.Management.Automation.PSObject
	#   System.String
	# .OUTPUTS
	#   None
	
	param (
		[Parameter(ValueFromPipeline = $true)]
		[PSObject]$UserInformation
	)
	
	begin
	{
		$validParameters = Get-CommandParameter New-MsolUser -ParameterSetName "AddUser__0" -AsHashtable
	}
	
	process
	{
		if ($UserInformation.Action -eq 'create-mail')
		{
			$params = @{
				ForceChangePassword = $true
			}
			
			
			# Rewrite the property set to support the O365 commands
			$o365UserInformation = $UserInformation | Select-Object -ExcludeProperty UserPrincipalName -Property *,
																	@{ Name = 'FirstName'; Expression = { $_.GivenName } },
																	@{ Name = 'LastName'; Expression = { $_.sn } },
																	@{ Name = 'UserPrincipalName'; Expression = { $_.mail } },
																	@{ Name = 'Country'; Expression = { $_.c } },
																	@{ Name = 'City'; Expression = { $_.l } },
																	@{ Name = 'State'; Expression = { $_.st } },
																	@{ Name = 'Office'; Expression = { $_.physicalDeliveryOfficeName } },
																	@{ Name = 'PhoneNumber'; Expression = { $_.telephoneNumber } },
																	@{ Name = 'MobilePhone'; Expression = { $_.mobile } },
																	@{ Name = 'UsageLocation'; Expression = { $_.c } }
			
			# Create a list of properties (from UserInformation) which will be written to the new user
			$propertiesToSet = $o365UserInformation.PSObject.Properties |
			Where-Object {
				$null -ne $_.Value -and
				($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
			}
			
			try
			{
				# Use as many named parameters as possible
				foreach ($property in $propertiesToSet)
				{
					if ($validParameters.Contains($property.Name))
					{
						$params.($property.Name) = $property.Value
					}
				}
				# Check if the User Exists
				$msolUser = Get-MsolUser -UserPrincipalName $UserInformation.mail -ErrorAction SilentlyContinue
				if ($msolUser)
				{
					try
					{
						Write-Host "Update MSOLUser initiated"
						Set-MsolUser @params -ErrorAction Stop
						
						$UserInformation.Action = ' '
						$UserInformation.Status = 'Not complete'
					}
					catch
					{
						$UserInformation.Status = 'Failed (Update O365)'
						$UserInformation.StatusMessage = $_.Exception.Message
					}
				}
				else
				{
					
					Write-Host "Create MSOLUser initiated"
					Write-Host "Creating O365 user with the following information"
					Write-Host ($params | ConvertTo-Json -Depth 3)
					
					try
					{
						New-MsolUser @params -ErrorAction Stop
						
						$UserInformation.Action = 'update-mail'
						$UserInformation.Status = 'Not complete'
					}
					catch
					{
						$UserInformation.Status = 'Failed (Create O365)'
						$UserInformation.StatusMessage = $_.Exception.Message
					}
				}
			}
			catch
			{
				# Might be able to remove this soon, it captures things we didn't expect to go wrong
			}
		}
	}
}

Open in new window

Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Excluded PasswordNeverExpires (in the large Select-Object statement). Allowed it to discover parameters for Set-MsolUser.

There's likely to be a bug. I've guessed at the parameter set name used by Set-MsolUser. Remember we went through this for New-MsolUser? Same thing again, need to make sure we have the right parameter set name or it won't pass anything properly.
function NewO365User
{
	# .SYNOPSIS
	#   Create a user in Office 365.
	# .DESCRIPTION
	#   Create a user in Office 365.
	# .INPUTS
	#   System.Management.Automation.PSObject
	#   System.String
	# .OUTPUTS
	#   None
	
	param (
		[Parameter(ValueFromPipeline = $true)]
		[PSObject]$UserInformation
	)
	
	begin
	{
		$validSetUserParameters = Get-CommandParameter Set-MsolUser -ParameterSetName "SetUser__0" -AsHashtable
		$validNewUserParameters = Get-CommandParameter New-MsolUser -ParameterSetName "AddUser__0" -AsHashtable
	}
	
	process
	{
		if ($UserInformation.Action -eq 'create-mail')
		{			
			# Rewrite the property set to support the O365 commands
			$o365UserInformation = $UserInformation | Select-Object -ExcludeProperty UserPrincipalName, PasswordNeverExpires -Property *,
                @{ Name = 'FirstName'; Expression = { $_.GivenName } },
                @{ Name = 'LastName'; Expression = { $_.sn } },
                @{ Name = 'UserPrincipalName'; Expression = { $_.mail } },
                @{ Name = 'Country'; Expression = { $_.c } },
                @{ Name = 'City'; Expression = { $_.l } },
                @{ Name = 'State'; Expression = { $_.st } },
                @{ Name = 'Office'; Expression = { $_.physicalDeliveryOfficeName } },
                @{ Name = 'PhoneNumber'; Expression = { $_.telephoneNumber } },
                @{ Name = 'MobilePhone'; Expression = { $_.mobile } },
                @{ Name = 'UsageLocation'; Expression = { $_.c } }
			
			# Create a list of properties (from UserInformation) which will be written to the new user
			$propertiesToSet = $o365UserInformation.PSObject.Properties |
			Where-Object {
				$null -ne $_.Value -and
				($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
			}
			
            $params = @{}
			try
			{
				# Check if the User Exists
				$msolUser = Get-MsolUser -UserPrincipalName $UserInformation.mail -ErrorAction SilentlyContinue
				if ($msolUser)
				{
                    # Use as many named parameters as possible
                    foreach ($property in $propertiesToSet)
                    {
                        if ($validSetUserParameters.Contains($property.Name))
                        {
                            $params.($property.Name) = $property.Value
                        }
                    }

					try
					{
						Write-Host "Update MSOLUser initiated"
						Set-MsolUser @params -ErrorAction Stop
						
						$UserInformation.Action = ' '
						$UserInformation.Status = 'Not complete'
					}
					catch
					{
						$UserInformation.Status = 'Failed (Update O365)'
						$UserInformation.StatusMessage = $_.Exception.Message
					}
				}
				else
				{
                    $params.ForceChangePassword = $true

                    # Use as many named parameters as possible
                    foreach ($property in $propertiesToSet)
                    {
                        if ($validNewUserParameters.Contains($property.Name))
                        {
                            $params.($property.Name) = $property.Value
                        }
                    }

					Write-Host "Create MSOLUser initiated"
					Write-Host "Creating O365 user with the following information"
					Write-Host ($params | ConvertTo-Json -Depth 3)
					
					try
					{
						New-MsolUser @params -ErrorAction Stop
						
						$UserInformation.Action = 'update-mail'
						$UserInformation.Status = 'Not complete'
					}
					catch
					{
						$UserInformation.Status = 'Failed (Create O365)'
						$UserInformation.StatusMessage = $_.Exception.Message
					}
				}
			}
			catch
			{
				# Might be able to remove this soon, it captures things we didn't expect to go wrong
                throw
			}
		}
	}
}

Open in new window

Author

Commented:
Progress!!
It updated the changed value!
However, if I delete an existing value, like I had Mobile Phone earlier & in the updated it was blank, it didn't remove....
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Ah yes, so we explicitly suppress blank values. That means it can't (at the moment) clear values out.

With the AD module there's very specific syntax to use when an attribute is cleared (I think anyway, can't test any more). I've no idea if this is the case or not with Msol.

Please can you let me know if this kind of command works:
Set-MsolUser -Identity someone@domain.com -Mobile ''

Open in new window

If this works, we can stop is killing blank values.

Author

Commented:
Set-MsolUser -Identity someone@domain.com -MobilePhone ' '

Works!
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
In that case, let's try letting it have blank values. The filter was applied when the propertiesToSet variable was created, it's been removed from the snippet below.
function NewO365User
{
	# .SYNOPSIS
	#   Create a user in Office 365.
	# .DESCRIPTION
	#   Create a user in Office 365.
	# .INPUTS
	#   System.Management.Automation.PSObject
	#   System.String
	# .OUTPUTS
	#   None
	
	param (
		[Parameter(ValueFromPipeline = $true)]
		[PSObject]$UserInformation
	)
	
	begin
	{
		$validSetUserParameters = Get-CommandParameter Set-MsolUser -ParameterSetName "AddUser__0" -AsHashtable
		$validNewUserParameters = Get-CommandParameter New-MsolUser -ParameterSetName "AddUser__0" -AsHashtable
	}
	
	process
	{
		if ($UserInformation.Action -eq 'create-mail')
		{			
			# Rewrite the property set to support the O365 commands
			$o365UserInformation = $UserInformation | Select-Object -ExcludeProperty UserPrincipalName, PasswordNeverExpires -Property *,
                @{ Name = 'FirstName'; Expression = { $_.GivenName } },
                @{ Name = 'LastName'; Expression = { $_.sn } },
                @{ Name = 'UserPrincipalName'; Expression = { $_.mail } },
                @{ Name = 'Country'; Expression = { $_.c } },
                @{ Name = 'City'; Expression = { $_.l } },
                @{ Name = 'State'; Expression = { $_.st } },
                @{ Name = 'Office'; Expression = { $_.physicalDeliveryOfficeName } },
                @{ Name = 'PhoneNumber'; Expression = { $_.telephoneNumber } },
                @{ Name = 'MobilePhone'; Expression = { $_.mobile } },
                @{ Name = 'UsageLocation'; Expression = { $_.c } }
			
			# Create a list of properties (from UserInformation) which will be written to the new user
			$propertiesToSet = $o365UserInformation.PSObject.Properties
			
            $params = @{}
			try
			{
				# Check if the User Exists
				$msolUser = Get-MsolUser -UserPrincipalName $UserInformation.mail -ErrorAction SilentlyContinue
				if ($msolUser)
				{
                    # Use as many named parameters as possible
                    foreach ($property in $propertiesToSet)
                    {
                        if ($validSetUserParameters.Contains($property.Name))
                        {
                            $params.($property.Name) = $property.Value
                        }
                    }

					try
					{
						Write-Host "Update MSOLUser initiated"
						Set-MsolUser @params -ErrorAction Stop
						
						$UserInformation.Action = ' '
						$UserInformation.Status = 'Not complete'
					}
					catch
					{
						$UserInformation.Status = 'Failed (Update O365)'
						$UserInformation.StatusMessage = $_.Exception.Message
					}
				}
				else
				{
                    $params.ForceChangePassword = $true

                    # Use as many named parameters as possible
                    foreach ($property in $propertiesToSet)
                    {
                        if ($validNewUserParameters.Contains($property.Name))
                        {
                            $params.($property.Name) = $property.Value
                        }
                    }

					Write-Host "Create MSOLUser initiated"
					Write-Host "Creating O365 user with the following information"
					Write-Host ($params | ConvertTo-Json -Depth 3)
					
					try
					{
						New-MsolUser @params -ErrorAction Stop
						
						$UserInformation.Action = 'update-mail'
						$UserInformation.Status = 'Not complete'
					}
					catch
					{
						$UserInformation.Status = 'Failed (Create O365)'
						$UserInformation.StatusMessage = $_.Exception.Message
					}
				}
			}
			catch
			{
				# Might be able to remove this soon, it captures things we didn't expect to go wrong
                throw
			}
		}
	}
}

Open in new window

Author

Commented:
It works for UpdateO365!
Now, in the SetADUser it just leaves the values there....?
Shall we try to see if it works in there also removing the check?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
SetADUser, I think it needs a completely different approach to handling empty values. However, it's worth a try, the limitation may only apply to objectAttributes, those definitely need a different handler.

Anyway, give it a try, see what happens. Same change to propertiesToSet.

Author

Commented:
I tried, but the SetADUser needs the value:
It throws error: Cannot validate argument on parameter 'Replace'. The argument is null or an element of the argument collection contains a null value.
:(

Author

Commented:
Chris,
How about this approach: https://www.experts-exchange.com/questions/28577073/PowerShell-Script-for-Importing-Data-from-CSV-into-Active-Directory.html

If we create Dynamic, then we don't have to worry at any stage?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Figures.

They need to feed the Clear parameter. This attempts to wire that up.
function SetADUser {
    # .SYNOPSIS
    #   Implements the update action.
    # .DESCRIPTION
    #   SetADUser dynamically binds parameters for the Set-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment', 'SamAccountName')
    )

    begin {
        $validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable
    }

    process {
        if ($UserInformation.Action -eq 'update') {
            $params = @{
                Identity = $_.SamAccountName
            }
            $clear = @()
            $replace = @{}

            # Create a list of properties (from UserInformation) which will be set on the existing user
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object Name -notin $ExcludeProperties

            if ($UserInformation.Action -eq 'update') {
                # Use as many named parameters as possible
                foreach ($property in $propertiesToSet) {
                    if ($validParameters.Contains($property.Name)) {
                        $params.($property.Name) = $property.Value
                    } else {
                        if ($_.Value -is [String] -and $_.Value.Trim() -eq '') {
                            $clear += $property.Name
                        } else {
                            $replace.Add($property.Name, $property.Value)
                        }
                    }
                }
                
                # Load everything else into OtherAttributes
                if ($clear.Count -gt 0) {
                    $params.Clear = $clear
                }
                if ($replace.Count -gt 0) {
                    $params.Replace = $replace
                }

                Write-Host "Updating user with the following information"
                Write-Host ($params | ConvertTo-Json -Depth 3)

                try {
                    Set-ADUser @params -ErrorAction Stop
                    # End of workflow
                    $UserInformation.Action = ''
                    $UserInformation.Status = 'OK'
                } catch {
                    $UserInformation.Status = 'Failed (Update)'
                    $UserInformation.StatusMessage = $_.Exception.Message

                    Write-Error -ErrorRecord $_
                }
            }
        }
    }
}

Open in new window

Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
> How about this approach

That's what it's doing already. This just ramps the dynamic nature up a level or two :)

Author

Commented:
Oh Ok, I made the changes, but still the same error
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Please can you post the output that shows the parameters it's pushing?

Author

Commented:
Updating user with the following information
{
    "PostalCode":  "8001",
    "Enabled":  true,
    "Replace":  {
                    "st":  "",
                    "mobile":  "",
                    "co":  "756",
                    "mail":  "test.now1@test.com",
                    "l":  "",
                    "telephoneNumber":  "",
                    "c":  "CH",
                    "physicalDeliveryOfficeName":  "2344",
                    "sn":  "Now1"
                },
    "userPrincipalName":  "ten@test.com",
    "GivenName":  "G ",
    "PasswordNeverExpires":  true,
    "Identity":  "ten",
    "StreetAddress":  "",
    "Title":  "Manager",
    "Department":  "Team A",
    "DisplayName":  "Test Now1"
}
ERROR: SetADUser : replace
TestO365.ps1 (768, 19): ERROR: At Line: 768 char: 19
ERROR: + $usersToProcess | SetADUser
ERROR: +                   ~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidOperation: (ten:ADUser) [Write-Error], ADInvalidOperationException
ERROR:     + FullyQualifiedErrorId : ActiveDirectoryServer:0,SetADUser
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Sorry, silly mistake. Fixed here, wrong variable name used.
function SetADUser {
    # .SYNOPSIS
    #   Implements the update action.
    # .DESCRIPTION
    #   SetADUser dynamically binds parameters for the Set-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment', 'SamAccountName')
    )

    begin {
        $validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable
    }

    process {
        if ($UserInformation.Action -eq 'update') {
            $params = @{
                Identity = $_.SamAccountName
            }
            $clear = @()
            $replace = @{}

            # Create a list of properties (from UserInformation) which will be set on the existing user
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object Name -notin $ExcludeProperties

            if ($UserInformation.Action -eq 'update') {
                # Use as many named parameters as possible
                foreach ($property in $propertiesToSet) {
                    if ($validParameters.Contains($property.Name)) {
                        $params.($property.Name) = $property.Value
                    } else {
                        if ($property.Value.ToString().Trim() -eq '') {
                            $clear += $property.Name
                        } else {
                            $replace.Add($property.Name, $property.Value)
                        }
                    }
                }
                
                # Load everything else into OtherAttributes
                if ($clear.Count -gt 0) {
                    $params.Clear = $clear
                }
                if ($replace.Count -gt 0) {
                    $params.Replace = $replace
                }

                Write-Host "Updating user with the following information"
                Write-Host ($params | ConvertTo-Json -Depth 3)

                try {
                    Set-ADUser @params -ErrorAction Stop
                    # End of workflow
                    $UserInformation.Action = ''
                    $UserInformation.Status = 'OK'
                } catch {
                    $UserInformation.Status = 'Failed (Update)'
                    $UserInformation.StatusMessage = $_.Exception.Message

                    Write-Error -ErrorRecord $_
                }
            }
        }
    }
}

Open in new window

Author

Commented:
still the same error?

Updating user with the following information
{
    "PostalCode":  "8001",
    "Enabled":  true,
    "Clear":  [
                  "l",
                  "st",
                  "telephoneNumber",
                  "mobile"
              ],
    "Replace":  {
                    "c":  "CH",
                    "physicalDeliveryOfficeName":  "2344",
                    "co":  "756",
                    "mail":  "test.now1@test.com",
                    "sn":  "Now1"
                },
    "userPrincipalName":  "ten@test.com",
    "GivenName":  "G",
    "PasswordNeverExpires":  true,
    "Identity":  "ten",
    "StreetAddress":  "",
    "Title":  "Manager",
    "Department":  "Team A",
    "DisplayName":  "Test Now1"
}
ERROR: SetADUser : replace
TestO365.ps1 (768, 19): ERROR: At Line: 768 char: 19
ERROR: + $usersToProcess | SetADUser
ERROR: +                   ~~~~~~~~~
ERROR:     + CategoryInfo          : InvalidOperation: (ten:ADUser) [Write-Error], ADInvalidOperationException
ERROR:     + FullyQualifiedErrorId : ActiveDirectoryServer:0,SetADUser
ERROR:
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
I reckon it's falling on streetAddress.

Please can you let me know if this works?
Set-ADUser someone -StreetAddress ""

Open in new window

If not, we'll have to add more to the Clear parameter.

Author

Commented:
yes it works
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Damn really? That's rather unfortunate because I can't see anything else wrong with it. Dammit, that takes us back to trimming it down until the offending attribute is identified. Perhaps start with the Clear operation since we just added that in.
Set-ADUser someone -Clear "l", "st", "telephoneNumber", "mobile"

Open in new window

Author

Commented:
so i removed all attributes & ran the script
still same error.
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
What do you mean by removed all attributes?

Author

Commented:
Sorry,
I meant I removed all attributes of the user in AD, street, phone, mail & all & then ran the script & it fails.
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Yeah, there's a problem with the command, not a problem with the user.

From the error it looks like it's in the replace block, but since Clear is the thing we added it makes sense to test that one first.

Author

Commented:
So what should we do?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Build the command up to find the error.

Start with the new stuff, Clear.
Set-ADUser someone -Clear "l", "st", "telephoneNumber", "mobile"

Open in new window

If that passes we'll add the properties we're filling before merging Replace back in.

Author

Commented:
shall I run this in a new shell or incorporate in the script?
I have already run it in the shell & after that when I run the script , it fails....
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
New shell, just a one-off to isolate the error. Then we'll add stuff to make sure the error doesn't occur in the real thing.

Author

Commented:
ok, give me 2 minutes..
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
If the first works, this is the second iteration:
Set-ADUser -Identity "ten" -PostalCode "8001" -Enabled $true -Clear "l", "st", "telephoneNumber", "mobile" -userPrincipalname "ten@test.com" -GivenName "G" -PasswordNeverExpires $true -StreetAddress "" -Title "Manager" -Department "Team A" -Displayname "Test Now1"

Open in new window

This has the other thing that changed, streetAddress is cleared using the parameter.
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
And this is the third iteration, this one is expected to fail. If it doesn't fail it will be incredibly annoying.
Set-ADUser -Identity "ten" -PostalCode "8001" -Enabled $true -Clear "l", "st", "telephoneNumber", "mobile" -userPrincipalname "ten@test.com" -GivenName "G" -PasswordNeverExpires $true -StreetAddress "" -Title "Manager" -Department "Team A" -Displayname "Test Now1" -Replace @{
    "c" = "CH"
    "physicalDeliveryOfficeName" = "2344"
    "co" = "756"
    "mail" = "test.now1@test.com"
    "sn" = "Now1"
}

Open in new window

Author

Commented:
so the first part I ran:

Set-ADUser -Identity "ten" -PostalCode "8001" -Enabled $true -Clear "l", "st", "telephoneNumber", "mobile" -userPrincipalname "ten@test.com" -GivenName "G" -PasswordNeverExpires $true -StreetAddress "" -Title "Manager" -Department "Team A" -Displayname "Test Now1"

I got this error:

Set-ADUser : replace
At line:1 char:1
+ Set-ADUser -Identity "ten" -PostalCode "8001" -Enabled $true -Clear "l", "st", " ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (ten:ADUser) [Set-ADUser], ADInvalidOperationException
    + FullyQualifiedErrorId : ActiveDirectoryServer:0,Microsoft.ActiveDirectory.Management.Commands.SetADUser

Author

Commented:
same error with the second script.
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Awesome, that helps a lot. It means the error is nothing at all to do with the replace parameter despite the error message.

Does the error go away if we eject the StreetAddress parameter?
Set-ADUser -Identity "ten" -PostalCode "8001" -Enabled $true -Clear "l", "st", "telephoneNumber", "mobile" -userPrincipalname "ten@test.com" -GivenName "G" -PasswordNeverExpires $true -Title "Manager" -Department "Team A" -Displayname "Test Now1"

Open in new window

Author

Commented:
yes, it works without StreetAddress!

Author

Commented:
in the first script, you had "street" after clear, i changed it to street address & it worked
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
What a surprise.

Does this version work?
Set-ADUser -Identity "ten" -PostalCode "8001" -Enabled $true -Clear "l", "st", "telephoneNumber", "mobile", "streetAddress" -userPrincipalname "ten@test.com" -GivenName "G" -PasswordNeverExpires $true -Title "Manager" -Department "Team A" -Displayname "Test Now1" -Replace @{
    "c" = "CH"
    "physicalDeliveryOfficeName" = "2344"
    "co" = "756"
    "mail" = "test.now1@test.com"
    "sn" = "Now1"
}

Open in new window

Author

Commented:
works!
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
if it does, this is a speculative fix for the function.
function SetADUser {
    # .SYNOPSIS
    #   Implements the update action.
    # .DESCRIPTION
    #   SetADUser dynamically binds parameters for the Set-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment', 'SamAccountName')
    )

    begin {
        $validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable
    }

    process {
        if ($UserInformation.Action -eq 'update') {
            $params = @{
                Identity = $_.SamAccountName
            }
            $clear = @()
            $replace = @{}

            # Create a list of properties (from UserInformation) which will be set on the existing user
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object Name -notin $ExcludeProperties

            if ($UserInformation.Action -eq 'update') {
                # Use as many named parameters as possible
                foreach ($property in $propertiesToSet) {
                    if ($property.Value.ToString().Trim() -eq '') {
                        if ($validParameters.Contains($property.Name)) {
                            $params.($property.Name) = $property.Value
                        } else {
                            $replace.Add($property.Name, $property.Value)
                        }
                    } else {
                        $clear += $property.Name
                    }
                }
                
                # Load everything else into OtherAttributes
                if ($clear.Count -gt 0) {
                    $params.Clear = $clear
                }
                if ($replace.Count -gt 0) {
                    $params.Replace = $replace
                }

                Write-Host "Updating user with the following information"
                Write-Host ($params | ConvertTo-Json -Depth 3)

                try {
                    Set-ADUser @params -ErrorAction Stop
                    # End of workflow
                    $UserInformation.Action = ''
                    $UserInformation.Status = 'OK'
                } catch {
                    $UserInformation.Status = 'Failed (Update)'
                    $UserInformation.StatusMessage = $_.Exception.Message

                    Write-Error -ErrorRecord $_
                }
            }
        }
    }
}

Open in new window

Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Not a very good speculative fix, logic error. Fixed here.
function SetADUser {
    # .SYNOPSIS
    #   Implements the update action.
    # .DESCRIPTION
    #   SetADUser dynamically binds parameters for the Set-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment', 'SamAccountName')
    )

    begin {
        $validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable
    }

    process {
        if ($UserInformation.Action -eq 'update') {
            $params = @{
                Identity = $_.SamAccountName
            }
            $clear = @()
            $replace = @{}

            # Create a list of properties (from UserInformation) which will be set on the existing user
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object Name -notin $ExcludeProperties

            if ($UserInformation.Action -eq 'update') {
                # Use as many named parameters as possible
                foreach ($property in $propertiesToSet) {
                    if ($property.Value.ToString().Trim() -ne '') {
                        if ($validParameters.Contains($property.Name)) {
                            $params.($property.Name) = $property.Value
                        } else {
                            $replace.Add($property.Name, $property.Value)
                        }
                    } else {
                        $clear += $property.Name
                    }
                }
                
                # Load everything else into OtherAttributes
                if ($clear.Count -gt 0) {
                    $params.Clear = $clear
                }
                if ($replace.Count -gt 0) {
                    $params.Replace = $replace
                }

                Write-Host "Updating user with the following information"
                Write-Host ($params | ConvertTo-Json -Depth 3)

                try {
                    Set-ADUser @params -ErrorAction Stop
                    # End of workflow
                    $UserInformation.Action = ''
                    $UserInformation.Status = 'OK'
                } catch {
                    $UserInformation.Status = 'Failed (Update)'
                    $UserInformation.StatusMessage = $_.Exception.Message

                    Write-Error -ErrorRecord $_
                }
            }
        }
    }
}

Open in new window

Author

Commented:
Can I send you some Chocolates ?

Author

Commented:
Works!!!
So far, I would like to run more tests, is that ok?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Of course, it's always okay :)

Author

Commented:
Chris,
So far the test is going Great!

If I want to add Alias for newly created user in function UpdateO365User  with samaccount name e.g.
ten@abc.com & test.now1@xyz.com

How would I call the variable for samaccount name? As we are not using it anywhere in the O365....
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
It can be added during the massive Select-Object statement.

How do you determine whether it's abc.com or xyz.com? The former is hard-coded in the example below.
			$o365UserInformation = $UserInformation | Select-Object -ExcludeProperty UserPrincipalName, PasswordNeverExpires -Property *,
                @{ Name = 'FirstName'; Expression = { $_.GivenName } },
                @{ Name = 'LastName'; Expression = { $_.sn } },
                @{ Name = 'UserPrincipalName'; Expression = { $_.mail } },
                @{ Name = 'Country'; Expression = { $_.c } },
                @{ Name = 'City'; Expression = { $_.l } },
                @{ Name = 'State'; Expression = { $_.st } },
                @{ Name = 'Office'; Expression = { $_.physicalDeliveryOfficeName } },
                @{ Name = 'PhoneNumber'; Expression = { $_.telephoneNumber } },
                @{ Name = 'MobilePhone'; Expression = { $_.mobile } },
                @{ Name = 'UsageLocation'; Expression = { $_.c } },
                @{ Name = 'Alias'; Expression = { '{0}@abc.com' -f $_.SamAccountName }}

Open in new window

Author

Commented:
it's not either or, its always and
so if i have a user test.now who's short is ten , then I need 3 accounts
test.now@test.com
test.now@abc.com
ten@test.com

this is the logic
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Three msol accounts? Ouch... It doesn't let you add multiple proxyAddresses?

Author

Commented:
i can, if i do manually

Author

Commented:
sorry, the first example is the primary account
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
So we need to run New-MsolUser 3 times for each incoming user?

If so, its likely the best way is to generate three aliases up-front and loop through those.

If we do so, what happens with UserPrincipalName? It'll be the same in each case. Same question applies to "mail" since we're using that as a primary identity for the online mailbox.

Author

Commented:
How about this:
Add multiple Proxy E-mail addresse
In case that we need to add multiple E-mail addresses, we can separate each E-mail address with a comma.
PowerShell command example

Set-Mailbox Angelina -EmailAddresses @{add="Angelina-Alias11@o365info.com","Angelina-Alias12@o365info.com"}
from: http://o365info.com/manage-email-address-using-powershell/

now, if we translate this to my scenario:
$Variable: ten
$variable2: test.now1
Set-Mailbox Angelina -EmailAddresses @{add="$variable@xyz.com","$variable@abc.com","$variable2@xyz.com"}

How about this?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
That's certainly easier. However, it would have to be in the UpdateO365 function, we can't be sure the mailbox will be there until that kicks in.

Author

Commented:
sounds good too, i really want it to be there..
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Perhaps...
function UpdateO365User {
    # .SYNOPSIS
    #   Update information held in Office 365.
    # .DESCRIPTION
    #   Update information held in Office 365.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None
    
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        $Services = (New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE)
    )

    begin {
        $ErrorActionPreference = 'Stop'
    }

    process {
        if ($UserInformation.Action -eq 'update-mail') {
            $mailbox = Get-Mailbox -Identity $UserInformation.mail -ErrorAction SilentlyContinue
            if ($mailbox) {
                try {
                    $alias = $UserInformation.mail -replace '@.+$'
                    $aliases = @(
                        '{0}@xyz.com' -f $UserInformation.SamAccountName
                        '{0}@abc.com' -f $alias
                        '{0}@xyz.com' -f $alias
                    )
                    $mailbox | Set-Mailbox -EmailAddresses @{add=$aliases}

                    # These params can be moved down into the Switch statement if any differ
                    $params = @{
                        Identity                  = $mailbox.Identity
                        TimeZone                  = 'W. Europe Standard Time'
                        DateFormat                = 'dd.MM.yyyy'
                        TimeFormat                = 'HH:mm'
                        LocalizeDefaultFolderName = $true
                    }
                    switch ($UserInformation.c) {
                        'CH' {
                            $params.Language = 'de-CH'
                        }
                        'DE' {
                            $params.Language = 'de-DE'
                        }
                    }
                    Set-MailboxRegionalConfiguration @params

                    $folderPath = '{0}:\calendar' -f $mailbox.Alias
                    $mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
                    if (-not $mailboxFolder) {
                        $folderPath = '{0}:\kalender' -f $mailbox.Alias
                        $mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
                    }
                    if ($mailboxFolder) {
                        Write-Host "Setting rights for $folderPath (Default: Reviewer)"

                        Set-MailboxFolderPermission -Identity $folderPath -User Default -AccessRights Reviewer | Out-Null

                        Write-Host "Adding rights for $folderPath (Calendar: Owner)"

                        Add-MailboxFolderPermission -Identity $folderPath -User Calendar -AccessRights Owner | Out-Null
                    }
                    
                    $mailbox | Set-Mailbox -LitigationHoldEnabled $true -LitigationHoldDuration 2555 -RetentionHoldEnabled $true

                    # Enable Services for User
                    if ($UserInformation.LicenseAssignment -eq "reseller-account:ENTERPRISEPACK" -and $null -ne $Services) {
                        Set-MsolUserLicense -UserPrincipalName $UserInformation.mail -LicenseOptions $Services
                    }

                    # End of workflow
                    $UserInformation.Action = ''
                    $UserInformation.Status = 'OK'
                } catch {
                    $UserInformation.Status = 'Failed (O365)'
                    $UserInformation.StatusMessage = $_.Exception.Message

                    Write-Error -ErrorRecord $_
                }
            }
        }
    }
}

Open in new window

Author

Commented:
so SamAccountName = ten or test.now1
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
This was my test data.
$UserInformation = ConvertFrom-Json '{
    "mail":  "test.now1@test.com",
    "userPrincipalName":  "ten@test.com",
    "sAMAccountName":  "ten",
}'

$alias = $UserInformation.mail -replace '@.+$'
$aliases = @(
    '{0}@xyz.com' -f $UserInformation.SamAccountName
    '{0}@abc.com' -f $alias
    '{0}@xyz.com' -f $alias
)

Open in new window

It makes aliases into:
ten@xyz.com
test.now1@abc.com
test.now1@xyz.com

Open in new window

To pretty much match your example up above :)

Author

Commented:
Awesome!!!
Perfecto!

Author

Commented:
Hi Chris,
I found a small glitch, when the Email account is created or updated it takes the country as "c" which is CH, whereas the Country should be "country" as Switzerland.
Can you suggest where do I have to make the change?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
The property mapping thing contains this entry:
                @{ Name = 'Country'; Expression = { $_.c } },

Open in new window

If Country is available in $userInformation we can delete that line. Otherwise it needs correcting to show the field it's supposed to draw in.

Author

Commented:
Thanks,
I'm sorry I don't get what you mean by, if the information is available in $UserInformation...
I'm not sure where $userinformation is being  created...
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Sorry, almost Monday :)

UserInformation is a single record (row) from the SQL database. Each instance of UserInformation has the properties we selected from SQL available. To automatically fill the Country field we'd need to select something named Country in this block:
$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%'"

Open in new window

Select-Object is used to convert some of those property names into value the MSOL commands understand.

Author

Commented:
Wow!
That was tricky, I created the Country in the block & AD creation started to throw error saying c is already as country.
Then I put Country as test & made the change in msoluser, then again aduser says unknown ....
then I put test in excluded properties & it worked fine!!!

Thanks a lot!!!
Let me find more glitches ;)

Author

Commented:
Chris,
Another help... When I change the users license, let's say from
reseller-account:EXCHANGEENTERPRISE to reseller-account:ENTERPRISEPACK
desired behaviour is that the License should be changed in O365, however we don't have this setup or at least it's not working...

Can you please suggest?
My guess is it should be in UpdateO365 in this area

process
	{
		if ($UserInformation.Action -eq 'update-mail')
		{
			$mailbox = Get-Mailbox -Identity $UserInformation.mail -ErrorAction SilentlyContinue
			if ($mailbox)
			{
				try
				{
					# These params can be moved down into the Switch statement if any differ
					$params = @{
						Identity = $mailbox.Identity
						TimeZone = 'W. Europe Standard Time'
						DateFormat = 'dd.MM.yyyy'
						TimeFormat = 'HH:mm'
						
					}
					switch ($UserInformation.c)
					{
						'CH' {
							$params.Language = 'de-CH'
						}
						'DE' {
							$params.Language = 'de-DE'
						}
					}
					Set-MailboxRegionalConfiguration @params -LocalizeDefaultFolderName:$true
     This is what I think should be added >>>> Set-MsolUser -UserPrincipalName $UserInformation.mail -AddLicenses $LicenseAssignment

Open in new window


Please suggest...

Author

Commented:
One thing I noticed is, that if you don'r remove the existing license, it assigns the second license too, that means the first part has to remove the existing license & then assign the changed license.... what the hell MS
So two commands would be:

Set-MsolUserLicense -UserPrincipalName -RemoveLicense "Existing License"
Set-MsolUserLicense -UserPrincipalName -AddLicense "New License"
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Is there a way to list the existing licenses for an account? That would help determine if Remove needs to be used at all. Or will an account always have exactly 1?

Author

Commented:
I thought the same, but the problem is that once the SQL has the update value in action at that time it already removes the existing one....

Author

Commented:
This is getting Ugly... if the license is EnterprisePack, then the service assignment will also have to be triggered at this step only.
We have this in UpdateO365, but since this is for existing user & being triggered in NewO365 part, when the mailbox is existing... it has to trigger here.

Author

Commented:
I think I have an idea!
We put if statement & reverse there logic, means of the license in SQL is A, that means it should first remove B then assign A!
What do you think?

Only problem is, it will always do that, point of to figure out how to run this only when license changes
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
Hmm I would suggest this needs a new step. If only because its relatively complex and it'd be nice not to have to think about it in the context of everything else (within an existing step).

Author

Commented:
Question is, how to figure out the trigger fort this step?

Author

Commented:
How about we make a column in SQL, call it "OldLicense", then the change is only happening on column "License"
Now, we create if statement, which checks if OldLicense = License, if yes nothing happens, if no then we can run this:

Set-MsolUserLicense -UserPrincipalName $upn -AddLicenses $License -RemoveLicenses $oldLicense

& after that we copy the value of License to OldLicense in SQL, that way when the next time no change is happening on License, nothing happens & if the change happens, then this triggers.

What do you think?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
It works, another approach is to check the license configuration for each user passing through the system and tag it onto the end of the normal update workflow. The assumption being that it's set correctly when someone is created under the new user workflow.

Author

Commented:
that works too, what do you think is best?
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
I'd go with the new function (no changes to SQL).

It's more sure, you're always checking the current licence state against an authoritative source, the database doesn't have to worry about being out of date because someone made a change outside of that process.

Author

Commented:
As you say!
I guess that makes more sense!
Can you please suggest, when you can....
Chris DentPowerShell Developer
CERTIFIED EXPERT
Top Expert 2010

Commented:
This is far from complete, without the commands I don't have much of a chance of getting this perfectly right. It'll need some experimentation and tweaking.
function UpdateO365License {
    # .SYNOPSIS
    #   Implements the update-license action.
    # .DESCRIPTION
    #   UpdateO365License tests and updates license and service information for a user.
    # .INPUTS
    #   System.Management.Automation.PSObject
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    begin {
        $ErrorActionPreference = 'Stop'
    }

    process {
        if ($UserInformation.Action -eq 'update-license') {
            try {
                # Get the mailbox, should give license information.
                $mailbox = Get-MsolUser -UserPrincipalName $UserInformation.mail

                # Not sure if this comparison will work at all. Depends how this is exposed, what type of object it is, etc.
                if ($mailbox) {
                    if ($mailbox.Licenses -ne $UserInformation.License) {
                        # Presumably the services will change depending on the license?
                        $services = (New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE)
                        # Looks like you can swap a license in a single step.
                        Set-MsolUserLicense -UserPrincipalName -AddLicense $UserInformation.License -RemoveLicense $mailbox.Licenses -LicenseOptions $services
                    }
                    # End of workflow
                    $UserInformation.Action = ''
                    $UserInformation.Status = 'OK'
                } else {
                    $UserInformation.Status = 'Failed (Update)'
                    $UserInformation.StatusMessage = 'Unable to find mailbox'
                }
            } catch {
                $UserInformation.Status = 'Failed (Update)'
                $UserInformation.StatusMessage = $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

Open in new window

It fits into the whole like this. Note: I may have out-dated functions in this script now, be careful incorporating anything from this into your own version. I'm sure you would, but the warning is important :)
#Requires -Module ActiveDirectory, MSOnline

# Utility functions

function Get-CommandParameter {
    # .SYNOPSIS
    #   Get a set of parameters from a specific parameter set.
    # .DESCRIPTION
    #   Get a set of parameters from a specific parameter set.
    # .INPUTS
    #   System.String
    # .OUTPUTS
    #   System.Management.Automation.ParameterMetadata
    # .EXAMPLE
    #   Get-CommandParameter Set-ADUser -ParameterSetName Identity
    
    [OutputType([System.Management.Automation.ParameterMetadata])]
    param (
        # Retrieve parameters for the specified command.
        [Parameter(Mandatory = $true)]
        [String]$CommandName,

        # Limit results to parameters from the specified parameter set.
        [String]$ParameterSetName = '__AllParameterSets',

        # Return the results as a hashtable, using the parameter name as a key.
        [Switch]$AsHashtable
    )

    try {
        $hashtable = @{}

        $command = Get-Command -Name $CommandName -ErrorAction Stop
        $command.Parameters.Values | 
            Where-Object { $_.ParameterSets.Keys -contains $ParameterSetName } |
            ForEach-Object {
                if ($AsHashtable) {
                    $hashtable.Add($_.Name, $_)
                } else {
                    $_
                }
            }

        if ($AsHashtable) {
            $hashtable
        }
    } catch {
        throw
    }
}

# Generic functions

function ConnectO365 {
    # .SYNOPSIS
    #   Create a connection to Office 365.
    # .DESCRIPTION
    #   Create a connection to Office 365.

    $ErrorActionPreference = 'Stop'
    try {
        $emailusername = "abc@test.com"
        $encrypted = Get-Content c:\encrypted_password1.txt | ConvertTo-SecureString
        $Credential = New-Object PSCredential($emailusername, $encrypted)

        $params = @{
            ConfigurationName = 'Microsoft.Exchange'
            ConnectionUri     = 'https://ps.outlook.com/powershell'
            Credential        = $Credential
            Authentication    = 'Basic'
            AllowRedirection  = $true
        }
        $PSSession = New-PSSession @params
        $null = Import-PSSession $PSSession -AllowClobber

        $null = Connect-MsolService –Credential $Credential

        return $PSSession
    } catch {
        Write-Error -ErrorRecord $_
    }
}

function Invoke-SqlQuery {
    # .SYNOPSIS
    #   Invoke an SQL query.
    # .DESCRIPTION
    #   Invoke an SQL query against a server described by a connection string.
    # .INPUTS
    #   System.String
    # .OUTPUTS
    #   System.Management.Automation.PSObject
    # .EXAMPLE
    #   Invoke-SqlQuery -Query "SELECT * FROM Table" -ConnectionString "Server=server\instance;Database=db;Trusted_Connection=yes;"

    [OutputType([System.Management.Automation.PSObject])]
    param (
        # An SQL query to execute.
        [Parameter(Mandatory = $true)]
        [String]$Query,

        # The connection string to use. A connection will be created prior to executing the query, then removed afterwards.
        [Parameter(Mandatory = $true)]
        [String]$ConnectionString
    )

    # Execute the SQL query and return an object representing each row.

    try {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()

        $SqlCommand = $SqlConnection.CreateCommand()
        $SqlCommand.CommandText = $Query
    
        $reader = $SqlCommand.ExecuteReader()

        if ($reader.HasRows) {
            while ($reader.Read()) {
                $psObject = New-Object PSObject
                for ($i = 0; $i -lt $reader.FieldCount; $i++) {
                    $name = $reader.GetName($i)

                    if (-not $psObject.PSObject.Properties.Item($name)) {
                        $value = $reader.GetValue($i)
                        
                        if ($value -is [DBNull]) {
                            $value = $null
                        }
                        if ($value -is [String] -and -not [String]::IsNullOrEmpty($value)) {
                            $value = $value.Trim()
                        }

                        $psObject | Add-Member $name $value
                    }
                }
                $psObject
            }
        }

        $reader.Close()
    } catch {
        throw
    } finally {
        if ($SqlConnection.State -eq 'Opened') {
            $SqlConnection.Close()
        }
    }
}

# Process (workflow) functions

#
# Create
#

function NewADUser {
    # .SYNOPSIS
    #   Implements the create action.
    # .DESCRIPTION
    #   NewADUser dynamically binds parameters for the New-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String]$Path = "OU=TestUser,OU=test,DC=test,DC=com",

        [String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment')
    )

    begin {
        $validParameters = Get-CommandParameter New-ADUser -AsHashtable
    }

    process {
        if ($UserInformation.Action -eq 'create') {
            $params = @{
                Path            = $Path
                AccountPassword = $UserInformation.Password | ConvertTo-SecureString -AsPlainText -Force
            }
            $otherAttributes = @{}

            # Create a list of properties (from UserInformation) which will be written to the new user
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object { 
                    $_.Name -notin $ExcludeProperties -and 
                    $null -ne $_.Value -and
                    ($_.Value -isnot [String] -or $_.Value.Trim() -ne '')
                }

            try {
                # Use as many named parameters as possible
                foreach ($property in $propertiesToSet) {
                    if ($validParameters.Contains($property.Name)) {
                        $params.($property.Name) = $property.Value
                    } else {
                        $otherAttributes.Add($property.Name, $property.Value)
                    }
                }
                
                # Load everything else into OtherAttributes
                if ($otherAttributes.Count -gt 0) {
                    $params.OtherAttributes = $otherAttributes
                }
                if (-not $params.Contains('Name') -and $UserInformation.DisplayName) {
                    $params.Name = $UserInformation.DisplayName
                }

                Write-Host "Creating user with the following information"
                Write-Host ($params | ConvertTo-Json -Depth 3)

                New-ADUser @params -ErrorAction Stop
                $UserInformation.Status = 'OK'
                $UserInformation.Action = 'create-mail'
            } catch {
                $UserInformation.Status = 'Failed (Create)'
                $UserInformation.StatusMessage = $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

function NewO365User
{
	# .SYNOPSIS
	#   Create a user in Office 365.
	# .DESCRIPTION
	#   Create a user in Office 365.
	# .INPUTS
	#   System.Management.Automation.PSObject
	#   System.String
	# .OUTPUTS
	#   None
	
	param (
		[Parameter(ValueFromPipeline = $true)]
		[PSObject]$UserInformation
	)
	
	begin
	{
		$validSetUserParameters = Get-CommandParameter Set-MsolUser -ParameterSetName "AddUser__0" -AsHashtable
		$validNewUserParameters = Get-CommandParameter New-MsolUser -ParameterSetName "AddUser__0" -AsHashtable
	}
	
	process
	{
		if ($UserInformation.Action -eq 'create-mail')
		{			
			# Rewrite the property set to support the O365 commands
			$o365UserInformation = $UserInformation | Select-Object -ExcludeProperty UserPrincipalName, PasswordNeverExpires -Property *,
                @{ Name = 'FirstName'; Expression = { $_.GivenName } },
                @{ Name = 'LastName'; Expression = { $_.sn } },
                @{ Name = 'UserPrincipalName'; Expression = { $_.mail } },
                @{ Name = 'Country'; Expression = { $_.c } },
                @{ Name = 'City'; Expression = { $_.l } },
                @{ Name = 'State'; Expression = { $_.st } },
                @{ Name = 'Office'; Expression = { $_.physicalDeliveryOfficeName } },
                @{ Name = 'PhoneNumber'; Expression = { $_.telephoneNumber } },
                @{ Name = 'MobilePhone'; Expression = { $_.mobile } },
                @{ Name = 'UsageLocation'; Expression = { $_.c } }
			
			# Create a list of properties (from UserInformation) which will be written to the new user
			$propertiesToSet = $o365UserInformation.PSObject.Properties
			
            $params = @{}
			try
			{
				# Check if the User Exists
				$msolUser = Get-MsolUser -UserPrincipalName $UserInformation.mail -ErrorAction SilentlyContinue
				if ($msolUser)
				{
                    # Use as many named parameters as possible
                    foreach ($property in $propertiesToSet)
                    {
                        if ($validSetUserParameters.Contains($property.Name))
                        {
                            $params.($property.Name) = $property.Value
                        }
                    }

					try
					{
						Write-Host "Update MSOLUser initiated"
						Set-MsolUser @params -ErrorAction Stop
						
						$UserInformation.Action = ' '
						$UserInformation.Status = 'Not complete'
					}
					catch
					{
						$UserInformation.Status = 'Failed (Update O365)'
						$UserInformation.StatusMessage = $_.Exception.Message
					}
				}
				else
				{
                    $params.ForceChangePassword = $true

                    # Use as many named parameters as possible
                    foreach ($property in $propertiesToSet)
                    {
                        if ($validNewUserParameters.Contains($property.Name))
                        {
                            $params.($property.Name) = $property.Value
                        }
                    }

					Write-Host "Create MSOLUser initiated"
					Write-Host "Creating O365 user with the following information"
					Write-Host ($params | ConvertTo-Json -Depth 3)
					
					try
					{
						New-MsolUser @params -ErrorAction Stop
						
						$UserInformation.Action = 'update-mail'
						$UserInformation.Status = 'Not complete'
					}
					catch
					{
						$UserInformation.Status = 'Failed (Create O365)'
						$UserInformation.StatusMessage = $_.Exception.Message
					}
				}
			}
			catch
			{
				# Might be able to remove this soon, it captures things we didn't expect to go wrong
                throw
			}
		}
	}
}

function UpdateO365User {
    # .SYNOPSIS
    #   Update information held in Office 365.
    # .DESCRIPTION
    #   Update information held in Office 365.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None
    
    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        $Services = (New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE)
    )

    begin {
        $ErrorActionPreference = 'Stop'
    }

    process {
        if ($UserInformation.Action -eq 'update-mail') {
            $mailbox = Get-Mailbox -Identity $UserInformation.mail -ErrorAction SilentlyContinue
            if ($mailbox) {
                try {
                    $alias = $UserInformation.mail -replace '@.+$'
                    $aliases = @(
                        '{0}@xyz.com' -f $UserInformation.SamAccountName
                        '{0}@abc.com' -f $alias
                        '{0}@xyz.com' -f $alias
                    )
                    $mailbox | Set-Mailbox -EmailAddresses @{add=$aliases}

                    # These params can be moved down into the Switch statement if any differ
                    $params = @{
                        Identity                  = $mailbox.Identity
                        TimeZone                  = 'W. Europe Standard Time'
                        DateFormat                = 'dd.MM.yyyy'
                        TimeFormat                = 'HH:mm'
                        LocalizeDefaultFolderName = $true
                    }
                    switch ($UserInformation.c) {
                        'CH' {
                            $params.Language = 'de-CH'
                        }
                        'DE' {
                            $params.Language = 'de-DE'
                        }
                    }
                    Set-MailboxRegionalConfiguration @params

                    $folderPath = '{0}:\calendar' -f $mailbox.Alias
                    $mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
                    if (-not $mailboxFolder) {
                        $folderPath = '{0}:\kalender' -f $mailbox.Alias
                        $mailboxFolder = Get-MailboxFolderPermission -Identity $folderPath -ErrorAction SilentlyContinue
                    }
                    if ($mailboxFolder) {
                        Write-Host "Setting rights for $folderPath (Default: Reviewer)"

                        Set-MailboxFolderPermission -Identity $folderPath -User Default -AccessRights Reviewer | Out-Null

                        Write-Host "Adding rights for $folderPath (Calendar: Owner)"

                        Add-MailboxFolderPermission -Identity $folderPath -User Calendar -AccessRights Owner | Out-Null
                    }
                    
                    $mailbox | Set-Mailbox -LitigationHoldEnabled $true -LitigationHoldDuration 2555 -RetentionHoldEnabled $true

                    # Enable Services for User
                    if ($UserInformation.LicenseAssignment -eq "reseller-account:ENTERPRISEPACK" -and $null -ne $Services) {
                        Set-MsolUserLicense -UserPrincipalName $UserInformation.mail -LicenseOptions $Services
                    }

                    # End of workflow
                    $UserInformation.Action = ''
                    $UserInformation.Status = 'OK'
                } catch {
                    $UserInformation.Status = 'Failed (O365)'
                    $UserInformation.StatusMessage = $_.Exception.Message

                    Write-Error -ErrorRecord $_
                }
            }
        }
    }
}

#
# Update
#

function SetADUser {
    # .SYNOPSIS
    #   Implements the update action.
    # .DESCRIPTION
    #   SetADUser dynamically binds parameters for the Set-ADUser command based on a UserInformation object.
    # .INPUTS
    #   System.Management.Automation.PSObject
    #   System.String
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation,

        [String[]]$ExcludeProperties = ('Action', 'Password', 'Status', 'StatusMessage', 'LicenseAssignment', 'SamAccountName')
    )

    begin {
        $validParameters = Get-CommandParameter Set-ADUser -ParameterSetName Identity -AsHashtable
    }

    process {
        if ($UserInformation.Action -eq 'update') {
            $params = @{
                Identity = $_.SamAccountName
            }
            $clear = @()
            $replace = @{}

            # Create a list of properties (from UserInformation) which will be set on the existing user
            $propertiesToSet = $UserInformation.PSObject.Properties | 
                Where-Object Name -notin $ExcludeProperties

            if ($UserInformation.Action -eq 'update') {
                # Use as many named parameters as possible
                foreach ($property in $propertiesToSet) {
                    if ($property.Value.ToString().Trim() -ne '') {
                        if ($validParameters.Contains($property.Name)) {
                            $params.($property.Name) = $property.Value
                        } else {
                            $replace.Add($property.Name, $property.Value)
                        }
                    } else {
                        $clear += $property.Name
                    }
                }
                
                # Load everything else into OtherAttributes
                if ($clear.Count -gt 0) {
                    $params.Clear = $clear
                }
                if ($replace.Count -gt 0) {
                    $params.Replace = $replace
                }

                Write-Host "Updating user with the following information"
                Write-Host ($params | ConvertTo-Json -Depth 3)

                try {
                    Set-ADUser @params -ErrorAction Stop
                    $UserInformation.Action = 'update-license'
                    $UserInformation.Status = 'OK'
                } catch {
                    $UserInformation.Status = 'Failed (Update)'
                    $UserInformation.StatusMessage = $_.Exception.Message

                    Write-Error -ErrorRecord $_
                }
            }
        }
    }
}

function UpdateO365License {
    # .SYNOPSIS
    #   Implements the update-license action.
    # .DESCRIPTION
    #   UpdateO365License tests and updates license and service information for a user.
    # .INPUTS
    #   System.Management.Automation.PSObject
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    begin {
        $ErrorActionPreference = 'Stop'
    }

    process {
        if ($UserInformation.Action -eq 'update-license') {
            try {
                # Get the mailbox, should give license information.
                $mailbox = Get-MsolUser -UserPrincipalName $UserInformation.mail

                # Not sure if this comparison will work at all. Depends how this is exposed, what type of object it is, etc.
                if ($mailbox) {
                    if ($mailbox.Licenses -ne $UserInformation.License) {
                        # Presumably the services will change depending on the license?
                        $services = (New-MsolLicenseOptions -AccountSkuId "reseller-account:ENTERPRISEPACK" -DisabledPlans FLOW_O365_P2, POWERAPPS_O365_P2, TEAMS1, PROJECTWORKMANAGEMENT, SWAY, INTUNE_O365, YAMMER_ENTERPRISE, RMS_S_ENTERPRISE, SHAREPOINTWAC, SHAREPOINTENTERPRISE)
                        # Looks like you can swap a license in a single step.
                        Set-MsolUserLicense -UserPrincipalName -AddLicense $UserInformation.License -RemoveLicense $mailbox.Licenses -LicenseOptions $services
                    }
                    # End of workflow
                    $UserInformation.Action = ''
                    $UserInformation.Status = 'OK'
                } else {
                    $UserInformation.Status = 'Failed (Update)'
                    $UserInformation.StatusMessage = 'Unable to find mailbox'
                }
            } catch {
                $UserInformation.Status = 'Failed (Update)'
                $UserInformation.StatusMessage = $_.Exception.Message

                Write-Error -ErrorRecord $_
            }
        }
    }
}

#
# Reporting
#

function UpdateAction {
    # .SYNOPSIS
    #   Remove the action flag.
    # .DESCRIPTION
    #   Remove the action flag for every object in UserInformation where status is set to 'OK'.
    # .INPUTS
    #   System.Management.Automation.PSObject
    # .OUTPUTS
    #   None

    param (
        [Parameter(ValueFromPipeline = $true)]
        [PSObject]$UserInformation
    )

    begin {
        $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
        $SqlConnection.ConnectionString = $ConnectionString
        $SqlConnection.Open()
    }

    process {
        try {
            $SqlCommand = $SqlConnection.CreateCommand()
            $SqlCommand.CommandText = "UPDATE dbo.GetActiveDirectoryUsers SET Action = '{1}' WHERE SamAccountName = '{0}';" -f $UserInformation.SamAccountName, $UserInformation.Action
            $SqlCommand.ExecuteNonQuery()
        } catch {
            Write-Error -ErrorRecord $_
        }
    }

    end {
        $SqlConnection.Close()
    }
}

function NewReport {
    # .SYNOPSIS
    #   Create a report.
    # .DESCRIPTION
    #   This step creates an HTML report based on the UserInformation collection.
    # .INPUTS
    #   System.Management.Automation.PSObject[]
    # .OUTPUTS
    #   System.String

    param (
        # The UserInformation set which holds the state of each action performed by this script.
        [Parameter(Mandatory = $true)]
        [AllowNull()]
        [PSObject[]]$UserInformation
    )

    # HTML header
    $htmlHead = "<style>
        body {
            font-family: Arial;
        }

        table {
            width: 100%;
            border-collapse: collapse;
            border: 1px solid;      
        }

        th {
            background-color: #87CEFA;
            border: 1px solid;
            padding: 1px;
        }

        td {
            border: 1px solid;
            padding: 1px;
        }

        td.Red {
            color: Red;
        }

        td.Orange {
            color: Orange;
        }

        td.Green {
            color: Green;
        }
    </style>"

    if ($null -ne $UserInformation) {
        $UserInformation |
            Select-Object SamAccountName, DisplayName, Action, Status, StatusMessage |
            Sort-Object {
                switch -Wildcard ($_.Status) {
                    'Failed*'      { 1 }
                    'Not complete' { 2 }
                    'OK'           { 3 }
                    default        { 3 }
                }
            }, DisplayName |
            ConvertTo-Html -Head $htmlHead |
            ForEach-Object {
                # Colour the status cells in.
                switch -Regex ($_) {
                    '<td>Failed'       { $_ -replace '<td>Failed', '<td class="Red">Failed'; break }
                    '<td>Not complete' { $_ -replace '<td>Not complete', '<td class="Orange">Not complete'; break }
                    '<td>OK'           { $_ -replace '<td>OK', '<td class="Green">OK'; break }
                    default            { $_ }
                }
            } | Out-String
    }
}

function SendMailReport {
    param(
        [Parameter(Mandatory = $true)]
        [PSObject[]]$UserInformation,

        [Parameter(Mandatory = $true)]
        [String]$Body,

        [String]$To = 'someone@domain.com',

        [String]$From = 'someone@domain.com',

        [String]$Subject = ('User update: {0}' -f (Get-Date).ToShortDateString()),

        [String]$SmtpServer = 'someserver.domain.com'
    )

    # Export this to add as an attachment.
    $UserInformation | Export-Csv UserInformation.csv -NoTypeInformation

    $params = @{
        To          = $To
        From        = $From
        Subject     = $Subject
        Body        = $Body
        BodyAsHtml  = $true
        SmtpServer  = $SmtpServer
        Attachments = '.\UserInformation.csv'
    }
    Send-MailMessage @params
}

#
# Main
#

# Connection string to SQL Server database
$connectionString = "Server=WIN8\SQLEXPRESS;Database=DBA_Utilities;Trusted_Connection=yes;"

# Query
$sql = "Select FirstName as GivenName,
               LastName as sn,
               DisplayName,
               samAccountName,
               EmailAddress as mail,
               City as l,
               State as st,
               c,
               CountryCode as co,
               Department,
               StreetAddress,
               samAccountName+'@test.com' as userPrincipalName,
               PostalCode,
               Title,
               Office as physicalDeliveryOfficeName,
               OfficePhone as telephoneNumber,
               MobilePhone as mobile,
               Action,            
               AccountisEnabled as Enabled,
               '11Passw0rd' as Password,
               License as LicenseAssignment
FROM GetActiveDirectoryUsers where Action LIKE 'create%' OR Action LIKE 'update%'"

# A status field is added to each entry. This is used to persistently record the result of our commands.
$usersToProcess = Invoke-SqlQuery -Query $sql -ConnectionString $connectionString |
    Select-Object *,
                  @{Name = 'PasswordNeverExpires'; Expression = { $true }},
                  @{Name = 'Status';               Expression = 'NotProcessed'},
                  @{Name = 'StatusMessage';        Expression = { '' }}

if (($usersToProcess | Measure-Object).Count -gt 0) {
    # Update any fields which need modification
    $usersToProcess | ForEach-Object {
        $_.Enabled = (Get-Variable $_.Enabled).Value
    }

    # Commit the changes to AD
    # NewADUser internally filters usersToProcess on Action = Create
    $usersToProcess | NewADUser

    # SetADUser internally filters usersToProcess on Action = Update
    $usersToProcess | SetADUser

    if ($PSSession = ConnectO365) {
        $usersToProcess | NewO365User
        $usersToProcess | UpdateO365User
        $usersToProcess | UpdateO365License

        Remove-PSSession -Session $PSSession
    } else {
        # What would you like to do if the O365 part fails?
    }

    # Update database
    # RemoveAction internally filters usersToProcess on Status = OK
    $usersToProcess | UpdateAction

    # Send job notification
    $report = NewReport -UserInformation $usersToProcess
    if ($report) {
        SendMailReport -UserInformation $usersToProcess -Body $report
    }
}

Open in new window