Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
ProjectWise
  • Product Communities
ProjectWise
ProjectWise Design Integration Forum SQL Query to reconstruct a users complete audit trail
    • Sign In

    • State Not Answered
    • Replies 4 replies
    • Subscribers 62 subscribers
    • Views 1040 views
    • Users 0 members are here
    • Audit Trail
    • SQL database
    • ProjectWise

    SQL Query to reconstruct a users complete audit trail

    Matthew Saxon
    Offline Matthew Saxon over 3 years ago

    Is it possible to build an SQL query pulling information out of tables like dms_audt, and whatever other ones contain the relevant data, in order to reconstruct all the actions a particular user has undertaken on any document in the system? I.E. every Copy Out, every Sent to Folder, every Check Out, every Check In etc. etc. ?

    • Sign in to reply
    • Cancel
    • Scott Turner
      0 Offline Scott Turner Tue, Apr 7 2020 6:20 PM

      You can using PowerShell.

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Bill Graefe
      0 Offline Bill Graefe Fri, Apr 10 2020 10:15 AM in reply to Scott Turner

      This is the only option as they don't have lookup tables in the database (that I found) which is needed for what was done. PowerShell has Get-PWUserAuditTrailRecords but it doesn't want to work for me.

      I have pwps_dab 1.20.5.0

      $me = Get-PWUser 'myID' #returns one correct object

      Get-PWUserAuditTrailRecords -StartDate 2020-01-01 -Users $me

      Adding the optional end date didn't help

      Get-PWUserAuditTrailRecords -EndDate 2020-04-01 -StartDate 2020-01-01 -Users $me

      Give's me these errors. Bold is mine

      Get-PWUserAuditTrailRecords : Cannot bind parameter 'Users'. Cannot convert the "Bentley.ProjectWise.PowerShell.Common.User" value of type "Bentley.ProjectWise.PowerShell.Common.User" to type "Bentley.ProjectWise.PowerShell.Common.User".
      At line:1 char:78
      + ... uditTrailRecords -EndDate 2020-04-01 -StartDate 2020-01-01 -Users $me
      + ~~~
      + CategoryInfo : InvalidArgument: (:) [Get-PWUserAuditTrailRecords], ParameterBindingException
      + FullyQualifiedErrorId : CannotConvertArgumentNoMessage,PWPS_DAB.GetPWUserAuditTrailRecords

      Yup, it doesn't like the object type that says it wants. Using PS 5.1 

      Brian Flaherty could you add this to the backlog?

      It only likes objects from Get-PWUsersInGroup (an array I presume). One object comes back from Get-PWUser 

      Bill

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Matthew Saxon
      0 Offline Matthew Saxon Fri, Apr 10 2020 8:51 PM in reply to Bill Graefe

      I ended up managing to do it using a somewhat inelegant solution: creating queries for the two relevant SQL tables and then running lookups in excel with the combined results.

      I'll post the details later but yes, it'd be nice if you could do it in powershell!  

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Matthew Saxon
      0 Offline Matthew Saxon Thu, Apr 30 2020 2:32 AM in reply to Matthew Saxon

      Okay, this query will produce all the document actions for a named user (description in this instance). 

      SELECT o_audtno, o_objtype, o_objguid, o_objno, o_action, o_userno, o_acttime, o_comments, o_numparam1, o_numparam2, o_textparam, o_guidparam, o_username, o_userdesc, o_itemname, o_itemdesc,
      o_parentguid
      FROM dms_audt
      where o_objguid is not null and o_parentguid is not null
      and o_userdesc = ''

      The only thing missing here is the work area number if needed. You could get this by running the additional query 

      select
      o_projectno,
      o_docguid,
      from [dms_doc]

      And then do a looked up of the guid from the second to the first in excel or equivalent. 

      • 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