#PowerShell, #PowerWiseScripting, #ProjectWise, PWPS_DAB

HowTo: Report on Large Files

In this post, we will be creating a PowerShell function script file (.psm1) to generate a report (Excel spreadsheet) containing a list of documents which meet or exceed a specified file size. 

All of the ProjectWise related cmdlets are available using the PWPS_DAB module. At the time of this post, I am using version 1.22.1.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

  • Get-PWCurrentDatasource
  • New-PWLogin
  • Select-PWSQL
  • Get-PWDocumentsByGUIDs
  • New-XLSXWorkbook
  • Undo-PWLogin

FUNCTION Definition

The following is the function wrapper which will contain the remainder of the code.

FUNCTION Save-PWLargeFileReport {
    <#
            .SYNOPSIS
Generates a report listing large files.
.DESCRIPTION
Generates a report listing files which exceed the provided size limit.
.EXAMPLE
Save-PWLargeFileReport -FileSizeToTestFor 50mb -OutputFileName 'd:\temp\export\LargeFileReport.xlsx' -Verbose
# Gets all document objects for documents with physical file sizes greater than the provided FileSizeToTestFor value.
# Exports the document Name, URN, FileSize, File Extension and creator of the document to an Excel spreadsheet. #> [CmdletBinding()] param (...) # end param... BEGIN {...} # end BEGIN... PROCESS {...} # end PROCESS... END{...} # end END... } # end FUNCTION Save-PWLargeFileReport... Export-ModuleMember -Function Save-PWLargeFileReport

Parameters

First thing we need to do is create our parameter and value pairs. The help messages will explain the purpose for each parameter. 

[CmdletBinding()]
param ( 
    # File size to test for. Example: 50mb
    [ValidateNotNullOrEmpty()] 
    [Parameter(
        HelpMessage = 'File size to test for.',
        Mandatory = $true,
        Position = 0)]
    [int32] $FileSizeToTestFor,

    # Folder path and name of report to be generated.
    [ValidateNotNullOrEmpty()]
    [Parameter(
        HelpMessage = 'Folder path and name of report to be generated.',
        Mandatory = $true,
        Position = 1)]
    [string] $OutputFileName
) # end param...

Begin

I want to take a second and point out a couple of conventions that I use in my scripts. First, you may notice that I put a comment at the end of many of the blocks of code. Doing this makes it easier for me to determine where a block of code starts and ends. Comes in handy when troubleshooting a script.  I also add text ([BEGIN], [PROCESS], [END]) in the output messages (Write-Verbose, Write-Warning, Write-Error, Write-Host) corresponding to the section of the script I am in. Again, this makes it obvious where something occurs within the script.

Here we will ensure we are logged into our ProjectWise datasource. If not, we will be prompted to log in.

BEGIN {  
    $CmdletName = $MyInvocation.MyCommand.Name
$StartTime = Get-Date
Write-Host "[BEGIN] $StartTime - Entering '$CmdletName' Function..." -ForegroundColor Cyan 

<# Determine if logged into a ProjectWise datasource.
If not, display login dialog. #>

if( -not (Get-PWCurrentDatasource)) {
New-PWLogin -UseGui
$LoggedIn = $true
} } # end BEGIN...

PROCESS

Now, we will proceed to the PROCESS code block.

We will wrap the entire contents of the PROCESS block within a try / catch block to capture any errors that may occur.

PROCESS {
    try {
        ...
    } catch {
Write-Warning "[PROCESS] Error occurred while generating report. $($Error[0])" } # end try/catch... } # end PROCESS...

Within the try / catch block, the first thing we will do is run a SQL select statement to get the document GUID to all documents which exceeds the value provided within the $FileSizeToTestFor parameter.

<# Use a SQL Select to return all documents from within the current datasource 
which are greater than the provided file size to test for. #>
$sql = "SELECT o_docguid AS DOCGUID FROM dms_doc WHERE o_size > $($FileSizeToTestFor)"
$sqlResults = Select-PWSQL -SQLSelectStatement $sql -ErrorAction Stop

Next, we will use the results of the Select-PWSQL cmdlet to get the ProjectWise Document objects by passing the GUIDs.

# Get ProjectWise document objects based on GUIDs returned in the SELECT.
$myLargeDocs = Get-PWDocumentsByGUIDs -DocumentGUIDs $sqlResults.DOCGUID -ErrorAction Stop

Write-Host "[PROCESS] $($myLargeDocs.Count) documents returned which have a file size greater than '$FileSizeToTestFor'." -ForegroundColor DarkGreen

We will need to create a new datatable to populate with the document data.

# Create new datatable
$dt = [System.Data.Datatable]::New('Files')
# Add the following columns to the datatable.
$dt.Columns.AddRange(@('DocName', 'FileName', 'FullPath', 'DocURN', 'FileSize (MB)', 'FileExtension', 'UserName'))

Now, we will loop through each of the document objects and populate the datatable with the corresponding data.

# Loop through each of the documents returned.
foreach ($thisDoc in $myLargeDocs) {

# Get the extension
$split = $thisDoc.FileName.Split(".")
$fileExt = $split[$split.Count - 1]
# Only use extensions the are 3 characters or less.
if ($fileExt.Length -gt 4) {
$fileExt = ''
}

#region Create and Populate new row

# Add new data row.
$dr = $dt.NewRow()
# Populate new data row.
$dr.DocName = $thisDoc.Name
$dr.FileName = $thisDoc.FileName
$dr.FullPath = $thisDoc.FullPath
$dr.DocURN = $thisDoc.DocumentURN
# Rounding to two decimal places.
$dr.'FileSize (MB)' = [math]::Round($thisDoc.FileSize / 1MB, 2)
$dr.FileExtension = $fileExt
$dr.UserName = $thisDoc.DocumentCreator.Email
# Add new row to datatable.
$dt.Rows.Add($dr)

#endregion Create and Populate new row

} # foreach ($thisDoc in $myLargeDocs...

Finally, now that the datatable has been populated we can export it to the Excel.

# Export datatable to Excel if records were added.
if($dt.Rows.Count -gt 0) {
New-XLSXWorkbook -InputTables $dt -OutputFileName $OutputFileName -ErrorAction Stop
Write-Host "[PROCESS] Successfully created report '$OutputFileName'." -ForegroundColor DarkGreen
}

END

Lastly, we will proceed to the END block of code. We will log out of our ProjectWise datasource ONLY if we logged in while in the function.

END {
# Log out of ProjectWise datasource if logged in within the function.
if($LoggedIn){
Undo-PWLogin
}

$EndTime = Get-Date
Write-Host "[END] It took $($EndTime - $StartTime) to complete the process." -ForegroundColor Cyan
Write-Host "[END] $EndTime - Exiting '$CmdletName' Function..." -ForegroundColor Cyan
} # end END...

The following shows an example of report generated.

report


Experiment with it and have fun. The following is a link to the full PowerShell script.

FUNCTION – Save-PWLargeFileReport

Hopefully, you find this useful. Please let me know if you have any questions or comments.  If you like this post, please click the Like button at the bottom of the page.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.