Bentley Communities
Bentley Communities
  • Site
  • User
  • Site
  • Search
  • User
ProjectWise
  • Product Communities
ProjectWise
ProjectWise PowerShell Extensions Forum Help creating "24hr Changes" export script for a Work Area folder
    • Sign In

    • State Suggested Answer
    • Replies 3 replies
    • Answers 1 answer
    • Subscribers 65 subscribers
    • Views 426 views
    • Users 0 members are here
    • powershell
    • ProjectWise
    • pwps_dab
    • Export-PWDocuments
    • ProjectWise PowerShell Modules

    Help creating "24hr Changes" export script for a Work Area folder

    roll_n_a_bentley
    Offline roll_n_a_bentley over 1 year ago

    I'm trying to create a script that will search for any changes to files/folders made to a "Work Area" folder within a 24 hour period and export out a copy to a local folder for those changed items. This script will be executed once a day via Task Scheduler.

    I've been following this blog post as an example to set up this script, but my attempts to modify it export within the desired parameters so far haven't worked. It currently exports a copy of the ENTIRE "Work Area" folder instead of changes that were made within the last 24 hours. Please see my current PowerShell script below:

    Import-Module -Name pwps_dab
    
    cls
    
    $my_datasource="SERVER:datasource"
    $my_pw_user="administrator"
    
    $destination_local_dir="D:\pwbk\Full Backup\MAR2022"
    $destination_local_meta_dir="d:\pwbk\metadata\"
    $pwDocumentsToExport="Get-PWDocumentsBySearch -FolderID 12345 -JustThisFolder"
    $saved_search_name="List all docs updated in last 24 hours"
    
    
    try
    {
    Undo-PWLogin
    }
    catch{}
    # New-PWLogin -gui
    
    $SecurePasswordFile = 'C:\Users\XXXX\AppData\Local\Bentley\secure_pw.txt'
    
    
    $SecurePassword = Get-SecureStringFromEncryptedFile -FileName $SecurePasswordFile
    
    Write-Host "trying to login to "  $my_datasource  " User=" $my_pw_user
    
    $is_pw_logged = New-PWLogin -DatasourceName $my_datasource -UserName $my_pw_user -Password $SecurePassword 
    
    if ( -Not $is_pw_logged  )
    	{
    		#Write-Host "cannot login to "  $my_datasource  " User="  $my_pw_user
    		#exit;
    	}
    
    Write-Host "logged to "  $my_datasource  " User=" + $my_pw_user
    	
    
    
    # Populate variable with ProjectWise document objects to be exported.
    $pwDocumentsToExport = Get-PWDocumentsBySearch -FolderID 12345
    # Show the number of documents being exported.
    $pwDocumentsToExport.Count
    # Show list of document names.
    $pwDocumentsToExport | Select-Object Name
    
    
    # Local folder to export documents to.
    $exportFolder = 'D:\pwbk\Full Backup\MAR2022'
    # Open the local folder to see content.
    Explorer $exportFolder
    
    # Export documents to local folder.
    # Using splat for readability.
    $Splat_Export = @{
        InputDocuments = $pwDocumentsToExport
        OutputFolder = $exportFolder
    }
    
    #Export-PWDocuments -Searches $saved_search_name -ServerIsUTC -OutputFolder $destination_local_dir
    
    $Results = Export-PWDocuments @Splat_Export -Verbose -Searches $saved_search_name -ServerIsUTC -ExportMetadata -ExportVersions -ExportMetaDataFile $destination_local_metadata_file
    # Save-PWDocumentReport  -SearchName  $saved_search_name  -OutputFileName  $destination_local_metadata
    
    # Frees the documents which were exported. Files are no longer locked.
    $results = CheckIn-PWDocumentsOrFree -InputDocument $pwDocumentsToExport -Free -Verbose
    
    # Look at the audit trail for one of the documents.
    $pwDocumentsToExport | Select-Object -First 1 | 
    Get-PWDocumentAuditTrailRecords -SkipSecondary -Verbose | 
    Select-Object Item_Name, Action, Action_Time | Sort-Object Action_Time -Descending

    On lines "10" and "41", the "12345" is a stand-in for the actual Work Area's "Folder ID".

    I have the following versions of pwps_dab installed:

     Get-Module -Name PWPS_DAB -ListAvailable
    
    
        Directory: C:\Program Files\WindowsPowerShell\Modules
    
    
    ModuleType Version    Name                                ExportedCommands                                                      
    ---------- -------    ----                                ----------------                                                      
    Binary     1.12.5.0   pwps_dab                            {Export-PWAAMFile, Import-PWAAMFile, Export-PWDocumentsToArchive, I...
    
    
        Directory: C:\Program Files (x86)\Bentley\ProjectWise\bin\PowerShell
    
    
    ModuleType Version    Name                                ExportedCommands                                                      
    ---------- -------    ----                                ----------------                                                      
    Binary     1.12.2.0   PWPS_DAB                            {Export-PWAAMFile, Import-PWAAMFile, Export-PWDocumentsToArchive, I...
    
    
        Directory: C:\Program Files (x86)\WindowsPowerShell\Modules
    
    
    ModuleType Version    Name                                ExportedCommands                                                      
    ---------- -------    ----                                ----------------                                                      
    Binary     1.17.1.0   pwps_dab                            {Export-PWAAMFile, Import-PWAAMFile, Export-PWDocumentsToArchive, I...
    
    

    I was hoping someone can explain what I'm doing wrong and provide an example I can use to get my desired result. I'm still a PowerShell novice, so apologies in advance if I'm missing something obvious.

    Thank You.

    • Sign in to reply
    • Cancel

    Top Replies

    • Chris Andrew
      Offline Chris Andrew Wed, Mar 2 2022 1:41 PM +1
      ##### ##### ##### ##### ## ## # # # # # # # # # # ##### #### # # # # # # # # # # # # # # # # ##### ##### ##### # # #### #### ## ### #### # # # # ### ### ### #### ### ### ## # # # # # # # # # #…
    • Chris Andrew
      0 Offline Chris Andrew Wed, Mar 2 2022 4:29 PM in reply to roll_n_a_bentley

      You could use the search I've done to find the changes in last 24 hours then pass the documents through an export.

      I would either use a pipe | or a for each.

      Regards

      Chris


      AECOM Roads UK&I Digital Engineering, Design & Solutions Lead | Sector Information Management Lead

      Associate Director – Digital

      OpenRoads Designer 10.12 | MicroStation 2023 | ProjectWise CE 10.3.4 | ContextCapture | ProjectWise PowerShell 2023 | ProjectWise WSG API | Generative Components | OpenBridge Designer 10.12

      Civil 3D 2023 | Dynamo | Navisworks Manage

      PowerShell | Visual Studio | Office 365 | Power Platform | Teams | SharePoint | Visio

      Speckle | BIMVision | Revizto | Solibri

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Reject Answer
      • Cancel
    • roll_n_a_bentley
      0 Offline roll_n_a_bentley Wed, Mar 2 2022 4:20 PM in reply to Chris Andrew

      Thank you for the reply Chris.

      From what I understand of the code you provided, this is just running a report of the changes made. However, I'm looking for a script that will export out a local copy of those 24-hour changes to a local drive. I may try to implement the code you provided separately, but getting the local exports working is my main objective.

      If you have any advice on how to accomplish that, it'd be much appreciated.

      • Cancel
      • Vote Up 0 Vote Down
      • Sign in to reply
      • Verify Answer
      • Cancel
    • Chris Andrew
      0 Offline Chris Andrew Wed, Mar 2 2022 1:41 PM

      #####  ##### ##### ##### ##   ##
      #   #  #     #     #   # # # # #
      #####  ####  #     #   # #  #  #
      #   #  #     #     #   # #     #
      #   #  ##### ##### ##### #     #
      
      #### ####  ##  ###  ####    #  # #  # ###   ###  ### #### ### ###  ##  #
      #  # #  # #  # #  # #       #  # # #   #    #  #  #  #     #   #  #  # #
      ###  #  # #### #  # ####    #  # ##    #    #  #  #  # ##  #   #  #### #
      #  # #  # #  # #  #    #    #  # # #   #    #  #  #  #  #  #   #  #  # #
      #  # #### #  # ##   ####    #### #  # ###   ###  ### #### ###  #  #  # ####
      
      #=====================================================================================================#
      # This Powershell script will run a daily document change report and send email                       #
      #                                                                                                     #
      # Chris J Andrew, 10/09/2021                                                                          #
      #                                                                                                     #
      #=====================================================================================================#
      
      #Requires -Modules PWPS_DAB
      #Requires -Modules ImportExcel
      #Requires -Module PSWriteHTML
      #Requires -RunAsAdministrator
      
      $Logfile = $MyInvocation.MyCommand.Path -replace '\.ps1$', '.log'
       
      Start-Transcript -Path $Logfile
      
      #Import-Module pwps
      #Import-Module pwps_dab -Verbose
      #$approvedVerbs = get-verb | foreach {$_.verb}
      #$myVerbs = get-command -module pwps_dab | foreach {$_.verb}
      #($myVerbs | foreach {$approvedVerbs -contains $_}) -contains $false
      #($myverbs | where {$approvedVerbs -notcontains $_})
      
      #Data entry section which Datasource, Project and Who is to recieve emails.
      $Projects = @(
              [pscustomobject]@{Server='pw.datasouce.address'; DSName='pw.datasoucename'; Project='workarea.folder.name'; EmailTo='email.to.send.report.to'; SaveFolder='location\relative\to\project\to\store\report\'}
              )
      
      $Projects | ForEach-Object {
      
          $SourceServer = "$($_.Server)"
          $SourceDatasource = "$($_.DSname)"
      
          $DatasourceName = "$($SourceServer):$($SourceDatasource)";
      
          
          try {New-PWLogin -BentleyIMS $DatasourceName}
          
          catch {
              Write-Error "Error logging in to $($SourceDatasource)"
              continue
          }
      
      #Get Data from PW and Create Data Table
      
      $Project = "$($_.Project)"
      $folderpath =  "$($Project)\"
      $a = [DateTime]::Today.AddDays(-1)
      $table = Get-PWDocumentsBySearchWithReturnColumns -FolderPath $folderpath -DocumentUpdatedAfter $a -ColumnsToReturn "TB_FILE_ID","RV_REV_1","TB_STATUS","TB_DRAWN_BY","TB_CHECKED_BY","TB_APPROVED_BY","TB_VERIFIED_BY","TB_AUTHORISED_BY","TB_TITLE_LINE_1","TB_TITLE_LINE_2","TB_TITLE_LINE_3","TB_TITLE_LINE_4","CDE_STATE","PW_STATE" -ReturnDataTable
      $thisScriptDirectoryPath = Split-Path -parent $MyInvocation.MyCommand.Definition
      
      $DocsArray =  New-Object System.Collections.ArrayList
      
      foreach ($value in $table) {
      
      # Custom object to hold Rule definition
      
       $obj = New-Object -TypeName psobject
      
      # Rule definition members
      
       $obj | Add-Member -MemberType NoteProperty -Name FileName -Value $Value.DocumentFileName
       $obj | Add-Member -MemberType NoteProperty -Name Description -Value $Value.DocumentDescription
       $obj | Add-Member -MemberType NoteProperty -Name DocID -Value $Value.TB_FILE_ID
       $obj | Add-Member -MemberType NoteProperty -Name Revision -Value $Value.RV_REV_1
       $obj | Add-Member -MemberType NoteProperty -Name Title1 -Value $Value.TB_TITLE_LINE_1
       $obj | Add-Member -MemberType NoteProperty -Name Title2 -Value $Value.TB_TITLE_LINE_2
       $obj | Add-Member -MemberType NoteProperty -Name Title3 -Value $Value.TB_TITLE_LINE_3
       $obj | Add-Member -MemberType NoteProperty -Name Title4 -Value $Value.TB_TITLE_LINE_4
       $obj | Add-Member -MemberType NoteProperty -Name State -Value $Value.PW_STATE
       $obj | Add-Member -MemberType NoteProperty -Name Status -Value $Value.TB_STATUS
       $obj | Add-Member -MemberType NoteProperty -Name DrawnBy -Value $Value.TB_DRAWN_BY
       $obj | Add-Member -MemberType NoteProperty -Name CheckedBy -Value $Value.TB_CHECKED_BY
       $obj | Add-Member -MemberType NoteProperty -Name ApprovedBy -Value $Value.TB_APPROVE_BY
       $obj | Add-Member -MemberType NoteProperty -Name VerifiedBy -Value $Value.TB_VERIFIED_BY
       $obj | Add-Member -MemberType NoteProperty -Name AuthorisedBy -Value $Value.TB_AUTHORISED_BY
      
       # Silently add the object to the array
      
       $DocsArray.Add($obj) | Out-Null
      
      } 
      
      $ReportFilename = "$(get-date -f yyyy-MM-dd)-$($Project)-DocumentReport.xlsx"
      $htmlFilename = "$(get-date -f yyyy-MM-dd)-$($Project)-DocumentReport.html"
      $Outputfilename = "$($thisScriptDirectoryPath)\$($ReportFilename)"
      #New-XLSXWorkbook $DocsArray -OutputFileName $Outputfilename
      Remove-Item -Path $Outputfilename
      $DocsArray | Export-Excel -Path $Outputfilename -FreezeTopRow -TableName PWDocsChanged -TableStyle Medium9 -AutoSize
      
      $DocsArray | Out-HtmlView -ScrollX -Filtering -AllProperties -FilePath "$($thisScriptDirectoryPath)\$($htmlFilename)"
      
      #Remove today's report if exists
      Get-PWDocumentsBySearch -FolderPath "$($folderpath)$($_.SaveFolder)" -FileName "$ReportFilename" | Remove-PWDocuments
      
      # Add Report to ProjectWise
      New-PWFolder -FolderPath "$($folderpath)$($_.SaveFolder)"
      $report = New-PWDocument -FolderPath "$($folderpath)$($_.SaveFolder)" -FilePath $Outputfilename -Description "$(get-date -f yyyy-MM-dd)-DocumentReport"
      
      $hyperlink = "pw://$($_.Server):$($_.DSName)/Documents/D{$($report.DocumentGUID)}"
      
      #####Define Variables#####
      $fromaddress = “uki-roads-digital@aecom.com”
      $toaddress = "$($_.EmailTo)"
      $Subject = “$($Project) Daily Activity Report”
      $body = "<HTML>" + 
          "<TITLE>ProjectWise Activity Report</TITLE>" +
          "</HEAD>" +
          "<BODY><h1></h1>" +
          "<p> Good Morning" +
          "<br>" +
          "<br>Please find attached today's ProjectWise Daily Activity Report for $($Project)." +
          "<br><a href='$($hyperlink)'>Link to File on ProjectWise</a>" +
          "<br>" +
          "<br>Regards" +
          "<br> AECOM Roads UK&I Digital" +
          "</p>" +
          "</BODY>" +
          "</HTML>";
      $attachment = $Outputfilename
      $attachment1 = "$($thisScriptDirectoryPath)\$($htmlFilename)"
      
      ##################################
      
      #create COM object named Outlook
      $Outlook = New-Object -ComObject Outlook.Application
      
      #create Outlook MailItem named Mail using CreateItem() method
      $Mail = $Outlook.CreateItem(0)
      
      #add properties as desired
      $Mail.To = $toaddress
      $mail.SentOnBehalfOfName = $fromaddress
      $Mail.Subject = $Subject 
      $Mail.HTMLBody = $body 
      $Mail.Attachments.Add($attachment)
      $Mail.Attachments.Add($attachment1)
      
      
      #send message
      $Mail.Send()
      
      #quit and cleanup
      $Outlook.Quit() 
      [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Outlook) | Out-Null
      
      Write-Host "Document Report Generated. Sir"
      
      Undo-PWLogin
      
      }
      
      Write-Output 'Writing some text to the log file'
       
      Stop-Transcript
      

      This is my daily report script, that stores report on PW and emails to who needs it

      Regards

      Chris


      AECOM Roads UK&I Digital Engineering, Design & Solutions Lead | Sector Information Management Lead

      Associate Director – Digital

      OpenRoads Designer 10.12 | MicroStation 2023 | ProjectWise CE 10.3.4 | ContextCapture | ProjectWise PowerShell 2023 | ProjectWise WSG API | Generative Components | OpenBridge Designer 10.12

      Civil 3D 2023 | Dynamo | Navisworks Manage

      PowerShell | Visual Studio | Office 365 | Power Platform | Teams | SharePoint | Visio

      Speckle | BIMVision | Revizto | Solibri

      • Cancel
      • Vote Up +1 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