Had a user request some assistance with searching the audit trail to determine the folder id of a folder(s) which were inadvertently deleted by a user. So, in this post, I will be demonstrating a quick method to retrieve the folder id of a folder(s) which have been deleted by a user. Again, this information is stored in the audit trail table and can be retrieved easily using PowerShell. Assuming the the Audit Trail is configured to capture folder events.
All of the ProjectWise related cmdlets are available using the PWPS_DAB module. At the time of this post, I am using version 1.30.4.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
To retrieve the folder id for the folder(s) deleted, we will use the Select-PWSQL cmdlet and build a query to include the user’s ID, the action type for folder delete (4) and a date to minimize the amount of data returned. You can refine the query to limit the number of columns returned.
NEVER RETURN THE ENTIRE AUDIT TRAIL TABLE
Use care when querying the Audit Trail table as it can be very large.
# Provide the user name of the user who deleted the folder.
$UserName = 'Joe.User'
# We can include the action type within a variable for future use.
$Action = 4 # folder delete.
# Provide a date to filter the amount of data returned.
$Date = '9/18/2021 07:00:00 AM'
# Get the user id for the provided user name.
$UserID = Get-PWUsersByMatch -UserName $UserName |
Select-Object -ExpandProperty ID
# Build the SQL query to be used.
$SQLSelectStatement = "SELECT * FROM dms_audt WHERE o_acttime > '$Date' AND o_action = $Action AND o_userno = $UserID"
# Run the query to retrieve the data.
$pwaudt = Select-PWSQL -SQLSelectStatement $SQLSelectStatement
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.
