This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SSL cert detail information required

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.

  • Hi,

    we required to pull data for all server in report/excel sheet from template. 

  • 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.

  • can you add under script 

    Valid From

    Thumbprint 

    Subject Alternative Name 

  • also i am unable to create custom report as required data filed is not showing under add columns

    can you share any advance database query for report?

  • 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 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

    Scripts with text output (solarwinds.com)

  • Yes, I am able to get Issuer, Expiration and Subject information by above script. i have tried to add other required information but no result found. 

    can you share any advance database query for report?

  • 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  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:

  • I tried you query and made some change 

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

    to

    # Set the Target URL
    $URL = "https://${IP}"



    not getting full information