Avatar of Ackles
Ackles
Flag for Switzerland asked on

Powershell help for creating accounts

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
PowershellMicrosoft SQL ServerMicrosoft 365

Avatar of undefined
Last Comment
Ackles

8/22/2022 - Mon
Chris Dent

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

ASKER
### 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 Dent

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Ackles

ASKER
Thanks Chris,
I wrote the stuff in the question as to what all I want after the account is created....
I guess I mislead ... sorry about that
The part I gave was for updating the existing user, however first part is to create the user, below is the code what works for creating the user:

		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 Dent

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?
Ackles

ASKER
It didn't throw any error, but the weird thing is that the script didn't even finish.....
Chris,
Can you please make first the function for creating new user on O365?
I would prefer to play with Update on it...
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ackles

ASKER
Just so that you know the whole picture:
I have a web form which is tied up with SQL, which has the possibility to retrieve & create record from SQL

When I want to change something for an existing user, I query the web form & make changes, which in turn reflect on SQL
Same way when I create a new user, the changes are reflected on SQL

After that the script comes in action, since the AD is in house & mails are on O365, the script will do the needful on both, but from Administration point of view, I only interact with web form.

Same will be handed over to HR who will fill the form when the new Hire, Employee leaving or some change is to be done.

I will just have to approve & with a click multiple tasks can be performed at once.
That's why 2 things are done, create & change & for both AD & O365.
Chris Dent

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

Ackles

ASKER
can we change License instead of static to pull from SQL?
I have a column named license in SQL
Your help has saved me hundreds of hours of internet surfing.
fblack61
Ackles

ASKER
Sorry, also usage location is same as "c"
Chris Dent

Awesome. Just having lunch, no problem on both counts.
Chris Dent

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ackles

ASKER
So, first thing:
New-ADUser doesn't like;ERROR: NewADUser : The specified directory service attribute or value does not exist


New-O365....
Doesn't like:

ERROR: Select-Object : Illegal key Experssion
TestO365.ps1 (362, 46): ERROR: At Line: 362 char: 46
ERROR: +             $o365UserInformation = $UserInformation | Select-Object *,
Chris Dent

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

Ackles

ASKER
No worries, here :

Creating user with the following information
{
    "AccountPassword":  {
                            "Length":  10
                        },
    "userPrincipalName":  "tel@test.com",
    "DisplayName":  "Test Last",
    "samAccountName":  "tel",
    "Name":  "Test Last",
    "Department":  "Team Z",
    "GivenName":  "Test",
    "OtherAttributes":  {
                            "st":  "Zürich",
                            "co":  "756",
                            "mail":  "test.last@test.com",
                            "l":  "Zürich",
                            "c":  "CH",
                            "LicenseAssignment":  "reseller-account: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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Ackles

ASKER
There is an issue with new o365

Creating O365 user with the following information
{
    "ForceChangePassword":  true
}
ERROR: NewO365User : You must provide a required property: Parameter name: UserPrincipalName


It seems, that the required parameter is missing....
Ackles

ASKER
the UPN in O365 in this environment would be the EmailAddress from SQL
Chris Dent

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 :)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ackles

ASKER
I've been only making the changes, let me double check.
Sorry about that....
Chris Dent

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

ASKER
where do you want me to insert (Get-Command New-MsolUser).ParameterSets.Item.Name
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Ackles

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

ASKER
I'm not running 5 :(
I only have V3
Chris Dent

(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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ackles

ASKER
If i run it in shell, after connecting to MSOL, it says: Item
Chris Dent

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

Ackles

ASKER
Chris,
Can you please tell me where all you made changes?
So I can just replace the parts....
my guess is only in: function NewO365User
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Ackles

ASKER
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:
Ackles

ASKER
sorry, this didn't get pasted

RROR: Select-Object : Property cannot be processed because property "UserPrincipalName" already exists.
TestO365.ps1 (362, 46): ERROR: At Line: 362 char: 46
ERROR: +             $o365UserInformation = $UserInformation | Select-Object *,
ERROR: +                                                       ~~~~~~~~~~~~~~~~
Chris Dent

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Chris Dent

Ohhh scratch that.. need to fix the other error first.
Chris Dent

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

Ackles

ASKER
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Ackles

ASKER
should i run the second or not?
Chris Dent

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

Ackles

ASKER
& BAM!!!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ackles

ASKER
Now, I start the update O365 part ;)
I had it disabled ....
Ackles

ASKER
ha ha ha

Command execution stopped because the preference variable "ErrorActionPreference" or common parameter is set to Stop: [ps.outlook.com] Processing data from remote server ps.outlook.com failed with the following error message: [ClientAccessServer=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.
Ackles

ASKER
I guess, we have to terminate the connection every time after the script....
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Chris Dent

LOL

Remove-PSSession at the end I reckon.
Ackles

ASKER
it asks for the id of pssession
Ackles

ASKER
so the account is made in o365, but the problem is that the script doesn't wait for the mailbox to be created & because of that the setting doesn't work....
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ackles

ASKER
i did get-pssession | remove-pssession
Chris Dent

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

Ackles

ASKER
It's waiting!!!
Your help has saved me hundreds of hours of internet surfing.
fblack61
Ackles

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

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

Thanks,
A
Chris Dent

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?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ackles

ASKER
I guess you are right, because now after running overnight it still hasn't done the job.
The only requirement is that I don't want to restart the script manually, if it can trigger on it's own after let's say an hour, then I'm fine with it.

Also, I guess it would make sense to somehow bind the Notification after every task is completed, that way we will know when the task was completed & what we should expect to come?

What do you think?
Ackles

ASKER
Any thoughts, Chris?
Chris Dent

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

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Ackles

ASKER
Morning Chris,
Thanks for the complete scenario, however on a second thought...
Since it's always the update in O365 which is causing the issue, I would suggest that we separate it completely.

Then I create a schedule on my another system, which actually is causing the trigger from web-page to SQL, to run this separated script at a given interval that is twice or three times a day.....
That way we can have notification sent once the job is done. I mean that only when the script finds something to be done, then only sends a notification that the job has been performed?

What do you think?
Chris Dent

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

ASKER
Oh sorry, I didn't mean not to have it in the same process, I meant along with this.
Instead of running it next time the script is called, I want to have it separated which I can run on schedule.
The practical implication is, we don't make users every day, it's only once or twice a month, max.

So if we have to wait that long, then it defeats the purpose...

What do you say?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ackles

ASKER
well, actually on a second thought,
if we timeout the update O365 part & since we are putting in DB to update-mail, then I can actually call the same script without creating a new one?
Chris Dent

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 Dent

> 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).
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Ackles

ASKER
Thanks Chris,
I ran & the first errors I got:

ERROR: Remove-PSSession : Cannot bind parameter 'Session'. Cannot convert the "tmp_wry2ujs2.g5n" value of type "System.Management.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 Dent

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 Dent

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ackles

ASKER
Alright, the mail is working!!!
The account is made in O365, however when i run the script again, it doesn't update the calendar settings

I have a question, with what property are we looking for the user in O365 at Update stage?
The mail address mentioned in the SQL will be the get-mailbox -identity
Ackles

ASKER
So, I changed it from samaccount name to mail & seems to find it correctly.
However, it's not setting the permissions for calendar...
Chris Dent

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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Ackles

ASKER
yes that's what I did, however I get this error:

ERROR: An existing permission entry was found for user: Calendar.
ERROR:     + CategoryInfo          : NotSpecified: (:) [Add-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 Dent

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

Ackles

ASKER
this is what I did, but I get the above mentioned error?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Chris Dent

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

ASKER
I guess we have to change the variable to alias for calendar to take impact, see in my previous script I had the code as below,
If you see, it first creates $mbox, which is $mbx.alias
I think without alias it won't find calendar or kalender

foreach ($mbx in Get-Mailbox | Where-Object { $_.WhenCreated –ge ((Get-Date).Adddays(-1)) } | Get-Mailbox)
            {
                  $mbox = $mbx.alias + ":\calendar"
                  $test = Get-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
                        
                  }
            }
Ackles

ASKER
I see you are making it in $mailboxfolder...
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Chris Dent

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 Dent

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

ASKER
Chris,
When the error is thrown, the Action get's updated in SQL, is it possible that we don't update SQL till the time no error is thrown?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Chris Dent

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 Dent

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

ASKER
hmmmm, one thing i noticed in the last update you sent, the line:
$folderPath = '{0}:\calendar' -f $mailbox.Alias

looks for the folder "calendar" how about if it's "kalender" ?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Chris Dent

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

Ackles

ASKER
unfortunately, it doesn't update the permissions, it doesn't throw error anymore after the latest update.
Also, when it throws error, it updates SQL... :(
Ackles

ASKER
WARNING: The command completed successfully but no permissions of 'test.last:\' have been modified.
ERROR: An existing permission entry was found for user: Calendar.
ERROR:     + CategoryInfo          : NotSpecified: (:) [Add-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....
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Chris Dent

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

Ackles

ASKER
Morning Chris,
On further testing, I have realised that it's not only the permissions on calendar, but also the services assignment for corresponding license not working...
I am now of opinion that we split the Update O365 part & put in another script, that way I will run it on schedule.

I suggest that we use the line:
            foreach ($mbx in Get-Mailbox | Where-Object { $_.WhenCreated –ge ((Get-Date).Adddays(-1)) } | Get-Mailbox)
that way we will have the variable for all the mailboxes made in last 1 day & we can run it on all of them.
Another benefit would be that all future changes for updates (which of course will keep on coming), will be done in this script, that way it will be the only part to be touched....

What do you think?
A