HowTo: Generate ProjectWise User Licensing 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.

I have been getting more and more requests from ProjectWise Administrators asking how to generate a report listing all consultant accounts (non-company user accounts) to determine where their ProjectWise license is coming from. You can access this information for a ProjectWise User account by looking at the user properties within the ProjectWise Administrator client. In this post we will be reporting on all ProjectWise user accounts within a datasource. However, you could easily filter the user accounts that you do not want to report on. We will be using the following cmdlets to accomplish this task. All of the ProjectWise related cmdlets are available using the PWPS_DAB module. At the time of this post, I am using version 1.12.2.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

• Get-PWUsersByMatch
• Get-PWUserSettingByUser
• New-XLSXWorkbook

---

Parameters

First thing we need to do is create our parameter and value pairs. The help messages will explain the purpose for each parameter. I am also going to include a few requires statements to ensure the correct version of PowerShell and the PWPS_DAB module are used. As well as require that the Run As Administrator option is used to run the PowerShell Console or PowerShell ISE.

#Requires -Version 5.0
#Requires -Modules @{ModuleName="PWPS_DAB";ModuleVersion='1.12.2.0'}
#Requires -RunAsAdministrator
[CmdletBinding()]
param(
    [ValidateNotNullOrEmpty()]
    [Parameter(
        HelpMessage = 'Path to local folder to create report in.',
        Position = 0 )]
    [string] $Path = 'D:\TEMP',

    [ValidateNotNullOrEmpty()]
    [Parameter(
        HelpMessage = 'Name of report to generate.',
        Position = 1 )]
    [string] $OutFilePathName = "PWLicensingReport_$(Get-Date -Format yyyyMMdd).xlsx",
) # end param

---

Begin

The next thing we will do is ensure the specified folder exists, and that we are logged into ProjectWise.  I am going to add these steps into the BEGIN section of the script. I want to take a second and point out a couple of conventions that I use in my scripts. First, you may notice that I put a comment at the end of many of the blocks of code. Doing this makes it easier for me to determine where a block of code starts and ends. Comes in handy when troubleshooting a script.  I also add text ([BEGIN], [PROCESS], [END]) in the output messages (Write-Verbose, Write-Warning, Write-Error) corresponding to the section of the script I am in. Again, this makes it obvious where something occurs within the script.

BEGIN {  
    $Continue = $true  

    $StartTime = Get-Date 
    Write-Verbose -Message "[BEGIN] Start time: $StartTime" 

    <# Test to see if the provided path exists. If not, set the variable Continue to false.  
        This will be used in the PROCESS code block to exit the script. #> 
    if( -not (Test-Path -Path $Path -PathType Container -Verbose -ErrorAction Stop)) { 
        Write-Warning -Message "[BEGIN] Folder '$Path' not found. Update path variable value and try again."  
        $Continue = $false 
    }  

    <# Determine if we are currently logged into a ProjectWise datasource.  
        If not, display the ProjectWise Login Dialog. #> 
    if(Get-PWCurrentDatasource) { 
        Write-Verbose -Message "[BEGIN] Currently logged into ProjectWise datasource '$(Get-PWCurrentDatasource)'." 
    } else { 
        if(New-PWLogin -UseGui) { 
            Write-Verbose -Message "[BEGIN] Successfully logged into ProjectWise datasource '$(Get-PWCurrentDatasource)'." 
        } else { 
            Write-Error -Message '[BEGIN] Failed to log into ProjectWise datasource.' 
            $Continue = $false 
        } 
    } # end if(Get-PWCurrentDatasource... 
} # end BEGIN

---

PROCESS

Now that we verified our local folder exists, and we are logged into a ProjectWise datasource, we can proceed to the PROCESS code block. You can see that if the Continue variable equals false, a warning message will be displayed and we will proceed to the END code block to log out of ProjectWise and exit the script.

PROCESS {
    if( -not ($Continue)) {
        Write-Warning -Message "[PROCESS] Exiting script."
    } else { ... }
} # end PROCESS

Next we will create a datatable to add the user information to. We will give the datatable a name. This name will correspond to the worksheet within the Excel file when we export the data.

# Create a datatable to return desired data.
$dtLicensingData = New-Object System.Data.DataTable ('Licensing_Info')
$dtLicensingData.Columns.AddRange(@("UserName", "UserDescription", "LicensingSource"))

Next we will get an array of ProjectWise user objects for all users within the current datasource. You could filter the user objects returned by including some of the additional parameters available with the Get-PWUsersByMatch cmdlet.

# Get ProjectWise User Account information.
$pwUsers = Get-PWUsersByMatch

Now we can loop through all of the user objects returned to determine where the ProjectWise license is coming from for each user. To accomplish this, we will get the user properties for each user and extract the value for the Admin_Licensing property. We will then populate the datatable with this information.

# The following is a counter for the progress bar. 
$Counter_Users = 1
$ItemCount_Users = $pwUsers.count
# Loop through each item in the collection.
foreach ($pwUser in $pwUsers) {

    #region PROGRESS SECTION

    $Progress_Users = @{ 
        Activity = "Getting user information for '$($pwUser.Name)'." 
        Status = "Processing $Counter_Users of $ItemCount_Users" 
        PercentComplete = $([math]::Round($(($Counter_Users / $ItemCount_Users) * 100 ), 2)) 
    } 
    Write-Progress @Progress_Users -Id 1

    # Increment the counter.
    $Counter_Users++

    #endregion PROGRESS SECTION
    
    # Using splatting for readability.
    $Splat_UserSettings = @{
        InputUsers = $pwUser
        SettingName = 'Admin_Licensing'
    }
    $Licensing = Get-PWUserSettingByUser @Splat_UserSettings
    if($Licensing.UserSettingValue -match 'client') { 
        Write-Verbose -Message "[PROCESS] User '$($pwUser.Name)' is using their own ProjectWise license."
    }

    #region Populate Datatable

    # Create new datarow.
    $dr = $dtLicensingData.NewRow()
    # Populate datarow.
    $dr.UserName = $pwUser.Name
    $dr.UserDescription = $pwUser.Description
    $dr.LicensingSource = $Licensing.UserSettingValue
    # Add datarow to the datatable.
    $dtLicensingData.Rows.Add($dr)

    #endregion Populate Datatable

} # end foreach ($pwUser...

The following shows the progress bar in action. The following shows the contents of the datatable. Now that we have the datatable updated with the user names, user descriptions, and licensing source, we can export it out to an Excel workbook.

# Create new report from datatable contents.
Write-Verbose -Message "Exporting data to '$Path\$OutFilePathName'."
New-XLSXWorkbook -InputTables $dtLicensingData -OutputFileName $OutputFilePath -Verbose

The following shows the contents of the report. Notice the worksheet name.

---

END

Lastly, we will proceed to the END block of code to log out of our ProjectWise session. You will also see that we have calculated the amount of time it had taken to complete the process.

END {
    $EndTime = Get-Date
    Write-Verbose -Message "[END] It took $([Math]::Round($EndTime.Subtract($StartTime).TotalMinutes, 2)) minutes to complete process."

    Write-Verbose -Message '[END] Logging out of ProjectWise.'
    Undo-PWLogin
} # end END

---

The following is a link to the full PowerShell script. HowTo_GenerateUserLicensingReport 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.