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.
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
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.
powerwisescripting.blog 