More efficient Account Status Application Monitor

Question

One of our previous admins created a pair of Application Monitors to check the lockout status and the time between password changes for a few of our service\development accounts. One of the application monitors uses a PowerShell script to query Active Directory for both the date and number of days until the password needs to be changed, while the other application monitor uses a similar PowerShell script to check whether or not the account is locked out, and generate a 0/1 statistic for threshold purposes. They generate the desired values, but I was hoping to make things more efficient. First, I'd like to have one application monitor instead of two, and secondly, I'd like to make how the data is stored more efficient. 
 As they currently function, teach application monitor creates two separate lines on the "APM_DynamicEvidenceCurrent" table, each line has a value for either the 'NumericData' or the 'StringData' column, and places a NULL value in the other column. My desired goal would be to reduce this so that I have one application monitor, running one PowerShell script, and have it create few lines in the table, one line for the password expiration check and one line for the lockout status, populating both the 'NumericData' and 'StringData' column of each. So far, I have the following PowerShell script, which does produce all four desired values in a single script, however, it is still creating four separate lines instead of the desired two. NOTE: " testanddevops" is not the real account name, it's used here for posting purposes. 
 $AccountName = "`testanddevops";
$account = Get-ADUser -identity $AccountName -Properties *
$expires = Get-ADUser -identity 'testanddevops'-Properties pwdLastSet| select @{name ="pwdExpires"; expression={$([datetime]::FromFileTime($_.pwdLastSet)).AddDays(60)}}
$ExpirationDate = ($expires.pwdExpires).ToLongDateString();
$TodaysDate = (get-date);
$TimeSpanRaw = New-TimeSpan -start $TodaysDate -end $ExpirationDate;
$ExpirationLeadTime = $TimeSpanRaw.days;
write-host $AccountName
write-host "Message.DaysUntilPasswordExpiration : $ExpirationDate" `n"Statistic.DaysUntilPasswordExpiration : $ExpirationLeadTime";
if
($account.lockedout -eq $False) {write-host Message.LockoutStatus:False `nStatistic.LockoutStatus:0}
Else {(write-host Message.LockoutStatus:True `nStatistic.LockoutStatus:1)}

KMSigma.SWI · Accepted Answer

I worked together a query that I think will work for you. 
 
 SWQL Pivot Example | THWACK Content Exchange - Custom Queries - The Orion Platform - THWACK (solarwinds.com) 
 
 You'll definitely need to change out a few of the elements. When I mention "Unique IDs" I mean the things that are displayed in the Application Component editor. 
 
 Similarly, it inspired me to write up something I've been meaning to do for a while now. 
 
 Adventures in PIVOTing - how to do a poor man's pivot in SWQL - Orion SDK - The Orion Platform - THWACK (solarwinds.com)

KMSigma.SWI · Answer

Before we even get to the script, I want to address the elephant database in the room. That table you reference: APM_DynamicEvidenceCurrent is behaving as it should. It's designed and indexed in such a way that it's supposed to have the nulls in there. 
 In short - no you cannot combine these two distinct statistics into a single line. But what you can do is make a single call against AD and extract out both pieces of data. 
 
 I went a little mad here... 
 # How many days in advance should we check for password expiration?
$DaysInAdvance = 60

# Use the same "date" for all calls
$Today = Get-Date

# Assume all accounts NOT locked out (used for the exit code)
$AnAccountIsLocked = $false


#region Define Exit Codes
# Sourced from: https://blog.kmsigma.com/2017/09/08/sam-powershell-scripting-template/
$ExitCode = @{
 "Up" = 0;
 "Down" = 1;
 "Warning" = 2;
 "Critical" = 3;
 "Unknown" = 4
}
#endregion Define Exit Codes

if ( $args.length -eq 0 ) {
 # No arguments passed into the script - using the 'testing' account
 # wrapping it in @( ... ) so that it's understood to be an array
 $AccountNames = @( "`testanddevops" )
}
elseif ( $args.length -gt 5 ) {
 Write-Warning -Message "SolarWinds Component Templates can only return 10 total component statistics (2 components * 5 accounts = 10). You have provided $( $args.Length ) accounts to check. Reduce this number and try again."
 break
}
else {
 # Store the passed arguments as account names
 $AccountNames = $args
}

# cycle through each entry in the accounts list provided by the arguments
ForEach ( $AccountName in $AccountNames ) {
 
 # I don't think we actually need to pull /everything/ in the properties. Streamlining this list of properties /could/ increase efficiency on slow links
 # We might be able to get away with just:
 # $AdAccount = Get-ADUser -Identity $AccountName -Properties Identity, SamAccountName, pwdLastSet, LockedOut
 $AdAccount = Get-ADUser -Identity $AccountName -Properties *
 
 # This is sort of advanced PowerShell stuff
 # Take an existing object ( $AdAccount ) and extend it with a new property - this one defined by a script
 $AdAccount | Add-Member -MemberType ScriptProperty -Name PasswordExpirationDate -Value { $( [datetime]::FromFileTime( $this.pwdLastSet ) ).AddDays($DaysInAdvance) } -Force

 # This is the part you'll need to test for yourself. I can't remember if "identity" is a good output. You may need to swap out for 'sAmAccountName'

 # First Metric: How many days until the account's password expires
 Write-Host "Message.Expiration_$( $AdAccount.Identity ): Password for '$( $AdAccount.Identity )' expires in $( ( $AdAccount.PasswordExpirationDate - $Today ).Days ) day(s)"
 Write-Host "Statistic.Expiration_$( $AdAccount.Identity ): $( ( $AdAccount.PasswordExpirationDate - $Today ).Days )"

 # Second Metric: Is the account currently locked out
 Write-Host "Message.Locked_$( $AdAccount.Identity ): Account for '$( $AdAccount.Identity )' $( if ( $AdAccount.LockedOut ) { "Locked Out" } else { "Not Locked Out" } )"
 Write-Host "Statistic.Locked_$( $AdAccount.Identity ): Account for '$( $AdAccount.Identity )' $( if ( $AdAccount.LockedOut ) { "1" } else { "0" } )"

 if ( $AdAccount.LockedOut ) {
 $AnAccountIsLocked = $true
 }

} # Move on to the next $AccountName in the $AccountNames array
# You still need an exit code for this and I'm going to go with the simplest option. If ANY of the accounts are locked out, throw "error"
if ( $AnAccountIsLocked ) {
 exit $ExitCode["Critical"]
}
else {
 exit $ExitCode["Up"]
}
 
 Comments are everywhere, so I hope you can follow along. 
 For me, I wanted to look to be as efficient as possible. That means check as many accounts as possible on a single call and return all the details. Those names should be put in the script arguments field separated by commas [Details here ]: 
 
 The script should cycle through each account name from that list (up to five because you can only have 10 total metrics returned in a single component) and determine the time until next password and if the account is locked out. 
 Again: I do not have an Active Directory infrastructure in my home lab from which to pull this information, so I'm going entirely from memory.

More efficient Account Status Application Monitor

Top Replies