Be sure to check out my Scripting4Crypto initiative. It’s a fun way to get into using cryptocurrencies all while getting your PowerShell needs met.
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.
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).
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.
The following demonstrates filtering the results by node using the date June 28, 2020.
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.
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.
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.
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.
powerwisescripting.blog 