SpeakeasyPSModule.psm1
|
#Region './Private/Format-BooleanValue.ps1' 0 function Format-BooleanValue([string]$ValueToFormat) { if($ValueToFormat -eq "Yes") {return 1} else { return 0} } #EndRegion './Private/Format-BooleanValue.ps1' 6 #Region './Private/Format-DateValue.ps1' 0 function Format-DateValue([string]$ValueToFormat) { [datetime] $dt = $ValueToFormat return "'" + $dt.ToString("yyyyMMdd HH:MM:ss") + "'" } #EndRegion './Private/Format-DateValue.ps1' 6 #Region './Private/Format-LookupValue.ps1' 0 function Format-LookupValue($ValueToFormat) { $LookupValue = [string]::join("; ",( $ValueToFormat | Select-Object -expandproperty LookupValue)) $LookupValue = $LookupValue -replace "'", "''" return "'" + $LookupValue + "'" } #EndRegion './Private/Format-LookupValue.ps1' 7 #Region './Private/Format-MMSValue.ps1' 0 function Format-MMSValue([Object]$ValueToFormat) { return "'" + $ValueToFormat.Label + "'" } #EndRegion './Private/Format-MMSValue.ps1' 5 #Region './Private/Format-StringValue.ps1' 0 function Format-StringValue([object]$ValueToFormat) { [string]$result = $ValueToFormat -replace "'", "''" return "'" + $result + "'" } #EndRegion './Private/Format-StringValue.ps1' 6 #Region './Private/Format-UserValue.ps1' 0 function Format-UserValue([object] $ValueToFormat) { $Users = [string]::join("; ",( $ValueToFormat | Select -expandproperty LookupValue)) $Users = $Users -replace "'", "''" return "'" + $Users + "'" } #EndRegion './Private/Format-UserValue.ps1' 7 #Region './Private/Get-AzureCertificateProperties.ps1' 0 function Get-AzureCertificateProperties { if ($null -eq $Script:azureCertificateProperties) { throw "Azure properties are null - Set-AzureCertificateProperties must be invoked to set these" } return $Script:azureCertificateProperties; } #EndRegion './Private/Get-AzureCertificateProperties.ps1' 9 #Region './Private/Get-AzureProperties.ps1' 0 function Get-AzureProperties { if ($null -eq $Script:azureProperties) { throw "Azure properties are null - Set-AzureProperties must be invoked to set these" } return $Script:azureProperties; } #EndRegion './Private/Get-AzureProperties.ps1' 9 #Region './Private/Get-ColumnDefinition.ps1' 0 #Get SQL column Definition for SharePoint List Field function Get-ColumnDefinition($field) { $ColumnDefinition=[string]::Empty $fieldTitle = $Field.Title.replace(' ','') # remove whitespace so that column names have no spaces # use field title instead of internal name as the internal name may be somehting like "field_1" where title would be "surname" Switch($Field.TypeAsString) { "Boolean" { $ColumnDefinition = '['+ $fieldTitle +'] [bit] NULL '} "Choice" { $ColumnDefinition = '['+ $fieldTitle +'] [nvarchar](MAX) NULL '} "Currency" { $ColumnDefinition = '['+ $fieldTitle +'] [decimal](18, 2) NULL '} "DateTime" { $ColumnDefinition = '['+ $fieldTitle +'] [datetime] NULL '} "Guid" { $ColumnDefinition = '['+ $fieldTitle +'] [uniqueidentifier] NULL '} "Integer" { $ColumnDefinition = '['+ $fieldTitle +'] [int] NULL '} "Lookup" { if ($fieldTitle.ToUpper().EndsWith("ID")) {$ColumnDefinition = '['+ $fieldTitle +'] [int] NULL '} else {$ColumnDefinition = '['+ $fieldTitle +'] [nvarchar] (500) NULL '}} "MultiChoice" { $ColumnDefinition = '['+ $fieldTitle +'] [nText] NULL '} #"MultiChoice" { $ColumnDefinition = '['+ $fieldTitle +'] [nText] (MAX) NULL '} "Note" { $ColumnDefinition = '['+ $fieldTitle +'] [nText] NULL '} "Number" { $ColumnDefinition = '['+ $fieldTitle +'] [decimal](18, 2) NULL '} "Text" { $ColumnDefinition = '['+ $fieldTitle +'] [nVarchar] (MAX) NULL '} "URL" { $ColumnDefinition = '['+ $fieldTitle +'] [nvarchar] (500) NULL '} "User" { $ColumnDefinition = '['+ $fieldTitle +'] [nvarchar] (255) NULL '} default { $ColumnDefinition = '['+ $fieldTitle +'] [nvarchar] (MAX) NULL '} } return $ColumnDefinition } #EndRegion './Private/Get-ColumnDefinition.ps1' 27 #Region './Private/Get-ColumnValue.ps1' 0 #function to get the value of a given field based on its data type function Get-ColumnValue($field, $fieldValue) { #Check for NULL if([string]::IsNullOrEmpty($fieldValue)) { return 'NULL'} $formattedValue = [string]::Empty Switch($field.TypeAsString) { "Boolean" {$formattedValue = Format-BooleanValue($fieldValue)} "Choice" {$formattedValue = Format-StringValue($fieldValue)} "Currency" {$formattedValue = $fieldValue} "DateTime" {$formattedValue = Format-DateValue($fieldValue)} "Guid" { $formattedValue = Format-StringValue($fieldValue)} "Integer" {$formattedValue = $fieldValue} "Lookup" {$formattedValue = Format-LookupValue($fieldValue) } "MultiChoice" {$formattedValue = Format-StringValue($fieldValue)} "Note" {$formattedValue = Format-StringValue($fieldValue)} "Number" {$formattedValue = $fieldValue} "Text" {$formattedValue = Format-StringValue($fieldValue)} "URL" {$formattedValue = Format-StringValue($fieldValue)} "User" {$formattedValue = Format-UserValue($fieldValue) } #Check MMS Field "Invalid" { if($field.TypeDisplayName -eq "Managed Metadata") { $formattedValue = Format-MMSValue($fieldValue) } else { $formattedValue =Format-StringValue($fieldValue)} } default {$formattedValue = Format-StringValue($fieldValue)} } Return $formattedValue } #EndRegion './Private/Get-ColumnValue.ps1' 30 #Region './Private/Get-ConnectedSharepointSiteProperties.ps1' 0 # retrieve the name of the last connected SharePoint site function Get-ConnectedSharePointSiteProperties { return $Script:connectedSharePointSiteProperties } #EndRegion './Private/Get-ConnectedSharepointSiteProperties.ps1' 7 #Region './Private/Save-ConnectedSharepointSiteProperties.ps1' 0 $Script:connectedSharePointSite = @{ SharePointSiteName = $null SharePointOrgRootUrl = $null } function Save-ConnectedSharePointSiteProperties { param( [Parameter(Mandatory)] [string]$SharePointSiteName, [Parameter(Mandatory)] [string]$SharePointOrgRootUrl ) $Script:connectedSharePointSiteProperties = @{ SharePointSiteName = $SharePointSiteName SharePointOrgRootUrl = $SharePointOrgRootUrl } } #EndRegion './Private/Save-ConnectedSharepointSiteProperties.ps1' 18 #Region './Public/Add-DataFromSharepointToSQL.ps1' 0 #Insert Data from SharePoint List to SQL Table function Add-DataFromSharePointToSQL($logFile,$dbConnection,$listFields,$listItems) { #Progress bar counter $counter=0 $listItemCount=$listItems.Count Write-Output "Total SharePoint List Items to Copy: $($listItemCount)" foreach ($item in $listItems) { Write-Progress -Activity "Copying SharePoint list items. Please wait...`n`n" -status "Processing list item: $($item['ID'])" -percentComplete ($counter/$ListItemCount*100) $sql = new-object System.Text.StringBuilder [void]$sql.Append("INSERT INTO [dbo].[$($tableName)] ( [ID] ") $vals = new-object System.Text.StringBuilder [void]$vals.Append("VALUES ("+ $item["ID"]) foreach ($field in $listFields) { $fieldInternalName = $field.InternalName $fieldTitle = $Field.Title.replace(' ','') # remove whitespace so that column names have no spaces [void]$sql.Append(",[$($fieldTitle)]") $fieldValue = $item[$fieldInternalName] $columnValue = Get-ColumnValue $field $fieldValue [void]$vals.Append( ","+ $columnValue) } [void]$sql.Append(") ") [void]$vals.Append(") ") #Combine Field and Values $SQLStatement = $sql.ToString() + $vals.ToString() #Run the Query Invoke-SQLQuery -logFile $logFile -dbConnection $dbConnection -Query $SQLStatement | Out-Null $Counter=$Counter+1; } "Total SharePoint List Items Copied: $($listItemCount)" >> $logFile } #EndRegion './Public/Add-DataFromSharepointToSQL.ps1' 43 #Region './Public/Add-ForeignKeyConstraints.ps1' 0 #function to create all foreign key constraints from the database function Add-ForeignKeyConstraints($logFile,$dbConnection) { $Query = "exec dbo.AddAllForeignKeyConstraints" #Run the Query # Write-Output $Query Invoke-SQLQuery -logFile $logFile -dbConnection $dbConnection -Query $Query | Out-Null } #EndRegion './Public/Add-ForeignKeyConstraints.ps1' 10 #Region './Public/Add-SQLTable.ps1' 0 #Create SQL Server table for SharePoint List function Add-SQLTable($logFile,$dbConnection,$listFields) { #Check if the table exists already $Query="IF (OBJECT_ID('[dbo].[$($TableName)]','U') IS NULL) CREATE TABLE [dbo].[$($TableName)]([ID] [int] NOT NULL PRIMARY KEY, " foreach ($field in $listFields) { $Query += Get-ColumnDefinition -Field $field $Query += "," } $Query += ")" #Run the Query Invoke-SQLQuery -logFile $logFile -dbConnection $dbConnection -Query $Query | Out-Null } #EndRegion './Public/Add-SQLTable.ps1' 16 #Region './Public/Confirm-ElapsedTimeHasExpired.ps1' 0 # retrieves a time value stored in an Azure Automation Value and returns true if that value is # older than the days/hours/minutes values provided # e.g. Confirm-ElapsedTimeHasExpired -varName $varName -days 0 -hours 0 -minutes 1 will return true # if the value stored in $varName is older than 1 minute. # If the time has elapsed, the current date is stored in the Azure Automation Value and the function # will return false until another 1 minute has elapsed. This function is typically used to limit how # often error exception emails are sent so that we don't exceed our limit in Twilio. If we have a # problem with a runbook and that runbook executes every 30 minutes we can easily end up with 48 emails # (or more over a weekend!) before the problem is resolved. function Confirm-ElapsedTimeHasExpired([string]$varName,[int]$days,[int]$hours,[int]$minutes) { $timeHasExpired = $false [dateTime]$dateNow = Get-Date $dateNow = $dateNow.ToUniversalTime() $savedVar = Get-AutomationVariableValue $varName if ($null -ne $savedVar) { [dateTime]$savedDate = $savedVar $savedDate = $savedDate.ToUniversalTime() } if ($null -eq $savedDate) { $ignore = newAutomationVariable -Name $varName -Value $utcValue } else { $expiryDate = $savedDate $expiryDate = $expiryDate.AddDays($days) $expiryDate = $expiryDate.AddHours($hours) $expiryDate = $expiryDate.AddMinutes($minutes) #$timeBeforeNextExpiry = New-TimeSpan -Start $dateNow -End $expiryDate } #Write-Output "savedDate = $($savedDate) (UTC)" #Write-Output "expiryDate = $($expiryDate) (UTC)" #Write-Output "dateNow = $($dateNow) (UTC)" #Write-Output "timeBeforeNextExpiry = $($timeBeforeNextExpiry.Hours):$($timeBeforeNextExpiry.Minutes):$($timeBeforeNextExpiry.Seconds)" if (($null-eq $savedDate) -or ($dateNow -ge $expiryDate)) { $timeHasExpired = $true Set-AutomationVariableValue -Name $varName -Value $dateNow } return $timeHasExpired } #EndRegion './Public/Confirm-ElapsedTimeHasExpired.ps1' 48 #Region './Public/Confirm-ScriptIsRunningInAzure.ps1' 0 # identifies whether the script is running on a user#s desktop computer on in Azure function Confirm-ScriptIsRunningInAzure { $runningInAzure = $false if ($env:AZUREPS_HOST_ENVIRONMENT -or $PSPrivateMetadata.JobId) { # Not local $runningInAzure = $true } return $runningInAzure } #EndRegion './Public/Confirm-ScriptIsRunningInAzure.ps1' 12 #Region './Public/Connect-Azure.ps1' 0 # connect to Azure if not already connected (or if connected user is not in the required domain function Connect-Azure([Parameter(Mandatory)] [string]$requiredUserDomain) { if (Confirm-ScriptIsRunningInAzure -eq $true) { # no need to do this if running as a runbook in Azure } else { $context = Get-AzContext if (!$context -or !($context.Account.Id -like "*$($requiredUserDomain)")) { $context = Connect-AzAccount if ($null -eq $context) { throw "Failed to establish an Azure connection" } else { $context = Get-AzContext if (!($context.Account.Id -like "*$($requiredUserDomain)")) { throw "Connected user must be in the $($requiredUserDomain) domain" } else { Write-Output "Azure connection successfully established" } } } else { Write-Output "Azure connection already established" } } } #EndRegion './Public/Connect-Azure.ps1' 38 #Region './Public/Connect-Exchange.ps1' 0 # establishes a connection to Exchange 365 function Connect-Exchange { $azureProperties = Get-AzureProperties $azureCertificateProperties = Get-AzureCertificateProperties Connect-ExchangeOnline ` -CertificateThumbPrint $azureCertificateProperties.thumbPrint ` -AppID $azureCertificateProperties.appId ` -Organization $azureProperties.organisationDomain Write-Output "Connection to Exchange was successful" } #EndRegion './Public/Connect-Exchange.ps1' 14 #Region './Public/Connect-SharepointSite.ps1' 0 # establishes a connection to a particular SharePoint site. The site will correspond to one of the teams # configured in Microsoft Teams function Connect-SharePointSite { param( [Parameter(Mandatory)] [string]$SharePointOrgRootUrl, [Parameter(Mandatory)] [string]$SharePointSiteName ) $azureProperties = Get-AzureProperties $azureCertificateProperties = Get-AzureCertificateProperties $SharePointSite = "$($SharePointOrgRootUrl)/$($SharePointSiteName)/" Connect-PnpOnline ` -Url $SharePointSite ` -Tenant $azureProperties.organisationDomain ` -ClientId $azureCertificateProperties.appId ` -ThumbPrint $azureCertificateProperties.thumbPrint # save the site name so we can reconnect if we lose connection context, e.g. whilst retrieving items from a SharePoint list Save-ConnectedSharePointSiteProperties -SharePointSiteName $SharePointSiteName -SharePointOrgRootUrl $SharePointOrgRootUrl # TODO: move Output line to calling script # Write-Output "Connection to SharePoint site $($SharePointSiteName) was successful" } #EndRegion './Public/Connect-SharepointSite.ps1' 26 #Region './Public/Convert-HashMapToStringValuePairs.ps1' 0 # converts a map into a string of key value pairs - which makes it easier to save to an Azure automation variable function Convert-HashMapToStringValuePairs { param($hashMap) $hashMapStringData = [string]($hashMap.GetEnumerator()|%{"$($_.Key)=$($_.Value);"}) return $hashMapStringData } #EndRegion './Public/Convert-HashMapToStringValuePairs.ps1' 9 #Region './Public/Disconnect-Exchange.ps1' 0 # disconnects any existing connection to Exchange 365 function Disconnect-Exchange { Disconnect-ExchangeOnline -Confirm:$false } #EndRegion './Public/Disconnect-Exchange.ps1' 6 #Region './Public/Disconnect-SharepointSite.ps1' 0 # disconnects any existing connection to SharePoint 365 function Disconnect-SharePointSite { Disconnect-PnpOnline } #EndRegion './Public/Disconnect-SharepointSite.ps1' 6 #Region './Public/Get-AutomationVariableValue.ps1' 0 # retrieve the value of an Azure automation variable - the function encapsulates the different mechanisms based upon whether the script # is running in Azure or on a user's desktop function Get-AutomationVariableValue { param($Name) $variableValue = $null if (Confirm-ScriptIsRunningInAzure -eq $true) { $variableValue = Get-AutomationVariable -Name $Name } else { try { $azureProperties = Get-AzureProperties $variableObj = Get-AzAutomationVariable -Name $Name -ResourceGroupName $azureProperties.resourceGroupName -AutomationAccountName $azureProperties.automationAccountName if ($null -ne $variableObj) { $variableValue = $variableObj.Value } } catch { # ignore } } return $variableValue } #EndRegion './Public/Get-AutomationVariableValue.ps1' 31 #Region './Public/Get-DbConnection.ps1' 0 # function to encapsulate connecting to a server instance or an Azure instance of a database function Get-DbConnection { param ($azureSqlServer,$applicationDatabase) $numTries = 2 $retryCount = 0 do { if ($azureSqlServer -like "*.database.windows.net") # i.e. the server is an azure SQL database { if (Confirm-ScriptIsRunningInAzure -eq $true) { # this uses the example from: https://jaliyaudagedara.blogspot.com/2022/01/azure-automation-runbook-execute-sql.html # Getting AccessToken for System assigned Managed Identity $Resource = "https://database.windows.net/" $QueryParameter = "?resource=$Resource" $Url = $env:IDENTITY_ENDPOINT + $QueryParameter $Headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" $Headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER) $Headers.Add("Metadata", "True") $Content =[System.Text.Encoding]::Default.GetString((Invoke-WebRequest ` -UseBasicParsing ` -Uri $Url ` -Method 'GET' ` -Headers $Headers).RawContentStream.ToArray()) | ConvertFrom-Json $AccessToken = $Content.access_token # PowerShell/ADO.NET Connected Architecture $SqlConnection = New-Object System.Data.SqlClient.SQLConnection $SqlConnection.ConnectionString = "Server=$azureSqlServer;Initial Catalog=$applicationDatabase;Connect Timeout=30" $SqlConnection.AccessToken = $AccessToken } else { $accessToken = (Get-AzAccessToken -ResourceUrl https://database.windows.net).Token $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=$azureSqlServer;Initial Catalog=$applicationDatabase;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30" $SqlConnection.AccessToken = $accessToken } } else { if (Confirm-ScriptIsRunningInAzure -eq $true) { throw "can't connect to local db $($azureSqlServer) from Azure RunBook" # there is no point in retrying to open a database in this case $retryCount = $numTries } else { $SqlConnection = New-Object System.Data.SqlClient.SqlConnection("Server=$azureSqlServer;Initial Catalog=$applicationDatabase;Integrated Security=True;") } } try { $SqlConnection.Open() $retryCount = $numTries } catch { $retryCount++ if ($retryCount -lt $numTries) { Start-Sleep -Seconds 10 } else { throw $_ } } } while ($retryCount -lt $numTries) return $SqlConnection } #EndRegion './Public/Get-DbConnection.ps1' 78 #Region './Public/Get-HashMapFromStringValuePairs.ps1' 0 # converts a string of key value pairs back to a hash map function Get-HashMapFromStringValuePairs { param($stringValuePairs) $hashMap = @{} $listOfStringValuePairs = $stringValuePairs.Split(";") foreach($stringValuePair in $listOfStringValuePairs) { if ($null -ne $stringValuePair) { $values = $stringValuePair.Split("=") if ($null -ne $values) { $keyName = $values[0] $value = $values[1] if ($null -ne $value) { $hashMap[$keyName.Trim()] = $value.Trim() } } } } return $hashMap } #EndRegion './Public/Get-HashMapFromStringValuePairs.ps1' 26 #Region './Public/Get-TwilioApiKey.ps1' 0 # get the API key for Twilio SendGrid to be able to send emails function Get-TwilioApiKey { if ($null -eq $Script:twilioSendGridApiKey) { throw "Twilio api key is null - Set-TwilioApiKey must be invoked to set this" } return $Script:twilioSendGridApiKey } #EndRegion './Public/Get-TwilioApiKey.ps1' 11 #Region './Public/Get-XeroTenantId.ps1' 0 # obtains the tenantId from Xero - this needs to be passed to Xero with the access token when making API requests function Get-XeroTenantId { param($tenantName,$accessToken) $headers = @{ "Authorization"="Bearer $($accessToken)" } $tenantId = $null $connectionsUrl = "https://api.xero.com/connections" # Note: this would need to change if there were more than one tenant associated with the account $response = Invoke-RestMethod -Method GET -Uri $connectionsUrl -Headers $headers if ($response.tenantName -eq $tenantName) { $tenantName = $response.tenantName $tenantId = $response.tenantId } else { Write-Output "Error - can't find Xero connection called $($tenantName)" } return $tenantId } #EndRegion './Public/Get-XeroTenantId.ps1' 26 #Region './Public/Invoke-ExportSharepointListToSQLTable.ps1' 0 function Invoke-ExportSharePointListToSQLTable($logFile,$dbConnection,$listName) { $tableName = $listName.replace(' ','') $allFields = Get-PnpField -List $ListName #include Created and Modified fields #$fields = $allFields | Where { ($_.Hidden -ne $true ) -and ($_.ReadOnlyField -ne $true ) -and ($_.InternalName -ne "Attachments") -and ($_.InternalName -ne "ContentType") } $fields = $allFields | Where-Object { ($_.Hidden -ne $true ) -and (($_.ReadOnlyField -ne $true) -or (($_.ReadOnlyField -eq $true)-and (($_.Title -eq "Created") -or ($_.Title -eq "Modified"))) ) -and ($_.InternalName -ne "Attachments") -and ($_.InternalName -ne "ContentType") } $fieldNameList = New-Object System.Collections.ArrayList foreach ($field in $fields) { $fieldNameList.Add($field.InternalName) | Out-Null } $listItems = Read-SharePointListItems -SharePointListName $listName -fieldsToReturn $fieldNameList if ($null -ne $listItems) { #Call functions to read SharePoint list and wrote contents to an SQL table try { Write-Output "Beginning export of $($listName) SharePoint data to SQL table $($tableName)..." Remove-SQLTable $logFile $dbConnection $tableName Add-SQLTable -logFile $logFile -dbConnection $dbConnection -listFields $fields Add-DataFromSharePointToSQL -logFile $logFile -dbConnection $dbConnection -listFields $fields -listItems $listItems Write-Output "Export of $($listName) SharePoint data to SQL table $($tableName) is complete" } catch { Write-Output "Exception during SQL operation: $($_.Exception.Message)" >> $logFile Write-Output "Exception during SQL operation: $($_.Exception.Message)" } } else { Write-Output "Ignoring export of $($listName) as there are no items in the list" } } #EndRegion './Public/Invoke-ExportSharepointListToSQLTable.ps1' 42 #Region './Public/Invoke-ReportExceptionViaEmail.ps1' 0 # and use some accessor functions instead - and check that accessors # have been populated at start of every runbook script function Invoke-ReportExceptionViaEmail { param($fromAddress,$toAddress,$scriptName,$exceptionDetails,$fileName,$attachment,$attachmentType) # Retrieve the Xero client id from an Azure automation variable $twilioSendGridApiKey = Get-TwilioApiKey $varName = "TimeOfLastErrorEmail_$($scriptName)" $hasExpired = Confirm-ElapsedTimeHasExpired -varName $varName -days 1 if ($hasExpired -eq $false) { Write-Output "The time since the last error email has not expired - forced into test mode so email is NOT sent, to limit the load on Twilio" $testMode = $true } elseif (Confirm-ScriptIsRunningInAzure -eq $true) { # script is running in Azure $TestMode = $false } else { $TestMode = $true } $Parameters = @{ FromAddress = $fromAddress ToAddress = $toAddress Subject = "Azure Runbook script exception: $scriptName" Body = "An error was found with the Azure Runbook ""$($scriptName)"". Error details are given below:<BR>$($exceptionDetails)<BR>$($exceptionDetails.ScriptStackTrace)" APIKey = $twilioSendGridApiKey FileName = $fileName FileNameWithFilePath = $attachment AttachmentType = $attachmentType TestMode = $TestMode } Invoke-SendGridMailWithAttachment @Parameters } #EndRegion './Public/Invoke-ReportExceptionViaEmail.ps1' 41 #Region './Public/Invoke-SendGridMailWithAttachment.ps1' 0 # TODO: make sure this is consistent with all references in all current scripts - there may be small differences # use Twilio SendGrid to send an email with an optional file attachment function Invoke-SendGridMailWithAttachment { param ( [cmdletbinding()] [parameter()] [string]$ToAddress, [parameter()] [string]$FromAddress, [parameter()] [string]$Subject, [parameter()] [string]$Body, [parameter()] [string]$APIKey, [parameter()] [string]$FileName, [parameter()] [string]$FileNameWithFilePath, [parameter()] [string]$AttachmentType, [parameter()] [bool]$TestMode ) if ($testMode -eq $true) { Write-Output "Simulating the sending of an email (testMode is true: the actual email will NOT be sent)" } else { Write-Output "Sending email..." } Write-Output "From: $($FromAddress)" Write-Output "To: $($ToAddress)" Write-Output "Subject: $($Subject)" Write-Output "Body: $($Body)" if (($FileNameWithFilePath -ne $null) -and ($FileNameWithFilePath.Length -gt 0)) { Write-Output "Attachment: $FileName" if ($testMode -eq $true) { Write-Output "Last 10 lines of attachment:" Get-Content $FileNameWithFilePath -Tail 10 } } else { Write-Output "Attachment: None" } if ($testMode -eq $true) { return } #Convert File to Base64 #Note: the attachment here is UTF format, so we need to take a different approach to reading the file # than that for the executive report generator script if ($FileNameWithFilePath.Length -gt 0) { $FileContent = get-content -Path $FileNameWithFilePath -Encoding Byte $EncodedFile = [System.Convert]::ToBase64String($FileContent) <# This used to be the technique used - since Sept 2033, the above technique works when running in or out of Azure if (Confirm-ScriptIsRunningInAzure -eq $true) { $FileContent = get-content $FileNameWithFilePath -Raw $ConvertToBytes = [System.Text.Encoding]::Unicode.GetBytes($FileContent) $EncodedFile = [System.Convert]::ToBase64String($ConvertToBytes) } else { $FileContent = get-content -Path $FileNameWithFilePath -Encoding Byte $EncodedFile = [System.Convert]::ToBase64String($FileContent) } #> } # this can be used to test if we can write the file back to its original format #$bytes = [Convert]::FromBase64String($EncodedFile) #[system.io.file]::WriteAllBytes('test.pdf',$bytes) # Body with attachment for SendGrid if ($FileNameWithFilePath.Length -gt 0) { $SendGridBody = @{ "personalizations" = @( @{ "to"= @( @{ "email" = $ToAddress } ) "subject" = $Subject } ) "content"= @( @{ "type" = "text/html" "value" = $Body } ) "from" = @{ "email" = $FromAddress } "attachments" = @( @{ "content"=$EncodedFile "filename"=$FileName "type"= $AttachmentType "disposition"="attachment" } ) } } else { $SendGridBody = @{ "personalizations" = @( @{ "to"= @( @{ "email" = $ToAddress } ) "subject" = $Subject } ) "content"= @( @{ "type" = "text/html" "value" = $Body } ) "from" = @{ "email" = $FromAddress } } } $BodyJson = $SendGridBody | ConvertTo-Json -Depth 4 #Header for SendGrid API $Header = @{ "authorization" = "Bearer $APIKey" } #Send the email through SendGrid API $Parameters = @{ Method = "POST" Uri = "https://api.sendgrid.com/v3/mail/send" Headers = $Header ContentType = "application/json" Body = $BodyJson } Invoke-RestMethod @Parameters Write-Output "Done sending email" } #EndRegion './Public/Invoke-SendGridMailWithAttachment.ps1' 162 #Region './Public/Invoke-SQLProcedure.ps1' 0 #function to Execute a SQL Stored procedure function Invoke-SQLProcedure($logFile,$dbConnection,[string]$Query) { #Write-Output "Executing SQL: ", $Query $Query >> $logFile $cmd = new-object System.Data.SqlClient.SqlCommand ($Query, $dbConnection) $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $cmd $dataset = New-Object System.Data.DataSet $adapter.Fill($dataSet) | Out-Null return $dataset } #EndRegion './Public/Invoke-SQLProcedure.ps1' 13 #Region './Public/Invoke-SQLQuery.ps1' 0 #function to Execute given SQL Query function Invoke-SQLQuery($logFile,$dbConnection,[string]$Query) { # diagnostics are commented out #Write-Output "Executing SQL: ", $Query $Query >> $logFile $cmd = new-object System.Data.SqlClient.SqlCommand ($Query, $dbConnection) $ReturnValue = $cmd.ExecuteNonQuery() return $ReturnValue } #EndRegion './Public/Invoke-SQLQuery.ps1' 13 #Region './Public/Invoke-StripCRLF.ps1' 0 # strip any carriage return and line feed characters from a string function Invoke-StripCRLF { param($stringToStrip) if ($null -ne $stringToStrip) { $stringToStrip = $stringToStrip.Replace("`r","") $stringToStrip = $stringToStrip.Replace("`n","") } return $stringToStrip } #EndRegion './Public/Invoke-StripCRLF.ps1' 14 #Region './Public/New-AutomationVariable.ps1' 0 # creates a new Azure automation variable - the function encapsulates the different mechanisms based upon whether the script # is running in Azure or on a user's desktop function New-AutomationVariable { param($Name,$Value) if (Confirm-ScriptIsRunningInAzure -eq $true) { # can't create in Azure automation throw "Automation variable $($Name) does not exist - it needs to be created by first running this runbook script on a desktop computer" } else { $azureProperties = Get-AzureProperties $variableValue = New-AzAutomationVariable -encrypted $false -Name $Name -Value $Value -ResourceGroupName $azureProperties.resourceGroupName -AutomationAccountName $azureProperties.automationAccountName } return $variableValue } #EndRegion './Public/New-AutomationVariable.ps1' 19 #Region './Public/Read-SharepointListItems.ps1' 0 # TODO: make sure that all references to this use same code - there may be multiple variants in scripts function Read-SharePointListItems { param( [Parameter(Mandatory)][string]$SharePointListName, [Parameter(Mandatory)][object]$fieldsToReturn, [object]$queryDateFrom = $null, [object]$queryDateTo = $null, [string]$queryTimeColumnName = $null, [bool]$includeTimeValue = $true ) if ($includeTimeValue -eq $true) { $includeTimeValueStr = "TRUE" } else { $includeTimeValueStr = "FALSE" } $viewFields = ""; foreach ($fieldName in $fieldsToReturn) { $viewFields += "<FieldRef Name='$($fieldName)'/>" } if ($null -eq $queryTimeColumnName -or $queryTimeColumnName.Length -eq 0) { $queryTimeColumnName = "Modified" } if ($null -eq $queryDateFrom) { $queryDateFromString = "1970-01-01:00:00:00Z" } else { $queryDateFromString = $queryDateFrom.GetDateTimeFormats("s") } if ($null -eq $queryDateTo) { $queryDateToString = (Get-Date).GetDateTimeFormats("s") } else { $queryDateToString = $queryDateTo.GetDateTimeFormats("s") } $dateFilter = " ` <Where> ` <And> ` <Geq> ` <FieldRef Name='$($queryTimeColumnName)' /> ` <Value IncludeTimeValue='$($includeTimeValueStr)' Type='DateTime'>$queryDateFromString</Value> ` </Geq> ` <Leq> ` <FieldRef Name='$($queryTimeColumnName)' /> ` <Value IncludeTimeValue='$($includeTimeValueStr)' Type='DateTime'>$queryDateToString</Value> ` </Leq> ` </And> ` </Where>" $query = "<View><Query>$dateFilter</Query><ViewFields>$viewFields</ViewFields></View>" $numTries = 0 $maxTries = 2 do { try { $numTries++ $results = Get-PnPListItem -Connection $global:SharePointConnection -List $SharePointListName -Query $query | ForEach-Object { $_.FieldValues } $numTries = $maxTries } catch { if ($_.Exception.Message -like "*Connection holds no SharePoint context*") { $lastConnectedSharePointSiteProperties = Get-ConnectedSharePointSiteProperties # try to reconnect to the SharePoint site Connect-SharePointSite ` -SharePointSiteName $lastConnectedSharePointSiteProperties.SharePointSiteName ` -SharePointOrgRootUrl $lastConnectedSharePointSiteProperties.SharePointOrgRootUrl } else { throw $_ } } } while ($numTries -lt $maxTries) return $results } #EndRegion './Public/Read-SharepointListItems.ps1' 96 #Region './Public/Remove-ForeignKeyConstraints.ps1' 0 #function to drop all foreign key constraints from the database function Remove-ForeignKeyConstraints($logFile,$dbConnection) { $Query = "exec dbo.DeleteAllForeignKeyConstraints" #Run the Query # Write-Output $Query Invoke-SQLQuery -logFile $logFile -dbConnection $dbConnection -Query $Query | Out-Null } #EndRegion './Public/Remove-ForeignKeyConstraints.ps1' 10 #Region './Public/Remove-SQLTable.ps1' 0 #function to Drop Table, if exists! function Remove-SQLTable($logFile,$dbConnection,[string]$TableName) { $Query = "IF (OBJECT_ID('[dbo].[$($TableName)]','U') IS NOT NULL) DROP TABLE [dbo].[$($TableName)]" #Run the Query # Write-Output $Query # can be used for diagnosing issues Invoke-SQLQuery -logFile $logFile -dbConnection $dbConnection -Query $Query | Out-Null } #EndRegion './Public/Remove-SQLTable.ps1' 10 #Region './Public/Set-AutomationVariableValue.ps1' 0 # sets the value of an Azure automation variable - the function encapsulates the different mechanisms based upon whether the script # is running in Azure or on a user's desktop function Set-AutomationVariableValue { param([Parameter(Mandatory)][string]$Name,[string]$Value,[bool]$Encrypted = $false) if (Confirm-ScriptIsRunningInAzure -eq $true) { $variableValue = Set-AutomationVariable -Name $Name -Value $Value } else { $azureProperties = Get-AzureProperties $variableObj = Set-AzAutomationVariable -Encrypted $Encrypted -Name $Name -Value $Value -ResourceGroupName $azureProperties.resourceGroupName -AutomationAccountName $azureProperties.automationAccountName } } #EndRegion './Public/Set-AutomationVariableValue.ps1' 17 #Region './Public/Set-AzureCertificateProperties.ps1' 0 $Script:azureCertificateProperties = @{ appId = $null thumbprint = $null } function Set-AzureCertificateProperties { param( [Parameter(Mandatory)] [string]$appId, [Parameter(Mandatory)] [string]$certThumbprint ) $Script:azureCertificateProperties = @{ appId = $appId thumbprint = $certThumbPrint } } #EndRegion './Public/Set-AzureCertificateProperties.ps1' 18 #Region './Public/Set-AzureProperties.ps1' 0 $Script:azureProperties = @{ resourceGroupName = $null automationAccountName = $null organisationDomain = $null } function Set-AzureProperties { param( [Parameter(Mandatory)] [string]$resourceGroupName, [Parameter(Mandatory)] [string]$automationAccountName, [Parameter(Mandatory)] [string]$organisationDomain ) $Script:azureProperties = @{ resourceGroupName = $resourceGroupName automationAccountName = $automationAccountName organisationDomain = $organisationDomain } } #TODO: do we put variables in a variables section of this Visual Studio project? #EndRegion './Public/Set-AzureProperties.ps1' 24 #Region './Public/Set-TwilioApiKey.ps1' 0 $Script:twilioSendGridApiKey = $null # set an API key so that Twilio SendGrid can be used to send emails function Set-TwilioApiKey([Parameter(Mandatory)][string]$apiKey) { $Script:twilioSendGridApiKey = $apiKey } #EndRegion './Public/Set-TwilioApiKey.ps1' 8 |