In this post, I’ll show a function I created for getting all audit trail records for a provided user. This function will poplulate a datatable with the records which can be exported to a report.
FUNCTION Definition
The following is the function wrapper which will contain the remainder of the code.
FUNCTION Get-PWAuditTrailRecordsByUser { <# .Synopsis Returns all audit trail records for the provided user. One caveat, it only returns audit trail records in the primary audit trail table. .DESCRIPTION Populates a datatable containing ALL audit trail records for the provided user. .EXAMPLE # Create a datatable to be populated with the user's audit trail data. $dtRecords = [Data.Datatable]::new("Records") $UserName = myUserName Get-PWAuditTrailRecordsByUser -UserName $UserName ([ref]$dtRecords) -Verbose #> [CmdletBinding()] param (...) # end param... BEGIN {...} # end BEGIN... PROCESS {...} # end PROCESS... END{...} # end END... } # end FUNCTION Get-PWAuditTrailRecordsByUser... Export-ModuleMember -Function Get-PWAuditTrailRecordsByUser
Parameters
First thing we need to do is create our parameter and value pairs. The help messages will explain the purpose for each parameter.
param (
# User name to return all audit trails records for.
[ValidateNotNullOrEmpty()]
[Parameter(Mandatory = $true)]
[string] $UserName,
# Referenced datatable to populate. This is required to return the data into a datatable.
# Otherwise, it will return an array of datarows.
# When adding the ValidateScript, be sure the [ref] is after the [Data.Datatable]. Otherwise it will fail.
[ValidateScript({
if($_.GetType().Name -eq 'DataTable'){
$true
} else {
$false
}})]
[Parameter(Mandatory = $true)]
[Data.Datatable][ref] $dtRecords
) # 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) corresponding to the section of the script I am in. Again, this makes it obvious where something occurs within the script.
BEGIN {
$CmdletName = $MyInvocation.MyCommand.Name
$StartTime = Get-Date
Write-Verbose -Message "[BEGIN] $StartTime - Entering '$CmdletName' Function..."
#region POPULATE AUDITTRAIL DATATABLES
# Populates a datatable containing the various audit trail object types.
# Added to make the data more readable.
$AuditTrailObjectTypes = @(
"1,FOLDER"
"2,DOCUMENT"
"3,SET"
"4,WORKFLOW"
"5,STATE"
"6,USER"
"7,GROUP"
"8,USERLIST"
"9,INTERFACE"
"10,ENVIRONMENT"
"11,VIEW"
"12,DEPARTMENT"
"13,APPLICATION"
"14,ENVIRONMENT ATTRIBUTES"
"15,WORKAREA TYPE"
)
$dtAuditTrailObjectTypes = [Data.Datatable]::new("AuditTrailObjectType")
$dtAuditTrailObjectTypes.Columns.Add("TYPEID", [int]) | Out-Null
$dtAuditTrailObjectTypes.Columns.Add("Description", [string]) | Out-Null
foreach($entry in $AuditTrailObjectTypes){
$dtAuditTrailObjectTypes.Rows.Add($($entry.Split(','))) | Out-Null
}
#endregion POPULATE AUDITTRAIL DATATABLES
} # end BEGIN...
PROCESS
Now, we will proceed to the PROCESS code block.
We will first determine if the provided user account exists within the active ProjectWise datasource. There are other ways to accomplish this. For example adding a VerifyScript block to the $UserName parameter.
Next we will query the primary audit trail table only. All returned data will be merged into the referenced datatable parameter.
PROCESS {
try {
# Verify the provided user account is valid in the active ProjectWise datasource.
if( -not ($pwUser = Get-PWUsersByMatch -UserName $UserName -ErrorAction Stop -WarningAction SilentlyContinue)){
throw "Invalid user name provided."
}
Write-Verbose -Message "[PROCESS] Verified user '$UserName' exists in current ProjectWise datasource."
$sqlQuery = "SELECT o_objtype AS TYPE, o_objguid AS OBJECT_GUID,
o_objno AS OBJECT_NUMBER, o_action AS ACTION_ID, o_acttime AS DATE_TIME,
o_comments AS COMMENTS, o_numparam1 AS PARAM_01, o_numparam2 AS PARAM_02,
o_textparam AS TEXT_PARAM, o_guidparam AS GUID_PARAM, o_itemname AS ITEM_NAME,
o_itemdesc AS ITEM_DESCRIPTION, o_parentguid AS PARENT_GUID
FROM dms_audt WHERE o_userno = $($pwUser.ID)
ORDER BY o_objtype, o_objno, o_action, o_acttime"
$sqlResults = Select-PWSQL -SQLSelectStatement $sqlQuery -Verbose -ErrorAction Stop
Write-Verbose -Message "[PROCESS] $($sqlResults.Rows.Count) audit trail records returned."
# Add two additional columns to add the action and type_name for readability.
$colAction = $sqlResults.Columns.Add("ACTION", [string])
$colAction.SetOrdinal(4)
$colTypeName = $sqlResults.Columns.Add("TYPE_NAME", [string])
$colTypeName.SetOrdinal(1)
foreach($row in $sqlResults.Rows){
# Add TYPE_NAME value from the previously created datatable.
$row.TYPE_NAME = $dtAuditTrailObjectTypes |
Where-Object TYPEID -eq $row.TYPE |
Select-Object -ExpandProperty Description
# Add ACTION value retrieved using a ProjectWise method.
$action = [string]::Empty
$row.ACTION = [pwwrapper]::aaApi_GetAuditTrailActionTypeName($row.ACTION_ID, [ref]$action)
}
# Populate the referenced datatable with the audit trail data.
$dtRecords.Merge($sqlResults)
} catch {
Write-Warning -Message "[PROCESS] $_"
} # end try/catch...
} # end PROCESS...
END
Lastly, we will proceed to the END block of code.
END {
$EndTime = Get-Date
Write-Verbose -Message "[END] It took $($EndTime - $StartTime) to complete the process."
Write-Verbose -Message "[END] $EndTime - Exiting '$CmdletName' Function..."
} # end END...
EXAMPLE
The following is a simple example usage of the Get-PWAuditTrailRecordsByUser function.
# Datatable to populate with the user audit trail records.
$UserName = 'pwadmin'
$dtRecords = [Data.Datatable]::new()
Get-PWAuditTrailRecordsByUser -UserName $UserName ([ref]$dtRecords) -Verbose
The following shows the results within PowerShell.
Experiment with it and have fun. Hopefully, you find this useful.
The following is a link to a zip file containing the script.
FUNCTION_Get-PWAuditTrailRecordsByUser
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.
powerwisescripting.blog 