HelperFunctions.psm1
|
#region Security 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 } #endregion #region Conversion 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-Debug -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-Debug -Message "$($property.Name): Supported datatype <$($dataType)>" } else { $dataType = $DefaultType Write-Debug -Message "$($property.Name): Unsupported datatype ($($property.TypeNameOfValue)), using default <$($DefaultType)>" } # create a new datacolumn $dataColumn = New-Object 'System.Data.DataColumn' $property.Name, $dataType Write-Debug -Message 'Created new DataColumn' # add column to DataTable $dataTable.Columns.Add($dataColumn) Write-Debug -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-Debug -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-Debug -Message 'Value added to row as XML' } else{ $dataRow.Item($property.Name) = $property.Value -as $dataType Write-Debug -Message "Value ($($property.Value)) added to row as $($dataType)" } } } # add DataRow to DataTable $dataTable.Rows.Add($dataRow) Write-Debug -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)) } } #endregion #region Logging function Write-PWPSLog { <# .SYNOPSIS Writes and appends logs to a log file. .DESCRIPTION Writes and appends logs to a log file. Format for each row is "DateTime [Level] Cmdlet - Message". Max file size for a single log is 10MB, and a maximum of four log files will be created. The older file will rollover if four files exist and the current file size exceeds the size limit. .PARAMETER Message Log message to write. .PARAMETER Path Path to target log file. File will be created if it doesn't exist. Default path is "C:\users\<username>\AppData\Local\Bentley\Logs\PowerShellLogging.log". .PARAMETER Level Severity level for the target log line. Accepts 'Error', 'Warn' and 'Info'. .PARAMETER Cmdlet Name of the cmdlet/function/script writing the log line. This is for sorting purposes when analysing logs, so source function/cmdlet/script can be easily identified. .PARAMETER NoClobber Switch to enagle NoClobber. If enabled and the target log file already exists, the log will not be written and the file cannot be overwritten. .EXAMPLE This example will write an info level log with function name Test-Logging to the default log location, with the message 'We are testing the logging'. Write-PWPSLog -Cmdlet 'Test-Logging' -Level Info -Message 'We are testing the logging'. .EXAMPLE This example will write an error level log with function name Test-Logging to C:\temp\log.log, with the message 'There was an error!'. Write-PWPSLog -Cmdlet 'Test-Logging' -Level Error -Message 'There was an error!'. #> [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" } } function Get-PWPSLogsFromPreviousDays { <# .SYNOPSIS Returns log entries written by Write-PWPSLog from a specified number of days ago. .DESCRIPTION Searches a log file written using the Write-PWPSLog 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 $LogFilePath } 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 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' if($FileBrowser.ShowDialog() -eq "OK") { $FilePath += $FileBrowser.FileName } return $FilePath } #endregion Windows #region SQL 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-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } try { Truncate-SQLTable -SQLConnection $SQLConnection -TableName $DataTable.TableName -ErrorAction Stop } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error truncating table." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully truncated table." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } #endregion Truncate Table #region Define SQL Objects ### Initiate Bulk Copy object $Message = "Initiating SQL Bulk Copy object." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } try { $SQLBulkCopy = New-Object Data.SqlClient.SqlBulkCopy $SQLConnection $SQLBulkCopy.DestinationTableName = $Datatable.TableName } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error initiating SQL Bulk Copy object." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully initiated SQL Bulk Copy object." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } #endregion Define SQL Objects } End { #region Bulk Copy table $Message = "Performing bulk copy of '$($Datatable.TableName)' to database '$($SQLConnection.DatabaseName)'..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } $Message = "Copying $($Datatable.Rows.Count) rows..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } try { $SQLBulkCopy.WriteToServer($Datatable) } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error bulk copying table." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully performed bulk copy." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } #endregion Bulk Copy table } } function Get-SQLDatabase { <# .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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } try { $SQLSMO = New-Object Microsoft.SqlServer.Management.Smo.Server $SQLConnection -ErrorAction Stop } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error initiating SQL Server object." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully initiated SQL Server Management object." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } #endregion Define SQL Objects #region Database Check ### Check if database exists if ($Database) { $Message = "Checking if '$Database' exists in '$($SQLConnection.ServerInstance)'..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } if ($Database -in $SQLSMO.Databases.Name) { $Message = "Found '$Database' in '$($SQLConnection.ServerInstance)'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } else { $Message = "'$Database' does not exist in '$($SQLConnection.ServerInstance)'." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } } #endregion Database Check } End { #region Return Database try { if ($Database) { $Message = "Returning $Database..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } Write-Output $SQLSMO.Databases[$Database] } else { $Message = "Returning databases..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } Write-Output $SQLSMO.Databases } } catch { $Message = "Error returning database. Aborting script..." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } #endregion Return Database } } 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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } $Message = "Input data type is '$DataType'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } 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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } #endregion Convert Data Type } End { #region Return Data Type if ($SQLDataType) { $Message = "Data type conversion successful." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } Write-Output -InputObject $SQLDataType } else { $Message = "Failed to covert data type." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error break; } #endregion Return Data Type } } 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-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } 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-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error returning SQL Database object." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully returned SQL Database object." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } #endregion Define SQL Objects #region Check Table if ($TableName) { $Message = "Checking if table '$TableName' exists in '$($SQLConnection.ServerInstance)\$Database'..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } if ($TableName -in $SQLDBO.Tables.Name) { $Message = "Found table '$TableName' in database '$Database'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } else { $Message = "Could not find table '$TableName' in database '$Database'." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } } #endregion Check table } End { #region Return Table if ($TableName) { try { $Message = "Returning table '$TableName'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } Write-Output $SQLDBO.Tables[$TableName] } catch { $Message = "Error returning table." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } } else { try { $Message = "Returning tables in '$Database'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } Write-Output $SQLDBO.Tables } catch { $Message = "Error returning tables." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } } #endregion Return Table } } 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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } try { if (!(Get-Module -Name SqlServer)) { $Message = "Importing SqlServer module..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } Import-Module -Name SqlServer -ErrorAction Stop } } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error importing SqlServer Module." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error break; } #endregion Requirements #region Parameter Checks if ($WindowsAuthentication) { $Message = "Authentication mode: Windows." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } else { $Message = "Authentication mode: SQL Server." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } ### No password provided if (!($Password)) { $Message = "No password provided for SQL Server authentication." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error break; } ### No username provided if (!($Username)) { $Message = "No username provided for SQL Server authentication." Write-Error $Message Write-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } try { $SQLServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection ($SQLServer) -ErrorAction Stop } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error establishing connection to '$SQLServer'." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error break; } } else { $Message = "Establishing connection to '$SQLServer' as '$Username' using SQL Server authentication." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } try { $SQLServerConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection ($SQLServer, $Username, $Password) -ErrorAction Stop } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error establishing connection to '$SQLServer' as '$Username'." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error break; } } $Message = "Successfully established connection to '$SQLServer'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } #endregion Establish Connection #region Target Database if ($Database) { $Message = "Setting target database to '$Database'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } try { $SQLServerConnection.DatabaseName = $Database } catch { $Message = "Error setting target database." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error break; } } #endregion Target Database #region Open Connection $Message = "Opening connection..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } try { $SQLServerConnection.Connect() } catch { $Message = "Error opening connection. Check login credentials and inputs. Aborting script..." Write-Error $Message Write-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } Write-Output -InputObject $SQLServerConnection } else { $Message = "Failed to open SQL connection." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error break; } #endregion Return Connection } } 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-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } 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-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error returning SQL Database object." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully returned $($SQLDBO.Name)." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } #endregion Define SQL Objects #region Define Table ### Define table object $Message = "Defining SQL Table object..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } try { $SQLTable = New-Object Microsoft.SqlServer.Management.Smo.Table ($SQLDBO, $DataTable.TableName) -ErrorAction Stop } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error defining SQL Table object." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully defined table '$($SQLTable.Name)'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } ### Add columns to table $Message = "Preparing to add $($DataTable.Columns.Count) columns to '$($SQLTable.Name)'..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } foreach ($Column in $Datatable.Columns) { ### Convert data type try { $Message = "Converting data type for column '$($Column.ColumnName)'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } $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-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error converting data type." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } ### Define column try { $Message = "Defining column object '$($Column.ColumnName)'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } $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-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error defining column object." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } ### Add column try { $Message = "Adding column object '$($Column.ColumnName)' to table object '$($SQLTable.Name)'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } $SQLTable.Columns.Add($SQLColumn) } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error adding column object." Write-Error $Message Write-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } $SQLTable.Create() } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error adding table object." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } #endregion Create Table } } 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-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } try { $SQLTable = Get-SQLTable -SQLConnection $SQLConnection -TableName $TableName } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error returning table object." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully returned table object." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } #endregion Return SQL Table } End { #region Truncate table $Message = "Performing truncate on table '$TableName'..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } try { $SQLTable.TruncateData() } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error truncating table." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error return; } $Message = "Successfully truncated table." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } #endregion Truncate } } #endregion SQL #region Reporting 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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -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-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } if ($IncludeIntermediateDays) { $Message = "Include intermediate days switch activated!" Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } #endregion Checks } Process { #region Return Records $Message = "Returning audit trail records..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -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-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message break; } $Message = "Returned $AuditTrailRecordsCount audit trail records." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } #endregion Return Records #region Add Properties if ($AddDatasourceInformation) { $Message = "Adding properties to audit trail records..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } catch { $Message = "Failed to add properties to audit trail records!" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message break; } } elseif ($AuditTrailRecordsCount -eq 0) { $Message = "No audit trail records returned." Write-Warning $Message Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message } else { Write-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } if ($AuditTrailRecordsCount -gt 0) { Write-Output $AuditTrailRecords } #endregion Write Output } } #endregion Reporting #region SharePoint function Copy-FileToSharePoint { <# .SYNOPSIS Copies a file from Windows to SharePoint. .DESCRIPTION Takes a single file as input and uploads to specified location in SharePoint. .PARAMETER FilePath Windows filepath to target file. .PARAMETER SharePointConnection SharePoint connection object. (Generated using New-SharePointConnection) .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 CheckSharePointBeforeCopy Switch to check whether the file already exists in SharePoint. The file 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 file to /TargetFolder, and check if the document exists before copying. $Connection = New-SharePointConnection -SharePointURL 'https://my-sharepoint-server.com/Transmittals' -Credentials $Credentials -SharePointVersion 2013 -Verbose Copy-FileToSharePoint -FilePath C:\Path\To\File.pdf -SharePointConnection $Connection -SharePointFolderPath 'TargetFolder' -CheckSharePointBeforeCopy #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$FilePath, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [SharePointPnP.PowerShell.Commands.Base.SPOnlineConnection]$SharePointConnection, [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SharePointFolderPath, [Parameter(Mandatory=$false)] [switch]$CheckSharePointBeforeCopy, [Parameter(Mandatory=$false)] [int]$NumberOfAttempts = 20 ) Begin { #region Startup $Cmdlet = 'Copy-FileToSharePoint' $FileName = $FilePath | Split-Path -Leaf $FileNameNoExtension = $FileName.Substring(0, $FileName.lastIndexOf('.')) #endregion Startup #region Parameter Checks ## SharePoint URL $Message = "SharePoint URL is '$($SharePointConnection.URL)/$SharePointFolderPath'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } ## Input File if ((Test-Path -LiteralPath $FilePath)) { $Message = "Found target file." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } else { $Message = "Target file does not exist!" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message break; } ## Number of attempts $Message = "Number of attempts set to $NumberOfAttempts." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } ## File name $Message = "File name is '$FileName'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } #endregion Parameter Checks } Process { #region Check SharePoint For File if ($CheckSharePointBeforeCopy) { $Message = "Checking if file exists in SharePoint..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } $i = 0 do { try { $i++ $ExistingFile = Get-PnPFile -Url "$SharePointFolderPath/$FileNameNoExtension" -Connection $SharePointConnection -ErrorAction Stop } catch { $Message = "Failed to find '$FileName' in SharePoint on attempt $i." Write-Warning $Message Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $_.Exception.Message } Start-Sleep -Seconds 3 } while (!($ExistingFile) -and $i -lt 3) if ($ExistingFile) { $Message = "'$FileName' already exists in SharePoint. Updating with latest version..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } else { $Message = "'$FileName' does not exist in SharePoint. Creating new file..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } } #endregion Check SharePoint For File #region Upload File To SharePoint $Message = "Uploading '$FileName' to SharePoint..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } $i = 0 do { try { $i++ $SharePointFile = Add-PnPFile -Path $FilePath -Folder $SharePointFolderPath -Connection $SharePointConnection -ErrorAction Stop } catch { $Message = "Failed to upload file to SharePoint on attempt $i." Write-Warning $Message Write-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message Write-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } else { $Message = "Failed to upload '$FileName' to SharePoint after $NumberOfAttempts attempts." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message } #endregion Upload File To SharePoint } 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 } } 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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } ## Working Directory if ((Test-Path -LiteralPath $WorkingDirectory)) { $Message = "Found working directory." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } else { $Message = "Working directory does not exist!" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message break; } ## Number of attempts $Message = "Number of attempts set to $NumberOfAttempts." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } ## SharePoint Metadata if ($SharePointMetadata) { $Message = "SharePoint metadata supplied." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } else { $Message = "No SharePoint metadata supplied." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } ## Check SharePoint if ($CheckSharePointBeforeCopy) { $Message = "Check SharePoint switch activated." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } else { $Message = "Check SharePoint switch not activated. SharePoint will not be checked before copy." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } #endregion Parameter Checks } Process { #region Export Document From ProjectWise $Message = "Exporting '$($ProjectWiseDocument.Name)' to '$WorkingDirectory'..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -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-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message Write-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } if ((Test-Path -LiteralPath $ExportedFilePath)) { $Message = "Successfully exported '$($ProjectWiseDocument.Name)' to '$WorkingDirectory'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } else { $Message = "'$($ProjectWiseDocument.Name)' could not be found in '$WorkingDirectory' after export!" Write-Error $Message Write-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } $ModifiedFileName = "$SharePointDocumentName$ExportedFileExtension" $Message = "Target FileName: '$ModifiedFileName'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } $ModifiedFilePath = "$WorkingDirectory\$ModifiedFileName" $Message = "Target FilePath: '$ModifiedFilePath'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } else { $Message = "Modifying exported file name to specified name..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } $ModifiedFileName = "$SharePointDocumentName$ExportedFileExtension" $Message = "Modified FileName: '$ModifiedFileName'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } $ModifiedFilePath = "$WorkingDirectory\$ModifiedFileName" $Message = "Modified FilePath: '$ModifiedFilePath'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -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-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message $Message = "Overwriting existing file with latest version..." Write-Warning $Message Write-PWPSLog -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-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $_.Exception.Message Break; } $Message = "Exported file successfully renamed to '$ModifiedFileName'." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -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-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message Write-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } else { $Message = "'$ModifiedFileName' does not exist in SharePoint. Creating new file..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } } #endregion Check SharePoint For File #region Upload File To SharePoint $Message = "Uploading file to SharePoint..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -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-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message Write-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } else { $Message = "Failed to upload $($ProjectWiseDocument.Name) to SharePoint after $NumberOfAttempts attempts." Write-Error $Message Write-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -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-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message Write-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $_.Exception.Message break; } $Message = "Successfully removed temporary file from working directory." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -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 } } 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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } ## Number of attempts $Message = "Number of attempts set to $NumberOfAttempts." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -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-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message Write-PWPSLog -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } } else { $Message = "Failed to return SharePoint list items after $NumberOfAttempts attempts!" Write-Error $Message Write-PWPSLog -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 } } 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-SharePoint2013Connection -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 if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } try { if (!($Module = Get-Module -Name SharePointPnPPowerShell2013)) { $Message = "Importing SharePointPnPPowerShell2013 module..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } Import-Module -Name SharePointPnPPowerShell2013 -ErrorAction Stop $Module = Get-Module -Name SharePointPnPPowerShell2013 } } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error importing SharePointPnPPowerShell2013 Module." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error break; } } "2016" { $Message = "Checking for SharePointPnPPowerShell2016 module..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } try { if (!($Module = Get-Module -Name SharePointPnPPowerShell2016)) { $Message = "Importing SharePointPnPPowerShell2016 module..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } Import-Module -Name SharePointPnPPowerShell2016 -ErrorAction Stop $Module = Get-Module -Name SharePointPnPPowerShell2016 } } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error importing SharePointPnPPowerShell2016 Module." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error break; } } "365" { $Message = "Checking for SharePointPnPPowerShellOnline module..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } try { if (!($Module = Get-Module -Name SharePointPnPPowerShellOnline)) { $Message = "Importing SharePointPnPPowerShellOnline module..." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } Import-Module -Name SharePointPnPPowerShellOnline -ErrorAction Stop $Module = Get-Module -Name SharePointPnPPowerShellOnline } } catch { $Message = "$($PSItem.Exception.Message)" Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error $Message = "Error importing SharePointPnPPowerShellOnline Module." Write-Error $Message Write-PWPSLog -Cmdlet $Cmdlet -Message $Message -Level Error break; } } } if (!($Module)) { break; } #endregion Requirements #region Parameter Checks ## SharePointServer $Message = "SharePoint server is $SharePointURL." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } ## Number of attempts $Message = "Number of attempts set to $NumberOfAttempts." Write-Verbose $Message if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } #endregion Parameter Checks } Process { #region Login to SharePoint $Message = "Logging into SharePoint..." if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -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-PWPSLog -Cmdlet $Cmdlet -Level Warn -Message $Message Write-PWPSLog -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-PWPSLog -Cmdlet $Cmdlet -Level Error -Message $Message break; } else { $Message = "Successfully opened SharePoint connection to $($SharePointConnection.URL) on attempt $i." if ($PSCmdlet.MyInvocation.BoundParameters["Verbose"].IsPresent) { Write-PWPSLog -Cmdlet $Cmdlet -Level Info -Message $Message } Write-Verbose $Message Write-Output $SharePointConnection } #endregion Return Connection } } #endregion SharePoint |