#PowerShell, #PowerWiseScripting, #ProjectWise, PWPS_DAB

HowTo: Generate Application Usage Report

Have you ever looked at the number of applications available within a ProjectWise datasource? In the  datasource used for this post, there are 199 defined applications. Of those, how many are actually being used? In this post, we will be generating a report to answer that question. The report will list each application and the number of documents associated. This information can be used to clean up your datasource if desired.

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

  • Select-PWSQL
  • New-XLSXWorkbook

Get Documents with an Application Associated

The first thing I want to do is get a complete list of documents within the datasource which have an associated application. We will actually run a query to populate a datatable with the document guid and the associated application id. By taking this approach, we minimize the number of database queries required.

# Get list of all documents with an application associated with it.
$SQL_Docs = "SELECT o_applno, o_docguid FROM dms_doc WHERE o_applno > 0"
$SQLResults_Docs = Select-PWSQL -SQLSelectStatement $SQL_Docs

Get All Applications

Next, we will do the same thing as the documents to get a list of all applications. We will populate a datatable with each application name and id.

# Get list of all applications
$SQL_Applications = "SELECT o_applno, o_applname FROM dms_appl ORDER BY o_applname"
$SQLResults_Applications = Select-PWSQL -SQLSelectStatement $SQL_Applications

Generate Report

Here, we will iterate through the list of applications and derive the number of usages from the documents datatable. 

Create Datatable to Store Data

# Create a datatable to store the data in and export to Excel.
$dt = [Data.Datatable]::New("Application_Usage")
$dt.Columns.AddRange(@("ApplicationName", "COUNT"))

Iterate the Application Data

# Loop through each application and get a usage count.
foreach($r in $SQLResults_Applications.Rows){

# Create new datarow.
$dr = $dt.NewRow()

# Populate datarow.
$dr.ApplicationName = $r.o_applname
$dr.COUNT = $SQLResults_Docs.Select("o_applno = '$($r.o_applno)'").Count

# Add new datarow to the datatable.
$dt.Rows.Add($dr)
} # end foreach($r in $SQLResults_Applications.Rows){...

Export to Excel

You can use the -Open switch parameter to open the Excel file after it is created.

# Export datatable to Excel spreadsheet.
$OutputFileName = "c:\temp\export\Application_Usage_$(Get-Date -Format yyyyMMddhhmmss).xlsx"
New-XLSXWorkbook -InputTables $dt -OutputFileName $OutputFileName -Open

Example Report

2021-07-24_13-00-21

Summary

We have quickly created a usage summary report for all applications within a datasource. In the datasource used for this post, there are 158 applications that have not been used. You can use this information to determine which, if any, applications can be removed from your datasource if desired. 


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.