Powershell scripts and Component Montioring

Below is part of my script. I just wanted someone to tell me if this should work in Orion. It currently works fine from the polling engine machine from within Powershell ISE, however, I can never get it to work in a component monitor. It is set for Local Host execution mode and the credentials does have access as it is in the local admins group.

if ($count -eq 0)
{
Write-Host Message: "There are no outstanding certificate expirations that are $reminderDays days out.";
Write-Host "Statistic: 0";
Exit 0;
if (test-path -path c:\scripts\messagebody.txt)
{
#Remove-Item -Path c:\scripts\messagebody.txt #-WhatIf
}
}
Else
{
$finalContent = Get-Content -Path c:\scripts\messagebody.txt
#Remove-Item -Path c:\scripts\messagebody.txt #-WhatIf
Write-Host "Message: $finalContent";
Write-Host "Statistic: 1";
Exit 1;
}

Find more posts tagged with

component monitor

PowerShell

Accepted answers

christopher.t.jones123

@miketartaglia, I've seen this before when I was messing around with VEEAM, met all the pre-reqs of having the VEEAM PowerShell module installed on the polling engine but everytime I attempted to use it in a component monitor it would just fail with the same error. I got around it but just doing an invoke-cmd to the VEEAM server itself and created the component as a remote execution one instead. Maybe you can do something similar here and have this component remotely executed on the SQL server in question.

All comments

christopher.t.jones123

@miketartaglia, when you are testing it within SolarWinds which are condition are you expecting? if its the else portion change the Exit code to 3 (warning) or 4 (critical). Using exit code 1 will cause the component to display as down but as a failed script execution.

miketartaglia

In the first part of the If statement, I am expecting UP or Exit 0

in the else part of the if statement, I am expecting down or critical. I had this part with an Exit 3 but it was never triggering with Exit 3 so I tried exit 1 with no luck.

christopher.t.jones123

@miketartaglia, what is the error message or the values it is returning that indicate it is not working? And from the test what are you expecting it to do, for instance, you know that it should be returning critical because the condition exists.

It'll also be helpful to see the whole script to add context to what is doing and give a better idea of what these variables are supposed to be.

miketartaglia

Sorry I didn't want to ugly up the post, but here is the script I am using. There is a $reminderDays variable currently set to 250 (this will be 30 when I have confirmed the script is working. If the data in the database comes back and says the cert expires in 250 or less, then exit 3. I have confirmed that the data does math that criteria for the test, but it always exits with ZERO.

$db = 'DNS'
$sqlServer = 'FC-SQL01P'
$query = 'SELECT [ID],[Common_Name],[Expiration_Date],[Provider] FROM [DNS].[dbo].[SSLCertificates]'
$reminderDays = 250
$certs = Invoke-Sqlcmd -Query $query -ServerInstance $sqlServer -Database $db -SuppressProviderContextWarning
$todayDate = Get-Date
$count = 0
[array]$finalOutput = ""
foreach ($cert in $certs)
{
$expirationDate = $cert.Expiration_Date.ToString()
$commonName = $cert.Common_Name
$message = " - this cert is ready for renewal"
$obj = NEW-TIMESPAN -Start $todayDate -End $cert.Expiration_Date #==> $obj.Days gives you how many days left from today till expiry
if ($obj.Days -lt $reminderDays -and $obj.Days -ge 0)
{
$content = "`n$commonName - $expirationDate`n "
Add-Content -path c:\scripts\messagebody.txt -value $content
#$finalOutput = "$finalOutput" + "$cert.expirationdate $cert.commonname 'this cert is ready for renewal'"
$count++
}
}
if ($count -eq 0)
{
Write-Host Message: "There are no outstanding certificate expirations that are $reminderDays days out.";
Write-Host "Statistic: 0";
Exit 0;
if (test-path -path c:\scripts\messagebody.txt)
{
#Remove-Item -Path c:\scripts\messagebody.txt #-WhatIf
}
}
Else
{
$finalContent = Get-Content -Path c:\scripts\messagebody.txt
#Remove-Item -Path c:\scripts\messagebody.txt #-WhatIf
Write-Host "Message: $finalContent";
Write-Host "Statistic: 1";
Exit 1;
}

miketartaglia

Oh so that I am clear, I get the following results no matter what.

Output Result:

Message.Statistic:

There are no outstanding certificate expirations that are 250 days out.

Statistic.Statistic:

KMSigma.SWI

I'm making some major assumptions here, but I think you might be over-engineering this. If you could post a sample of what the SQL table looks like and what you are hoping to see as output that would help. Regardless, here's what I came up with:

<# This is a HUGE guess depending on what exactly you are looking to do.  More than a few assumptions were made #>



#region Define Exit Codes
# Source: 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

$db = 'DNS'
$sqlServer = 'FC-SQL01P'

# Move the Date/Time stuff to the SQL Query so it returns less rows
$today = Get-Date
$reminderDays = 250

# Move the query to a 'here-string' for readability
$query = @"
SELECT [ID]
      ,[Common_Name]
      ,[Expiration_Date]
      ,[Provider]
FROM [DNS].[dbo].[SSLCertificates]
WHERE [Expiration_Date] <= '$( ( $today ).AddDays($reminderDays).ToString('yyyy-MM-dd') )'"


# Test to see if the Invoke-Sqlcmd is available

if ( Get-Command -Name Invoke-Sqlcmd -ErrorAction SilentlyContinue ) {
    $certs = Invoke-Sqlcmd -Query $query -ServerInstance $sqlServer -Database $db -SuppressProviderContextWarning
    <#
    Assuming data looks something like this:

    ID | Common_Name                | Expiration_Date | Provider
    ---+----------------------------+-----------------+----------
    27 | "My Important Certificate" | 2021-12-11      | GoDaddy

    #>

    # if we have matching certificates
    if ( $certs ) {
        $Statistic = $certs.Count
        $Messages = @() # build an empty array for messages

        # cycle through each of the certificates starting with the one with the earliest expiration date
        ForEach ( $cert in $certs | Sort-Object -Property Expiration_Date ) {
            $Messages += "$( $cert.Common_Name ) expires in $( ( ( Get-Date $cert.ExpirationDate ) - $today ).Days ) day(s) [from $( $cert.Provider )]"
        }
        Write-Host "Statistic: $Statistic"
        Write-Host "Message: $( $Messages -join ' / ' )"
        exit $ExitCode["Warning"]
    } else {
        # If we do not have matching certificates
        $Statistic = 0
        $Message   = "No certificates are expiring in the next $reminderDays day(s)"
        exit $ExitCode["Up"]
    }
} else {
    Write-Host "Statistic: -1"
    Write-Host "Message: Missing 'Invoke-Sqlcmd'"
    exit $ExitCode["Unknown"]
}

Note: I could not test this because I don't have a database like yours. Also - how are you passing login credentials to the SQL Server? I didn't see a -Username and -Password or a -Trusted parameter on the Invoke-Sqlcmd function.

Seashore

Or why go through powershell at all? Why don't do it all in SQL with a "SQL Component" instead of a powershell component.
Below will give you (I hope) how many certificates that expires within 14 days from the database. Normally it gives you 0 back. Set a critical threshold when statistics is more than 0. The message part will contain the certificate name and expiration date.

;WITH current_state AS
(
	SELECT 
		[Common name] + ' expires ' + CAST([EXPIRATION_DATE] as nvarchar(10) AS Cert
	FROM [DNS].[dbo].[SSLCertificates] 
	WHERE 
		[EXPIRATION_DATE]