Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
ProjectWise
  • Product Communities
ProjectWise
ProjectWise PowerShell Extensions Forum Document State Change Report
    • Sign In

    • State Verified Answer
    • Replies 10 replies
    • Subscribers 66 subscribers
    • Views 1508 views
    • Users 0 members are here

    Document State Change Report

    Scott Turner
    Offline Scott Turner over 2 years ago

    I am writing a script to dump Audit Trail records, specifically document state changes so they can be reported.
    I've tried Export-PWAuditTrailToSQLite, Export-PWAuditTrailNotificationReport and Get-PWAuditTrailFromPreviousDays and none of them seem to capture action 1021, which is a state change.

    Am I missing something? Has anyone been able to get at this data?

    Thank you,

    Scott

    • Sign in to reply
    • Cancel

    Top Replies

    • Kevin van Haaren
      Offline Kevin van Haaren Thu, Oct 7 2021 1:35 PM in reply to Scott Turner +1 verified
      OK, I figured this out. After seeing your command-line I kind of realized what was happening but figuring all out kind of drove me crazy. First up - When it worked for me I also setting an EndTime in…
    Parents
    • MWBSI
      0 MWBSI Wed, Oct 6 2021 4:45 PM

      Scott,

      Action type 5 is Folder State Change, 1012 is Document State Change and 1021 is Document Export.  There is really no way for you to know this as the action types are not documented.   As to why they are used in the SQLLite export, welll, I think the intent of the Export-PWAuditTrailToSQLLite was to create a relational database, not a report per se.  The tables AuditTrail and Actions are normalized and the numerical action type is a key, according to relational database speak.   That said you can create something more along the lines of what you are looking for by creating a Report table from an inner join.   For example you can open the exported file in SQLLite Studio, open the SQL Editor and execute the following statement: 

      CREATE TABLE Report AS SELECT *
      FROM AuditTrail
      JOIN Actions
      ON AuditTrail."action" = Actions.id;

      A few of other things:

      In order for the Document State Change and Folder State change events to show up in the audit trail they need to be checked off in the Audit Trail section of the datasource settings.  It looks a folder state change occurs every time a document's state is changed in a that folder but hey, I'm just a Powershell guy so you may want to verify. 

      Please obtain the latest update of PWPS_DAB as more action types have recently been added to the audit trail cmdlets.

      HTH,

      Mark Weisman | Senior Consultant | Bentley Systems, Inc.

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Scott Turner
      0 Offline Scott Turner Wed, Oct 6 2021 5:25 PM in reply to MWBSI

      Hi Mark,

      Thank you. I am running the latest PWPS_DAB.

      The SQLite method will work fine, but it will be more beneficial if we can use that to demonstrate the history of a document, inclusive of all state changes. Can the PWAuditTrailToSQLite cmdlet be enhanced with this?

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Kevin van Haaren
      0 Offline Kevin van Haaren Wed, Oct 6 2021 7:04 PM in reply to Scott Turner

      The document change state actions appear for me in the SQLite table when I do an export.

      As for why they don't appear for you:

      1. Is your datasource actually auditing those events? I believe they are enabled by default on recent versions but if your ProjectWise originated in an older version they may not have been. If you check the audit trail from ProjectWise Explorer do you see the events
      2. What command line are you using with Export-PWAuditTrailToSQLite?
      3. How are you verifying document state changes aren't included? The Action number for document state changes is 1012, not 1021. There is a table of action numbers to action names included in the export. I've listed all the action items mine shows below. 1021 is a document export.

      id	name
      1	Folder Create
      2	Folder Modify
      3	Folder Workflow Change
      4	Folder Delete
      5	Folder State Change
      6	Folder Access Control Assigned
      7	Folder Access Control Modified
      8	Folder Access Control Removed
      9	Folder Connect Assign
      10	Folder Connect Change
      11	Folder Connect Remove
      1000	Document Unknown Action
      1001	Document Create
      1002	Document Modify
      1003	Document Attribute Create/Modify
      1004	Document File Add
      1005	Document File Remove
      1006	Document File Replace
      1007	Document Check In
      1008	Document View
      1009	Document Check Out
      1010	Document Copy Out
      1011	Document Give Out
      1012	Document State Change
      1013	Document Final Status Set
      1014	Document Final Status Removed
      1015	Document Version Change
      1016	Document Move
      1017	Document Copy
      1018	Document Permission Change
      1019	Document Markup
      1020	Document Delete
      1021	Document Export
      1022	Document Free
      1023	Document Extract
      1024	Document Distribute
      1025	Document Send to Mail Recipient
      1026	Document Comment
      1027	Document Import
      1028	Document Access Control Assigned
      1029	Document Access Control Modified
      1030	Document Access Control Removed
      1031	Document Revit Worksharing Mode
      1032	Document Pack
      1033	Document Unpack
      2001	Document Set Create
      2002	Document Set Add Member
      2003	Document Set Remove Member
      3001	User Login
      3002	User Logout
      3003	User Create
      3004	User Modified
      3005	Change User Settings
      3006	User Renamed
      3007	User Deleted
      3008	User Disabled
      3009	User Enabled
      4001	Group Create
      4002	Group Modify
      4003	Group Add Member
      4004	Group Rmove Member
      5001	UserList Create
      5002	UserList Modify
      5003	UserList Add Member
      5004	UserList Remove Member
      

       

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Scott Turner
      0 Offline Scott Turner Wed, Oct 6 2021 8:53 PM in reply to Kevin van Haaren

      Hi Kevin,

      Thank you for having a look.

      I tried two datasources, but using WRE and in both the state change even appears in the audit trail for the document.

      My script:

      New-PWLogin -UseGui
      Export-PWAuditTrailToSQLite -OutputFile C:\PowerShell\EDS_Audit_Trail.sqlite -StartDate 10/01/2021
      Close-PWConnection

      To check the results, I did two things:

      First, using DB Browser, I wrote a query to return all the 1012 events (sorry for transposing the numbers). That resulted in zero records, even though I was literally looking at the audit trail of a document in the same datasource that had state change events.

      When that didn't work (I did it twice to be sure) I wrote another query to return all events ordered by the action. Still zero, even though there were plenty of other records.

      I did check the audit trail settings at the DS level, and right now everything is being audited, and there is no secondary table.

      Would you be willing to share what you used to successfully return 1012, please?

      Thank you!

      Scott

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Kevin van Haaren
      +1 Offline Kevin van Haaren Thu, Oct 7 2021 1:35 PM in reply to Scott Turner

      OK, I figured this out. After seeing your command-line I kind of realized what was happening but figuring all out kind of drove me crazy.

      First up - When it worked for me I also setting an EndTime in my command-line. My EndTime was tomorrow's date (clue 1).

      Next I realized the Audit Trail Logs (at least for me) are in UTC time. I'm in US Central (UTC -6). If I did an export using an end time of the exact date/time of my current time (from Get-Date), it wouldn't get anything that occurred in the last 6 hours.

      My solution is basically to always use an end time of the current time, in UTC, but an hour in the future. Using an exact time end time seemed to fail too.

      $UTCNow      = (Get-Date).ToUniversalTime()
      $UTCLastHour = $UTCNow.AddHours(-1)
      $UTCNextHour = $UTCNow.AddHours(1)
      Export-PWAuditTrailToSQLite -OutputFile $AuditFile -StartDate $UTCLastHour -EndDate $UTCNextHour -ErrorAction Stop
      

      I wrote a whole script to repeatedly test this if you're interested in that. I use the SimplySQL module to do SQLite queries from PowerShell.

      Try {
      	write-verbose "Loading modules"
      	# PWPS_DAB outputs info when module imported, the output does not obey the -InformationAction value, redirect info stream 6 to null
      	Import-Module PWPS_DAB        -Verbose:$false -ErrorAction Stop -Force -DisableNameChecking 6> $Null
      	Import-Module SimplySQL       -Verbose:$false -ErrorAction Stop
      } catch {
      	write-error "Unable to load one or more required modules.`n`n$($_)"
      	throw $Error[0]
      }
      
      $UTCNow      = (Get-Date).ToUniversalTime()
      $UTCLastHour = $UTCNow.AddHours(-1)
      $UTCNextHour = $UTCNow.AddHours(1)
      
      $AuditFile = 'C:\CADDLib\AuditTrail.sqlite3'
      $LocalFile = 'C:\CADDLib\TestFile.txt'
      
      $PWFolder = 'Users\KvanHaaren\AuditTesting'
      $PWFile = 'TestFile.txt'
      
      $result = New-PWLogin -datasource 'pw-int.hntb.org:workspaces' -BentleyIMS -UseGUI:$false
      
      # File already exists?
      $pwdoc = Get-PWDocumentsBySearch -FolderPath $PWFolder -FileName $PWFile -WarningAction SilentlyContinue
      
      # Delete existing
      if (-not ([string]::IsNullOrEmpty($pwdoc))) {
      	write-host 'deleting existing file'
      	$result = Remove-PWDocuments -InputDocument $pwdoc
      }
      
      write-host 'Adding file'
      $pwdoc = New-PWDocument -FilePath $LocalFile -FolderPath $PWFolder
      
      write-host 'Changing state on file'
      $pwdoc = Set-PWDocumentState -InputDocuments $pwdoc -State 'In Process'
      
      # Export the audit trail to sqlite file. Export fails if file already exists.
      if (Test-Path -Path $AuditFile -PathType Leaf) {
      	write-host 'Deleting existing audit trail db file'
      	Remove-Item -Path $AuditFile -Force
      }
      write-host 'Exporting Audit Trail'
      # This fails to get last few events in audit trail:
      #Export-PWAuditTrailToSQLite -OutputFile $AuditFile -StartDate '10/07/2021' -ErrorAction Stop
      
      # This works:
      Export-PWAuditTrailToSQLite -OutputFile $AuditFile -StartDate $UTCLastHour -EndDate $UTCNextHour -ErrorAction Stop
      
      # Query sqlite file with SimplySQL
      $result = Open-SQLiteConnection -ConnectionString "Data Source=$($AuditFile);Version=3;FailIfMissing=True;" -ConnectionName "AuditTrail" -ErrorAction Stop
      
      $Query = @"
      SELECT *
      FROM AuditTrail
      WHERE ((objectid1 = $($pwdoc.ProjectID)) and (objectid2 = $($pwdoc.DocumentID)))
      "@
      
      write-host "Querying Audit Trail, query:`n$($Query)"
      $result = Invoke-SqlQuery -Query $query -ConnectionName "AuditTrail"
      Write-Host "Current time: $(Get-Date)"
      Write-Host "Returned record count: $($result.count)"
      Write-Host "Results:"
      $result | FT LogDate,Objectid1,Objectid2,Action
      
      # Close connection or sqlite db file is held open!
      Close-SQLConnection -ConnectionName 'AuditTrail'
      
      # Logout
      $result = Undo-PWLogin
      

      I noticed your example uses a start date at the beginning of the month. I'm assuming there were no state changes prior to your testing? I couldn't explain why you wouldn't see any state results that fell in the proper UTC time window.

      My script results:

      deleting existing file
      Adding file
      Changing state on file
      Deleting existing audit trail db file
      Exporting Audit Trail
      Querying Audit Trail, query:
      SELECT *
      FROM AuditTrail
      WHERE ((objectid1 = 18923) and (objectid2 = 23))
      Current time: 10/07/2021 12:22:56
      Returned record count: 2
      Results:
      
      LogDate              objectid1 objectid2 action
      -------              --------- --------- ------
      10/7/2021 5:22:53 PM     18923        23   1001
      10/7/2021 5:22:53 PM     18923        23   1012

       

      Answer Verified By: Scott Turner 

      • Cancel
      • Vote Up +1 Vote Down
      • Sign in to reply
      • Reject Answer
      • Cancel
    Reply
    • Kevin van Haaren
      +1 Offline Kevin van Haaren Thu, Oct 7 2021 1:35 PM in reply to Scott Turner

      OK, I figured this out. After seeing your command-line I kind of realized what was happening but figuring all out kind of drove me crazy.

      First up - When it worked for me I also setting an EndTime in my command-line. My EndTime was tomorrow's date (clue 1).

      Next I realized the Audit Trail Logs (at least for me) are in UTC time. I'm in US Central (UTC -6). If I did an export using an end time of the exact date/time of my current time (from Get-Date), it wouldn't get anything that occurred in the last 6 hours.

      My solution is basically to always use an end time of the current time, in UTC, but an hour in the future. Using an exact time end time seemed to fail too.

      $UTCNow      = (Get-Date).ToUniversalTime()
      $UTCLastHour = $UTCNow.AddHours(-1)
      $UTCNextHour = $UTCNow.AddHours(1)
      Export-PWAuditTrailToSQLite -OutputFile $AuditFile -StartDate $UTCLastHour -EndDate $UTCNextHour -ErrorAction Stop
      

      I wrote a whole script to repeatedly test this if you're interested in that. I use the SimplySQL module to do SQLite queries from PowerShell.

      Try {
      	write-verbose "Loading modules"
      	# PWPS_DAB outputs info when module imported, the output does not obey the -InformationAction value, redirect info stream 6 to null
      	Import-Module PWPS_DAB        -Verbose:$false -ErrorAction Stop -Force -DisableNameChecking 6> $Null
      	Import-Module SimplySQL       -Verbose:$false -ErrorAction Stop
      } catch {
      	write-error "Unable to load one or more required modules.`n`n$($_)"
      	throw $Error[0]
      }
      
      $UTCNow      = (Get-Date).ToUniversalTime()
      $UTCLastHour = $UTCNow.AddHours(-1)
      $UTCNextHour = $UTCNow.AddHours(1)
      
      $AuditFile = 'C:\CADDLib\AuditTrail.sqlite3'
      $LocalFile = 'C:\CADDLib\TestFile.txt'
      
      $PWFolder = 'Users\KvanHaaren\AuditTesting'
      $PWFile = 'TestFile.txt'
      
      $result = New-PWLogin -datasource 'pw-int.hntb.org:workspaces' -BentleyIMS -UseGUI:$false
      
      # File already exists?
      $pwdoc = Get-PWDocumentsBySearch -FolderPath $PWFolder -FileName $PWFile -WarningAction SilentlyContinue
      
      # Delete existing
      if (-not ([string]::IsNullOrEmpty($pwdoc))) {
      	write-host 'deleting existing file'
      	$result = Remove-PWDocuments -InputDocument $pwdoc
      }
      
      write-host 'Adding file'
      $pwdoc = New-PWDocument -FilePath $LocalFile -FolderPath $PWFolder
      
      write-host 'Changing state on file'
      $pwdoc = Set-PWDocumentState -InputDocuments $pwdoc -State 'In Process'
      
      # Export the audit trail to sqlite file. Export fails if file already exists.
      if (Test-Path -Path $AuditFile -PathType Leaf) {
      	write-host 'Deleting existing audit trail db file'
      	Remove-Item -Path $AuditFile -Force
      }
      write-host 'Exporting Audit Trail'
      # This fails to get last few events in audit trail:
      #Export-PWAuditTrailToSQLite -OutputFile $AuditFile -StartDate '10/07/2021' -ErrorAction Stop
      
      # This works:
      Export-PWAuditTrailToSQLite -OutputFile $AuditFile -StartDate $UTCLastHour -EndDate $UTCNextHour -ErrorAction Stop
      
      # Query sqlite file with SimplySQL
      $result = Open-SQLiteConnection -ConnectionString "Data Source=$($AuditFile);Version=3;FailIfMissing=True;" -ConnectionName "AuditTrail" -ErrorAction Stop
      
      $Query = @"
      SELECT *
      FROM AuditTrail
      WHERE ((objectid1 = $($pwdoc.ProjectID)) and (objectid2 = $($pwdoc.DocumentID)))
      "@
      
      write-host "Querying Audit Trail, query:`n$($Query)"
      $result = Invoke-SqlQuery -Query $query -ConnectionName "AuditTrail"
      Write-Host "Current time: $(Get-Date)"
      Write-Host "Returned record count: $($result.count)"
      Write-Host "Results:"
      $result | FT LogDate,Objectid1,Objectid2,Action
      
      # Close connection or sqlite db file is held open!
      Close-SQLConnection -ConnectionName 'AuditTrail'
      
      # Logout
      $result = Undo-PWLogin
      

      I noticed your example uses a start date at the beginning of the month. I'm assuming there were no state changes prior to your testing? I couldn't explain why you wouldn't see any state results that fell in the proper UTC time window.

      My script results:

      deleting existing file
      Adding file
      Changing state on file
      Deleting existing audit trail db file
      Exporting Audit Trail
      Querying Audit Trail, query:
      SELECT *
      FROM AuditTrail
      WHERE ((objectid1 = 18923) and (objectid2 = 23))
      Current time: 10/07/2021 12:22:56
      Returned record count: 2
      Results:
      
      LogDate              objectid1 objectid2 action
      -------              --------- --------- ------
      10/7/2021 5:22:53 PM     18923        23   1001
      10/7/2021 5:22:53 PM     18923        23   1012

       

      Answer Verified By: Scott Turner 

      • Cancel
      • Vote Up +1 Vote Down
      • Sign in to reply
      • Reject Answer
      • Cancel
    Children
    • Scott Turner
      0 Offline Scott Turner Thu, Oct 7 2021 2:45 PM in reply to Kevin van Haaren

      Hi Kevin,

      Wow, thank you!

      The UTC makes sense... I did have a test file that I had just changed the state on, so my error in part was not taking the date into consideration.

      I am trying a new version of the script now, with this in mind. One thing I am noticing is that it is taking a very long time to run now. Did you see this also?

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Kevin van Haaren
      0 Offline Kevin van Haaren Thu, Oct 7 2021 4:06 PM in reply to Scott Turner

      not particularly, but my tests are run from a computer with almost a direct connection to the data center the server is in and this is a test datasource on fairly lightly loaded database server.

      are you using the -IncludeFullObjectNames option? I think that slows it down quite a bit.

       

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Scott Turner
      0 Offline Scott Turner Thu, Oct 7 2021 5:00 PM in reply to Kevin van Haaren

      Thank you.

      I am back to pretty much what I started with, but nothing is exported. I'm going to go take a look, perhaps someone did some housecleaning and my test doc is gone.

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel

    Communities
    • Home
    • Getting Started
    • Community Central
    • Products
    • Support
    • Secure File Upload
    • Feedback
    Support and Services
    • Home
    • Product Support
    • Downloads
    • Subscription Services Portal
    Training and Learning
    • Home
    • About Bentley Institute
    • My Learning History
    • Reference Books
    Social Media
    •    LinkedIn
    •    Facebook
    •    Twitter
    •    YouTube
    •    RSS Feed
    •    Email

    © 2023 Bentley Systems, Incorporated  |  Contact Us  |  Privacy |  Terms of Use  |  Cookies