HowTo: Generate Environment Usage Report

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 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

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.