HowTo: Generate UserList And Group 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 a UserList and Group usage report. This report will list each UserList and Group within a datasource and return the number of times each are explicitly used for access control. I have seen where there are many hundreds of userlists and groups within a datasource that are never used. I understand that groups can be used to synchronize user accounts, so those obviously would be ignored in the analysis stage of cleanup.

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-PWCurrentDatasource
• Get-PWUserLists
• Get-PWGroups
• Select-PWSQL
• New-XLSXWorkbook

---

Get UserLists and Groups

The first thing I want to do is get a list of all userlists and a list of all groups within the datasource. I am also going to include a variable to store the datasource name.

# Parsing out the datasource name to be used to name the datatable.
$DSName = (Get-PWCurrentDatasource).Split(':')[0]
# Get a list of all userlists.
$pwUserLists = Get-PWUserLists
# Get a list of all groups.
$pwGroups = Get-PWGroups

Get All Access Control Records

Next, we will run a query to gather all of the records within the dms_acce table where the member type is either 2 or 3 (Group or Userlist). We will use this data later to get specific usage counts based on userlist and group 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.

# Query the dms_acce table for all userlist and group records.
$SQL_Access = "SELECT * FROM dms_acce WHERE o_memtype IN (2, 3)"
$SQLResults = Select-PWSQL -SQLSelectStatement $SQL_Access

Generate Report

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

Create Datatable to Store Data

# Create a datatable to store the data in and export to Excel.
$dt = [Data.Datatable]::New($DSName)
$dt.Columns.AddRange(@("Type", "Name", "COUNT"))

Iterate the UserLists

Here we will iterate through all of the userlists and populate the datatable with the name and number of times each is found within the access table. I have included the Write-Progress for fun.

$Counter_UserList = 1
$itemCount_UserList = $pwUserLists.Count
foreach($u in $pwUserLists){

    #region PROGRESS SECTION

    $Progress_UserList = @{ 
        Activity = "'$($u.Name)' userlist." 
        Status = "Processing $Counter_UserList of $itemCount_UserList" 
        PercentComplete = $([math]::Round($(($Counter_UserList / $itemCount_UserList) * 100 ), 2)) 
    } 
    Write-Progress @Progress_UserList -Id 2 -ParentId 1

    # Increment counter.
    $Counter_UserList++

    #endregion PROGRESS SECTION

    # Get number of records where the type is 3 (userlist) and the member equals the userlist id.
    $temp = $sqlResults.Select("o_memtype = '3' and o_memberno = '$($u.ID)'")

    # Create new datarow.
    $dr = $dt.NewRow()

    # Populate the datarow.
    $dr.Type = "UserList"
    $dr.Name = $u.Name
    $dr.Count = $temp.Count

    # Add the datarow to the datatable.
    $dt.Rows.Add($dr)

}

Write-Progress -Completed -Activity "Complete" -Id 2 -ParentId 1

Iterate the Groups

We are going to repeat the process for all of the groups and populate the datatable with the name and number of times each is found within the access table.

$Counter_Group = 1
$itemCount_Group = $pwGroups.Count
foreach($g in $pwGroups){

    #region PROGRESS SECTION

    $Progress_Group = @{ 
        Activity = "'$($g.Name)' group." 
        Status = "Processing $Counter_Group of $itemCount_Group" 
        PercentComplete = $([math]::Round($(($Counter_Group / $itemCount_Group) * 100 ), 2)) 
    } 
    Write-Progress @Progress_Group -Id 3 -ParentId 1 

    # Increment counter.
    $Counter_Group++

    #endregion PROGRESS SECTION

    # Get number of records where the type is 2 (group) and the member equals the group id.
    temp = $sqlResults.Select("o_memtype = '2' and o_memberno = '$($g.ID)'")

    $dr = $dt.NewRow()

    $dr.Type = "Group"
    $dr.Name = $g.Name
    $dr.Count = $temp.Count

    $dt.Rows.Add($dr)

}

Write-Progress -Completed -Activity "Complete" -Id 3 -ParentId 1

Export to Excel

You can use the -Open switch parameter to open the Excel file after it is created.

# The following is the path and name of the Excel file to be created.
$OutputFileName = "c:\temp\export\UserList_And_Group_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 userlists and groups within a datasource. You can use this information to determine which, if any, userlists or groups 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.