Common.psm1
<#PSScriptInfo .VERSION 1.0 .GUID 2c1a2f98-e781-403c-9c81-85b736767abc .AUTHOR RaviCKolandaiswamy .COMPANYNAME .COPYRIGHT .TAGS .LICENSEURI .PROJECTURI .ICONURI .EXTERNALMODULEDEPENDENCIES .REQUIREDSCRIPTS .EXTERNALSCRIPTDEPENDENCIES .RELEASENOTES .PRIVATEDATA #> <# .DESCRIPTION Basic Functions for calling various APIs #> #Get public and private function definition files. #check PS version for this, PS 6 and above use -SkipCertificateCheck for Invoke-RestMethod if ($PSVersionTable.PSVersion.Major -lt 6) { #Ignore SSL errors If ($Null -eq ([System.Management.Automation.PSTypeName]'TrustAllCertsPolicy').Type) { Add-Type -Debug:$False @" using System.Net; using System.Security.Cryptography.X509Certificates; public class TrustAllCertsPolicy : ICertificatePolicy { public bool CheckValidationResult( ServicePoint srvPoint, X509Certificate certificate, WebRequest request, int certificateProblem) { return true; } } "@ } [System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy } #Enable TLS, TLS1.1, TLS1.2 in this session if they are available IF([Net.SecurityProtocolType]::Tls) {[Net.ServicePointManager]::SecurityProtocol=[Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls} IF([Net.SecurityProtocolType]::Tls11) {[Net.ServicePointManager]::SecurityProtocol=[Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls11} IF([Net.SecurityProtocolType]::Tls12) {[Net.ServicePointManager]::SecurityProtocol=[Net.ServicePointManager]::SecurityProtocol -bor [Net.SecurityProtocolType]::Tls12} $currentDirectory = Get-Location Import-Module $currentDirectory\Helpers.psm1 -Force function Invoke-RestApiRequest { [CmdletBinding(DefaultParameterSetName = 'EndpointName')] param ( [Parameter(ParameterSetName = 'Endpoint')] $url, [Parameter(ParameterSetName = 'Endpoint')] $user, [Parameter(ParameterSetName = 'Endpoint')] $pass, [Parameter(ParameterSetName = 'EndpointName')] $endpointName, [Parameter(ParameterSetName = 'EndpointName')] [Parameter(ParameterSetName = 'Endpoint')] $relativeUrl, [Parameter(ParameterSetName = 'EndpointName')] [Parameter(ParameterSetName = 'Endpoint')] $jsonBody, [Parameter(ParameterSetName = 'EndpointName')] [Parameter(ParameterSetName = 'Endpoint')] $method = 'GET' ) if ($PSCmdlet.ParameterSetName -eq 'EndpointName') { $connection = Get-ApiConnection $endpointName $url = $connection.ApiEndpointUrl $user = $connection.ApiUser $pass = $connection.ApiKey } $decodedCreds = Invoke-Decode $pass #[Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($pass)) $pair = "$($user):$($decodedCreds)" $encodedCreds = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes($pair)) $basicAuthValue = "Basic $encodedCreds" $Headers = @{ Authorization = $basicAuthValue } $params = @{ Uri = $url.TrimEnd('/') + '/' + $relativeUrl.TrimStart('/') Headers = $Headers Method = $method Body = $jsonBody ContentType = 'application/json' } Write-Output "URL: $($params.Uri)" if($PSVersionTable.PSVersion.Major -GT 5) { return Invoke-RestMethod @params -SkipCertificateCheck } else { Write-Verbose -Message "Note: SkipCertificateCheck for Invoke-RestMethod supported only from PS 6 and above." return Invoke-RestMethod @params } } function Invoke-Decode { param($text) return [Runtime.InteropServices.Marshal]::PtrToStringAuto([Runtime.InteropServices.Marshal]::SecureStringToBSTR($text)) } function Add-ApiConnection { $name = Read-Host 'API Connection Name' $url = Read-Host 'API Endpoint Url' $user = Read-Host 'API User' $key = Read-Host "API Key" -AsSecureString $enc = $key | ConvertFrom-SecureString $apiConnection = @{ Name = $name ApiEndpointUrl = $url ApiUser = $user ApiKey = $enc } $json = ConvertTo-Json $apiConnection $json | Out-File "$name.api.dat" } function Get-ApiConnection { param ( $name ) if (!(Test-Path $name)) { $fileName = "$name.api.dat" } else { $fileName = $name } if (!(Test-Path $fileName)) { throw "Local Endpoint data file does not exist for the given connection name: $fileName" } $connectionData = Get-Content $fileName | ConvertFrom-Json $url = $connectionData.psobject.Properties['ApiEndpointUrl'].Value $user = $connectionData.psobject.Properties['ApiUser'].Value $enc = $connectionData.psobject.Properties['ApiKey'].Value $key = $enc | ConvertTo-SecureString $connection = @{ Name = $connectionData.Name ApiEndpointUrl = $url ApiUser = $user ApiKey = $key } return $connection } function Add-HostConnection { $name = Read-Host 'Connection Name' $hostName = Read-Host 'Host Name (Machine Name or IP)' $creds = Get-Credential $user = $creds.UserName $encPassword = $creds.Password | ConvertFrom-SecureString try { $portInput = Read-Host 'WMI Port (Ex: 5985)' $port = [System.Convert]::ToInt($portInput) } catch { $port = 5985 } try { $sslInput = Read-Host 'WMI Is Use SSL ? (yes / no)?' $isUseSsl = [System.Convert]::ToBoolean($sslInput) } catch { $isUseSsl = $False } $hostConnection = @{ Name = $name HostName = $hostName User = $user Password = $encPassword Port = $port IsUseSsl = $isUseSsl } $json = ConvertTo-Json $hostConnection $json | Out-File "$name.host.dat" } function Get-HostConnection { param ( $name ) if (!(Test-Path $name)) { $fileName = "$name.host.dat" } else { $fileName = $name } if (!(Test-Path $fileName)) { throw "Local Host data file does not exist for the given connection name: $fileName" } $connectionData = Get-Content $fileName | ConvertFrom-Json $hostName = $connectionData.psobject.Properties['HostName'].Value $port= [int] ($connectionData.psobject.Properties['Port'].Value) $isUseSsl= [bool] ($connectionData.psobject.Properties['IsUseSsl'].Value) $user = $connectionData.psobject.Properties['User'].Value $enc = $connectionData.psobject.Properties['Password'].Value $key = $enc | ConvertTo-SecureString $connection = @{ Name = $connectionData.Name HostName = $hostName User = $user Password = $key Port = $port IsUseSsl = $isUseSsl } return $connection } function Add-SqlDbConnection { $name = Read-Host 'Connection Name' $dbServerName = Read-Host 'DB Server Name' $user = Read-Host 'DB User' $pass = Read-Host "DB Password" -AsSecureString $dbName = Read-Host 'DB Name (optional)' $encPassword = $pass | ConvertFrom-SecureString $sqlDbConnection = @{ Name = $name DbServerName = $dbServerName User = $user Password = $encPassword DbName = $dbName } $json = ConvertTo-Json $sqlDbConnection $json | Out-File "$name.sqldb.dat" } function Get-SqlDbConnection { param ( $name ) if (!(Test-Path $name)) { $fileName = "$name.sqldb.dat" } else { $fileName = $name } if (!(Test-Path $fileName)) { throw "Local SQL DB data file does not exist for the given connection name: $fileName" } $connectionData = Get-Content $fileName | ConvertFrom-Json $dbServerName = $connectionData.psobject.Properties['DbServerName'].Value $dbName = $connectionData.psobject.Properties['DbName'].Value $user = $connectionData.psobject.Properties['User'].Value $enc = $connectionData.psobject.Properties['Password'].Value $pass = $enc | ConvertTo-SecureString $connection = @{ Name = $connectionData.Name DbServerName = $dbServerName User = $user Password = $pass DbName = $dbName } return $connection } function Add-SqlTransactionLogShrinkJob { param($connectionName, $backupPath, $purgeBackupOlderThanDays) if($connectionName -eq $null) { $connectionName = Read-Input -message "Enter SQL connection name" } if($backupPath -eq $null) { $backupPath = Read-Input -message "Enter backup folder path" } if($purgeBackupOlderThanDays -eq $null) { $purgeBackupOlderThanDays = Read-Input -message "Enter number of days to purge old backup" } $backupPath = $backupPath.Trim('\')+'\' $sqlDbConn = Get-SqlDbConnection $connectionName $decoded = Invoke-Decode $sqlDbConn.Password $hybrDb = "Hybr"; $billingDb = "CloudAssert.Billing"; $vconnectDb = "CloudAssert.Vconnect"; $dacmDb = "CloudAssert.Dacm"; if(!(Get-YesOrNoUserResponse -message "Is Hybr database name - $hybrDb ?")) { $hybrDb = Read-Input -message "Enter Hybr database name" } if(!(Get-YesOrNoUserResponse -message "Is Billing database name - $billingDb ?")) { $billingDb = Read-Input -message "Enter Billing database name" } if(!(Get-YesOrNoUserResponse -message "Is VConnect database name - $vconnectDb ?")) { $vconnectDb = Read-Input -message "Enter VConnect database name" } if(!(Get-YesOrNoUserResponse -message "Is Cost Management database name - $dacmDb ?")) { $dacmDb = Read-Input -message "Enter Cost Management database name" } $query = "IF EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name='shrink_cloudassert_logs') BEGIN EXEC msdb.dbo.sp_delete_job @job_name = N'shrink_cloudassert_logs', @delete_unused_schedule = 1 END IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name='shrink_cloudassert_logs') BEGIN EXEC msdb.dbo.sp_add_job @job_name = N'shrink_cloudassert_logs', @description = N'shrink_cloudassert_logs Description'; EXEC msdb.dbo.sp_add_jobstep @job_name = N'shrink_cloudassert_logs', @step_name = N'backup_vconnect_db', @subsystem = N'TSQL', @command = N' DECLARE @result INT DECLARE @path nvarchar(500) = ''${backupPath}\${vconnectDb}.bak'' EXEC master.dbo.xp_fileexist @path ,@result OUTPUT IF (DB_ID(''$vconnectDb'') IS NOT NULL AND @result = 1) Begin BACKUP DATABASE [$vconnectDb] TO DISK = @path With Differential; End Else Begin BACKUP DATABASE [$vconnectDb] TO DISK = @path End', @on_success_action = 3, @database_name = 'master'; EXEC msdb.dbo.sp_add_jobstep @job_name = N'shrink_cloudassert_logs', @step_name = N'backup_vconnect_log', @subsystem = N'TSQL', @command = N' DECLARE @result INT DECLARE @path nvarchar(500) = ''${backupPath}\${vconnectDb}.TRN'' EXEC master.dbo.xp_fileexist @path ,@result OUTPUT IF (DB_ID(''$vconnectDb'') IS NOT NULL) Begin BACKUP LOG [$vconnectDb] TO DISK = @path; End', @on_success_action = 3, @database_name = 'master'; EXEC msdb.dbo.sp_add_jobstep @job_name = N'shrink_cloudassert_logs', @step_name = N'shrink_vconnect_log', @subsystem = N'TSQL', @command = N'IF DB_ID(''$vconnectDb'') IS NOT NULL Begin USE [$vconnectDb] DBCC SHRINKFILE (''${vconnectDb}_log'',0, TRUNCATEONLY) END', @on_success_action = 3, @database_name = 'master'; EXEC msdb.dbo.sp_add_jobstep @job_name = N'shrink_cloudassert_logs', @step_name = N'backup_billing_db', @subsystem = N'TSQL', @command = N' DECLARE @result INT DECLARE @path nvarchar(500) = ''${backupPath}\${billingDb}.bak'' EXEC master.dbo.xp_fileexist @path ,@result OUTPUT IF (DB_ID(''$billingDb'') IS NOT NULL AND @result = 1) Begin BACKUP DATABASE [$billingDb] TO DISK = @path With Differential; End Else Begin BACKUP DATABASE [$billingDb] TO DISK = @path End', @on_success_action = 3, @database_name = 'master'; EXEC msdb.dbo.sp_add_jobstep @job_name = N'shrink_cloudassert_logs', @step_name = N'backup_billing_log', @subsystem = N'TSQL', @command = N' DECLARE @result INT DECLARE @path nvarchar(500) = ''${backupPath}\${billingDb}.TRN'' EXEC master.dbo.xp_fileexist @path ,@result OUTPUT IF (DB_ID(''$billingDb'') IS NOT NULL) Begin BACKUP LOG [$billingDb] TO DISK = @path; End', @on_success_action = 3, @database_name = 'master'; EXEC msdb.dbo.sp_add_jobstep @job_name = N'shrink_cloudassert_logs', @step_name = N'shrink_billing_log', @subsystem = N'TSQL', @command = N'IF DB_ID(''$billingDb'') IS NOT NULL Begin USE [$billingDb] DBCC SHRINKFILE (''${billingDb}_log'',0, TRUNCATEONLY) END', @on_success_action = 3, @database_name = 'master'; EXEC msdb.dbo.sp_add_jobstep @job_name = N'shrink_cloudassert_logs', @step_name = N'backup_hybr_db', @subsystem = N'TSQL', @command = N' DECLARE @result INT DECLARE @path nvarchar(500) = ''${backupPath}\${hybrDb}.bak'' EXEC master.dbo.xp_fileexist @path ,@result OUTPUT IF (DB_ID(''$hybrDb'') IS NOT NULL AND @result = 1) Begin BACKUP DATABASE [$hybrDb] TO DISK = @path With Differential; End Else Begin BACKUP DATABASE [$hybrDb] TO DISK = @path End', @on_success_action = 3, @database_name = 'master'; EXEC msdb.dbo.sp_add_jobstep @job_name = N'shrink_cloudassert_logs', @step_name = N'backup_hybr_log', @subsystem = N'TSQL', @command = N' DECLARE @result INT DECLARE @path nvarchar(500) = ''${backupPath}\${hybrDb}.TRN'' EXEC master.dbo.xp_fileexist @path ,@result OUTPUT IF (DB_ID(''$hybrDb'') IS NOT NULL) Begin BACKUP LOG [$hybrDb] TO DISK = @path; End', @on_success_action = 3, @database_name = 'master'; EXEC msdb.dbo.sp_add_jobstep @job_name = N'shrink_cloudassert_logs', @step_name = N'shrink_hybr_log', @subsystem = N'TSQL', @command = N'IF DB_ID(''$hybrDb'') IS NOT NULL Begin USE [$hybrDb] DBCC SHRINKFILE (''${hybrDb}_log'',0, TRUNCATEONLY) END', @on_success_action = 3, @database_name = 'master'; EXEC msdb.dbo.sp_add_jobstep @job_name = N'shrink_cloudassert_logs', @step_name = N'backup_dacm_db', @subsystem = N'TSQL', @command = N' DECLARE @result INT DECLARE @path nvarchar(500) = ''${backupPath}\${dacmDb}.bak'' EXEC master.dbo.xp_fileexist @path ,@result OUTPUT IF (DB_ID(''$dacmDb'') IS NOT NULL AND @result = 1) Begin BACKUP DATABASE [$dacmDb] TO DISK = @path With Differential; End Else Begin BACKUP DATABASE [$dacmDb] TO DISK = @path End', @on_success_action = 3, @database_name = 'master'; EXEC msdb.dbo.sp_add_jobstep @job_name = N'shrink_cloudassert_logs', @step_name = N'backup_dacm_log', @subsystem = N'TSQL', @command = N' DECLARE @result INT DECLARE @path nvarchar(500) = ''${backupPath}\${dacmDb}.TRN'' EXEC master.dbo.xp_fileexist @path ,@result OUTPUT IF (DB_ID(''$dacmDb'') IS NOT NULL) Begin BACKUP LOG [$dacmDb] TO DISK = @path; End', @on_success_action = 3, @database_name = 'master'; EXEC msdb.dbo.sp_add_jobstep @job_name = N'shrink_cloudassert_logs', @step_name = N'shrink_dacm_log', @subsystem = N'TSQL', @command = N'IF DB_ID(''$dacmDb'') IS NOT NULL Begin USE [$dacmDb] DBCC SHRINKFILE (''${dacmDb}_log'',0, TRUNCATEONLY) END', @on_success_action = 3, @database_name = 'master'; EXEC msdb.dbo.sp_add_jobstep @job_name = N'shrink_cloudassert_logs', @step_name = N'purge_db_backup', @subsystem = N'TSQL', @command = N'DECLARE @path nvarchar(500) = ''$backupPath'' DECLARE @DeleteDateTime DATETIME SET @DeleteDateTime = DateAdd(DAY, -$purgeBackupOlderThanDays, GetDate()) EXECUTE master.dbo.xp_delete_file 0, @path, ''bak'', @DeleteDateTime, 0', @on_success_action = 3, @database_name = 'master'; EXEC msdb.dbo.sp_add_jobstep @job_name = N'shrink_cloudassert_logs', @step_name = N'purge_log_backup', @subsystem = N'TSQL', @command = N'DECLARE @path nvarchar(500) = ''$backupPath'' DECLARE @DeleteDateTime DATETIME SET @DeleteDateTime = DateAdd(DAY, -$purgeBackupOlderThanDays, GetDate()) EXECUTE master.dbo.xp_delete_file 0, @path, ''trn'', @DeleteDateTime, 0', @on_success_action = 1, @database_name = 'master'; EXEC msdb.dbo.sp_add_jobschedule @job_name = N'shrink_cloudassert_logs', @name = N'shrink_cloudassert_logs_SCHEDULE', @freq_type=4, @freq_interval=1, @freq_subday_type=1, @freq_subday_interval=0, @active_start_time=0, @active_end_time=235959; EXEC msdb.dbo.sp_add_jobserver @job_name = N'shrink_cloudassert_logs' END"; $dbName = 'master' Invoke-Sqlcmd -ServerInstance $sqlDbConn.DbServerName -Database $dbName -Username $sqlDbConn.User -Password "$decoded" -Query $query } function Invoke-SqlDbQuery { param($name, $query, $dbName, [Switch]$exportCsv, [Switch]$printToConsole, [ValidateScript({$_ -match "https\:\/\/cloudassertcustenvlogs\.blob.core.windows.net\/(.)*\?(.)*"})] $blobURL) $sqlDbConn = Get-SqlDbConnection $name $decoded = Invoke-Decode $sqlDbConn.Password if($dbName -eq $null) { $dbName = $sqlDbConn.DbName } if($dbName -eq $null) { $data = Invoke-Sqlcmd -ServerInstance $sqlDbConn.DbServerName -Username $sqlDbConn.User -Password "$decoded" -Query $query -OutputAs DataSet } else { $data = Invoke-Sqlcmd -ServerInstance $sqlDbConn.DbServerName -Database $dbName -Username $sqlDbConn.User -Password "$decoded" -Query $query -OutputAs DataSet } if($data.Tables.Count -eq 1) { if($exportCsv -or $blobURL -ne $null) { $dateTimeTicks = (Get-Date).Ticks $outFileName = "$dateTimeTicks.csv" Write-Host "Writing to File: $outFileName" $data = $data | Invoke-RemoveSensitiveDataFromDataSet $data.Tables[0] | Export-Csv -Path $outFileName -NoTypeInformation } if($blobURL -ne $null) { Add-FileToBlobStorage $outFileName $blobURL } if(!($exportCsv) -and $blobURL -eq $null) { if(!($printToConsole)) { $data.Tables[0] | Out-GridView } else { $data.Tables[0] } } } else { $data } } function Invoke-SqlDbQueryFromFile { param($name, $queryFile, $dbName, [Switch]$exportCsv, [Switch]$printToConsole, [ValidateScript({$_ -match "https\:\/\/cloudassertcustenvlogs\.blob.core.windows.net\/(.)*\?(.)*"})] $blobURL) $sqlDbConn = Get-SqlDbConnection $name $decoded = Invoke-Decode $sqlDbConn.Password if($dbName -eq $null) { $dbName = $sqlDbConn.DbName } $fileNameWotEx = [System.IO.Path]::GetFileNameWithoutExtension($queryFile) $machineName = [System.Net.DNS]::GetHostByName('').HostName $dateTimeTicks = (Get-Date).Ticks if($dbName -eq $null) { $dataSet = Invoke-Sqlcmd -ServerInstance $sqlDbConn.DbServerName -Username $sqlDbConn.User -Password "$decoded" -InputFile $queryFile -OutputAs DataSet } else { $dataSet = Invoke-Sqlcmd -ServerInstance $sqlDbConn.DbServerName -Database $dbName -Username $sqlDbConn.User -Password "$decoded" -InputFile $queryFile -OutputAs DataSet } if($dataSet -ne $null -and $dataSet.Tables.Count -gt 0) { if($exportCsv -or $blobURL -ne $null) { $dataSet = $dataSet | Invoke-RemoveSensitiveDataFromDataSet } foreach($table in $dataSet.Tables) { $outFileName = "$fileNameWotEx-$machineName-$($table.TableName)-$dateTimeTicks.csv" if($exportCsv -or $blobURL -ne $null) { $table | Export-Csv -Path $outFileName -NoTypeInformation Write-Host "Writing to File: $outFileName" } if($blobURL -ne $null) { Add-FileToBlobStorage $outFileName $blobURL } if(!($exportCsv) -and $blobURL -eq $null) { if(!($printToConsole)) { $table | Out-GridView } else { $table } } } } } function Invoke-SqlDbQueryFromBlob { param($name, [ValidateScript({$_ -match "https\:\/\/cloudassertenvscripts.blob.core.windows.net\/(.)*\.*"})] $queryUrl, $dbName, [Switch]$exportCsv, [Switch]$printToConsole, [ValidateScript({$_ -match "https\:\/\/cloudassertcustenvlogs.blob.core.windows.net\/(.)*\?(.)*"})] $blobURL) $sqlDbConn = Get-SqlDbConnection $name $decoded = Invoke-Decode $sqlDbConn.Password if($dbName -eq $null) { $dbName = $sqlDbConn.DbName } $fileNameFromUrl = [System.IO.Path]::GetFileNameWithoutExtension($queryUrl) $machineName = [System.Net.DNS]::GetHostByName('').HostName $dateTimeTicks = (Get-Date).Ticks $queryFile = [System.IO.Path]::GetFileName($queryUrl) Write-Host "Downloading $queryUrl to $queryFile" Invoke-WebRequest $queryUrl -outfile $queryFile if($dbName -eq $null) { $dataSet = Invoke-Sqlcmd -ServerInstance $sqlDbConn.DbServerName -Username $sqlDbConn.User -Password "$decoded" -InputFile $queryFile -OutputAs DataSet } else { $dataSet = Invoke-Sqlcmd -ServerInstance $sqlDbConn.DbServerName -Database $dbName -Username $sqlDbConn.User -Password "$decoded" -InputFile $queryFile -OutputAs DataSet } if($dataSet -ne $null -and $dataSet.Tables.Count -gt 0) { if($exportCsv -or $blobURL -ne $null) { $dataSet = $dataSet | Invoke-RemoveSensitiveDataFromDataSet } foreach($table in $dataSet.Tables) { if($exportCsv -or $blobURL -ne $null) { $outFileName = "$fileNameFromUrl-$machineName-$($table.TableName)-$dateTimeTicks.csv" Write-Host "Writing to File: $outFileName" $table | Export-Csv -Path $outFileName -NoTypeInformation } if($blobURL -ne $null) { Add-FileToBlobStorage $outFileName $blobURL } if(!($exportCsv) -and $blobURL -eq $null) { if(!($printToConsole)) { $table | Out-GridView } else { $table } } } } } function Invoke-RemoveSensitiveDataFromDataSet { param( [Parameter(ValueFromPipeline)] $dataSet) foreach($table in $dataSet.Tables) { $redactColumns = @() $sensitiveColumns = @() $isEncryptColumns = @() foreach($column in $table.Columns) { if($column.ColumnName -like '*Key*' -or $column.ColumnName -like '*Setting*') { $sensitiveColumns += $column.Ordinal } elseif($column.ColumnName -like '*Encrypt*') { $isEncryptColumns += $column.Ordinal } elseif($column.ColumnName -like '*UserId*' ` -or $column.ColumnName -like '*UserName*' ` -or $column.ColumnName -like '*Email*' ` -or $column.ColumnName -like '*Address*' ` -or $column.ColumnName -like '*City*' ` -or $column.ColumnName -like '*State*' ` -or $column.ColumnName -like '*Postal*' ` -or $column.ColumnName -like '*Zip*' ` -or $column.ColumnName -like '*LastName*' ` -or $column.ColumnName -like '*FirstName*' ` -or $column.ColumnName -like '*ssn*' ` -or $column.ColumnName -like '*taxid*' ` -or $column.ColumnName -like '*phone*' ` -or $column.ColumnName -like '*Upn*') { $redactColumns += $column.Ordinal } } $toRemoveRows = @() $rowIndex = 0 foreach($row in $table.Rows) { $rowIndex++ $isRowRemoved = $false foreach($encryptColIndex in $isEncryptColumns) { if($row[$encryptColIndex] -like 'True') { $toRemoveRows += $row $isRowRemoved = $true Write-Host "Row: $rowIndex contains Encrypted Data. Removed." break } } if(!($isRowRemoved)) { foreach($sensitiveColIndex in $sensitiveColumns) { if($row[$sensitiveColIndex] -like '*Secret*' -or $row[$sensitiveColIndex] -like '*Password*' -or $row[$sensitiveColIndex] -like '*Secure*' -or $row[$sensitiveColIndex] -like '*Key*') { $toRemoveRows += $row $isRowRemoved = $true Write-Host "Row: $rowIndex contains Sensitive Data Removed." break } } } if(!($isRowRemoved)) { foreach($redactCol in $redactColumns) { $row[$redactCol] = "<RMVD>" Write-Host "Row: $rowIndex Col: $redactCol Data Removed." } } } foreach($row in $toRemoveRows) { $table.Rows.Remove($row) } } return $dataSet } function Invoke-EnterPSSession { param($name) $hostConnection = Get-HostConnection $name $hostCreds=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $hostConnection.User, $hostConnection.Password if($hostConnection.IsUseSsl) { Enter-PSSession -ComputerName $hostConnection.HostName -Port $hostConnection.Port -UseSSL -Credential $hostCreds } else { Enter-PSSession -ComputerName $hostConnection.HostName -Port $hostConnection.Port -Credential $hostCreds } } function Invoke-ExitPSSession { Exit-PSSession } function Invoke-HostCommand { param($name, $cmd) $hostConnection = Get-HostConnection $name $hostCreds=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $hostConnection.User, $hostConnection.Password $sc=[Scriptblock]::Create($cmd) if($hostConnection.IsUseSsl) { Invoke-Command -ComputerName $hostConnection.HostName -Port $hostConnection.Port -UseSSL -Credential $hostCreds -ScriptBlock $sc } else { Invoke-Command -ComputerName $hostConnection.HostName -Port $hostConnection.Port -Credential $hostCreds -ScriptBlock $sc } } function Get-HostEventLog { param($name, $logName = "Application", [int] $newest = 10, $entryType = "Error", [datetime] $after, [datetime] $before, $source, $message, [int] $index) $hostConnection = Get-HostConnection $name $hostCreds=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $hostConnection.User, $hostConnection.Password $cmd = GenerateCmd($name, $logName, $newest = 10, $entryType, $after, $before, $source, $message, $index) Write-Host "Executing: $cmd" $sc=[Scriptblock]::Create($cmd) if($hostConnection.IsUseSsl) { Invoke-Command -ComputerName $hostConnection.HostName -Port $hostConnection.Port -UseSSL -Credential $hostCreds -ScriptBlock $sc } else { Invoke-Command -ComputerName $hostConnection.HostName -Port $hostConnection.Port -Credential $hostCreds -ScriptBlock $sc } } function Push-HostEventLogToBlob { param($name, $logName = "Application", [int] $newest = 10, $entryType = "Error", [datetime] $after, [datetime] $before, $source, $message, [int] $index, [ValidateScript({$_ -match "https\:\/\/cloudassertcustenvlogs\.blob.core.windows.net\/(.)*\?(.)*"})] $blobURL) $hostConnection = Get-HostConnection $name $hostCreds=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $hostConnection.User, $hostConnection.Password $cmd = GenerateCmd($name, $logName, $newest = 10, $entryType, $after, $before, $source, $message, $index) Write-Host "Executing: $cmd" $sc=[Scriptblock]::Create($cmd) $machineName = $hostConnection.HostName $dateTimeTicks = (Get-Date).Date.Ticks $fileName = "EventLog-$machineName-$dateTimeTicks.txt" if($hostConnection.IsUseSsl) { Invoke-Command -ComputerName $hostConnection.HostName -Port $hostConnection.Port -UseSSL -Credential $hostCreds -ScriptBlock $sc | Out-File $fileName } else { Invoke-Command -ComputerName $hostConnection.HostName -Port $hostConnection.Port -Credential $hostCreds -ScriptBlock $sc | Out-File $fileName } Add-FileToBlobStorage $fileName $blobURL } function Invoke-InstallOrUpdateModule { param($name) if ((Get-InstalledModule -Name $name -ErrorAction SilentlyContinue) -eq $null) { #-MinimumVersion 5.0 # Optionally specify minimum version to have Install-Module -Name CloudAssert.Common } else { Update-Module -Name CloudAssert.Common -Force } } function Add-FileToBlobStorage{ Param( [Parameter(Mandatory=$true)] [ValidateScript({Test-Path $_ })] [string] $file, [Parameter(Mandatory=$true)] [ValidateScript({$_ -match "https\:\/\/cloudassertcustenvlogs.blob.core.windows.net\/(.)*\?(.)*"})] [string] $connectionstring ) $HashArguments = @{ uri = $connectionstring.replace("?","/$($(get-item $file).name)?") method = "Put" InFile = $file headers = @{"x-ms-blob-type" = "BlockBlob"} } Write-Host "Uploading File $file to $connectionstring" Invoke-RestMethod @HashArguments } function Get-InstallerVersions { Param($hostConnectionName) $hostConnection = Get-HostConnection $hostConnectionName $hostCreds=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $hostConnection.User, $hostConnection.Password $cmd = "Get-WmiObject -Class Win32_Product | where vendor -eq 'CLOUD ASSERT LLC' | select Name, Version" Write-Host "Executing: $cmd" $sc=[Scriptblock]::Create($cmd) if($hostConnection.IsUseSsl) { Invoke-Command -ComputerName $hostConnection.HostName -Port $hostConnection.Port -UseSSL -Credential $hostCreds -ScriptBlock $sc } else { Invoke-Command -ComputerName $hostConnection.HostName -Port $hostConnection.Port -Credential $hostCreds -ScriptBlock $sc } } function Get-ServiceStatus { Param($hostConnectionName) $hostConnection = Get-HostConnection $hostConnectionName $hostCreds=New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $hostConnection.User, $hostConnection.Password $cmd = "Get-Service -Include 'Billing*','Vconnect*','Cost*'" Write-Host "Executing: $cmd" $sc=[Scriptblock]::Create($cmd) if($hostConnection.IsUseSsl) { Invoke-Command -ComputerName $hostConnection.HostName -Port $hostConnection.Port -UseSSL -Credential $hostCreds -ScriptBlock $sc } else { Invoke-Command -ComputerName $hostConnection.HostName -Port $hostConnection.Port -Credential $hostCreds -ScriptBlock $sc } } function GenerateCmd { param($name, $logName, [int] $newest, $entryType, [datetime] $after, [datetime] $before, $source, $message, [int] $index) $cmd = "Get-EventLog -LogName $logName -Newest $newest -EntryType $entryType" if($after -ne $null) { $cmd = $cmd + " -After $after" } if($before -ne $null) { $cmd = $cmd + " -Before $before" } if($source -ne $null) { $cmd = $cmd + " -Source $source" } if($message -ne $null) { $cmd = $cmd + " -Message $message" } if($index -ne $null -and $index -gt 0) { $cmd = $cmd + " -Index $index" } return $cmd } function Show-CertificateExpiry { Import-Module WebAdministration $certs = Get-ChildItem IIS:SSLBindings | ForEach-Object { $_.Thumbprint } $certExpiry = Get-ChildItem CERT:LocalMachine/My | Where-Object { $certs -contains $_.Thumbprint } | Select-Object @{E = { $_.NotAfter }; L = "ExpiryDate (dd.mm.yy HH:MM:SS)" }, Subject, ThumbPrint $certExpiry } function Remove-UpdateNewCertificate() { $thumbPrintToRemove = Read-Host "Please enter the thumbprint to remove?" $certToRemove = Get-ChildItem -Path Cert:\LocalMachine\My | Where-Object {$_.Thumbprint -eq $thumbPrintToRemove} while (-not $certToRemove) { Write-Verbose -Message "Thumbprint not found in certstore under Cert:\Localmachine\My" -Verbose $thumbPrintToRemove = Read-Host "Please enter the thumbprint to remove?" $certToRemove = Get-ChildItem -Path Cert:\LocalMachine\My | Where-Object {$_.Thumbprint -eq $thumbPrintToRemove} } $certPath = Read-Host "Please enter the path to new certificate(Pfx)?" while (-not (Test-Path $certPath)) { Write-Verbose -Message "Certificate not found or invalid path." -Verbose $certPath = Read-Host "Please enter the path to new certificate(Pfx)?" } $certPassword = Get-Password -message "Enter Certificate Password" Import-Module WebAdministration $sites = Get-ChildItem IIS:SSLBindings | Where-Object { $thumbPrintToRemove -contains $_.Thumbprint } Write-Verbose -Message "Sites which uses the thumprint for SSL bindings:" -Verbose $sites $userResponse = Get-YesOrNoUserResponse -message "Do you want to remove and update new certifcate for the above sites ?" if(-not $userResponse){ Write-Verbose -Message "Aborting SSL Certificate remove and update" -Verbose return } Get-ChildItem Cert:\LocalMachine\My\$thumbPrintToRemove | Remove-Item $importResult = Import-PfxCertificate -CertStoreLocation cert:\LocalMachine\My -FilePath $certPath -Password $certPassword -Exportable $newThumbprint = $importResult.Thumbprint $sites = Get-ChildItem -Path IIS:\Sites | Where-Object { $sites.Sites.Value -contains $_.Name } Write-Verbose -Message "Updating SSL binding for the above sites with new certificate thumbprint: $newThumbprint" -Verbose Foreach ($site in $sites) { Foreach ($bindings in $site.Bindings.Collection) { if ($bindings.protocol.ToString() -eq "https") { Set-IisWebsiteBinding -SiteName $site.name -LocalPort $bindings.BindingInformation.split(":")[1] -SslCertificateThumbprint $newThumbprint } } } } # SIG # Begin signature block # MIIQQAYJKoZIhvcNAQcCoIIQMTCCEC0CAQExCzAJBgUrDgMCGgUAMGkGCisGAQQB # gjcCAQSgWzBZMDQGCisGAQQBgjcCAR4wJgIDAQAABBAfzDtgWUsITrck0sYpfvNR # AgEAAgEAAgEAAgEAAgEAMCEwCQYFKw4DAhoFAAQUsxoZApua+9jgl8kFNY0QdFUt # 9bmgggz8MIIGcjCCBFqgAwIBAgIIZDNR08c4nwgwDQYJKoZIhvcNAQELBQAwfDEL # MAkGA1UEBhMCVVMxDjAMBgNVBAgMBVRleGFzMRAwDgYDVQQHDAdIb3VzdG9uMRgw # FgYDVQQKDA9TU0wgQ29ycG9yYXRpb24xMTAvBgNVBAMMKFNTTC5jb20gUm9vdCBD # ZXJ0aWZpY2F0aW9uIEF1dGhvcml0eSBSU0EwHhcNMTYwNjI0MjA0NDMwWhcNMzEw # NjI0MjA0NDMwWjB4MQswCQYDVQQGEwJVUzEOMAwGA1UECAwFVGV4YXMxEDAOBgNV # BAcMB0hvdXN0b24xETAPBgNVBAoMCFNTTCBDb3JwMTQwMgYDVQQDDCtTU0wuY29t # IENvZGUgU2lnbmluZyBJbnRlcm1lZGlhdGUgQ0EgUlNBIFIxMIICIjANBgkqhkiG # 9w0BAQEFAAOCAg8AMIICCgKCAgEAn4MTc6qwxm0hy9uLeod00HHcjpdymuS7iDS0 # 3YADxi9FpHSavx4PUOqebXjzn/pRJqk9ndGylFc++zmJG5ErVu9ny+YL4w45jMY1 # 9Iw93SXpAawXQn1YFkDc+dUoRB2VZDBhOmTyl9dzTH17IwJt83XrVT1vqi3Er750 # rF3+arb86lx56Q9DnLVSBQ/vPrGxj9BJrabjQhlUP/MvDqHLfP4T+SM52iUcuD4A # SjpvMjA3ZB7HrnUH2FXSGMkOiryjXPB8CqeFgcIOr4+ZXNNgJbyDWmkcJRPNcvXr # nICb3CxnxN3JCZjVc+vEIaPlMo4+L1KYxmA3ZIyyb0pUchjMJ4f6zXWiYyFMtT1k # /Summ1WvJkxgtLlc/qtDva3QE2ZQHwvSiab/14AG8cMRAjMzYRf3Vh+OLzto5xXx # d1ZKKZ4D2sIrJmEyW6BW5UkpjTan9cdSolYDIC84eIC99gauQTTLlEW9m8eJGB8L # uv+prmpAmRPd71DfAbryBNbQMd80OF5XW8g4HlbUrEim7f/5uME77cIkvkRgp3fN # 1T2YWbRD6qpgfc3C5S/x6/XUINWXNG5dBGsFEdLTkowJJ0TtTzUxRn50GQVi7Inj # 6iNwmOTRL9SKExhGk2XlWHPTTD0neiI/w/ijVbf55oeC7EUexW46fLFOuato95tj # 1ZFBvKkCAwEAAaOB+zCB+DAPBgNVHRMBAf8EBTADAQH/MB8GA1UdIwQYMBaAFN0E # CQei9Xp9UlMSkpXuOIAlDaZZMDAGCCsGAQUFBwEBBCQwIjAgBggrBgEFBQcwAYYU # aHR0cDovL29jc3BzLnNzbC5jb20wEQYDVR0gBAowCDAGBgRVHSAAMBMGA1UdJQQM # MAoGCCsGAQUFBwMDMDsGA1UdHwQ0MDIwMKAuoCyGKmh0dHA6Ly9jcmxzLnNzbC5j # b20vc3NsLmNvbS1yc2EtUm9vdENBLmNybDAdBgNVHQ4EFgQUVML+EJUAk81q9efA # 19myS7iPDOMwDgYDVR0PAQH/BAQDAgGGMA0GCSqGSIb3DQEBCwUAA4ICAQD1DyaH # cK+Zosr11snwjWY9OYLTiCPYgr+PVIQnttODB9eeJ4lNhI5U0SDuYEPbV0I8x7CV # 9r7M6qM9jk8GxitZhn/rcxvK5UAm4D1vzPa9ccbNfQ4gQDnWBdKvlAi/f8JRtyu1 # e4Mh8GPa5ZzhaS51HU7LYR71pTPfAp0V2e1pk1e6RkUugLxlvucSPt5H/5CcEK32 # VrKk1PrW/C68lyGzdoPSkfoGUNGxgCiA/tutD2ft+H3c2XBberpotbNKZheP5/Dn # V91p/rxe4dWMnxO7lZoV+3krhdVtPmdHbhsHXPtURQ8WES4Rw7C8tW4cM1eUHv5C # NEaOMVBO2zNXlfo45OYS26tYLkW32SLK9FpHSSwo6E+MQjxkaOnmQ6wZkanHE4Jf # /HEKN7edUHs8XfeiUoI15LXn0wpva/6N+aTX1R1L531iCPjZ16yZSdu1hEEULvYu # YJdTS5r+8Yh6dLqedeng2qfJzCw7e0wKeM+U9zZgtoM8ilTLTg1oKpQRdSYU6iA3 # zOt5F3ZVeHFt4kk4Mzfb5GxZxyNi5rzOLlRL/V4DKsjdHktxRNB1PjFiZYsppu0k # 4XodhDR/pBd8tKx9PzVYy8O/Gt2fVFZtReVT84iKKzGjyj5Q0QA07CcIw2fGXOho # v88uFmW4PGb/O7KVq5qNncyU8O14UH/sZEejnTCCBoIwggRqoAMCAQICEA0SjRWQ # uYT7eM+eDgHqTTMwDQYJKoZIhvcNAQELBQAweDELMAkGA1UEBhMCVVMxDjAMBgNV # BAgMBVRleGFzMRAwDgYDVQQHDAdIb3VzdG9uMREwDwYDVQQKDAhTU0wgQ29ycDE0 # MDIGA1UEAwwrU1NMLmNvbSBDb2RlIFNpZ25pbmcgSW50ZXJtZWRpYXRlIENBIFJT # QSBSMTAeFw0yMTEwMjUyMDQ1NTNaFw0yMzEwMjUyMDQ1NTNaMHcxCzAJBgNVBAYT # AlVTMRMwEQYDVQQIDApXYXNoaW5ndG9uMRAwDgYDVQQHDAdSZWRtb25kMRkwFwYD # VQQKDBBDbG91ZCBBc3NlcnQgTExDMQswCQYDVQQLDAJVUzEZMBcGA1UEAwwQQ2xv # dWQgQXNzZXJ0IExMQzCCAaIwDQYJKoZIhvcNAQEBBQADggGPADCCAYoCggGBAOr+ # k6LFTYXntOaV4dGI/mIyACrEE3JCr4RP5Aur5QgWuraKhL2JSh63eADxOOuk5P4E # KXhNG8F1XW67gDLNfUlQ9ZagD3+xts/Vc8hZOqmwGw57K0/EUy5RoVVVWntMQ7DX # q0VNp2SgMVHBuRWLvB7MX7OGTJ96+IWgEzMITBxx+bToBl+iefkJhOVZi2lCG9oN # M3i5Yrq2T7cV1uCQwl6JNBrsaCJ64vs6pz8LzR0XmhXtg5rLYehFCqcWYCcH4Njm # ZUVharTmBozLOTPdL6y3UReZRM5J1SxvrfRvalFQGWX4hK6OirBey1yPnhzqNHAt # iwCLxn5l+pnTh89LLmtc1Bp8OI2nN7yaiXK13441EQFpIYnBSQJ6e8n0dDpwwoux # OSfxtgX8iila0DBoy9vLCyGTnyXdO1zZYGoll9v8aSbvWOZu4n4gvQPVIhgROU74 # wkfGXI61Ab9ZtltF5W5WQesJoDiRIYgHUxYWU5fsTPzsoQFIXzHyaTqeJKXOtwID # AQABo4IBhzCCAYMwHwYDVR0jBBgwFoAUVML+EJUAk81q9efA19myS7iPDOMwegYI # KwYBBQUHAQEEbjBsMEgGCCsGAQUFBzAChjxodHRwOi8vY2VydC5zc2wuY29tL1NT # TGNvbS1TdWJDQS1Db2RlU2lnbmluZy1SU0EtNDA5Ni1SMS5jZXIwIAYIKwYBBQUH # MAGGFGh0dHA6Ly9vY3Nwcy5zc2wuY29tMFEGA1UdIARKMEgwCAYGZ4EMAQQBMDwG # DCsGAQQBgqkwAQMDATAsMCoGCCsGAQUFBwIBFh5odHRwczovL3d3dy5zc2wuY29t # L3JlcG9zaXRvcnkwEwYDVR0lBAwwCgYIKwYBBQUHAwMwTQYDVR0fBEYwRDBCoECg # PoY8aHR0cDovL2NybHMuc3NsLmNvbS9TU0xjb20tU3ViQ0EtQ29kZVNpZ25pbmct # UlNBLTQwOTYtUjEuY3JsMB0GA1UdDgQWBBT5QOeOXNcPYtBWMGBY9lkdLp4AczAO # BgNVHQ8BAf8EBAMCB4AwDQYJKoZIhvcNAQELBQADggIBAGL909UmLyhbmLPe0AyH # mItkDXXIonmIsCrNrquwtFB5ZFhV2eQEEcFi8N+R1Pw2CGWNQe8EGN83nr1ItDNc # JqweHvadc6i5FF1DVRPKEVHzORKKsKGZ97KyYQkT+YxJLfVCLdFCemCd2QYQuFJQ # 4LdKcR9QZE0LvoiE9qVZ0fv2oO/4Yg/jgFTS4m1znT1IXIfCgnxfK9dr5QwQt/wX # 3ayq554Ptbl7f6g9AGnD3U7cEaDvaPqRX16AGgxWbJU4W740UeNZnsFvdNcBHY/7 # wWxCzR03dzTGivW1aozokn05KeOyF0ZU7vhhXSeKyoaLzJXEr96r7pBUfBlVL9p9 # 6IVsHxsnPGFVZiaaZ0YQFsBWJZLEpVOIXCl2Jb2KX/NshRJGeijK0a6msVYIHKPv # mhLnDruJkadj4RIgk8AQ2wsttUWtjWRKjD072OnAVZatRsCPIPQJsk+8gSKqfDZR # o3DZhnrCd6TfjuoU9aULSXrwJljrOqLNOZHFoBuT7y3dZHPoo596yCmwUs+7dYCR # nBU+hQ0Fca9aWpaYw4lKdxxhXn66EIR00TbaE3HYdHhlOc8koA9VUI/eiWdd1rKL # j67luXYkCEJ37fE6SlyL1Jkhu3dd79+GSYlTINRnH415fH4DwiOMckj8kRbdyRV1 # tT1R5QeVMAdZHzQ80j8shEydMYICrjCCAqoCAQEwgYwweDELMAkGA1UEBhMCVVMx # DjAMBgNVBAgMBVRleGFzMRAwDgYDVQQHDAdIb3VzdG9uMREwDwYDVQQKDAhTU0wg # Q29ycDE0MDIGA1UEAwwrU1NMLmNvbSBDb2RlIFNpZ25pbmcgSW50ZXJtZWRpYXRl # IENBIFJTQSBSMQIQDRKNFZC5hPt4z54OAepNMzAJBgUrDgMCGgUAoHgwGAYKKwYB # BAGCNwIBDDEKMAigAoAAoQKAADAZBgkqhkiG9w0BCQMxDAYKKwYBBAGCNwIBBDAc # BgorBgEEAYI3AgELMQ4wDAYKKwYBBAGCNwIBFTAjBgkqhkiG9w0BCQQxFgQUtegI # z5rO5MhdmBUX5i2zkEkyjEUwDQYJKoZIhvcNAQEBBQAEggGAzNZ54m9WkWSL9vtY # BTQK+D6bFPyDjZlemBoAg9ZOGpsY0oQFl9SP1Js8n/LrfH7g2F0PA9ihIbVhZZM2 # o8BhHVtlDqgdgTwQm6k5GMtTiU9RIoHCeoG3AqXqOroEeHzHBuRYaU2DLe2IiaCV # JOce89OrmwlDs4HUoHaE/CyzdWep0iqCVnR/uPrh4Q8F0h0n1M8XramvCfC47Mht # 4w4hcuRsEczra+MAyIzuStwhaY11kE4OmF7395BeXPzRrrMkDpwGifzEhTnu6bOJ # 2tm0EkAzt8+dqtSsW0TX+IKYiqh0t+ExQDB7rdNah9E5BF3dvNLBtEyzirid4iI+ # 5OfhvHwtHEA5kCXdaTdwA/n25a7nL0bbQkTx+i9qV6NA+MpwqHTqmE3S0njMb+6X # 5VV0xuJmlljKxECZ2PWFe/zqNBLGBDnkey8Q3E+bsGaZ+p9GIp7N7mpFeasmmxEC # CDIZ3xeXNTT4fzN7e5O8ysAGVG5w3YVNoWVmUdOoCC/xPgN0 # SIG # End signature block |