Invoke-IcingaCheckMSSQLResource uses wrong/insufficient query

[Donien]

When using Invoke-IcingaCheckMSSQLResource the effective query used is

SELECT
    RTRIM(object_name) as object_name,
    RTRIM(counter_name) as counter_name,
    RTRIM(instance_name) as instance_name,
    RTRIM(cntr_value) as cntr_value,
    RTRIM(cntr_type) as cntr_type
        FROM sys.dm_os_performance_counters
            WHERE (object_name = 'SQLServer:Buffer Manager' AND counter_name = 'page life expectancy')
               OR (object_name = 'SQLServer:Buffer Manager' AND counter_name = 'Buffer cache hit ratio')
               OR (object_name = 'SQLServer:Latches' AND counter_name = 'Average Latch Wait Time (ms)')
               OR (object_name = 'SQLServer:Buffer Manager' AND counter_name = 'Buffer cache hit ratio base')
               OR (object_name = 'SQLServer:Latches' AND counter_name = 'Average Latch Wait Time Base');

In my case, using MSSQL-Server Express Edition, this query returns no results since the

object_name column does not look like

object_name = 'SQLServer:Buffer Manager'

but instead looks like

object_name = 'MSSQL$SQLEXPRESS01:Buffer Manager'

for example.

At least in the case of MSSQL-Server Express Edition the direct string comparison does not seem to be sufficient.

[winter1967]

Additionaly:
At our servers, we use 'named instances', so the following queries may be helpful:

WHERE (object_name LIKE '%:Buffer Manager%'  AND counter_name = 'page life expectancy')
               OR (object_name LIKE '%:Buffer Manager%' AND counter_name = 'Buffer cache hit ratio')
               OR (object_name LIKE '%:Latches%' AND counter_name = 'Average Latch Wait Time (ms)')
               OR (object_name LIKE '%:Buffer Manager%' AND counter_name = 'Buffer cache hit ratio base')
               OR (object_name LIKE '%:Latches%' AND counter_name = 'Average Latch Wait Time Base');

[audiocoach]

Same for me. I am also using named instances.

Same problem with Invoke-IcingaCheckMSSQLPerfCounter.
If you set "\SQLServer:Buffer Manager\Buffer cache hit ratio" as performance counter value the result is "No checks added to this package"
If you use the named instance instead of \SQLServer it is working as expected. For example my named instance is MYDATABASE01. So I need to use "\MSSQL$MYDATABASE01::Buffer Manager\Buffer cache hit ratio" as performance counter value.

[audiocoach]

And if you are using the direcor you have to set '\MSSQL$$MYDATABASE01::Buffer Manager\Buffer cache hit ratio'

[audiocoach]

Ok, I think I have found a solution. I don't know if it is the best way to solve the problem but for me it works. You have to change the follwing files:

C:\Program Files\WindowsPowerShell\Modules\icinga-powershell-mssql\provider\mssql\Get-IcingaMSSQLPerformanceCounter.psm1
Change Line 108
"{0}(object_name = '{1}' AND counter_name = '{2}') OR ", to "{0}(object_name LIKE '{1}' AND counter_name = '{2}') OR ",

Change Line 117
"{0}(object_name = '{1}' AND counter_name = '{2}' AND instance_name = '{3}') OR ", to "{0}(object_name LIKE '{1}' AND counter_name = '{2}' AND instance_name = '{3}') OR ",

C:\Program Files\WindowsPowerShell\Modules\icinga-powershell-mssql\plugins\Invoke-IcingaCheckMSSQLResource.psm1
Change Line 102-107 to:

            '\%:Buffer Manager%\page life expectancy',
            '\%:Buffer Manager%\Buffer cache hit ratio',
            '\%:Latches%\Average Latch Wait Time (ms)',
            '\%:Buffer Manager%\Buffer cache hit ratio base',
            '\%:Latches%\Average Latch Wait Time Base'

Change Line 140-158 to:

        switch -Wildcard ($FullName) {
            '\*:Buffer Manager\page life expectancy' {
                $Check = (New-IcingaCheck -Name $Entry.counter_name -Value $Entry.cntr_value -Unit 's' -MetricIndex $SerializedCounter.Category -MetricName $SerializedCounter.Counter).WarnOutOfRange($PageLifeExpectancyWarning).CritOutOfRange($PageLifeExpectancyCritical);  
                break;
            };
            '\*:Buffer Manager\Buffer cache hit ratio' {
                $Check = (New-IcingaCheck -Name $Entry.counter_name -Value (($Entry.cntr_value * 1.0 / $BufferRatioBase) * 100) -Unit '%' -MetricIndex $SerializedCounter.Category -MetricName $SerializedCounter.Counter).WarnOutOfRange($BufferCacheHitRatioWarning).CritOutOfRange($BufferCacheHitRatioCritical);
                break;
            };
            '\*:Latches\Average Latch Wait Time (ms)' {
                $Check = (New-IcingaCheck -Name $Entry.counter_name -Value ($Entry.cntr_value / $LatchWaitTimeBase) -Unit 'ms' -MetricIndex $SerializedCounter.Category -MetricName $SerializedCounter.Counter).WarnOutOfRange($AverageLatchWaitTimeWarning).CritOutOfRange($AverageLatchWaitTimeCritical);
                break;
            };
        }

        # Do not add these metrics to our check package of create checks for them
        if ($FullName -like '\*:Buffer Manager\Buffer cache hit ratio base' -Or $FullName -like '\*:Latches\Average Latch Wait Time Base') {
            continue;
        }

After the manual changes you have to rebuild the cache and restart the icinga services:

icinga -rebuildcache
Restart-IcingaService icinga2
Restart-IcingaService icingapowershell