Monitoring Azure SQL Managed Instance with Zabbix

[finkrandy]

Hello. I wanted to share with those Azure customers who have SQL Managed Instances how I began monitoring the databases with Zabbix. My environment consists of both on-premise, SQL on Azure VM, Azure SQL, and Azure SQL Managed Instance. I was able to utilize both the Template/Databases - MSSQL by ODBC and an Azure plugin for Zabbix to monitor everything except SQL Managed Instances. After looking at the results of the queries and the system views from which MSSQL by ODBC was attempting, I figured out an easy fix to begin monitoring managed instances

1. Perform a full clone of the template MSSQL by ODBC. I called mine MSSQL Managed Instance by ODBC
2. Add your instance to the ODBC configuration utilized on your server. Mine is running on CentOS
3. Go to Configuration - Templates and select the new template
4. Select the Item "MSSQL: Get performance counters"
5. Change the first SELECT of the unions from
OLD
SELECT object_name,counter_name,instance_name,cntr_value
FROM sys.dm_os_performance_counters

NEW
SELECT c.object_name,c.counter_name,d.name as instance_name,c.cntr_value
FROM sys.dm_os_performance_counters c
JOIN sys.databases d ON c.instance_name = d.physical_database_name

6. Click on Update to save your change
7. Add your host and assign the new Template to the host
8. Set a macro of "{$MSSQL.INSTANCE}" and set the value to what is returned by "SELECT DISTINCT SUBSTRING(object_name,1,18) FROM sys.dm_os_performance_counters WHERE object_name like 'MSSQL%"
9. Set a macro of "{$MSSQL.DSN}" to the friendly server name that you use for the Managed Instance.
10. Set the other macros as required by ODBC (username and password)

It will then autodisover the databases and gather performance metrics

[henryshao]

Change the first SELECT of the unions from
OLD:
SELECT object_name,counter_name,instance_name,cntr_value
FROM sys.dm_os_performance_counters

to NEW:
SELECT 'SQLSERVER'+convert(varchar,substring(object_name, charindex(':',object_name),100)) object_name, counter_name,d.name as instance_name,cntr_value
FROM sys.dm_os_performance_counters c
JOIN sys.databases d ON c.instance_name = d.physical_database_name
UNION all
SELECT 'SQLSERVER'+convert(varchar,substring(object_name, charindex(':',object_name),100)) object_name, counter_name,
case when instance_name like 'MSSQL%' then 'SQLServer' else instance_name end instance_name,
cntr_value
FROM sys.dm_os_performance_counters c
where not exists
(
select 1
from sys.databases d
where c.instance_name=d.physical_database_name
)

fixed all problems, "{$MSSQL.INSTANCE}" macro does not need change, the default value "SQLServer" works for me.

[henryshao]

Sorry, the json string is case sensitive, should be "SQLServer" instead "SQLSERVER":

SELECT 'SQLServer'+convert(varchar,substring(object_name, charindex(':',object_name),100)) object_name, counter_name,d.name as instance_name,cntr_value
FROM sys.dm_os_performance_counters c
JOIN sys.databases d ON c.instance_name = d.physical_database_name
UNION
SELECT 'SQLServer'+convert(varchar,substring(object_name, charindex(':',object_name),100)) object_name, counter_name,
case when instance_name like 'MSSQL%' then 'SQLServer' else instance_name end instance_name,
cntr_value
FROM sys.dm_os_performance_counters c
where not exists
(
select 1
from sys.databases d
where c.instance_name=d.physical_database_name
)

[Leachman85]

Did you have a fix on this issue? Challenging a relative issue yet no response from anyone and couldn't see this point taking a gander at in google.

Myhdfs.com

[henryshao]

-- updated SQL:
with counter as
(
SELECT '{$MSSQL.INSTANCE}'+convert(varchar,substring(obje ct_name, charindex(':',object_name),100)) object_name, counter_name,d.name as instance_name,cntr_value
FROM sys.dm_os_performance_counters c
JOIN sys.databases d ON c.instance_name = d.physical_database_name
UNION
SELECT '{$MSSQL.INSTANCE}'+convert(varchar,substring(obje ct_name, charindex(':',object_name),100)) object_name, counter_name,
case when instance_name like 'MSSQL%' then '{$MSSQL.INSTANCE}' else instance_name end instance_name,
cntr_value
FROM sys.dm_os_performance_counters c
where not exists
(
select 1
from sys.databases d
where c.instance_name=d.physical_database_name
)
)
select * from counter
UNION
SELECT '{$MSSQL.INSTANCE}' as object_name,'Version' as counter_name,@@version as instance_name,0 as cntr_value
UNION
SELECT '{$MSSQL.INSTANCE}' as object_name,'Uptime' as counter_name,'' as instance_name,DATEDIFF(second,sqlserver_start_time ,GETDATE()) as cntr_value
FROM sys.dm_os_sys_info
UNION
SELECT '{$MSSQL.INSTANCE}atabases' as object_name,'State' as counter_name,name as instance_name,state as cntr_value
FROM sys.databases
UNION
SELECT a.object_name,'BufferCacheHitRatio' as counter_name,'' as instance_name,cast(a.cntr_value*100.0/b.cntr_value as dec(3,0)) as cntr_value
FROM counter a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM counter
WHERE counter_name='Buffer cache hit ratio base' AND OBJECT_NAME='{$MSSQL.INSTANCE}:Buffer Manager') b
ON a.OBJECT_NAME=b.OBJECT_NAME
WHERE a.counter_name='Buffer cache hit ratio' AND a.OBJECT_NAME='{$MSSQL.INSTANCE}:Buffer Manager'
UNION
SELECT a.object_name,'WorktablesFromCacheRatio' as counter_name,'' as instance_name,cast(a.cntr_value*100.0/b.cntr_value as dec(3,0)) as cntr_value
FROM counter a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM counter
WHERE counter_name='Worktables From Cache Base' AND OBJECT_NAME='{$MSSQL.INSTANCE}:Access Methods') b
ON a.OBJECT_NAME=b.OBJECT_NAME
WHERE a.counter_name='Worktables From Cache Ratio' AND a.OBJECT_NAME='{$MSSQL.INSTANCE}:Access Methods'
UNION
SELECT a.object_name,'CacheHitRatio' as counter_name,'_Total' as instance_name,cast(a.cntr_value*100.0/b.cntr_value as dec(3,0)) as cntr_value
FROM counter a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM counter
WHERE counter_name='Cache Hit Ratio base' AND OBJECT_NAME='{$MSSQL.INSTANCE}:Plan Cache' AND instance_name='_Total') b
ON a.OBJECT_NAME=b.OBJECT_NAME
WHERE a.counter_name='Cache Hit Ratio' AND a.OBJECT_NAME='{$MSSQL.INSTANCE}:Plan Cache' AND instance_name='_Total'