Account Lockout Dashboard Help!

I could use some help creating a dashboard (preferably modern) showing the user account lockouts on each domain controller. I have a SAM template gathering the username, lockout time, and domain controller the account is locked out on. The template is applied to each domain controller, but I'm trying to consolidate the lockout data into a dashboard showing all Usernames, Lockout Date\Time, and the Domain controller the lockout occured on.

I'm new to making dashboards and my SWQL skills are not strong. Is there any quick guidance you can give me to create this simple dashboard? If it helps, the Powershell script I'm using to gather the information is shown below.

$lockedAccounts = Search-ADAccount -LockedOut -UsersOnly | Sort-Object -Descending -Property LastLogonDate

If (!$lockedAccounts) 
{
    Write-Host "Statistic.LockedAccounts: 0"
    Write-Host "Message.LockedAccounts: No accounts are locked out."
    Break
}

$lockedOutput = @()

ForEach ($account in $lockedAccounts) {
    If ($account.SamAccountName -notin $args)
    {
        $lockedUser = $account.UserPrincipalName
        $lockedDate = $account.LastLogonDate

        $lockedOutput += "$lockedUser ($lockedDate)"
    }
}

$lockedCount = $lockedOutput.Count
$lockedList = $lockedOutput -join ', '

Write-Host "Statistic.LockedAccounts: $lockedCount"
Write-Host "Message.LockedAccounts: $lockedList"

Find more posts tagged with

sam

Dashboards

Accepted answers

All comments

LatteLarry

I had a colleague assist with the SQL to achieve what I needed. It was a bit tricky because the Event message contained the information we needed to place in the table. The SQL returns the information we need, but now I'm struggling a bit with converting this to SWQL for the Table widget. I could use this SQL query in a classic dashboard, but I'd really like to utilize a modern dashboard since this tool will be used by the helpdesk.

Could anyone assist with helping me convert this to SWQL? I think a lot of people would find this useful.

SELECT  DATEADD(MILLISECOND,DATEDIFF(MILLISECOND,getutcdate(),GETDATE()),TimeGeneratedUtc) AS UTC_To_LocalTime
       ,trim(SUBSTRING(message,(LEN(Message) - CHARINDEX(':emaN tnuoccA',REVERSE(message)) + 4) ,CHARINDEX('Additional Information:',message)-(LEN(Message) - CHARINDEX(':emaN tnuoccA',REVERSE(message)) + 4))) AS [User Name]
       ,trim(SUBSTRING(message,(CHARINDEX('Caller Computer Name:',message)+22),(CHARINDEX('Caller Computer Name:',message)+22) - CHARINDEX(CHAR(13),Message))) AS [Computer Name]
       ,trim(SUBSTRING(message,(CHARINDEX('Account Domain:',message)+17),CHARINDEX('Logon ID:',Message) - (CHARINDEX('Account Domain:',message)+17) )) AS [Domain]
FROM [SolarWindsOrion].[dbo].[APM_WindowsEvent_Detail]
WHERE Message LIKE 'A user account was locked out.%'
AND TimeGeneratedUtc BETWEEN DATEADD(Day, -7, GETUTCDATE()) AND GETUTCDATE()
ORDER BY TimeGeneratedUtc DESC

LatteLarry

Here's the SWQL to be placed in a modern dashboard table widget to report on Account Lockouts. This will extract the important details from the event id message. I'd have to think someone would find this useful in addition to me,

SELECT DISTINCT ToLocal(TimeGeneratedUtc) as LocalTime

,SubString(message, CHARINDEX('Account Name:',message,CHARINDEX('Account That Was Locked Out:',message))+15, CHARINDEX('Additional Information:',message)-31-CHARINDEX('Account Name:',message,
CHARINDEX('Account That Was Locked Out:',message))+15) as Account
,SUBSTRING(message,CHARINDEX('Account Domain:',message,CHARINDEX('Account Domain:',message))+15, CHARINDEX('Logon ID:',message,CHARINDEX('Account Domain:',message))- (CHARINDEX('Account Domain:',message,CHARINDEX('Account Domain:',message))+15)) AS Domain
,SubString(message, CHARINDEX('Caller Computer Name:',message)+21, length(Message) - (CHARINDEX('Caller Computer Name:',message)+21)) as Source

FROM Orion.APM.WindowsEvent

WHERE Message LIKE 'A user account was locked out.%'
--AND Message NOT LIKE ''
AND TimeGeneratedUtc BETWEEN ADDDATE('Day',-1, GETUTCDATE()) AND GETUTCDATE()