HowTo: Report on Users In Group/UserList Including LastLogin

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, I will be covering one method of generating a report containing user information for members of a group or userlist which will also include the LastLogin.

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.15.2.0. Take a look at the help for each of the cmdlets to become familiar with their functionality, available parameters, etc.

• Get-PWUserByLastLogin
• Get-PWUsersInGroup
• New-XLSXWorkbook
• ConvertTo-DataTable

Assumption

It is assumed you are logged into a ProjectWise datasource with a user account which has administrative privileges.

Get User Data

All Users Which Have Logged In

First, we will get a list of all users within the current ProjectWise datasource which have logged into the datasource using the Get-PWUsersByLastLogin.  NOTE: This could take a bit of time to obtain, however, this seems to be the way to get the last login information. Once we get this data, we will convert it into a data table which will allow us to use some of the datatable functionality. Specifically the .Select method.

<# Get all users including lastlogin data.
    Pipe to the Convert-DataTable cmdlet to convert the data
    to a datatable to allow use of the .Select method. #>
$usersByLogin = Get-PWUserByLastLogin -Since 0 | ConvertTo-DataTable

The following shows the contents of the $usersByLogin variable. Viewing the data using the Out-GridView cmdlet.

$usersByLogin | Out-GridView

Group / UserList Members

Next, we will get a list of members of a Group.

# Get members of a specified group.
$usersInGroup = Get-PWMembers -Type Group -Name 'grpCAD_Users'

Or members of a UserList. Keep in mind that a UserList can have a group(s) and or a userlist(s) as members. We need to ensure we only grab users.

# Get user members of a specified userlist.
$usersInGroup = Get-PWMembers -Type Userlist -Name 'ulCAD_Users'
$usersInUserList = $usersInUserList | Where-Object -Property MemberType -eq User

The following shows the membership for both the group and userlist.

Generate Report

Create DataTable

We will now create a new datatable object to store the user data. We will only capture the UserName, Description, Email, and LastLogin data.

# Create new datatable and add desired columns.
$usersToExportDT = New-Object Data.DataTable ('UserInfo')
$usersToExportDT.Columns.Add("description") | Out-Null
$usersToExportDT.Columns.Add("UserName")    | Out-Null
$usersToExportDT.Columns.Add("Email")       | Out-Null
$usersToExportDT.Columns.Add("LastLogin")   | Out-Null

Populate DataTable

Next, we will loop through all of the users within the group and populate the datatable.

# Loop through each member of the group.
foreach ($user in $usersInGroup) {
    # Select the user data from the usersByLogin datatable.
    $tempUser = $usersByLogin.Select("Name = '$($user.MemberName)'")

    <# Tests to determine if a row is empty. 
        If yes, populate the datarow by getting the default 
        user data using the Get-PWUsersByMatch cmdlet. #>
    if([string]::IsNullOrEmpty($tempUser[0].UserName)){
        $tempUser = Get-PWUsersByMatch -UserName $user.MemberName
    }

    # Create and populate new datarow. 
    $dr = $usersToExportDT.NewRow()
    $dr.description = $tempUser[0].Description
    $dr.UserName = $tempUser[0].UserName
    $dr.Email = $tempUser[0].Email
    $dr.LastLogin = $tempUser[0].LastLogin

    $usersToExportDT.Rows.Add($dr)
} # end foreach ($user...

The same thing can be accomplished for a userlist. Simply change the foreach loop definition to use the $usersInUserList variable. Everything else will work the same.

# Loop through each member of the group.
foreach ($user in $usersInUserList) {
    ...
} # end foreach ($user...

The following will show the datatable results for each process.

Group:

UserList:

Notice that the LastLogin value is empty for the CAD_User5 user. This user is a member of the specified userlist, however they have never logged in to the datasource.

Export Data

Finally, we can export our data. I am going export the group membership data to a csv file and the userlist membership data to an Excel spreadsheet.

CSV

# Export Group membership data to CSV.
$usersToExportDT | Out-File 'd:\temp\Group_MemberShip_Users.csv'

The following shows the contents of the csv file.

EXCEL

# Export UserList membership data to Excel.
New-XLSXWorkbook -InputTables $usersToExportDT 'd:\temp\UserList_MemberShip_Users.xlsx'

The following shows the contents of the xlsx file.

---

The following is a link to the full PowerShell script.

HowTo_ReportOnUsersInGroupUserlistIncludingLastLogin

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.