HowTo: Get Document Count for Disabled User(s)

Be sure to check out my Scripting4Crypto initiative. It’s a fun way to get into using cryptocurrencies all while getting your PowerShell needs met.

In this post, I will demonstrate how to generate a report listing all disabled accounts within a ProjectWise (PW) datasource and the number of documents they were the creator of. The purpose was to determine which accounts could safely be removed from the datasource without losing any data. Basically, if an account was not the creator of any documents, it is safe to remove. This is not a difficult task but I found it pretty useful and I hope you do to.

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

• Get-PWUsersByMatch
• Select-PWSQL
• New-XLSXWorkbook

Get All ProjectWise User Accounts

The first thing I did was grab ALL of the PW user accounts within the datasource. This may seem a bit excessive, however, it is actually more efficient (in most cases) to grab all of the data needed in one gulp as opposed to little bits at a time. Everything is in memory on the local machine.

Once I had all PW user account objects, I filtered them to only include disabled accounts.

# Get all ProjectWise user accounts.
$pwUsers_All = Get-PWUsersByMatch -LastLogin
# Filter results to only include disabled PW User account objects.
$pwUsers_Disabled = $pwUsers_All | Where-Object IsDisabled -eq $True

Run SQL Query to Get Document Data

Next, we will build and run a SQL query to return all of the documents associated with the disabled PW users. The result will be a datatable being returned containing ONLY the documents associated with the disabled PW user accounts.

With the later releases of ProjectWise, run SQL queries may not be an option. You could contact your DBA and request this information. Or you could use the Get-PWDocumentsBySearch cmdlet. This will take a bit longer to run but will give you the same results.

# Building the SQL select statement to get the document information.
# Get only the user IDs. The following will result with a string where each user id is separated by a comma.
$IDs = ($pwUsers_Disabled | Select-Object -ExpandProperty ID) -join ','
# The SQL query statement to be run.
$sqlSelect = "SELECT o_itemname, o_creatorno FROM dms_doc WHERE o_creatorno IN ($IDs)"
# Run and return a datatable containing the results of the query.
$sqlResults_Docs = Select-PWSQL -SQLSelectStatement $sqlSelect

# Review first 10 rows of the results.
$dtDisabled.Rows | Select-Object -First 10 | Format-Table

Create Datatable

Here we will create a datatable to store the data to be exported to an Excel file. 

# Create the new datatable with the name 'DisabledAccounts'
$dtDisabled = [Data.Datatable]::new('DisabledAccounts')
# Columns to be added to the new datatable.
$columns = @('UserID', 'UserName', 'UserDescription', 'IsDisabled', 'HasDocuments')
$dtDisabled.Columns.AddRange($columns) | Out-Null
# Adding an integer column to store the document count.
$dtDisabled.Columns.Add('DocumentCount', [int]) | Out-Null

Populate Datatable

Next, we will loop through the user accounts and get the corresponding document count by selecting data from the SQL query results.  Notice I have included a Write-Progess section which will display a progress bar within PowerShell when run.

# The following is a counter for the progress bar. 
$Counter = 1
$itemCount = $pwUsers_Disabled.Count
# Loop through each datasource.
foreach($pwUser in $pwUsers_Disabled){

    #region PROGRESS SECTION

    $Progress = @{ 
    Activity = "Getting document information for '$($pwUser.UserName) ($($pwUser.Description))'." 
    Status = "Processing $Counter of $ItemCount" 
    PercentComplete = $([math]::Round($(($Counter / $ItemCount) * 100 ), 2)) 
    } 
    Write-Progress @Progress -Id 1

    # Increment counter.
    $Counter++

    #endregion PROGRESS SECTION

    # Create a new row which will be added to the datatable.
    $dr = $dtDisabled.NewRow()
    # Populate the new datarow.
    $dr.UserID          = $pwUser.ID
    $dr.UserName        = $pwUser.UserName
    $dr.UserDescription = $pwUser.Description
    $dr.IsDisabled      = $pwUser.IsDisabled

    $DocCount = 0
    $HasDocuments = $False

    try {
        $id = $pwUser.ID
        $results = $sqlResults_Docs.Select("o_creatorno = $id").Count

        if($results.Count -gt 0) {
            $DocCount = $results
        }
    } catch {
        Write-Warning -Message "Error occurred attempting to get document count for user '$($pwUser.UserName)'."
    }

    if($DocCount -gt 0){
        $HasDocuments = $True
    }
    $dr.HasDocuments  = $HasDocuments
    $dr.DocumentCount = $DocCount

    # Add the new row to the datatable.
    $dtDisabled.Rows.Add($dr)

} # end foreach($pwUser in $pwUsers_Disabled)

Export to Excel

Finally, we will export the datatable to Excel. The result will be an Excel spreadsheet with one worksheet with the name corresponding to the datatable name exported.

# Generate new report. Remove the -Open switch parameter if you do not want to open Excel upon completion.
New-XLSXWorkbook -InputTables $dtDisabled -OutputFileName "c:\temp\PWUsers_Disabled_DocCount_20210409.xlsx" -Open

Summary

To recap, we have generated an Excel file containing the disabled accounts within a PW datasource and their corresponding document counts. This report can be used to help determine which PW user accounts can safely be removed.

---

Experiment with it and have fun.

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.

Please let me know if you have any questions, comments, etc.