I’ve wrote this retention policy script in Sitecore PowerShell to list all Extranet users ordered by Login Date.

Sitecore PowerShell is a powerful plugin module where you can write scripts to work with Sitecore items by calling native Sitecore API using standard Windows PowerShell syntax. Either download the latest version from the Sitecore PowerShell Extensions web site or an older version from Sitecore Marketplace. Note that the Marketplace site is no longer maintained, and should not be used. So to be sure to have all the latest PowerShell for Sitecore with all the updates better download from https://github.com/SitecorePowerShell

Today I finnished writing a script to list all Extranet users in a public web site to be able to see if any user accounts should be deleted or not regarding the company retention policy. The report is presented using the powerful Show-ListView command beeing able to export the result in different formats.

• Red icon = user has not logged in within the last year
• Yellow icon = user has not logged in within the last 330 days. The number indicates how many days that are left until the user account should be deleted
• Green icon = User has logged in recently
• White icon = User is ok

I had some issues while coding:

• First I was not able to use the [System.Web.Security.Membership]::GetAllUsers() command because I did not find a way to filter on ”\extranet” nor sort on Last Login Date. Thats why I needed to first get all users using Get-User with a -Filter parameter
• Then I iterate through the list of users with a foreach command filtering the ”extranet\Anonymous” user adding the user info to a variable $userList
• I also calculate the user Status using some comparison operators
• The report output format is declared in $userProperty showing different icons depending on user status and user friendly date formats
• Then the output is sorted on the Sort parameter that is equal an unformatted LastLoginDate
• At the end the report is generated via Show-ListView and with some report stats in the header.

<#
    .SYNOPSIS
        Lists all Extranet users ordered by Last Login Date

    .NOTES
        Script written 2021-10-11 by Daniel Rytterqvist inspired of

        https://tom.stevens.se/get-a-list-of-sitecore-users/
        https://doc.sitecorepowershell.com/appendix/security/get-user/
        https://doc.sitecorepowershell.com/appendix/common/show-listview/
        https://stackoverflow.com/questions/38340617/sitecore-powershell-find-users-last-login/
        https://blog.martinmiles.net/post/sitecore-powershell-extension-snippets-collection/
        https://sitecore.stackexchange.com/questions/18661/how-to-get-sitecore-user-create-date-with-powershell/
        https://stackoverflow.com/questions/36128042/powershell-error-adding-to-an-array/
        https://docs.microsoft.com/sv-se/powershell/module/microsoft.powershell.core/about/about_comparison_operators?view=powershell-7.1/
        https://stackoverflow.com/questions/44151502/getting-the-of-days-difference-between-two-dates-in-powershell/44151764#44151764/
        Launch PowerShell Scripts in the Item Context Menu using Sitecore PowerShell Extensions
        Useful Sitecore Query String Parameters
#>

$OnlineUserMinutes = 120
$InactiveDays = 365
$InactiveUserCounter = 0
$SoonToBeInactiveDays = 330
$SoonToBeInactiveUserCounter = 0
$DateFormat = "yyyy-MM-dd" # "dd MMM yyyy"
$TodaysDate = Get-Date -Format "yyyy-MM-dd"
$allExtranetUsers = Get-User -Filter "extranet\*"

# Add values from Membership
$userList = @()
foreach($user in $allExtranetUsers) {
    $muser = [System.Web.Security.Membership]::GetUser($user.Name)
    if($muser.UserName -eq "extranet\Anonymous"){continue}

    $userList += [PSCustomObject]@{
        IsOnline = (Get-Date).AddMinutes(-$OnlineUserMinutes) -lt $muser.LastLoginDate
        Status = if((Get-Date).AddDays(-$InactiveDays) -gt $muser.LastLoginDate) {
            [math]::Ceiling((($muser.LastLoginDate)-(Get-Date)).TotalDays)
            $InactiveUserCounter++
        } elseif ((Get-Date).AddDays(-$SoonToBeInactiveDays) -gt $muser.LastLoginDate) {
            $InactiveDays + [math]::Ceiling((($muser.LastLoginDate)-(Get-Date)).TotalDays)
            $SoonToBeInactiveUserCounter++
        };
        Email=$($muser.Email)
        UserName=$($muser.UserName)
        LastLoginDate=$($muser.LastLoginDate)
        CreationDate=$($muser.CreationDate)
        Comment=$($muser.Comment)
        IsApproved=$($muser.IsApproved)
        IsLockedOut=$($muser.IsLockedOut)
        IsAdministrator=$($user.IsAdministrator)
        LastPasswordChangedDate=$($muser.LastPasswordChangedDate)
    }
}

$userProperty = @{Label="Icon"; Expression={
        if (-not $_.IsApproved -or $_.Status -le -1){ "People/32x32/pawn_glass_red.png"}
        elseif ($_.IsLockedOut -or $_.Status -ge 0) { "People/32x32/pawn_glass_yellow.png"}
        elseif($_.IsOnline) { "People/32x32/pawn_glass_green.png" }
        else {"People/32x32/pawn_glass_white.png"}}},
    @{Label="Retention<br>Due Days"; Expression={$_.Status} },
    @{Label="Email"; Expression={$_.Email} },
    @{Label="Username"; Expression={$_.UserName} },
    @{Label="LastLoginDate"; Expression={$_.LastLoginDate.ToString($DateFormat)} },
    @{Label="Creation Date"; Expression={ $_.CreationDate.ToString($DateFormat)} },
    @{Label="Comment"; Expression={$_.Comment} },
    @{Label="Is Approved"; Expression={ $_.IsApproved} },
    @{Label="Is Locked Out"; Expression={ $_.IsLockedOut} },
    @{Label="Is Administrator"; Expression={ $_.IsAdministrator } },
    @{Label="Password Changed"; Expression={ $_.LastPasswordChangedDate.ToString($DateFormat)} },
    @{Label="Sort"; Expression={$_.LastLoginDate} }
    # @{Label="Last Activity Date"; Expression={ $_.Profile.LastActivityDate} }  Cannot have this propery because it will register todays date as LastActivityDate on the user

$InactiveAction = $InactiveUserCounter + $SoonToBeInactiveUserCounter

$users = $userList | Select-Object -Property $userProperty | Sort-Object -Property Sort

$window = @{
    Title = "$InactiveAction of total $($users.Count) Extranet users need attention"
    InfoTitle = "No of Extranet users"
    InfoDescription = "* not beeing active the last $InactiveDays days: $InactiveUserCounter<br>* not logged in the last $SoonToBeInactiveDays days: $SoonToBeInactiveUserCounter"
    MissingDataMessage = "No Extranet users were found"
    PageSize = 200
}

$users | Show-ListView @window

if($InactiveAction -eq 0){
    Show-Alert "All Extranet user accounts are ok regarding the retention policy."
}

The report could be stored and accessed via the Sitecore Start-button -> Reporting Tools -> PowerShell Reports -> Users -> Extranet users

Notice: Do not sort on LastActivityDate because then that user account will be updated with the current date and time.

Powershell fro Sitecore issues exporting the report to Excel, CSV etc.

Noticed I could not export the report to Excel or CSV. I’ve got an exception

Exception: System.ArgumentNullException
Message: Value cannot be null.
Parameter name: name
Source: Sitecore.Kernel
at Sitecore.Diagnostics.Assert.ArgumentNotNull(Object argument, String argumentName) at Sitecore.Globalization.Language.Parse(String name) at Sitecore.Shell.Applications.ContentEditor.File.OpenFile(ClientPipelineArgs args)

Found out that the Powershell for Sitecore version I used was an outdated version not supporting [PSCustomObject]. Downloaded the latest version 5.1 from the Marketplace but I was not able to install it. Searching for the issue and found that some file names in the zip file are not valid according to Sitecore ItemNameValidation rule setting name=”ItemNameValidation” value=”^[\w*$][\w\s-$]*((\d{1,})){0,1}$”. An item name cannot start or end with blanks or exclamation marks.

The solution is to open the Sitecore.PowerShell.Extensions-5.1.zip with 7-Zip and remove the last trailing blank space in the following items and its corresponding xml file:

\items\core\sitecore\system\Dictionary\PowerShell\T\The data for the dialog is not available Either your server was restarted or the server cache was
\items\core\sitecore\system\Dictionary\PowerShell\T\The data for the dialog is not available Either your server was restarted or the server cache was{90A4B70F-D65F-477F-A3B0-CF580530DD68}\en\1\xml

Still did not install and I found another 2 items that need to be edited because they end with an exclamation mark [!] when I tested the regex pattern using this site

\Sitecore.PowerShell.Extensions-5.1.zip\package.zip\items\core\sitecore\system\Dictionary\PowerShell\E\Execution prevented!
\Sitecore.PowerShell.Extensions-5.1.zip\package.zip\items\core\sitecore\system\Dictionary\PowerShell\E\Execution prevented!\{701479AE-13AD-4121-B003-9AE31665BFD9}\en\1\xml

and

\Sitecore.PowerShell.Extensions-5.1.zip\package.zip\items\core\sitecore\system\Dictionary\PowerShell\S\Script cannot be executed as it is of a wrong data template!
\Sitecore.PowerShell.Extensions-5.1.zip\package.zip\items\core\sitecore\system\Dictionary\PowerShell\S\Script cannot be executed as it is of a wrong data template!\{065E0E9F-8E39-4463-9AB0-64537051802F}\en\1\xml

Note that the Marketplace site is no longer maintained, and should not be used. So to be sure to have all the latest PowerShell for Sitecore with all the updates better download from https://github.com/SitecorePowerShell

If you find this script useful, please leave a comment!

Sitecore

By Daniel  -      

---