Squared Up dashboard for locked Active Directory Users – Only in PowerShell

Introduction

Squared Up’s Web-API tile allows it to integrate information from any web-service that returns JSON data.

With Polaris, a free and open source framework it is possible to build web-services just in PowerShell.

This example explains the steps to create web-service in Polaris which returns locked out user information and how to integrate them nicely in Squared Up.

su-Dashboard
Locked User Dashboard

Requirement

  • A windows server that will host your Polaris web-service
  • On that server PowerShell version 5.1
  • Active Directory Users & Computer and its module installed ( part of the RSAT )
  • Administrative permissions on to install Polaris (Install-Module -Name Polaris)
  • Create an empty directory adsvc insight of C:\Program Files\WindowsPowerShell\Modules\Polaris
  • Open the Windows firewall to allow incoming connection to the port you specify, here 8082.
  • Limit this port to only accept request from your Squared Up server.
  • NSSM – the Non-Sucking Service Manage to run your web-service script as a service.
    https://nssm.cc/

Realization

The solution consists of two PowerShell scripts. The first one exports locked user information into a JSON file. It needs to be scheduled via Task Scheduler to provide up-to-date information for the dashboard. It would be also possible to extract locked user information on each dashboard load, but that would be very slow.

Export Script

Create a directory C:\ScheduledTasks and copy the following lines into text file. Name it Export-ADLockedAndExpiredUsers.ps1. Place the following content into it:

Import-module ActiveDirectory

$jsonFilePath = 'C:\ScheduledTasks\SquaredUpExports\ADLockedAndExpiredUsers.json'

# storing raw active directory information in ArrayList
$rawLockedUersList = New-Object -TypeName System.Collections.ArrayList

Search-ADAccount -LockedOut | Select-Object -Property Name,SamAccountName,Enabled,PasswordNeverExpires,LockedOut,`
                                    LastLogonDate,PasswordExpired,DistinguishedName | ForEach-Object {
                                        if ($_.Enabled) {
                                            $null = $rawLockedUersList.Add($_)
                                        }
}


# helper function to get account lock out time
Function Get-ADUserLockedOutTime {

    param(
        [Parameter(Mandatory=$true)]
        [string]$userID
    )

    $time = Get-ADUser -Identity $_.SamAccountName -Properties AccountLockoutTime `
        | Select-Object @{Name = 'AccountLockoutTime'; Expression = {$_.AccountLockoutTime | Get-Date -Format "yyyy-MM-dd HH:mm"}}

    $rtnValue = $time | Select-Object -ExpandProperty AccountLockoutTime

    $rtnValue

} #End Function Get-ADUserLockedOutTime


# main function that sorts and formats the output to fit better in the dashboard
Function Get-ADUsersRecentLocked {

    param(
        [Parameter(Mandatory=$true)]
        [System.Collections.ArrayList]$userList
    )

    $tmpList = New-Object -TypeName System.Collections.ArrayList
    
    $tmpList = $userList | Sort-Object -Property LastLogonDate -Descending
    $tmpList = $tmpList  | Select-Object -Property Name,`
                    @{Name = 'UserId' ; Expression = { $_.SamAccountName }}, `
                    @{Name = 'OrgaUnit' ; Expression = { ($_.DistinguishedName -replace('(?i),DC=\w{1,}|CN=|\\','')) -replace(',OU=',' / ')} }, `
                    Enabled,PasswordExpired,PasswordNeverExpires, `
                    @{Name = 'LastLogonDate'; Expression = { $_.LastLogonDate | Get-Date -Format "yyyy-MM-dd HH:mm" }}, `
                    @{Name = 'AccountLockoutTime'; Expression = { (Get-ADUserLockedOutTime -userID $_.SamAccountName) }}

    $tmpList = $tmpList | Sort-Object -Property AccountLockoutTime -Descending                    
    
    # adding a flag character for improved visualization (alternating)
    $rtnList   = New-Object -TypeName System.Collections.ArrayList    
    $itmNumber = $tmpList.Count
    
    for ($counter = 0; $counter -lt $itmNumber; $counter ++) {

        $flack = ''
        if ($counter % 2) { 
            $flack = ''
        } else {
            $flack = '--'
        }

        $userProps = @{
            UserId               = $($flack + $tmpList[$counter].UserId)
            OrgaUnit             = $($flack + $tmpList[$counter].OrgaUnit)
            Enabled              = $($flack + $tmpList[$counter].Enabled)
            PasswordExpired      = $($flack + $tmpList[$counter].PasswordExpired)
            PasswordNeverExpires = $($flack + $tmpList[$counter].PasswordNeverExpires)
            LastLogonDate        = $($flack + $tmpList[$counter].LastLogonDate)
            AccountLockoutTime   = $($flack + $tmpList[$counter].AccountLockoutTime)
        }

        $userObject = New-Object -TypeName psobject -Property $userProps
        
        $null = $rtnList.Add($userObject)        
        Write-Host $userObject

    } #end for ()          

    $rtnList

} #End Function Get-ADUsersRecentLocked

if (Test-Path -Path $jsonFilePath) {
    Remove-Item -Path $jsonFilePath -Force
}


# exporting result to a JSON file and storing it on $jsonFilePath
Get-ADUsersRecentLocked -userList $rawLockedUersList  | ConvertTo-Json | Out-File $jsonFilePath -Encoding utf8 

Publish Script

Create a directory C:\WebSrv and create an empty text file in it. Rename the file Publish-ADData.ps1. Place the following content into it. This directory contains your web-service.

Import-Module -Name Polaris
$polarisPath = 'C:\Program Files\WindowsPowerShell\Modules\Polaris'

# runs every time the code runs and ensure valid JSON output
$middleWare = @"
    `$PolarisPath = '$polarisPath\adsvc'
    if (-not (Test-path `$PolarisPath)) {
        [void](New-Item `$PolarisPath -ItemType Directory)
    }
    if (`$Request.BodyString -ne `$null) {
        `$Request.Body = `$Request.BodyString | ConvertFrom-Json
    }
    `$Request | Add-Member -Name PolarisPath -Value `$PolarisPath -MemberType Noteproperty    
"@

New-PolarisRouteMiddleware -Name JsonBodyParser -ScriptBlock ([scriptblock]::Create($middleWare)) -Force


# the Get route is launched every time the web-service is called 
New-PolarisGetRoute -Path "/adsvc" -ScriptBlock {
        
    $rawLockedUersList = New-Object -TypeName System.Collections.ArrayList    
    

    $rawData  = Get-Content -Path 'C:\ScheduledTasks\SquaredUpExports\ADLockedAndExpiredUsers.json'
    $jsonData = $rawData | ConvertFrom-Json
    
    if ($jsonData.Count -ne 0) {
        $jsonData | ForEach-Object {
            $null = $rawLockedUersList.Add($_)
        }
    }
     
    $reportTime = Get-item -Path C:\ScheduledTasks\SquaredUpExports\ADLockedAndExpiredUsers.json `
                    | Select-Object -ExpandProperty LastWriteTime | Get-Date -Format "yyyy-MM-dd HH:mm"
    
    $maxNoOfUsers = $null    
    $maxNoOfUsers = $request.Query['maxNoOfUsers']   

    $getReportTime = 'no'
    $getReportTime = $request.Query['getReportTime'] 
    
    $getLockedUserCount = 'no'
    $getLockedUserCount = $request.Query['getLockedUserCount'] 
    
    #if getLockedUserCoutn is yes then return number of locked users
    if ($getLockedUserCount -eq 'yes') {
        $noProps = @{ 'number' = $rawLockedUersList.Count }
        $noObj = New-Object psobject -Property $noProps           
        $response.Send(($noObj | ConvertTo-Json))        
    } 

    #if maxNumber is a number than return locked user information
    if ($maxNoOfUsers -match '\d') {
        $rawLockedUersList = $rawLockedUersList | Select-Object -First $maxNoOfUsers
        $response.Send(($rawLockedUersList | ConvertTo-Json))                
    } 

    #if getReportTime is yes then the time of export will be returned    
    if  ($getReportTime -eq 'yes') {
        
        $tmProps = @{
            'Time' = $reportTime
            'DisplayName' = [System.TimezoneInfo]::Local | Select-Object -ExpandProperty DisplayName
        }
        $tmObj = New-Object psobject -Property $tmProps           
        $response.Send(($tmObj | ConvertTo-Json))        
    }

} -Force

Start-Polaris -Port 8082 

#Keep Polaris running
while($true) {
    Start-Sleep -Milliseconds 10
} 

Configure your web-service to run as a service

Download NSSM and store the nssm.exe in C:\WebSrv . Run the following PowerShell line to convert Publish-ADData.ps1 into a service. – Use ISE or VSCode.

function Install-Service {
    Param(
        [string]$nssmPath = '.',
        [string]$Name,
        [string]$Description,
        [string]$Executable,
        [string]$Arguments
    )

    $nssm = Join-Path -Path $nssmPath -ChildPath 'nssm.exe'
    & $nssm install $name $executable $arguments
    $null = & $nssm set $name Description $description
    Start-Service $name
}

Install-Service -Name WWW-Polaris-ADUserLockData -Description 'PowerShell HTTP API Service - Serves ADUserLockedInfo via REST' -Executable powershell.exe -Arguments '-ExecutionPolicy Bypass -Command C:\WebSrv\Publish-ADData.ps1'

The result can be found in the Windows Services:

Testing

From your Squared Up server, start a web browser and query the web-service.

Locked User Account
Report Time
Locked User Details

Dashboard Building

Add a provider

In Squared Up, switch to System and add a new WEB API provider.  The URL is the one of your Polaris web-service.

Adding WEB-API provider

Add the Dashboard

Create a new dashboard and name it Locked User Info for example. Add a Web-API tile to show the locked user count information.

1-      Locked User Counter

choose the recently created Provider
name the URL with ?getLockedUserCount=yes
skip headers & data with next
specify the key path with .number
optimally, specify the size, complete with done.

2 – Locked Users Details

select WEB API (Grid)
skip the scoping with next
choose the provider previously created
as a URL type in ?MaxNoOfUsers=20
as a key path name propery
edit the columns via edit
place the following code to ensure alternating colors and the removal of “–“
finalize with done

3 – Report time

Add a last WEB-API tile type grid and specify the following URL ?getReportTime=yes

Summery

This walk through shows how to integrate own data via Polaris as REST web-service.

Hope it is useful to one of you.

Feedback is appreciated