HowTo: Generate Managed Workspace 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 this post, we will be stepping through the process of generating an Excel file containing a list of Projects which have a specified Managed Workspace associated with them.

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

• Get-PWManagedWorkspaceConfigBlocks
• Select-PWSQL
• Get-PWFoldersByGUIDs
• New-XLSXWorkbook

 

Get List of Managed Workspaces

You can use the following to get a list of all of the available Managed Workspaces within your ProjectWise (PW) datasource.

# Get a list of all available Managed Workspace CSBs.
Get-PWManagedWorkspaceConfigBlocks -WorkspaceType All

The following shows the available Managed Workspaces within my PW datasource.

Setting Variables

The following are two variables to be used within the script. One contains the desired Managed Workspace to report on and the other contains the path and name of the Excel file to be generated.

# Managed Workspace to report on.
$WorkspaceName = 'BSI-MS10 Configuration Root'

# Path and name of Excel workbook to be generated.
$OutputFileName = "c:\Temp\$($WorkspaceName)_Managed_Workspace_Report_$(Get-Date -Format yyyyMMdd).xlsx"

Get Managed Workspace ID

Next, we will need to get the ID associated with the desired Managed Workspace. To do this, we will need to use the Select-PWSQL cmdlet to query the PW database table.

# Get Managed Workspace ID
$WorkspaceID = Select-PWSQL -SQLSelectStatement "SELECT o_objectID AS ID FROM workspace_mgt_block WHERE o_name LIKE '$WorkspaceName'"

The following shows the Managed Workspace ID returned.

Get Project IDs

Now, we will run another query to get a list of Project IDs where the project has the specified Managed Workspace associated with it by using the Workspace ID.

# Get a list of all folders with the specified managed workspace associated with it.
$sql = "SELECT b.o_objectid AS ProjectID 
        FROM workspace_mgt_cblink B, workspace_mgt_cblinkassoc C 
        WHERE b.o_associd = c.frominstid AND c.toinstid = $($WorkspaceID.ID) 
        ORDER BY ProjectID"
$Results = Select-PWSQL -SQLSelectStatement $sql

The following shows the results of the query.

Get Folder Objects by GUID

We will use the list of Project IDs to get the corresponding folder GUIDs and then the PW folder objects. First, we will use the -join to generate a string containing each of the Project IDs, separated by a comma. Use this string in a query to get the PW folder GUIDs and then get the folder objects using the GUIDs.

# Create a string containing all Project IDs separated by a comma.
$ProjectIDs = $Results.ProjectID -join ','

# Get the GUIDs for each project.
$SQL_FolderGUIDs = "SELECT o_projguid as GUID FROM dms_proj WHERE o_projectno IN ($ProjectIDs)"
$Results_FolderGUIDs = Select-PWSQL -SQLSelectStatement $SQL_FolderGUIDs

# Select folder objects by the GUIDs returned.
$pwFolders = Get-PWFoldersByGUIDs -FolderGUIDs $Results_Folders.GUID

The following shows the string generated.

The following shows the list of Project GUIDs.

The following shows the resulting PW folders.

Create DataTable and Columns

We will need to create a new datatable to add the folder information to. This is required to use the New-XLSXWorkbook cmdlet.

# Create a new datatable and add desired columns.
$dt = [Data.Datatable]::New("WorkAreas")
$dt.Columns.AddRange(@(
    "ProjectID"
    "Name"
    "Description"
    "FullPath"
    "ProjectURN"
))

Add Folder Data to DataTable

Here we will loop through each of the PW folders returned and add the corresponding data to the datatable.

# Add data to the datatable for each folder.
foreach($f in $pwFolders){
    # Create a new datarow.
    $dr = $dt.NewRow()
    # Populate the datarow.
    $dr.ProjectID = $f.ProjectID
    $dr.Name = $f.Name
    $dr.Description = $f.Description
    $dr.FullPath = $f.FullPath
    $dr.ProjectURN = $f.ProjectURN
    # Add new row to the datatable.
    $dt.Rows.Add($dr)
}

The following shows the contents of the datatable by outputting using the Out-Gridview (ogv) cmdlet.

$dt | ogv   

# OR  

$dt | Out-GridView

Generate Report

Finally, we will export the datatable to the Excel workbook.

# Export datatable to an Excel workbook.
New-XLSXWorkbook -InputTables $dt -OutputFileName $OutputFileName

The following shows the contents of the generated report.

 

---

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.