HelperFunctions.psm1
|
function Save-ClearTextToEncryptedFile ($Password, $FileName) { #$secureStringPwd = $Password | ConvertTo-SecureString -AsPlainText -Force $secureStringPwd = New-Object PSCredential ("Dummy User", $Password) | Select-Object -ExpandProperty Password $secureStringText = $secureStringPwd | ConvertFrom-SecureString Set-Content $FileName $secureStringText } function Save-SecureStringToEncryptedFile ($FileName, $Prompt) { if ($Prompt -eq $null) {$Prompt = "Enter Password:"} $secureStringPwd = Read-Host -Prompt $Prompt -AsSecureString $secureStringText = $secureStringPwd | ConvertFrom-SecureString Set-Content $FileName $secureStringText } function Get-SecureStringFromEncryptedFile ($FileName) { $pwdTxt = Get-Content $FileName $securePwd = $pwdTxt | ConvertTo-SecureString Write-Output $securePwd } function Get-ClearTextFromEncryptedFile ($FileName) { $pwdTxt = Get-Content $FileName $securePwd = $pwdTxt | ConvertTo-SecureString $BSTR = [System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($securePwd) $clearText = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto($BSTR) Write-Output $clearText } function ConvertTo-DataTable { <# .SYNOPSIS Convert regular PowerShell objects to a DataTable object. .DESCRIPTION Convert regular PowerShell objects to a DataTable object. .EXAMPLE $myDataTable = $myObject | ConvertTo-DataTable # using the SqlServer PowerShell module to connect to SQL Server and query for and return data # returns data as an array of DataRow objects $drs=Invoke-Sqlcmd -ServerInstance "ServerName" -Database Databasename -Username UserName -Password Password -Query "SELECT * FROM [dbo].[DrawingValidation] where Owner='None' and UpToDate=1 order by stamptime desc" # use this function to Convert the DataRow array to a DataTable $dt=ConvertTo-DataTable $drs # use PWPS_DAB cmdlet to output the DataTable into a spreadsheet New-XLSXWorkbook -InputTables $dt -OutputFileName c:\temp\Output.xlsx #> [CmdletBinding()] param ( # The object to convert to a DataTable [Parameter(ValueFromPipeline = $true)] [PSObject[]] $InputObject, # Override the default type. [Parameter()] [string] $DefaultType = 'System.String' ) begin { # create an empty datatable try { $dataTable = New-Object -TypeName 'System.Data.DataTable' Write-Verbose -Message 'Empty DataTable created' } catch { Write-Warning -Message $_.Exception.Message break } # define a boolean to keep track of the first datarow $first = $true # define array of supported .NET types $types = @( 'System.String', 'System.Boolean', 'System.Byte[]', 'System.Byte', 'System.Char', 'System.DateTime', 'System.Decimal', 'System.Double', 'System.Guid', 'System.Int16', 'System.Int32', 'System.Int64', 'System.Single', 'System.UInt16', 'System.UInt32', 'System.UInt64' ) } process { # iterate through each input object foreach ($object in $InputObject) { try { # create a new datarow $dataRow = $dataTable.NewRow() Write-Verbose -Message 'New DataRow created' # iterate through each object property foreach ($property in $object.PSObject.get_properties()) { # check if we are dealing with the first row or not if ($first) { # handle data types if ($types -contains $property.TypeNameOfValue) { $dataType = $property.TypeNameOfValue Write-Verbose -Message "$($property.Name): Supported datatype <$($dataType)>" } else { $dataType = $DefaultType Write-Verbose -Message "$($property.Name): Unsupported datatype ($($property.TypeNameOfValue)), using default <$($DefaultType)>" } # create a new datacolumn $dataColumn = New-Object 'System.Data.DataColumn' $property.Name, $dataType Write-Verbose -Message 'Created new DataColumn' # add column to DataTable $dataTable.Columns.Add($dataColumn) Write-Verbose -Message 'DataColumn added to DataTable' } # add values to column if ($property.Value -ne $null) { # handle data types if ($types -contains $property.TypeNameOfValue) { $dataType = $property.TypeNameOfValue Write-Verbose -Message "$($property.Name): Supported datatype <$($dataType)>" } # if array or collection, add as XML if (($property.Value.GetType().IsArray) -or ($property.TypeNameOfValue -like '*collection*')) { $dataRow.Item($property.Name) = $property.Value | ConvertTo-Xml -As 'String' -NoTypeInformation -Depth 1 Write-Verbose -Message 'Value added to row as XML' } else{ $dataRow.Item($property.Name) = $property.Value -as $dataType Write-Verbose -Message "Value ($($property.Value)) added to row as $($dataType)" } } } # add DataRow to DataTable $dataTable.Rows.Add($dataRow) Write-Verbose -Message 'DataRow added to DataTable' } catch { Write-Warning -Message $_.Exception.Message } $first = $false } } end { #"properties" that aren't really columns when this is passed an array of or DataRows if ($dataTable.Columns.Contains("RowError")) { $dataTable.Columns.Remove("RowError")} if ($dataTable.Columns.Contains("RowState")) { $dataTable.Columns.Remove("RowState")} if ($dataTable.Columns.Contains("Table")) { $dataTable.Columns.Remove("Table")} if ($dataTable.Columns.Contains("ItemArray")) { $dataTable.Columns.Remove("ItemArray")} if ($dataTable.Columns.Contains("HasErrors")) { $dataTable.Columns.Remove("HasErrors")} Write-Output (,($dataTable)) } } #region Logging # Purpose : Write error messages to log files # Requirements : Write permission to log directory function Write-Log { [CmdletBinding()] Param ( [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)] [ValidateNotNullOrEmpty()] [Alias("LogContent")] [string]$Message, [Parameter(Mandatory=$false)] [Alias('LogPath')] [string]$Path="$env:LOCALAPPDATA" + "\Bentley\Logs\PowerShellLogging.log", [Parameter(Mandatory=$false)] [ValidateSet("Error","Warn","Info")] [string]$Level="Info", [Parameter(Mandatory=$true)] [string]$Cmdlet, [Parameter(Mandatory=$false)] [switch]$NoClobber ) Begin { # Set VerbosePreference to Continue so that verbose messages are displayed. #$VerbosePreference = 'Continue' # # Rollover logs if size is exceeded # if ((Get-Item -LiteralPath $Path -ErrorAction SilentlyContinue)) { if ((Get-Item -LiteralPath $Path).Length -gt 9999999) { if ((Test-Path -LiteralPath "$Path.3")) { Remove-Item -LiteralPath "$Path.3" -Force } if ((Test-Path -LiteralPath "$Path.2")) { Rename-Item -LiteralPath "$Path.2" -NewName "$Path.3" -Force } if ((Test-Path -LiteralPath "$Path.1")) { Rename-Item -LiteralPath "$Path.1" -NewName "$Path.2" -Force } Rename-Item -LiteralPath $Path -NewName "$Path.1" -Force New-Item $Path -Force -ItemType File | Out-Null } } } Process { # If the file already exists and NoClobber was specified, do not write to the log. if ((Test-Path $Path) -AND $NoClobber) { Write-Error "Log file $Path already exists, and you specified NoClobber. Either delete the file or specify a different name." Return } # If attempting to write to a log file in a folder/path that doesn't exist create the file including the path. elseif (!(Test-Path $Path)) { Write-Verbose "Creating $Path." $NewLogFile = New-Item $Path -Force -ItemType File } else { # Nothing to see here yet. } # Format Date for our Log File $FormattedDate = Get-Date -Format "yyyy/MM/dd HH:mm:ss" # Write message to error, warning, or verbose pipeline and specify $LevelText switch ($Level) { 'Error' { $LevelText = 'ERROR' } 'Warn' { $LevelText = 'WARNING' } 'Info' { $LevelText = 'INFO' } } # Write log entry to $Path "$FormattedDate [$LevelText] $Cmdlet - $Message" | Out-File -FilePath $Path -Append } End { # Write-Verbose "$LevelText log written to $Path" } } # Purpose : Searches a log file written using the Write-Log function, and returns entries from a specified number of days ago. Returns records from a single day only. # Requirements : Write-Log function function Get-PowerShellLogsFromPreviousDays { <# .SYNOPSIS Returns log entries written by Write-Log from a specified number of days ago. .DESCRIPTION Searches a log file written using the Write-Log function, and returns entries from a specified number of days ago. Returns records from a single day only. There is no logging for this cmdlet by design. .PARAMETER LogFilePath Target log file path to search. Default is 'C:\users\<username>\AppData\Local\Bentley\Logs\PowerShellLogging.log'. .PARAMETER DaysAgo Integer value to specify the desired number of days ago to return logs for. A value of '1' is equal to yesterday. .PARAMETER IncludeIntermediateDays Switch to include intermediate results. If days is '5' and this switch is activated, log entries from 5 days ago up until now will be returned, as opposed to just the log entries from 5 days ago if this switch is not activated. .PARAMETER Level Target level to return. Default is set to return all levels. Takes multiple inputs. Acceptable input values are 'INFO','WARN' and 'ERROR'. .EXAMPLE This example will return all log entries from 10 days ago. Get-PowerShellLogsFromPreviousDays -DaysAgo 10 -Verbose .EXAMPLE This example will return all log entries from the last 10 days. Get-PowerShellLogsFromPreviousDays -DaysAgo 10 -IncludeIntermediateDays -Verbose .EXAMPLE This example will return info and warn log entries from 10 days ago. Get-PowerShellLogsFromPreviousDays -DaysAgo 10 -Level INFO,WARN -Verbose .EXAMPLE This example will return error log entries from the last 10 days. Get-PowerShellLogsFromPreviousDays -DaysAgo 10 -IncludeIntermediateDays -Level ERROR -Verbose #> [CmdletBinding()] Param ( [Parameter(Mandatory=$false)] [string]$LogFilePath="$env:LOCALAPPDATA" + "\Bentley\Logs\PowerShellLogging.log", [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [int]$DaysAgo, [Parameter(Mandatory=$false)] [switch]$IncludeIntermediateDays, [Parameter(Mandatory=$false)] [ValidateNotNullOrEmpty()] [ValidateSet("INFO","WARN","ERROR")] [string[]]$Level ) Begin { ## Check log file exists if (!(Test-Path -LiteralPath $LogFilePath)) { Write-Error "Log file not found! ($LogFilePath)" break; } } Process { #region Get Content try { $RawLogContent = Get-Content -LiteralPath $Path } catch { Write-Error "Failed to retrieve log content!" Write-Error $_.Exception.Message break; } #endregion Get Content #region Sort Log Content [System.Collections.ArrayList]$TargetLogEntries = @() if ($IncludeIntermediateDays) { While ($DaysAgo -ge 0) { foreach ($row in $RawLogContent) { try { $RawDate = $row.Split(' ')[0] $CalculatedDate = (Get-Date -Date $row.Split(' ')[0]).Date $TargetDate = (Get-Date).AddDays(-$DaysAgo).Date if ($CalculatedDate -eq $TargetDate) { $TargetLogEntries.Add($row) | Out-Null } } Catch { Write-Warning "Could not read row!" Write-Warning "Raw row content: $row" } } $DaysAgo-- } } else { foreach ($row in $RawLogContent) { try { $RawDate = $row.Split(' ')[0] $CalculatedDate = (Get-Date -Date $row.Split(' ')[0]).Date $TargetDate = (Get-Date).AddDays(-$DaysAgo).Date if ($CalculatedDate -eq $TargetDate) { $TargetLogEntries.Add($row) | Out-Null } } Catch { Write-Warning "Could not read row!" Write-Warning "Raw row content: $row" } } } $TargetLogEntriesCount = ($TargetLogEntries | Measure-Object).Count Write-Verbose "Returned $TargetLogEntriesCount target log entries." #endregion Sort Log Content #region Filter Log Content if ($Level) { [System.Collections.ArrayList]$FilteredLogEntries = @() foreach ($row in $TargetLogEntries) { try { $CalculatedLevel = $row.Split(' ')[2].TrimStart('[').TrimEnd(']') if ($CalculatedLevel -in $Level) { $FilteredLogEntries.Add($row) | Out-Null } } Catch { Write-Warning "Could not read row!" Write-Warning "Raw row content: $row" } } $FilteredLogEntriesCount = ($FilteredLogEntries | Measure-Object).Count Write-Verbose "Returned $FilteredLogEntriesCount filtered log entries." } #endregion Filter Log Content } End { if ($Level -and ($FilteredLogEntriesCount -gt 0)) { Write-Output $FilteredLogEntries } elseif ($Level -and ($FilteredLogEntriesCount -eq 0)) { Write-Warning "No log entries found for specified dates and levels." } elseif (!($Level) -and ($TargetLogEntriesCount -gt 0)) { Write-Output $TargetLogEntries } elseif (!($Level) -and ($TargetLogEntriesCount -eq 0)) { Write-Warning "No log entries found for specified dates." } else { Write-Error "Unable to output log entries!" } } } #endregion Logging #region Windows # Purpose : Allows user to select a folder/file using the GUI, and returns the folder/file path. Function Get-WindowsFolderPath($InitialDirectory) { [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")|Out-Null $FolderBrowser = New-Object System.Windows.Forms.FolderBrowserDialog $FolderBrowser.Description = "Select a folder" $FolderBrowser.rootfolder = "MyComputer" if($FolderBrowser.ShowDialog() -eq "OK") { $FolderPath += $FolderBrowser.SelectedPath } return $FolderPath } Function Get-WindowsFilePath($InitialDirectory) { [System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")|Out-Null $FileBrowser = New-Object System.Windows.Forms.OpenFileDialog $FileBrowser.Multiselect = $false $FileBrowser.Filter = 'Excel Workbooks (*.xls, *.xlsx)|*.xls;*.xlsx|All files|*.*' if($FileBrowser.ShowDialog() -eq "OK") { $FilePath += $FileBrowser.FileName } return $FilePath } #endregion Windows #region SQL # Purpose : Bulk copies a datatable into a SQL table. # Requirements: # - Write-Log -Cmdlet $Cmdlet function # - SQLPS module (Install-Module SQLPS) # - SQL Server login credentials # - Get-SQLDataType function BulkCopy-SQLTable { <# .SYNOPSIS Bulk copies a datatable into a SQL table. .DESCRIPTION Copies the contents of an input datatable to a target table in the database. The target table can be truncated before bulk copy is executed. Database is specified in New-SQLConnection. .PARAMETER SQLConnection SQL Server connection generated using New-SQLConnection. Database must be specified in New-SQLConnection to use this BulkCopy-SQLTable. .PARAMETER Datatable Datatable containing the records to be bulk copied to the target SQL table. Must have matching schema with target table. .PARAMETER TruncateBeforeCopy If this switch parameter is activated, the target SQL table will be truncated before records from the input datatable are bulk copied. .EXAMPLE This example will bulk copy all records from the dms_audt datatable to the dms_audt SQL table in the database specified during SQL Connection. $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -Database 'DATABASEONE' -WindowsAuthentication $DataTable = Select-PWSQLDataTable -SQLSelectStatement "SELECT * FROM dms_audt" $DataTable.TableName = "dms_audt" BulkCopy-SQLTable -SQLConnection $SQLConnection -DataTable $DataTable .EXAMPLE This example will bulk copy all records from the dms_audt datatable to the dms_audt SQL table in the database specified during SQL Connection, truncating the SQL table before bulk copying. $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -Database 'DATABASEONE' -WindowsAuthentication $DataTable = Select-PWSQLDataTable -SQLSelectStatement "SELECT * FROM dms_audt" $DataTable.TableName = "dms_audt" BulkCopy-SQLTable -SQLConnection $SQLConnection -DataTable $DataTable -TruncateBeforeCopy #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [Microsoft.SqlServer.Management.Common.ConnectionManager]$SQLConnection, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [System.Data.DataTable]$DataTable, [Parameter(Mandatory=$false)] [switch]$TruncateBeforeCopy ) Begin { #region Startup $Cmdlet = 'BulkCopy-SQLTable' #endregion Startup #region Parameter Checks ### Check database is specified in SQL Connection object if (!($SQLConnection.DatabaseName)) { $Message = "No database specified in SQL Connection. Create new SQL Connection using New-SQLConnection, making sure to specify the database parameter." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } #endregion Parameter Checks } Process { #region Truncate Table if ($TruncateBeforeCopy) { $Message = "Truncate switch activated. Truncating table '$($DataTable.TableName)' in database '$($SQLConnection.DatabaseName)'..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { Truncate-SQLTable -SQLConnection $SQLConnection -TableName $DataTable.TableName -ErrorAction Stop } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error truncating table." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully truncated table." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info } #endregion Truncate Table #region Define SQL Objects ### Initiate Bulk Copy object $Message = "Initiating SQL Bulk Copy object." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { $SQLBulkCopy = New-Object Data.SqlClient.SqlBulkCopy $SQLConnection $SQLBulkCopy.DestinationTableName = $Datatable.TableName } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error initiating SQL Bulk Copy object." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully initiated SQL Bulk Copy object." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info #endregion Define SQL Objects } End { #region Bulk Copy table $Message = "Performing bulk copy of '$($Datatable.TableName)' to database '$($SQLConnection.DatabaseName)'..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info $Message = "Copying $($Datatable.Rows.Count) rows..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { $SQLBulkCopy.WriteToServer($Datatable) } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error bulk copying table." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully performed bulk copy." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info #endregion Bulk Copy table } } # Purpose : Returns databases in a SQL instance # Requirements: # - Write-Log -Cmdlet $Cmdlet function # - SQLPS module (Install-Module SQLPS) # - SQL Server login credentials function Get-SQLDatabaseFunction { <# .SYNOPSIS Returns databases in a SQL instance. .DESCRIPTION Returns either all databases or a specified database from the SQL Server specified in New-SQLConnection. .PARAMETER SQLConnection SQL Server connection generated using New-SQLConnection. .PARAMETER Database Optional parameter to specify the name of database within the SQL Server instance to return. If not specified, all databases within the SQL Server instance will be returned. .EXAMPLE This example will return all databases in the SQL instance SQLONE. $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -WindowsAuthentication Get-SQLDatabase -SQLConnection $SQLConnection .EXAMPLE This example will return the database object for DATABASEONE from the SQL instance SQLONE. $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -WindowsAuthentication Get-SQLDatabase -SQLConnection $SQLConnection -Database "DATABASEONE" #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [Microsoft.SqlServer.Management.Common.ConnectionManager]$SQLConnection, [Parameter(Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$Database ) Begin { #region Startup $Cmdlet = 'Get-SQLDatabase' #endregion Startup } Process { #region Define SQL Objects ### Initiate SMO $Message = "Initiating SQL Server Management object." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { $SQLSMO = New-Object Microsoft.SqlServer.Management.Smo.Server $SQLConnection -ErrorAction Stop } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error initiating SQL Server object." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully initiated SQL Server Management object." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info #endregion Define SQL Objects #region Database Check ### Check if database exists if ($Database) { $Message = "Checking if '$Database' exists in '$($SQLConnection.ServerInstance)'..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info if ($Database -in $SQLSMO.Databases.Name) { $Message = "Found '$Database' in '$($SQLConnection.ServerInstance)'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info } else { $Message = "'$Database' does not exist in '$($SQLConnection.ServerInstance)'." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } } #endregion Database Check } End { #region Return Database try { if ($Database) { $Message = "Returning $Database..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info Write-Output $SQLSMO.Databases[$Database] } else { $Message = "Returning databases..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info Write-Output $SQLSMO.Databases } } catch { $Message = "Error returning database. Aborting script..." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } #endregion Return Database } } # Purpose : Converts data types to SQL data types # Requirements: # - Write-Log -Cmdlet $Cmdlet function # - SQLPS module (Install-Module SQLPS) function Get-SQLDataType { <# .SYNOPSIS Converts data types to SQL data types. .DESCRIPTION Takes an input data type, and returns the corresponding SQL data type. .PARAMETER DataType Input data type. .EXAMPLE This example will return the SQL data type for input type 'string'. Get-SQLDataType -DataType 'String' .EXAMPLE This example will return the SQL data type for input type 'int32'. Get-SQLDataType -DataType 'int32' #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [string]$DataType ) Begin { #region Startup $Cmdlet = 'Get-SQLDataType' #endregion Startup } Process { #region Convert Data Type $Message = "Beginning conversion." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info $Message = "Input data type is '$DataType'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info switch ($DataType) { 'Boolean' {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Bit } 'Byte[]' {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::VarBinary} 'Byte' {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::VarBinary} 'Datetime' {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::DateTime} #'Datetime' #{$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::DateTime2} 'Decimal' {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Decimal} 'Double' {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Float} 'Guid' {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::UniqueIdentifier} 'Int16' {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::SmallInt} 'Int32' {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Int} #'Int32' #{$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Numeric} 'Int64' {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::BigInt} #'Int64' #{$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Numeric} 'UInt16' {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::SmallInt} 'UInt32' {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Int} 'UInt64' {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::BigInt} 'Single' {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::Decimal} default {$SQLDataType = [Microsoft.SqlServer.Management.Smo.SqlDataType]::VarChar} } $Message = "Output data type is '$SQLDataType'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info #endregion Convert Data Type } End { #region Return Data Type if ($SQLDataType) { $Message = "Data type conversion successful." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info Write-Output -InputObject $SQLDataType } else { $Message = "Failed to covert data type." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error break; } #endregion Return Data Type } } # Purpose : Returns tables in a specified SQL database # # Requirements: # - Write-Log -Cmdlet $Cmdlet function # - SQLPS module (Install-Module SQLPS) function Get-SQLTable { <# .SYNOPSIS Returns tables in a specified SQL database. .DESCRIPTION Uses a database specified in this cmdlet or in New-SQLConnection, and returns either all or a specified table from the target database. .PARAMETER SQLConnection SQL Server connection generated using New-SQLConnection. .PARAMETER Database Optional parameter to specify the name of database within the SQL Server instance. Only required if the -database paramater was not used when generating the SQL Server connection. .PARAMETER TableName Optional parameter to return a target table only. If not specified, all tables in the target database will be returned. .EXAMPLE This example will return the dms_audt table in the database specified during SQL Connection. $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -Database 'DATABASEONE' -WindowsAuthentication Get-SQLTable -SQLConnection $SQLConnection -TableName "dms_audt" .EXAMPLE This example will return all tables in the database specified during SQL Connection. $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -Database 'DATABASEONE' -WindowsAuthentication Get-SQLTable -SQLConnection $SQLConnection .EXAMPLE This example will return the dms_audt table in the database specified during table return. $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -WindowsAuthentication Get-SQLTable -SQLConnection $SQLConnection -Database 'DATABASEONE' -TableName "dms_audt" .EXAMPLE This example will return all tables in the database specified during table return. $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -WindowsAuthentication Get-SQLTable -SQLConnection $SQLConnection -Database 'DATABASEONE' #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [Microsoft.SqlServer.Management.Common.ConnectionManager]$SQLConnection, [Parameter(Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$Database, [Parameter(Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$TableName ) Begin { #region Startup $Cmdlet = 'Get-SQLTable' #endregion Startup #region Parameter Checks if (!($SQLConnection.DatabaseName) -and !($Database)) { $Message = "No database parameter specified, and no database specified in SQL Connection. Either specify the database, or create new SQL Connection using New-SQLConnection, making sure to specify the database parameter." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } #endregion Parameter Checks } Process { #region Define SQL Objects ### Return DBO $Message = "Returning SQL Database object." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { if ($Database) { $SQLDBO = Get-SQLDatabase -SQLConnection $SQLConnection -Database $Database } else { $Database = $SQLConnection.DatabaseName $SQLDBO = Get-SQLDatabase -SQLConnection $SQLConnection -Database $Database } } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error returning SQL Database object." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully returned SQL Database object." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info #endregion Define SQL Objects #region Check Table if ($TableName) { $Message = "Checking if table '$TableName' exists in '$($SQLConnection.ServerInstance)\$Database'..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info if ($TableName -in $SQLDBO.Tables.Name) { $Message = "Found table '$TableName' in database '$Database'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info } else { $Message = "Could not find table '$TableName' in database '$Database'." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } } #endregion Check table } End { #region Return Table if ($TableName) { try { $Message = "Returning table '$TableName'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info Write-Output $SQLDBO.Tables[$TableName] } catch { $Message = "Error returning table." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } } else { try { $Message = "Returning tables in '$Database'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info Write-Output $SQLDBO.Tables } catch { $Message = "Error returning tables." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } } #endregion Return Table } } # Purpose : Opens a connection to a specified SQL server # Requirements: # - Write-Log -Cmdlet $Cmdlet function # - SQLPS module (Install-Module SQLPS) # - SQL Server login credentials function New-SQLConnection { <# .SYNOPSIS Opens a connection to a specified SQL server. .DESCRIPTION Uses the assemblies delivered with the SQLServer module to open a connection to the specified SQL Server. .PARAMETER SQLServer The name of the SQL Server. If connecting to a named instance, use the format SQLServerName\InstanceName. .PARAMETER Database The name of database within the SQL Server instance to which you would like to connect. .PARAMETER Username Username to connect with if using SQL Server Authentication. .PARAMETER Password Password to connect with if using SQL Server Authentication. .PARAMETER WindowsAuthentication Activate this switch to connect using Windows Authentication. .EXAMPLE This example will open a general connection to the SQL Server 'SQLONE' using Windows authentication. New-SQLConnection -SQLServer 'SQLONE' -WindowsAuthentication .EXAMPLE This example will open a connection to the database 'DATABASEONE' in the SQL Server 'SQLONE' using Windows authentication. New-SQLConnection -SQLServer 'SQLONE' -Database 'DATABASEONE' -WindowsAuthentication .EXAMPLE This example will open a connection to the database 'DATABASEONE' in the SQL Server 'SQLONE' using SQL Server authentication. New-SQLConnection -SQLServer 'SQLONE' -Database 'DATABASEONE' -Username 'sa' -Password 'sa' #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SQLServer, [Parameter(Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$Database, [Parameter(Mandatory=$false)] [string]$Username, [Parameter(Mandatory=$false)] [string]$Password, [Parameter(Mandatory=$false)] [switch]$WindowsAuthentication ) Begin { #region Startup $Cmdlet = 'New-SQLConnection' #endregion Startup #region Requirements ### Import SqlServer module to load required assemblies # Assemblies can be loaded manually, instructions here: https://docs.microsoft.com/en-us/sql/powershell/load-the-smo-assemblies-in-windows-powershell?view=sql-server-2017 $Message = "Checking for SqlServer module..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { if (!(Get-Module -Name SqlServer)) { $Message = "Importing SqlServer module..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info Import-Module -Name SqlServer -ErrorAction Stop } } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error importing SqlServer Module." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error break; } #endregion Requirements #region Parameter Checks if ($WindowsAuthentication) { $Message = "Authentication mode: Windows." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info } else { $Message = "Authentication mode: SQL Server." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info ### No password provided if (!($Password)) { $Message = "No password provided for SQL Server authentication." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error break; } ### No username provided if (!($Username)) { $Message = "No username provided for SQL Server authentication." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error break; } } #endregion Parameter Checks } Process { #region Establish Connection if ($WindowsAuthentication) { $Message = "Establishing connection to '$SQLServer' using Windows authentication." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { $SQLServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection ($SQLServer) -ErrorAction Stop } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error establishing connection to '$SQLServer'." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error break; } } else { $Message = "Establishing connection to '$SQLServer' as '$Username' using SQL Server authentication." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { $SQLServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection ($SQLServer, $Username, $Password) -ErrorAction Stop } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error establishing connection to '$SQLServer' as '$Username'." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error break; } } $Message = "Successfully established connection to '$SQLServer'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info #endregion Establish Connection #region Target Database if ($Database) { $Message = "Setting target database to '$Database'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { $SQLServerConnection.DatabaseName = $Database } catch { $Message = "Error setting target database." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error break; } } #endregion Target Database #region Open Connection $Message = "Opening connection..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { $SQLServerConnection.Connect() } catch { $Message = "Error opening connection. Check login credentials and inputs. Aborting script..." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error break; } #endregion Open Connection } End { #region Return Connection if ($($SQLServerConnection.IsOpen) -eq $True) { $Message = "Successfully opened connection to '$SQLServer'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info Write-Output -InputObject $SQLServerConnection } else { $Message = "Failed to open SQL connection." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error break; } #endregion Return Connection } } # Purpose : Creates a table in a specified database # Requirements: # - Write-Log -Cmdlet $Cmdlet function # - SQLPS module (Install-Module SQLPS) # - SQL Server login credentials function New-SQLTable { <# .SYNOPSIS Creates a table in a specified database. .DESCRIPTION Uses a specified datatable for the schema template and table namme, then creates an empty table with the same schema in a target database. .PARAMETER SQLConnection SQL Server connection generated using New-SQLConnection. .PARAMETER Database Optional parameter to specify the name of database within the SQL Server instance. Only required if the -database paramater was not used when generating the SQL Server connection. .PARAMETER Datatable Datatable to use as the template schema and table name. .EXAMPLE This example will create an empty replica table for dms_audt in the database specified during SQL Connection. $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -Database 'DATABASEONE' -WindowsAuthentication $DataTable = Select-PWSQLDataTable -SQLSelectStatement "SELECT * FROM dms_audt" $DataTable.TableName = "dms_audt" New-SQLTable -SQLConnection $SQLConnection -DataTable $DataTable .EXAMPLE This example will create an empty replica table for dms_audt in the database specified during table creation. $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -WindowsAuthentication $DataTable = Select-PWSQLDataTable -SQLSelectStatement "SELECT * FROM dms_audt" $DataTable.TableName = "dms_audt" New-SQLTable -SQLConnection $SQLConnection -Database 'DATABASEONE' -DataTable $DataTable #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [Microsoft.SqlServer.Management.Common.ConnectionManager]$SQLConnection, [Parameter(Mandatory=$false)] [ValidateNotNullOrEmpty()] [string]$Database, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [System.Data.DataTable]$DataTable ) Begin { #region Startup $Cmdlet = 'New-SQLTable' #endregion Startup #region Parameter Checks if (!($SQLConnection.DatabaseName) -and !($Database)) { $Message = "No database parameter specified, and no database specified in SQL Connection. Either specify the database, or create new SQL Connection using New-SQLConnection, making sure to specify the database parameter." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } #endregion Parameter Checks } Process { #region Define SQL Objects ### Return DBO $Message = "Returning SQL Database object." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { if ($Database) { $SQLDBO = Get-SQLDatabase -SQLConnection $SQLConnection -Database $Database } else { $SQLDBO = Get-SQLDatabase -SQLConnection $SQLConnection -Database $SQLConnection.DatabaseName } } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error returning SQL Database object." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully returned $($SQLDBO.Name)." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info #endregion Define SQL Objects #region Define Table ### Define table object $Message = "Defining SQL Table object..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { $SQLTable = New-Object Microsoft.SqlServer.Management.Smo.Table ($SQLDBO, $DataTable.TableName) -ErrorAction Stop } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error defining SQL Table object." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully defined table '$($SQLTable.Name)'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info ### Add columns to table $Message = "Preparing to add $($DataTable.Columns.Count) columns to '$($SQLTable.Name)'..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info foreach ($Column in $Datatable.Columns) { ### Convert data type try { $Message = "Converting data type for column '$($Column.ColumnName)'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info $SQLDatabaseType = Get-SQLDataType -DataType $Column.DataType.Name if ($SQLDatabaseType -eq 'VarBinary' -or $SQLDatabaseType -eq 'VarChar') { $SQLDataType = New-Object Microsoft.SqlServer.Management.Smo.DataType ("$($SQLDatabaseType)Max") -ErrorAction Stop } else { $SQLDataType = New-Object Microsoft.SqlServer.Management.Smo.DataType ($SQLDatabaseType) -ErrorAction Stop } } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error converting data type." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } ### Define column try { $Message = "Defining column object '$($Column.ColumnName)'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info $SQLColumn = New-Object Microsoft.SqlServer.Management.Smo.Column ($SQLTable, $Column.ColumnName, $SQLDataType) -ErrorAction Stop $SQLColumn.Nullable = $Column.AllowDBNull } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error defining column object." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } ### Add column try { $Message = "Adding column object '$($Column.ColumnName)' to table object '$($SQLTable.Name)'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info $SQLTable.Columns.Add($SQLColumn) } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error adding column object." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } } #endregion Define Table } End { #region Create Table try { $Message = "Adding table object '$($SQLTable.Name)' to database '$($SQLDBO.Name)'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info $SQLTable.Create() } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error adding table object." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } #endregion Create Table } } # Purpose : Truncates a specified SQL table. # Requirements: # - Write-Log -Cmdlet $Cmdlet function # - SQLPS module (Install-Module SQLPS) # - SQL Server login credentials function Truncate-SQLTable { <# .SYNOPSIS Truncates a specified SQL table. .DESCRIPTION Targets a database specified in New-SQLConnection, and truncates the specified table. .PARAMETER SQLConnection SQL Server connection generated using New-SQLConnection. Connection must created specifying the -database parameter. .PARAMETER TableName Name of the SQL table to truncate. .EXAMPLE This example will truncate the table "dms_audt" in the database specified during SQL connection. $SQLConnection = New-SQLConnection -SQLServer 'SQLONE' -Database 'DATABASEONE' -WindowsAuthentication Truncate-SQLTable -SQLConnection $SQLConnection -TableName "dms_audt" #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [Microsoft.SqlServer.Management.Common.ConnectionManager]$SQLConnection, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$TableName ) Begin { #region Startup $Cmdlet = 'Truncate-SQLTable' #endregion Startup #region Parameter Checks ### Check database is specified in SQL Connection object if (!($SQLConnection.DatabaseName)) { $Message = "No database specified in SQL Connection. Create new SQL Connection using New-SQLConnection, making sure to specify the database parameter." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } #endregion Parameter Checks } Process { #region Define SQL Objects ### Return SQL Table $Message = "Returning table '$TableName'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { $SQLTable = Get-SQLTable -SQLConnection $SQLConnection -TableName $TableName } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error returning table object." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully returned table object." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info #endregion Return SQL Table } End { #region Truncate table $Message = "Performing truncate on table '$TableName'..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { $SQLTable.TruncateData() } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error truncating table." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully truncated table." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info #endregion Truncate } } #endregion SQL #region Reporting # Purpose : Returns audit trail records from previous days # Requirements: # - Write-Log function function Get-PWAuditTrailRecordsFromPreviousDays { <# .SYNOPSIS Returns audit trail records from previous days. .DESCRIPTION Uses a SQL query to return audit trail records from previous days. .PARAMETER DaysAgo Integer value to specify the desired number of days ago to return records for. A value of '1' is equal to yesterday. .PARAMETER IncludeIntermediateDays Switch to include records from intermediate results. If days ago is '5' and this switch is activated, audit trail records from 5 days ago up until now will be returned, as opposed to just the audit trail records from 5 days ago if this switch is not activated. .PARAMETER AddDatasourceInformation Switch to add datasource information (DatasourceString = 'servername:datasourcename';DatasourceName = 'datasourcename'; ServerName = 'servername') to the returned DataTable. Useful when reporting against multiple servers and datasources. .EXAMPLE This example will return audit trail records from 10 days ago. Get-PWAuditTrailRecordsFromPreviousDays -DaysAgo 10 -Verbose .EXAMPLE This example will return audit trail records from 10 days ago, and add datasource information to the output DataTable. Get-PWAuditTrailRecordsFromPreviousDays -DaysAgo 10 -AddDatasourceInformation -Verbose .EXAMPLE This example will return audit trail records from the last 10 days up until this moment. Get-PWAuditTrailRecordsFromPreviousDays -DaysAgo 10 -IncludeIntermediateDays -Verbose #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [int]$DaysAgo, [Parameter(Mandatory=$false)] [switch]$IncludeIntermediateDays, [Parameter(Mandatory=$false)] [switch]$AddDatasourceInformation ) Begin { #region Startup $Cmdlet = 'Get-PWAuditTrailRecordsFromPreviousDays' #endregion Startup #region Checks # Check ProjectWise connection $Message = "Checking for ProjectWise connection..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message if(!(Get-PWCurrentDatasource)) { $Message = "Get-PWAuditTrailRacordsFromPreviousDays requires an active PW connection. Please open a connection using New-PWLogin." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Level Error -Message $Message break; } else { $Datasource = Get-PWCurrentDatasource $DatasourceName = $Datasource.Split(':')[1] $ServerName = $Datasource.Split(':')[0] } $Message = "Connected to '$Datasource'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message if ($IncludeIntermediateDays) { $Message = "Include intermediate days switch activated!" Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message } else { $Message = "Include intermediate days switch not activated! Only records from the target day will be returned." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message } #endregion Checks } Process { #region Return Records $Message = "Returning audit trail records..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message if ($IncludeIntermediateDays) { $SQLQuery = "SELECT * from dms_audt WHERE o_acttime >= dateadd(day,datediff(day,$DaysAgo,GETDATE()),0) AND o_acttime < dateadd(day,datediff(day,0,GETDATE()),0)" } else { $DaysAgoMinusOne = ($DaysAgo -1) $SQLQuery = "SELECT * from dms_audt WHERE o_acttime >= dateadd(day,datediff(day,$DaysAgo,GETDATE()),0) AND o_acttime < dateadd(day,datediff(day,$DaysAgoMinusOne,GETDATE()),0)" } try { $AuditTrailRecords = Select-PWSQL -SQLSelectStatement $SQLQuery -Verbose $AuditTrailRecordsCount = ($AuditTrailRecords | Measure-Object).Count } catch { $Message = "Failed to return audit trail records!" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Level Error -Message $Message break; } $Message = "Returned $AuditTrailRecordsCount audit trail records." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message #endregion Return Records #region Add Properties if ($AddDatasourceInformation) { $Message = "Adding properties to audit trail records..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message if ($AuditTrailRecordsCount -gt 0) { try { # Properties $AuditTrailRecords | Add-Member -MemberType NoteProperty -Name DatasourceString -Value $Datasource $AuditTrailRecords | Add-Member -MemberType NoteProperty -Name DatasourceName -Value $DatasourceName $AuditTrailRecords | Add-Member -MemberType NoteProperty -Name ServerName -Value $ServerName $Message = "Finished adding properties to audit trail records." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message } catch { $Message = "Failed to add properties to audit trail records!" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Level Error -Message $Message break; } } elseif ($AuditTrailRecordsCount -eq 0) { $Message = "No audit trail records returned." Write-Warning $Message Write-Log -Cmdlet $Cmdlet -Level Warn -Message $Message } else { Write-Log -Cmdlet $Cmdlet -Level Error -Message "This should never happen! Something has gone wrong..." break; } } #endregion Add Properties } End { #region Write Output $Message = "Writing output..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message if ($AuditTrailRecordsCount -gt 0) { Write-Output $AuditTrailRecords } #endregion Write Output } } #endregion Reporting #region SharePoint # Purpose : Copies a document from ProjectWise to SharePoint # Requirements: # - Write-Log function # - SharePointPnPPowerShell* module (Install-Module SharePointPnPPowerShell2013 or Install-Module SharePointPnPPowerShell2016 or Install-Module SharePointPnPPowerShellOnline) function Copy-PWDocumentToSharePoint { <# .SYNOPSIS Copies a document from ProjectWise to SharePoint. .DESCRIPTION Takes a single ProjectWise document as input, copies it to a local working directory, then uploads to specified location in SharePoint. .PARAMETER ProjectWiseDocument Input ProjectWise document. .PARAMETER WorkingDirectory Path to local working directory. .PARAMETER SharePointConnection SharePoint connection object. (Generated using New-SharePointConnection) .PARAMETER SharePointDocumentName Name to use for document in SharePoint. .PARAMETER SharePointFolderPath URL path to the target SharePoint folder, minus the server name. (e.g. the folder path input for 'https://my-sharepoint-server.com/TargetFolderPath' would be 'TargetFolderPath') .PARAMETER SharePointMetadata Hashtable of metadata to apply to the SharePoint document. Note, this must use the correct field names as defined in SharePoint, or document upload will fail. .PARAMETER CheckSharePointBeforeCopy Switch to check whether the document already exists in SharePoint. The document will be updated with the latest version if it exists. .PARAMETER NumberOfAttemps Integer value for number of attempts before returning a failure. Default number is 20. .EXAMPLE This example will copy the input ProjectWise document and metadata to /Transmittals/Drawings, and check if the document exists before copying. $Connection = New-SharePointConnection -SharePointURL 'https://my-sharepoint-server.com/Transmittals' -Credentials $Credentials -SharePointVersion 2013 -Verbose $SharePointMetadata = @{ Title = "Test Title"; Revision = "1"; Description = "Test Description"; Design_Package = "Design Package One"; Created = $ProjectWiseDocument.DocumentUpdateDate; } Copy-PWDocumentToSharePoint -ProjectWiseDocument $ProjectWiseDocument -WorkingDirectory "C:\temp" -SharePointConnection $Connection -SharePointDocumentName "ModifiedNameTest" -SharePointFolderPath '/Drawings' -CheckSharePointBeforeCopy -SharePointMetadata $SharePointMetadata -Verbose #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [PWPS_DAB.CommonTypes+ProjectWiseDocument]$ProjectWiseDocument, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$WorkingDirectory, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [SharePointPnP.PowerShell.Commands.Base.SPOnlineConnection]$SharePointConnection, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SharePointDocumentName, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SharePointFolderPath, [Parameter(Mandatory=$false)] [ValidateNotNullOrEmpty()] [System.Collections.HashTable]$SharePointMetadata, [Parameter(Mandatory=$false)] [switch]$CheckSharePointBeforeCopy, [Parameter(Mandatory=$false)] [int]$NumberOfAttempts = 20 ) Begin { #region Startup $Cmdlet = 'Copy-PWDocumentToSharePoint' #endregion Startup #region Parameter Checks ## SharePoint URL $Message = "SharePoint URL is '$($SharePointConnection.URL)/$SharePointFolderPath'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message ## Working Directory if ((Test-Path -LiteralPath $WorkingDirectory)) { $Message = "Found working directory." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message } else { $Message = "Working directory does not exist!" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Level Error -Message $Message break; } ## Number of attempts $Message = "Number of attempts set to $NumberOfAttempts." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message ## SharePoint Metadata if ($SharePointMetadata) { $Message = "SharePoint metadata supplied." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message } else { $Message = "No SharePoint metadata supplied." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message } ## Check SharePoint if ($CheckSharePointBeforeCopy) { $Message = "Check SharePoint switch activated." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message } else { $Message = "Check SharePoint switch not activated. SharePoint will not be checked before copy." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message } #endregion Parameter Checks } Process { #region Export Document From ProjectWise $Message = "Exporting '$($ProjectWiseDocument.Name)' to '$WorkingDirectory'..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message try { CheckOut-PWDocuments -InputDocument $ProjectWiseDocument -CopyOut -ExportFolder $WorkingDirectory -NoReferences -ErrorAction Stop | Out-Null } catch { $Message = "Failed to export '$($DocumentToCopy.Name)' to '$WorkingDirectory'!" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Level Error -Message $Message Write-Log -Cmdlet $Cmdlet -Level Error -Message $_.Exception.Message Break; } $ExportedFilePath = "$WorkingDirectory\$($ProjectWiseDocument.FileName)" $ExportedFileExtension = ".$($ProjectWiseDocument.FileName.Split('.')[$ProjectWiseDocument.FileName.Split('.').Length -1])" $Message = "Exported FilePath: '$ExportedFilePath'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message if ((Test-Path -LiteralPath $ExportedFilePath)) { $Message = "Successfully exported '$($ProjectWiseDocument.Name)' to '$WorkingDirectory'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message } else { $Message = "'$($ProjectWiseDocument.Name)' could not be found in '$WorkingDirectory' after export!" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Level Error -Message $Message Break; } #endregion Export Document From ProjectWise #region Modify Exported File if ($SharePointDocumentName -eq $ProjectWiseDocument.FileName.Split('.')[0]) { $Message = "Target SharePoint document name matches ProjectWise file name. No modification required." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message $ModifiedFileName = "$SharePointDocumentName$ExportedFileExtension" $Message = "Target FileName: '$ModifiedFileName'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message $ModifiedFilePath = "$WorkingDirectory\$ModifiedFileName" $Message = "Target FilePath: '$ModifiedFilePath'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message } else { $Message = "Modifying exported file name to specified name..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message $ModifiedFileName = "$SharePointDocumentName$ExportedFileExtension" $Message = "Modified FileName: '$ModifiedFileName'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message $ModifiedFilePath = "$WorkingDirectory\$ModifiedFileName" $Message = "Modified FilePath: '$ModifiedFilePath'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message try { if ((Test-Path -LiteralPath $ModifiedFilePath)) { $Message = "File name matching specified name ($ModifiedFileName) already exists in '$WorkingDirectory'." Write-Warning $Message Write-Log -Cmdlet $Cmdlet -Level Warn -Message $Message $Message = "Overwriting existing file with latest version..." Write-Warning $Message Write-Log -Cmdlet $Cmdlet -Level Warn -Message $Message Remove-Item -LiteralPath $ModifiedFilePath -Force Rename-Item -LiteralPath $ExportedFilePath -NewName $ModifiedFileName } else { Rename-Item -LiteralPath $ExportedFilePath -NewName $ModifiedFileName } } catch { $Message = "Failed to rename file!" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Level Error -Message $Message Write-Log -Cmdlet $Cmdlet -Level Error -Message $_.Exception.Message Break; } $Message = "Exported file successfully renamed to '$ModifiedFileName'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message } #endregion Modify Exported File #region Check SharePoint For File if ($CheckSharePointBeforeCopy) { $Message = "Checking if file exists in SharePoint..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message $i = 0 do { try { $i++ $ExistingFile = Get-PnPFile -Url "$SharePointFolderPath/$ModifiedFileName" -Connection $SharePointConnection -ErrorAction Stop } catch { $Message = "Failed to find $($ProjectWiseDocument.Name) in SharePoint on attempt $i." Write-Warning $Message Write-Log -Cmdlet $Cmdlet -Level Warn -Message $Message Write-Log -Cmdlet $Cmdlet -Level Warn -Message $_.Exception.Message } Start-Sleep -Seconds 3 } while (!($ExistingFile) -and $i -lt 3) if ($ExistingFile) { $Message = "'$ModifiedFileName' already exists in SharePoint. Updating with latest version..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message } else { $Message = "'$ModifiedFileName' does not exist in SharePoint. Creating new file..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message } } #endregion Check SharePoint For File #region Upload File To SharePoint $Message = "Uploading file to SharePoint..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message $i = 0 do { try { $i++ if ($SharePointMetadata) { $SharePointFile = Add-PnPFile -Path $ModifiedFilePath -Folder $SharePointFolderPath -Connection $SharePointConnection -Values $SharePointMetadata -ErrorAction Stop } else { $SharePointFile = Add-PnPFile -Path $ModifiedFilePath -Folder $SharePointFolderPath -Connection $SharePointConnection -ErrorAction Stop } } catch { $Message = "Failed to upload $($ProjectWiseDocument.Name) to SharePoint on attempt $i." Write-Warning $Message Write-Log -Cmdlet $Cmdlet -Level Warn -Message $Message Write-Log -Cmdlet $Cmdlet -Level Warn -Message $_.Exception.Message } Start-Sleep -Seconds 3 } while (!($SharePointFile) -and $i -lt $NumberOfAttempts) if ($SharePointFile) { $Message = "Successfully uploaded file to SharePoint on attempt $i." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message } else { $Message = "Failed to upload $($ProjectWiseDocument.Name) to SharePoint after $NumberOfAttempts attempts." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Level Error -Message $Message } #endregion Upload File To SharePoint #region Remove Temporary File $Message = "Removing temporary file '$ModifiedFileName' from '$WorkingDirectory'..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message try { Remove-Item -LiteralPath $ModifiedFilePath -Force } catch { $Message = "Failed to remove temporary file from working directory!" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Level Error -Message $Message Write-Log -Cmdlet $Cmdlet -Level Error -Message $_.Exception.Message break; } $Message = "Successfully removed temporary file from working directory." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message #endregion Remove Temporary File } End { #region Return SharePoint File if ($SharePointFile) { $Properties = @{ Name = $SharePointFile.Name; URL = "$($SharePointConnection.Url)/$SharePointFolderPath/$($SharePointFile.Name)" } $OutputObject = New-Object -TypeName PSObject -Property $Properties Write-Output $OutputObject } #endregion Return SharePoint File } } # Purpose : Returns items from a given SharePoint list # Requirements: # - Write-Log function # - SharePointPnPPowerShell* module (Install-Module SharePointPnPPowerShell2013 or Install-Module SharePointPnPPowerShell2016 or Install-Module SharePointPnPPowerShellOnline) function Get-SharePointListItem { <# .SYNOPSIS Opens a connection to a specified SharePoint2013 server. .DESCRIPTION This function is a simplified wrapper for the login cmdlet in the SharePointPnPPowerShell2013 module. It does not provide the same ability to change granular settings. The login is placed in a loop, as the SharePoint module often fails on the first few attempts, but succeeds after - this pattern is common to all the SharePoint2013 wrapper functions. .PARAMETER SharePointConnection SharePoint connection object. (Generated using New-SharePointConnection) .PARAMETER SharePointList Name of target SharePoint list .PARAMETER NumberOfAttemps Integer value for number of attempts before returning a failure. .EXAMPLE This example will return the list items from the SharePoint list 'Target List'. Get-SharePointListItem -SharePointConnection $SharePointConnection -SharePointList 'Target List' -NumberOfAttempts 10 -Verbose #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [SharePointPnP.PowerShell.Commands.Base.SPOnlineConnection]$SharePointConnection, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SharePointList, [Parameter(Mandatory=$false)] [int]$NumberOfAttempts = 20 ) Begin { #region Startup $Cmdlet = 'Get-SharePointListItem' #endregion Startup #region Parameter Checks ## SharePoint URL $Message = "SharePoint URL is '$($SharePointConnection.URL)'." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message ## Number of attempts $Message = "Number of attempts set to $NumberOfAttempts." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message #endregion Parameter Checks } Process { #region Return SharePoint List Items $Message = "Returning items in SharePoint list '$SharePointList'..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message $i = 0 do { try { $i++ $SharePointListItems = Get-PnPListItem -List $SharePointList -Connection $SharePointConnection -ErrorAction Stop } catch { $Message = "Failed to return SharePoint list items on attempt $i." Write-Warning $Message Write-Log -Cmdlet $Cmdlet -Level Warn -Message $Message Write-Log -Cmdlet $Cmdlet -Level Warn -Message $_.Exception.Message } Start-Sleep -Seconds 3 } while (!($SharePointListItems) -and $i -lt $NumberOfAttempts) if ($SharePointListItems) { $Message = "Successfully returned SharePoint list items on attempt $i." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message } else { $Message = "Failed to return SharePoint list items after $NumberOfAttempts attempts!" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Level Error -Message $Message break; } #endregion Return SharePoint List Items } End { #region Return SharePoint List Items if ($SharePointListItems) { Write-Output $SharePointListItems } #endregion Return SharePoint List Items } } # Purpose : Opens a connection to a specified SharePoint server # Requirements: # - Write-Log function # - SharePointPnPPowerShell* module (Install-Module SharePointPnPPowerShell2013 or Install-Module SharePointPnPPowerShell2016 or Install-Module SharePointPnPPowerShellOnline) function New-SharePointConnection { <# .SYNOPSIS Opens a connection to a specified SharePoint server. .DESCRIPTION This function is a simplified wrapper for the login cmdlet in the SharePointPnPPowerShell* module. It does not provide the same ability to change granular settings. The login is placed in a loop, as the SharePoint module often fails on the first few attempts, but succeeds after - this pattern is common to all the SharePoint2013 wrapper functions. .PARAMETER SharePointURL The URL of the SharePoint server. ('https://my-sharepoint-server.com') .PARAMETER Credentials PSCredential object containing the username and login for the SharePoint server. $Credentials = New-Object -TypeName PSCredential -ArgumentList ('UserName',(Read-Host -Prompt "Enter password" -AsSecureString)) .PARAMETER SharePointVersion Version of SharePoint being used. (2013/2016/365) .PARAMETER NumberOfAttemps Integer value for number of attempts before returning a failure. Default number is 20. .EXAMPLE This example will open a connection to the specified SharePoint2013 server. New-SharePointConnection -SharePointURL 'https://my-sharepoint-server.com' -Credentials $Credentials -SharePointVersion 2013 -Verbose #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SharePointURL, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [System.Management.Automation.PSCredential]$Credentials, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [ValidateSet("2013","2016","365")] [string]$SharePointVersion, [Parameter(Mandatory=$false)] [int]$NumberOfAttempts = 20 ) Begin { #region Startup $Cmdlet = 'New-SharePointConnection' #endregion Startup #region Requirements ## Import SharePointPnPPowerShell switch ($SharePointVersion) { "2013" { $Message = "Checking for SharePointPnPPowerShell2013 module..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { if (!($Module = Get-Module -Name SharePointPnPPowerShell2013)) { $Message = "Importing SharePointPnPPowerShell2013 module..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info Import-Module -Name SharePointPnPPowerShell2013 -ErrorAction Stop $Module = Get-Module -Name SharePointPnPPowerShell2013 } } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error importing SharePointPnPPowerShell2013 Module." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error break; } } "2016" { $Message = "Checking for SharePointPnPPowerShell2016 module..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { if (!($Module = Get-Module -Name SharePointPnPPowerShell2016)) { $Message = "Importing SharePointPnPPowerShell2016 module..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info Import-Module -Name SharePointPnPPowerShell2016 -ErrorAction Stop $Module = Get-Module -Name SharePointPnPPowerShell2016 } } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error importing SharePointPnPPowerShell2016 Module." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error break; } } "365" { $Message = "Checking for SharePointPnPPowerShellOnline module..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info try { if (!($Module = Get-Module -Name SharePointPnPPowerShellOnline)) { $Message = "Importing SharePointPnPPowerShellOnline module..." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Info Import-Module -Name SharePointPnPPowerShellOnline -ErrorAction Stop $Module = Get-Module -Name SharePointPnPPowerShellOnline } } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error importing SharePointPnPPowerShellOnline Module." Write-Error $Message Write-Log -Cmdlet $Cmdlet -Message $Message -Level Error break; } } } if (!($Module)) { break; } #endregion Requirements #region Parameter Checks ## SharePointServer $Message = "SharePoint server is $SharePointURL." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message ## Number of attempts $Message = "Number of attempts set to $NumberOfAttempts." Write-Verbose $Message Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message #endregion Parameter Checks } Process { #region Login to SharePoint $Message = "Logging into SharePoint..." Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message Write-Verbose $Message $i = 0 do { try { $i++ $SharePointConnection = Connect-PnPOnline -Url $SharePointURL -Credentials $Credentials -ReturnConnection -ErrorAction Stop } catch { $Message = "Failed to open SharePoint connection on attempt $i." Write-Warning $Message Write-Log -Cmdlet $Cmdlet -Level Warn -Message $Message Write-Log -Cmdlet $Cmdlet -Level Warn -Message $_.Exception.Message } Start-Sleep -Seconds 3 } while (!($SharePointConnection) -and $i -lt $NumberOfAttempts) #endregion Login to SharePoint } End { #region Return Connection if (!($SharePointConnection)) { $Message = "Failed to open SharePoint connection after $NumberOfAttempts attempts!" Write-Error $Message Write-Log -Cmdlet $Cmdlet -Level Error -Message $Message break; } else { $Message = "Successfully opened SharePoint connection to $($SharePointConnection.URL) on attempt $i." Write-Log -Cmdlet $Cmdlet -Level Info -Message $Message Write-Verbose $Message Write-Output $SharePointConnection } #endregion Return Connection } } #endregion SharePoint |