#PowerShell, #PowerWiseScripting, #ProjectWise, PWPS_DAB

HowTo: Generate Environment Usage Report

In the past few posts, you may have noticed a theme. I am generating reports to help determine if any extraneous information exists within our datasources which can be cleaned up. I am not going to cover how to do the cleanup, but rather how to gather the information. The cleanup may come later.  So, to keep this theme, I am going to cover how to generate an Environment usage report. This report will list each Environment Name within a datasource and return the number of folders each Environment is associated with. This was requested by a user so hopefully it helps.

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.

  • Get-PWEnvironments
  • Select-PWSQL
  • New-XLSXWorkbook

Get Environments

The first thing I want to do is get a list of all environments within the datasource. We only need to capture the name and id of each environment.

# Get a list of all environments within the datasource.
$pwEnvironments= (Get-PWEnvironments).GetEnumerator() |
Select-Object Name, ID

Get Folder Data

Next, we will run a query to get all of the project ids and corresponding environment id within the datasource. We will use this data later to get specific usage counts based on the environment ids. We will populate a datatable with the resulting data. By taking this approach, we minimize the number of database queries required. Keep in mind, this table can be large and may take a moment to populate.

# Get a list of all project ids and the corresponding environment id.
$pwFolderInfo = Select-PWSQL -SQLSelectStatement "SELECT o_projectno, o_envno FROM dms_proj"

Generate Report

Here, we will iterate through the list of environments and derive the number of usages from the SQLResults datatable previously populated. 

Create Datatable to Store Data

First, we need to create a new datatable to store our results.

<# Create new datatable and add the columns.
Parse out the datasource name to be used as the datatable name. #>
$DSName = (Get-PWCurrentDatasource).Split(':')[1]
$dt = [Data.Datatable]::New($DSName)
$dt.Columns.AddRange(@("Environment", "COUNT"))

Iterate the Environments

Here we will iterate through all of the environments and populate the datatable with the name and number of times each is associated with a folder.

<# Loop through the list of environments and determine the number of times
each is associated with a folder. #>
foreach($env in $pwEnvironments){

# Select all rows from the SQLResults table where the current environment id is used.
$results = $pwFolderInfo.Select("o_envno = $($env.ID)").Count

# Add data to the datatable.
$dr = $dt.NewRow()
$dr.Environment = $env.Name
$dr.Count = $results
$dt.Rows.Add($dr)

} # end foreach($env in $pwEnvironments){...

Export to Excel

Now, we can either view the data or export it to an Excel file. You can use the -Open switch parameter to open the Excel file after it is created.

# View the datatable
$dt | Out-GridView

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

Example Report

2021-07-30_9-39-28

Summary

We have quickly created a usage summary report for all Environments within a datasource. You can use this information to determine which, if any, Environments 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.

1 thought on “HowTo: Generate Environment Usage Report”

  1. Thanks a lot Brian for the script , i get a blank Excel with the 2 columns , can you suggest what i can check hear.

    Like

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.