can we get any report option to fetch below SSL cert details from template. see attached screen short
Issuer:
Valid From:
Valid To:
Subject:
Subject Alternative Name:
Thumbprint
Did you want to record that information or only send it over via an alert? The data is there (though not parsed well), and can be included in an alert message without too much difficulty.
But if you wanted to "record" this kind of data (for storage in the Orion database), then you'd have to write your own SAM Script template. There are a few examples over in the SAM Content Exchange.
Yeah - if that's what you want then you'll need to use a script component to pull that information. Each script component can pull up to 10 "pieces" of information (normally numeric value), but you can also store the "messages" (plain text) as well.
The out-of-the-box (OOTB) SSL expiration component doesn't need to access the operating system/file system on the target server itself - it only makes an HTTPS call and interprets the certificate. Some script templates already do this. This is one such that would work for PowerShell.
# Tested with PowerShell 5.1 #region Define Exit Codes # Shamelessly stolen 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 # Ignore SSL Warnings [System.Net.ServicePointManager]::ServerCertificateValidationCallback = { $true } # Set the Target URL $Url = "https://www.amazon.com/" # Build the request $WebRequest = [Net.HttpWebRequest]::Create($Url) $WebRequest.Method = "HEAD" #region This does the web requesdt 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 requesdt 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
You'd need to make some slight tweaks to "optimize" it for your environment, but then you'd be collecting the data you wanted. Then you could build a report (or a custom query widget or a Modern Dashboard) that reports this information for you and your leadership teams.
The full certificate information does not appear to be collected as part of the full poll. It is interesting that the test brings back more detail, but a quick look in to the SAM tables in the database, I could not immediately see that extended data.
Therefore, the advice from KMSigma.SWI above is your only route to collecting this data. You will need to adapt the script he has provided, to conform to the requirements that SAM has for structuring the output in manner it can store. For this I suggest you look at the PowerShell examples already within SAM and you will quickly see the 3 values you need.
The following KB article will also provide strong guidance
How about this:
SELECT n.Caption AS [Node] ,ce0.StringData AS [Subject] ,MAX(ce1.StringData) AS [Issuer] ,MAX(ce2.StringData) AS [Expiration] ,MAX(ce2.AvgNumericData) AS [Days until expire] FROM Orion.APM.Component(nolock=true) c INNER JOIN Orion.APM.CurrentComponentStatus(nolock=true) AS ccs ON c.ComponentID = ccs.ComponentID INNER JOIN Orion.APM.DynamicEvidence(nolock=true) AS ce0 ON ce0.ComponentStatusID = ccs.ComponentStatusID AND CE0.ColumnName='Subject' and columntype=0 INNER JOIN Orion.APM.DynamicEvidence(nolock=true) AS ce1 ON ce1.ComponentStatusID = ccs.ComponentStatusID AND CE1.ColumnName='Issuer' and columntype=0 INNER JOIN Orion.APM.DynamicEvidence(nolock=true) AS ce2 ON ce2.ComponentStatusID = ccs.ComponentStatusID AND CE2.ColumnName='Expiration' and columntype=0 INNER JOIN Orion.APM.Application(nolock=true) AS a ON c.ApplicationID = a.ApplicationID INNER JOIN Orion.Nodes(nolock=true) AS n ON a.NodeID = n.NodeID WHERE c.componentname='SSL Cert Expiration' GROUP BY N.Caption, Ce0.Stringdata
This code assumes you have named the components 'SSL Cert Expiration'. Change it to your needs.
Hi
I took the script from KMSigma.SWI above and changed is just a bit, added that the URL is taken from the argument field in the component. Script then goes like this:
# Tested with PowerShell 5.1 #region Define Exit Codes # Shamelessly stolen 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 # 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
When configuring the component it looks like this to check the certificate at www.solarwinds.com:
Then I rewrote my previous script. It looks for all components with names starting with "SSL Cert Expiration" and gives you a list of them all. Script, that can be used both as widget or base for a report, looks like this:
SELECT C.Name AS [Name] ,ISNULL(CS.Value,CTS.Value) AS [Website] ,S2.NumericData AS [Days until expire] ,S1.StringData AS [Issuer] ,S2.StringData AS [Expiration] ,S3.StringData AS [Subject] --Links For Columns ,ISNULL(CS.Value,CTS.Value) AS [_LinkFor_Website] ,C.DetailsURL AS [_LinkFor_name] --Icon For Columns ,CASE WHEN c.Status=1 THEN '/Orion/APM/images/StatusIcons/Components/Small-Up.gif' WHEN c.Status=3THEN '/Orion/APM/images/StatusIcons/Components/Small-Warning.gif' WHEN c.Status=14 THEN '/Orion/APM/images/StatusIcons/Components/Small-Critical.gif' WHEN c.Status=27 THEN '/Orion/APM/images/StatusIcons/Components/Small-Unmanaged.gif' ELSE '/Orion/APM/images/StatusIcons/Components/Small-Unknown.gif' END as [_IconFor_Name] FROM Orion.APM.Component AS C INNER JOIN Orion.APM.MultipleStatisticData AS S1 ON C.ComponentID=S1.ComponentID AND S1.Name='Issuer' INNER JOIN Orion.APM.MultipleStatisticData AS S2 ON C.ComponentID=S2.ComponentID AND S2.Name='Expiration' INNER JOIN Orion.APM.MultipleStatisticData AS S3 ON C.ComponentID=S3.ComponentID AND S3.Name='Subject' LEFT OUTER JOIN Orion.APM.ComponentSetting AS CS ON C.ComponentID=CS.ComponentID AND CS.key='Scriptarguments' LEFT OUTER JOIN Orion.APM.ComponentTemplateSetting AS CTS ON C.TemplateID=CTS.ComponentTemplateID AND CTS.key='Scriptarguments' WHERE C.Name LIKE 'SSL Cert Expiration%' ORDER BY S2.NumericData
A widget with above could then look like this:
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.