HowTo: Monitoring Recent ProjectWise Activity with PowerShell

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.

Identify What’s Happening in Your Datasource (and Why Performance Might Be Hurting)

When users complain that Bentley Systems ProjectWise feels “slow,” the root cause is often increased activity inside the datasource: bulk imports, Work Area creation/deletion, workflow transitions, automated processes, or permission changes.

One of the fastest ways to understand what’s happening right now is to look at the audit trail. This post introduces a PowerShell function that retrieves the latest ProjectWise audit records so administrators can quickly identify patterns that may be impacting performance.

Why Audit Trail Monitoring Matters

The ProjectWise audit table (dms_audt) is a goldmine of operational insight. By querying recent records, you can:

Spot spikes in document check-ins, exports, or imports
Identify workflows or bulk operations running during peak hours
Detect automated processes or integrations creating heavy load
Correlate user complaints with actual system activity

Instead of guessing, you get hard data about what the system has been doing in the last few minutes or hours.

What the Function Does

The Get-PWAuditTrailRecords function:

Queries the dms_audt table for the most recent activity
Lets you control:
- How many records to return
- How far back in time to look (in hours)
Translates numeric action codes into human-readable action names
Returns a structured dataset you can filter, export, or analyze further

This makes it ideal for real-time troubleshooting and ad-hoc diagnostics.

PowerShell Script

			
FUNCTION Get-PWAuditTrailRecords{
    <# 
        .SYNOPSIS 
        Used to get the latest audit trail data. 
        .DESCRIPTION 
        Used to get the latest audit trail data. Allows you to specify the number of records and how many hours to go back.
        .EXAMPLE 
        # Returns the latest 2000 records from last hour.
        $results = Get-PWAuditTrailRecords -NumberOfRecords 2000 -Hours 1
    #> 
    [CmdletBinding()]
    param(
       # Number of records to return.Default is 1000.
       [ValidateNotNullOrEmpty()]
       [Parameter(
               HelpMessage = "Number of records to return. Default is 1000.",
               Mandatory = $false,
       Position = 0)]
       [int] $NumberOfRecords = 1000,
       # Number of hours back to return data for. Default is 2.
       [ValidateNotNullOrEmpty()]
       [Parameter(
               HelpMessage = "Number of hours back to return data for. Default is 2.",
               Mandatory = $false,
       Position = 1)]
       [int] $Hours = 2
    ) #end param...
    BEGIN {
    
        $CmdletName = $MyInvocation.MyCommand.Name
        $StartTime = Get-Date
        Write-Host "[BEGIN] $Start - Entering '$CmdletName' Function..." -ForegroundColor Cyan
    
        $AuditTrailActions = @{
            1   = 'FOLDER_CREATE'
            2   = 'FOLDER_MODIFY'
            3   = 'FOLDER_WFLOW'
            4   = 'FOLDER_DELETE'
            5   = 'FOLDER_STATE'
            6   = 'FOLDER_ACL_ASSIGN'
            7   = 'FOLDER_ACL_MODIFY'
            8   = 'FOLDER_ACL_REMOVE'
            9   = 'FOLDER_CONNECT_ASSIGN'
            10  = 'FOLDER_CONNECT_CHANGE'
            11  = 'FOLDER_CONNECT_REMOVE'
            12  = 'FOLDER_RESTORE'
            100 = 'FOLDER_CUSTOM_FIRST'
            999 = 'FOLDER_CUSTOM_LAST'
            # 'DOCUMENT_ATTR_CREATE' = 1
            # 'DOCUMENT_ATTR_MODIFY' = 2
            # 'DOCUMENT_ATTR_DELETE' = 3
            1000 = 'DOCUMENT_UNKNOWN'
            1001 = 'DOCUMENT_CREATE'
            1002 = 'DOCUMENT_MODIFY'
            1003 = 'DOCUMENT_ATTR'
            1004 = 'DOCUMENT_FILE_ADD'
            1005 = 'DOCUMENT_FILE_REM'
            1006 = 'DOCUMENT_FILE_REP'
            1007 = 'DOCUMENT_CIN'
            1008 = 'DOCUMENT_VIEW'
            1009 = 'DOCUMENT_CHOUT'
            1010 = 'DOCUMENT_CPOUT'
            1011 = 'DOCUMENT_GOUT'
            1012 = 'DOCUMENT_STATE'
            1013 = 'DOCUMENT_FINAL_S'
            1014 = 'DOCUMENT_FINAL_R'
            1015 = 'DOCUMENT_VERSION'
            1016 = 'DOCUMENT_MOVE'
            1017 = 'DOCUMENT_COPY'
            1018 = 'DOCUMENT_SECUR'
            1019 = 'DOCUMENT_REDLINE'
            1020 = 'DOCUMENT_DELETE'
            1021 = 'DOCUMENT_EXPORT'
            1022 = 'DOCUMENT_FREE'
            1023 = 'DOCUMENT_EXTRACT'
            1024 = 'DOCUMENT_DISTRIBUTE'
            1025 = 'DOCUMENT_SEND_TO'
            1026 = 'DOCUMENT_COMMENT'
            1027 = 'DOCUMENT_IMPORT'
            1028 = 'DOCUMENT_ACL_ASSIGN'
            1029 = 'DOCUMENT_ACL_MODIFY'
            1030 = 'DOCUMENT_ACL_REMOVE'
            1031 = 'DOCUMENT_REVIT'
            1032 = 'DOCUMENT_PACK'
            1033 = 'DOCUMENT_UNPACK'
            1034 = 'DOCUMENT_WRE_START'
            1035 = 'DOCUMENT_WRE_END'
            1036 = 'DOCUMENT_WRE_FAILURE'
            1037 = 'DOCUMENT_RESTORE'
            1100 = 'DOCUMENT_CUSTOM_FIRST'
            1999 = 'DOCUMENT_CUSTOM_LAST'
            2001 = 'SET_CREATE'
            2002 = 'SET_ADD'
            2003 = 'SET_REMOVE'
            2100 = 'SET_CUSTOM_FIRST'
            2999 = 'SET_CUSTOM_LAST'
            7001 = 'WORKFLOW_CREATE'
            7002 = 'WORKFLOW_ADD'
            7003 = 'WORKFLOW_REMOVE'
            7004 = 'WORKFLOW_RENAME'
            7005 = 'WORKFLOW_DELETE'
            7006 = 'WORKFLOW_MOVE'
            6001 = 'STATE_CREATE'
            6002 = 'STATE_RENAME'
            6003 = 'STATE_DELETE'
            3001 = 'USER_LOGIN'
            3002 = 'USER_LOGOUT'
            3003 = 'USER_CREATE'
            3004 = 'USER_MODIFY'
            3005 = 'USER_SETTINGS'
            3006 = 'USER_RENAME'
            3007 = 'USER_DELETE'
            3008 = 'USER_DISABLE'
            3009 = 'USER_ENABLE'
            4001 = 'GROUP_CREATE'
            4002 = 'GROUP_MODIFY'
            4003 = 'GROUP_ADD'
            4004 = 'GROUP_REMOVE'
            4005 = 'GROUP_RENAME'
            4006 = 'GROUP_DELETE'
            4007 = 'GROUP_ACL_ASSIGN'
            4008 = 'GROUP_ACL_MODIFY'
            4009 = 'GROUP_ACL_REMOVE'
            5001 = 'USER_LIST_CREATE'
            5002 = 'USER_LIST_MODIFY'
            5003 = 'USER_LIST_ADD'
            5004 = 'USER_LIST_REMOVE'
            5005 = 'USER_LIST_RENAME'
            5006 = 'USER_LIST_DELETE'
            5007 = 'USER_LIST_ACL_ASSIGN'
            5008 = 'USER_LIST_ACL_MODIFY'
            5009 = 'USER_LIST_ACL_REMOVE'
            8001 = 'INTERFACE_CREATE'
            8002 = 'INTERFACE_RENAME'
            8003 = 'INTERFACE_DELETE'
            8101 = 'ENVIRONMENT_CREATE'
            8102 = 'ENVIRONMENT_MODIFY'
            8103 = 'ENVIRONMENT_RENAME'
            8104 = 'ENVIRONMENT_DELETE'
            8105 = 'ENVIRONMENT_VIEW_REMOVE'
            8106 = 'ENVIRONMENT_VIEW_CHANGE'
            9001 = 'VIEW_CREATE'
            9002 = 'VIEW_RENAME'
            9003 = 'VIEW_DELETE'
            9004 = 'VIEW_CHANGE'
            9201 = 'DEPARTMENT_CREATE'
            9202 = 'DEPARTMENT_RENAME'
            9203 = 'DEPARTMENT_DELETE'
            9101 = 'APPLICATION_CREATE'
            9102 = 'APPLICATION_RENAME'
            9103 = 'APPLICATION_DELETE'
            9301 = 'ENVIRONMENT_ATTR_ADD'
            9302 = 'ENVIRONMENT_ATTR_REMOVE'
            9303 = 'ENVIRONMENT_ATTR_MODIFY'
            20001 = 'WORKAREA_CREATE'
            20002 = 'WORKAREA_PROP_ADD'
            20003 = 'WORKAREA_PROP_REMOVE'
            20004 = 'WORKAREA_RENAME'
            20005 = 'WORKAREA_DELETE'
            9104 = 'APPLICATION_VIEWER'
        }
    } #end BEGIN...
    PROCESS {
        try{
            $currentDate = (Get-Date).AddHours(- $Hours)
            
            $sqlResults = Select-PWSQL "SELECT TOP $NumberOfRecords * FROM dms_audt WHERE o_acttime > '$currentDate' ORDER BY o_acttime DESC"
                        
            $sqlResults.Columns.Add('ActionType') | Out-Null
            $sqlResults.Columns["ActionType"].SetOrdinal(5)
            foreach($row in $sqlResults.Rows){ # break
                $row.ActionType = $AuditTrailActions[$row.o_action]
            }
            Write-Output $sqlResults
        } catch {
            Write-Warning -Message $_
        }
    } #end PROCESS...
    END{
    
       $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...
} #end FUNCTION Get-PWAuditTrailRecords...
Export-ModuleMember -Function Get-PWAuditTrailRecords

		

Example Usage

			
# Returns the latest 2000 audit trail records from the last hour
$results = Get-PWAuditTrailRecords -NumberOfRecords 2000 -Hours 1

From here, you can easily explore the results:

$results | Group-Object ActionType | Sort-Object Count -Descending

This gives you an instant breakdown of what types of actions are dominating your system activity.

Real-World Use Cases

Here are a few practical scenarios where this function shines:

1. Performance Troubleshooting
Users report slow performance at 10 AM every day. Run the function around that time and identify whether mass exports, document imports, or workflow changes are spiking.

2. Identifying Noisy Integrations
If you have integrations or scheduled jobs running against ProjectWise, audit trail data can reveal whether they’re overactive or misbehaving.

3. Change Auditing
Quickly review recent deletes, moves, ACL changes, or workflow actions when investigating “who changed what.”

Tips for Production Use

Be mindful of record counts.
Pulling tens of thousands of rows from dms_audt repeatedly can impact your SQL server.
Use short time windows for diagnostics.
Start with -Hours 1 or -Hours 2 when troubleshooting live issues.
Export for deeper analysis.
Combine with Export-Csv to trend activity over time:

$results | Export-Csv "C:\Temp\PWAuditTrail.csv" -NoTypeInformation

Final Thoughts

ProjectWise doesn’t always make it obvious what’s happening under the hood. By surfacing recent audit activity in a clean, readable format, this function gives administrators immediate visibility into real-time system behavior.

If you’re troubleshooting performance issues, this should be one of the first tools you reach for.

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. And thank you for checking out my blog.

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.

Why Audit Trail Monitoring Matters

What the Function Does

PowerShell Script

Example Usage

Real-World Use Cases

Tips for Production Use

Final Thoughts

Share this:

Related

Published by Brian Flaherty

Leave a comment Cancel reply