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
    • 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
    • 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
    Reply
    • 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
    Children
    No Data

    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