#PowerShell, #PowerWiseScripting, #ProjectWise, PWPS_DAB

HowTo: Cleanup DMS_CHKL Table

In this post, we will be cleaning up the dms_chkl table. This table tracks  where documents have been copied out, checked out or exported,  and by whom and on which computer node. Over the course of time, there could be a lot of ‘copy out’ orphaned records in this table and these are the records we would like to cleanup.

You can you use the provided chklcleanup.exe utility provided with the ProjectWise install, located in the ‘c:\Program Files (x86)\Bentley\ProjectWise\bin’ directory. But what fun is that?

We will be using the following cmdlets to accomplish this task. All of the ProjectWise related cmdlets are available using the PWPS_DAB module. At the time of this post, I am using version 1.21.8.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

  • Get-PWDocumentCopyOutLocationCounts
  • Get-PWUsersByMatch
  • Remove-PWDocumentCopyOutLocations

NOTE: It is assumed that you are logged into a ProjectWise datasource using an administrator account.

Get Copy Out Location Counts

First, we can run the Get-PWDocumentCopyOutLocationCounts cmdlet (that’s one long cmdlet name). This will give us a list of users or nodes and the corresponding count for all documents copied out.

By User

If you simply call the cmdlet with no parameters, the default behavior is to return the number of copied out documents by user. Even if the same user has documents copied out to various computer nodes.

In the following example, you see we have three unique users and the corresponding number of documents copied out.

results1

By Node

To return the data by node, you simply include the -ByNode switch parameter.  Again, there can be multiple users that have documents copied out to the same node. The count is a summation of the documents copied out to all users on a particular computer node.

This time you see that the count is based on the node, not the user. The same number of documents have been copied out (283).

resultsbynode

Filter By Date

We also have the  option to filter the results based on a date using the -Before parameter. This can be used to filter by user or by node.

In the following example, we are filtering the results based on the date July 1st, 2020. Here you seen that only 78 of the 147 documents copied out by the pwadmin, where copied out prior to July 1st, 2020.

resultsfiltered

The following demonstrates filtering the results by node using the date June 28, 2020.

resultsfiltered2

Remove Copy Out Records

Now that we know what data we have we can determine which records to keep and which records can be cleaned up.

By User

The first scenario will be to remove ALL copied out document records for a particular user. We will use the Get-PWUsersByMatch cmdlet to return the ProjectWise user object(s). Then we can pass the results to the Remove-PWDocumentCopyOutLocation cmdlet. We could have piped the results of the Get-PWUsersByMatch cmdlet instead. Also, keep in mind that the -InputUsers parameter can accept an array of ProjectWise user objects.

# Get the user object to remove the copied out records for.
$pwUser = Get-PWUsersByMatch -UserName '_powershelluser'
Remove-PWDocumentCopyOutLocations -InputUsers $pwUser

Now, if we rerun the Get-PWDocumentCopyOutLocation cmdlet, we will no longer see any data related to the user ‘_powershelluser’. 67 records have been removed.

remove

By Node

Next, we will remove records by node. Again, the -Nodes parameter can accept an array of (strings) node names.

# Remove by node.
Remove-PWDocumentCopyOutLocations -Nodes 'bmf-ws2016-pwdi'

Again, if we rerun the Get-PWDocumentCopyOutLocation cmdlet with the -ByNode switch parameter, we will no longer see any data related to the ‘bmf-ws2016-pwdi’ node. 138 records have been removed.

removebynode

By User, Node and Date

Finally, we will be removing records for a specific user, on a specified node and filtered on a date.

# Remove by user, node and date.
Get-PWUsersByMatch -UserName 'pwadmin' |
Remove-PWDocumentCopyOutLocations -Nodes 'naou22225' -Before '2020-06-28'

In the following, you see that all but 42 records have been cleaned up.

removebyusernodeanddate

Summary

Let’s summarize what we have done.

We used the Get-PWDocumentCopyOutLocationCounts cmdlet to determine how many records were contained in the dms_chkl table. We retrieved the records based on user(s) and by node(s). We were also able to filter the results by using the -Before parameter with a specified date.

We can then use this data to determine which records we would like to cleanup.  In this little example, we successfully removed 241 records from the ProjectWise datasource.


Experiment with it and have fun.

Hopefully, you find this useful. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.