#PowerShell, #PowerWiseScripting, #ProjectWise, PWPS_DAB

How To: Get a List of UserList and Groups a User is a Member Of (V2.0)

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.

This is an updated method for generating a report containing all users, and the group(s), and userlist(s) that each user within a ProjectWise datasource is a member of.  In this example, I am writing the data to a datatable and outputting to an Excel spreadsheet.

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

  • Get-PWUsersByMatch
  • New-XLSXWorkbook

Get All Users

First thing we will do is get a list of ALL of the ProjectWise User accounts within the current datasource.

# Get a list of all user objects.
$Users = Get-PWUsersByMatch

Build Datatable to Store User Data

Next, we will build a datatable to store the user data. We will be gathering the user id, user name, group name(s), and userlist name(s).

# Build datatable to store user data.
$dt = [Data.Datatable]::new('UserData')
$dt.Columns.Add('UserID', [int])         | Out-Null
$dt.Columns.Add('UserName', [string])    | Out-Null
$dt.Columns.Add('Group(s)', [string])    | Out-Null
$dt.Columns.Add('UserList(s)', [string]) | Out-Null

Get Membership Data

Now that we have the list of user objects, we can loop through the list and get any group(s) or userlist(s) that each user is a member of. We will use the user methods to get the group (.GroupsMemberOf) and userlist (.UserListsMemberOf) membership.

# Loop through list of names, and get the groups and userlists they are a member of.
foreach ($User in $Users) {
    Write-Host "$($User.Name)" -Foreground Green

    # Get all Groups that user is a member of.
    $groups = [string]::Empty
    if($groupsMemberOf = $User.GroupsMemberOf()){
        $groups = $groupsMemberOf.Values -join ', '
    }
    # Get all UserLists that user is a member of.
    $userLists = [string]::Empty
    if($userListsMemberOf = $User.UserListsMemberOf()){
        $userLists = $userListsMemberOf.Values -join ', '
    }

    # Add user data to datatable.
    $dr = $dt.NewRow()
    $dr.UserID = $User.ID
    $dr.UserName = $User.Name
    $dr.'Group(s)' = $groups
    $dr.'UserList(s)' = $userLists

    $dt.Rows.Add($dr)
}

The following is an example of the datatable.

userdata

Export To Excel

The final step is to output the data to an Excel spreadsheet.

# Output data to an Excel spreadsheet.
# Use the -Open switch parameter if you want to immediately open the file.
New-XLSXWorkbook -InputTables $dt -OutputFileName "c:\temp\UserReport_$(Get-Date -Format yyyyMMdd).xlsx" -Open

Experiment with it and have fun.

Hopefully, you find this useful. Please let me know if you have any questions or comments.

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 )

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.