SWQL query to bring back component details

Hi Guys,

Hoping you can help. I'm trying to bring back a table of SSL's sorted by their expiration to show upcoming certificates that need replacing

This is based upon a monitor that uses the following PowerShell. The monitor itself works fine, and provides the values for expiration

# Tested with PowerShell 5.1

#region Define Exit Codes
# Shamelessly stolen from blog.kmsigma.com/.../
$ExitCode = @{ "Up" = 0;
"Down" = 1;
"Warning" = 2;
"Critical" = 3;
"Unknown" = 4 }
#endregion Define Exit Codes

# Ignore SSL Warnings
[System.Net.ServicePointManager]::ServerCertificateValidationCallback = { $true}

# Set the Target URL
$Url = $args[0]

# Build the request
$WebRequest = [Net.HttpWebRequest]::Create($Url)
$WebRequest.Method = "HEAD"

#region This does the web request and extracts the important data
# Make the request, but we don't need to output anything
$WebRequest.GetResponse() | Out-Null

# Dig out the SSL Certrificate
$Certificate = $WebRequest.ServicePoint.Certificate

$CertExpiration = [datetime]( $Certificate.GetExpirationDateString() )
$CertIssuer = ( $Certificate.GetIssuerName() ).Split("=")[-1]
$CertSubject = $Certificate.Subject
#endregion This does the web request and extracts the important data

if ( $CertExpiration -and $CertIssuer -and $CertSubject ) {
# Calculate days until certificate expiration
$ExpiresIn = ( $CertExpiration - ( Get-Date ) )

Write-Host "Message.Issuer: Certificate Issuer is '$CertIssuer'"
Write-Host "Statistic.Issuer: 1"
Write-Host "Message.Expiration: Certificate expires on $( $CertExpiration )"
Write-Host "Statistic.Expiration: $( $ExpiresIn.Days )"
Write-Host "Message.Subject: $CertSubject"
Write-Host "Statistic.Subject: 1"

$Status = "Up"
}
else {
# We don't have the data we want, so let's output the same "things" but change them out for errors
Write-Host "Message.Issuer: [Not Detected]"
Write-Host "Statistic.Issuer: 0"
Write-Host "Message.Expiration: [Not Detected]"
Write-Host "Statistic.Expiration: 0"
Write-Host "Message.Subject: [Not Detected]"
Write-Host "Statistic.Subject: 0"

$Status = "Unknown"
}
# In a component monitor, you'll need to send back an exit code
exit $ExitCode[$Status]
# just remove the '#' from the above line to enable that

I can get a list of the ssl certs using the following SWQL

SELECT
[Components].Name AS [Certificate]
FROM
Orion.APM.Application AS [Applications]
JOIN
Orion.APM.Component AS [Components]
ON [Applications].ApplicationID = [Components].ApplicationID
WHERE
[Applications].Name = 'SSL Cert Monitor'
ORDER BY
[Components].Name

However, i can't seem to figure out how to pull the expiration stat

Does anyone know how i might include that in the query?

Find more posts tagged with

dashboard

solarwinds

SWQL

application_monitor

Accepted answers

narzsa

And this is even better as it gives me the days remaining. Cheers @bobmarley

SELECT
[Components].Name AS [Certificate],
cp.ComponentStatisticData AS [Days Till Expiration] -- Statistic data from CurrentStatistics
FROM
Orion.APM.Application AS [Applications]
JOIN
Orion.APM.Component AS [Components]
ON [Applications].ApplicationID = [Components].ApplicationID
JOIN
Orion.APM.CurrentStatistics AS cp
ON [Components].ComponentID = cp.ComponentID -- Joining on ComponentID to get statistics
WHERE
[Applications].Name = 'SSL Cert Monitor'
ORDER BY
cp.ComponentStatisticData ASC -- Sorting by Days Till Expiration in ascending order

All comments

bobmarley

Possibly try this:

SELECT Caption, ErrorMessage, NodeID, ComponentName, ComponentType, ComponentAvailability, ComponentPortNumber, ComponentStatisticData 
FROM Orion.APM.CurrentStatistics cp
INNER JOIN Orion.nodes nd on cp.NodeID = nd.NodeID
Where ComponentName LIKE 'SSL Certificate Expiration Date Monitor' --AND ComponentStatisticData < 100 --displays only 100 days or less
Order BY ComponentStatisticData, ErrorMessage ASC

narzsa

Cheers, the above didn't error but came back with anything

I even tried to remove the qualifier of

ComponentStatisticData < 100 --displays only 100 days or less

No luck

bobmarley

You will need to make the Component Name in the Where statement match whatever you called your SAM template component name in your environment

It should return data similar to this

narzsa

you did give me an idea though. Using the above i drilled down and found that the field that seems to hold the value is cp.errormessage.

Amending my script to the following then brings back a list of the certs added and includes the expiration date

SELECT
[Components].Name AS [Certificate],
cp.ErrorMessage AS [Error Message] -- Error message from CurrentStatistics
FROM
Orion.APM.Application AS [Applications]
JOIN
Orion.APM.Component AS [Components]
ON [Applications].ApplicationID = [Components].ApplicationID
JOIN
Orion.APM.CurrentStatistics AS cp
ON [Components].ComponentID = cp.ComponentID -- Joining on ComponentID to get statistics
WHERE
[Applications].Name = 'SSL Cert Monitor'
ORDER BY
[Components].Name

narzsa