5037 lines
213 KiB
YAML
5037 lines
213 KiB
YAML
zabbix_export:
|
|
version: '7.0'
|
|
template_groups:
|
|
- uuid: 748ad4d098d447d492bb935c907f652f
|
|
name: Templates/Databases
|
|
templates:
|
|
- uuid: 001a1677f6a949b6bddfdb2926023300
|
|
template: 'MSSQL by ODBC'
|
|
name: 'MSSQL by ODBC'
|
|
description: |
|
|
This template is designed for the effortless deployment of MSSQL monitoring by Zabbix via ODBC and doesn't require any external scripts.
|
|
|
|
Setup:
|
|
|
|
1. Create an MSSQL user for monitoring.
|
|
|
|
View Server State and View Any Definition permissions should be granted to the user.
|
|
Grant this user read permissions to the sysjobschedules, sysjobhistory, and sysjobs tables.
|
|
|
|
For more information, see MSSQL documentation:
|
|
|
|
- Create a database user: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-user?view=sql-server-ver16
|
|
|
|
- GRANT Server Permissions: https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-server-permissions-transact-sql?view=sql-server-ver16
|
|
|
|
- Configure a User to Create and Manage SQL Server Agent Jobs: https://docs.microsoft.com/en-us/sql/ssms/agent/configure-a-user-to-create-and-manage-sql-server-agent-jobs?view=sql-server-ver16
|
|
|
|
2. Set the user name and password in the host macros ({$MSSQL.USER} and {$MSSQL.PASSWORD}).
|
|
|
|
3. Do not forget to install Microsoft ODBC driver on Zabbix server or Zabbix proxy and specify data source name in macro {$MSSQL.DSN}.
|
|
|
|
See Microsoft documentation for instructions: https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16.
|
|
|
|
Note! Credentials in the odbc.ini do not work for MSSQL.
|
|
|
|
The "Service's TCP port state" item uses the {$MSSQL.HOST} and {$MSSQL.PORT} macros to check the availability of the MSSQL instance, change these if necessary. Keep in mind that if dynamic ports are used on the MSSQL server side, this check will not work correctly.
|
|
|
|
If your instance uses a non-default TCP port, set the port in your section of odbc.ini in the line Server = IP or FQDN name, port.
|
|
|
|
Note: You can use the context macros {$MSSQL.BACKUP_FULL.USED}, {$MSSQL.BACKUP_LOG.USED}, and {$MSSQL.BACKUP_DIFF.USED} to disable backup age triggers for a certain database. If set to a value other than "1", the trigger expression for the backup age will not fire.
|
|
|
|
Generated by official Zabbix template tool "Templator"
|
|
vendor:
|
|
name: Zabbix
|
|
version: 7.0-7
|
|
groups:
|
|
- name: Templates/Databases
|
|
items:
|
|
- uuid: f495898e37c94bfda4ce387f7129b996
|
|
name: 'Get availability groups'
|
|
type: ODBC
|
|
key: 'db.odbc.get[get_availability_group,"{$MSSQL.DSN}"]'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
params: |
|
|
SELECT
|
|
ag.name as group_name,
|
|
ISNULL(ags.primary_recovery_health, 2) as primary_recovery_health,
|
|
ISNULL(ags.primary_replica, 'Unknown') as primary_replica,
|
|
ISNULL(
|
|
ags.secondary_recovery_health, 2
|
|
) as secondary_recovery_health,
|
|
ags.synchronization_health as synchronization_health
|
|
FROM
|
|
sys.dm_hadr_availability_group_states ags
|
|
JOIN sys.availability_groups ag ON ag.group_id = ags.group_id
|
|
username: '{$MSSQL.USER}'
|
|
password: '{$MSSQL.PASSWORD}'
|
|
description: 'The item gets availability group states - name, primary and secondary health, synchronization health.'
|
|
timeout: 30s
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: 9c902b33ed254e1892e44218a67876d3
|
|
name: 'Get database'
|
|
type: ODBC
|
|
key: 'db.odbc.get[get_database,"{$MSSQL.DSN}"]'
|
|
delay: 1h
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
params: |
|
|
SELECT name as dbname, recovery_model
|
|
FROM sys.databases
|
|
username: '{$MSSQL.USER}'
|
|
password: '{$MSSQL.PASSWORD}'
|
|
description: 'Getting databases - database name and recovery model.'
|
|
timeout: 30s
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: 9948ac27a2f84ba5a74ee0860d934f4e
|
|
name: 'Get DB mirroring'
|
|
type: ODBC
|
|
key: 'db.odbc.get[get_db_mirroring,"{$MSSQL.DSN}"]'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
params: |
|
|
SELECT
|
|
ISNULL(m.mirroring_role, 0) as mirroring_role,
|
|
ISNULL(m.mirroring_role_sequence, 0) as mirroring_role_sequence,
|
|
ISNULL(m.mirroring_state, 7) as mirroring_state,
|
|
ISNULL(m.mirroring_witness_state, 3) as mirroring_witness_state,
|
|
ISNULL(m.mirroring_safety_level, 3) as mirroring_safety_level,
|
|
db_name(m.database_id) as dbname
|
|
FROM
|
|
sys.database_mirroring as m
|
|
WHERE
|
|
m.mirroring_state_desc IS NOT NULL
|
|
username: '{$MSSQL.USER}'
|
|
password: '{$MSSQL.PASSWORD}'
|
|
description: 'Getting DB mirroring.'
|
|
timeout: 30s
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: cae1f9b034b04357b038212b04cac794
|
|
name: 'Get job status'
|
|
type: ODBC
|
|
key: 'db.odbc.get[get_job_status,"{$MSSQL.DSN}"]'
|
|
delay: 10m
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
params: |
|
|
SELECT sj.name AS job_name,
|
|
sj.enabled AS enabled,
|
|
sjs.last_run_outcome AS run_status,
|
|
sjs.last_outcome_message AS last_run_status_message,
|
|
sjs.last_run_duration/10000*3600 + sjs.last_run_duration/100%100*60 + sjs.last_run_duration%100 AS run_duration,
|
|
CASE sjs.last_run_date
|
|
WHEN 0 THEN NULL
|
|
ELSE CONVERT(
|
|
VARCHAR(25),
|
|
TODATETIMEOFFSET(msdb.dbo.agent_datetime(sjs.last_run_date,sjs.last_run_time), DATEPART(TZoffset, SYSDATETIMEOFFSET())),
|
|
126)
|
|
END AS last_run_date_time,
|
|
sja.next_scheduled_run_date AS next_run_date_time
|
|
FROM msdb..sysjobs AS sj
|
|
LEFT JOIN msdb..sysjobservers AS sjs
|
|
ON sj.job_id = sjs.job_id
|
|
LEFT JOIN (
|
|
SELECT job.job_id,
|
|
max(act.session_id) AS s_id,
|
|
CONVERT(
|
|
VARCHAR(25),
|
|
TODATETIMEOFFSET(max(act.next_scheduled_run_date), DATEPART(TZoffset, SYSDATETIMEOFFSET())),
|
|
126) AS next_scheduled_run_date
|
|
FROM msdb..sysjobs AS job
|
|
LEFT JOIN msdb..sysjobactivity AS act
|
|
ON act.job_id = job.job_id
|
|
GROUP BY job.job_id ) AS sja
|
|
ON sja.job_id = sj.job_id
|
|
WHERE Enabled = 1
|
|
username: '{$MSSQL.USER}'
|
|
password: '{$MSSQL.PASSWORD}'
|
|
description: 'The item gets the SQL agent job status.'
|
|
timeout: 30s
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: 9b106ca122fb4b75a761e80f4239f906
|
|
name: 'Get last backup'
|
|
type: ODBC
|
|
key: 'db.odbc.get[get_last_backup,"{$MSSQL.DSN}"]'
|
|
delay: 10m
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
params: |
|
|
SELECT bs.database_name as dbname,
|
|
[type],
|
|
DATEDIFF(SECOND, bs.backup_finish_date, getdate()) as time_since_last_backup,
|
|
(DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)) as duration,
|
|
db.recovery_model as db_recovery_model
|
|
FROM msdb.dbo.backupset as bs
|
|
LEFT JOIN sys.databases as db ON bs.database_name = db.name
|
|
WHERE bs.database_name not in (
|
|
SELECT AGDatabases.database_name AS Databasename
|
|
FROM sys.dm_hadr_availability_group_states States
|
|
INNER JOIN master.sys.availability_groups Groups
|
|
ON States.group_id = Groups.group_id
|
|
INNER JOIN sys.availability_databases_cluster AGDatabases
|
|
ON Groups.group_id = AGDatabases.group_id
|
|
WHERE primary_replica != @@Servername OR primary_replica is NULL
|
|
) and db.name is not NULL
|
|
GROUP BY bs.database_name,
|
|
backup_finish_date,
|
|
[type],
|
|
backup_start_date,
|
|
db.recovery_model
|
|
HAVING backup_finish_date = (
|
|
SELECT MAX(backup_finish_date)
|
|
FROM msdb.dbo.backupset
|
|
WHERE database_name = bs.database_name
|
|
AND bs.type = [type]
|
|
)
|
|
ORDER BY bs.database_name
|
|
username: '{$MSSQL.USER}'
|
|
password: '{$MSSQL.PASSWORD}'
|
|
description: 'The item gets information about backup processes.'
|
|
timeout: 30s
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: 3960ea4dc0054ee099d5bca005535e6b
|
|
name: 'Get local DB'
|
|
type: ODBC
|
|
key: 'db.odbc.get[get_local_db,"{$MSSQL.DSN}"]'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
params: |
|
|
SELECT ag.name AS group_name,
|
|
drs.database_state as database_state,
|
|
drs.is_suspended as is_suspended,
|
|
drs.synchronization_health as synchronization_health,
|
|
arcs.replica_server_name AS replica_name,
|
|
db_name(drs.database_id) AS dbname,
|
|
drs.is_local
|
|
FROM sys.dm_hadr_database_replica_states drs
|
|
JOIN sys.dm_hadr_availability_replica_cluster_states arcs
|
|
ON arcs.replica_id = drs.replica_id
|
|
JOIN sys.availability_groups ag
|
|
ON ag.group_id = arcs.group_id
|
|
JOIN sys.dm_hadr_availability_replica_states ars
|
|
ON ars.replica_id = arcs.replica_id
|
|
WHERE drs.is_local = 1
|
|
username: '{$MSSQL.USER}'
|
|
password: '{$MSSQL.PASSWORD}'
|
|
description: 'Getting the states of the local availability database.'
|
|
timeout: 30s
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: e4480bba876d472294920020734eadf5
|
|
name: 'Get non-local DB'
|
|
type: ODBC
|
|
key: 'db.odbc.get[get_non_local_db,"{$MSSQL.DSN}"]'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
params: |
|
|
SELECT ag.name AS group_name,
|
|
ISNULL(drs.log_send_queue_size, 0) as log_send_queue_size,
|
|
ISNULL(drs.redo_queue_size, 0) as redo_queue_size,
|
|
arcs.replica_server_name AS replica_name,
|
|
db_name(drs.database_id) AS dbname,
|
|
drs.is_local
|
|
FROM sys.dm_hadr_database_replica_states drs
|
|
JOIN sys.dm_hadr_availability_replica_cluster_states arcs
|
|
ON arcs.replica_id = drs.replica_id
|
|
JOIN sys.availability_groups ag
|
|
ON ag.group_id = arcs.group_id
|
|
JOIN sys.dm_hadr_availability_replica_states ars
|
|
ON ars.replica_id = arcs.replica_id
|
|
WHERE drs.is_local = 0
|
|
username: '{$MSSQL.USER}'
|
|
password: '{$MSSQL.PASSWORD}'
|
|
description: 'Getting the non-local availability database.'
|
|
timeout: 30s
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: 3fcb3b84bdee42428bedc39fcc582c9e
|
|
name: 'Get quorum'
|
|
type: ODBC
|
|
key: 'db.odbc.get[get_quorum,"{$MSSQL.DSN}"]'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
params: |
|
|
SELECT quorum_type, quorum_state, cluster_name
|
|
FROM sys.dm_hadr_cluster
|
|
username: '{$MSSQL.USER}'
|
|
password: '{$MSSQL.PASSWORD}'
|
|
description: 'Getting quorum - cluster name, type, and state.'
|
|
timeout: 10s
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: 69a31cdfbf9b4099bf89adb26e8b9f06
|
|
name: 'Get quorum member'
|
|
type: ODBC
|
|
key: 'db.odbc.get[get_quorum_member,"{$MSSQL.DSN}"]'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
params: |
|
|
SELECT a.member_name, a.member_type, a.member_state, a.number_of_quorum_votes, b.cluster_name
|
|
FROM sys.dm_hadr_cluster_members a
|
|
CROSS JOIN sys.dm_hadr_cluster b
|
|
username: '{$MSSQL.USER}'
|
|
password: '{$MSSQL.PASSWORD}'
|
|
description: 'Getting quorum members - member name, type, state, and number of quorum votes.'
|
|
timeout: 10s
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: 7a82483c605d4e8eb67c2576069cf8a9
|
|
name: 'Get replica'
|
|
type: ODBC
|
|
key: 'db.odbc.get[get_replica,"{$MSSQL.DSN}"]'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
params: |
|
|
SELECT
|
|
ars.connected_state as connected_state,
|
|
ars.is_local as is_local,
|
|
arcs.join_state as join_state,
|
|
ISNULL(ars.operational_state, 6) as operational_state,
|
|
ISNULL(ars.recovery_health, 2) as recovery_health,
|
|
ars.role as role,
|
|
ars.synchronization_health as synchronization_health,
|
|
ag.name as group_name,
|
|
arcs.replica_server_name as replica_name
|
|
FROM
|
|
sys.dm_hadr_availability_replica_cluster_states as arcs
|
|
JOIN sys.availability_groups ag ON ag.group_id = arcs.group_id
|
|
JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = arcs.replica_id
|
|
username: '{$MSSQL.USER}'
|
|
password: '{$MSSQL.PASSWORD}'
|
|
description: 'Getting the database replica.'
|
|
timeout: 30s
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: ff07b83d161c4e0fbddf8c2e2c282258
|
|
name: 'Get performance counters'
|
|
type: ODBC
|
|
key: 'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
|
|
delay: 0;m0-59
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
params: |
|
|
DECLARE @SQLNAME NVARCHAR(22)
|
|
SET @SQLNAME = CASE WHEN @@SERVICENAME = 'MSSQLSERVER' THEN 'SQLServer' ELSE 'MSSQL$' + @@SERVICENAME END
|
|
SELECT object_name,
|
|
counter_name,
|
|
instance_name,
|
|
cntr_value
|
|
FROM sys.dm_os_performance_counters
|
|
UNION SELECT @SQLNAME AS object_name,
|
|
'Version' AS counter_name,
|
|
@@version AS instance_name,
|
|
0 AS cntr_value
|
|
UNION SELECT @SQLNAME 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 @SQLNAME + ':Databases' 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 * ISNULL((100.0 / NULLIF(b.cntr_value,0)),0) AS dec(3, 0)) AS cntr_value
|
|
FROM sys.dm_os_performance_counters a
|
|
JOIN (
|
|
SELECT cntr_value,
|
|
OBJECT_NAME
|
|
FROM sys.dm_os_performance_counters
|
|
WHERE counter_name = 'Buffer cache hit ratio base'
|
|
AND OBJECT_NAME = @SQLNAME + ':Buffer Manager'
|
|
) b
|
|
ON a.OBJECT_NAME = b.OBJECT_NAME
|
|
WHERE a.counter_name = 'Buffer cache hit ratio'
|
|
AND a.OBJECT_NAME = @SQLNAME + ':Buffer Manager'
|
|
UNION SELECT a.object_name,
|
|
'WorktablesFromCacheRatio' AS counter_name,
|
|
'' AS instance_name,
|
|
cast(a.cntr_value * ISNULL((100.0 / NULLIF(b.cntr_value,0)),0) AS dec(3, 0)) AS cntr_value
|
|
FROM sys.dm_os_performance_counters a
|
|
JOIN (
|
|
SELECT cntr_value,
|
|
OBJECT_NAME
|
|
FROM sys.dm_os_performance_counters
|
|
WHERE counter_name = 'Worktables From Cache Base'
|
|
AND OBJECT_NAME = @SQLNAME + ':Access Methods'
|
|
) b
|
|
ON a.OBJECT_NAME = b.OBJECT_NAME
|
|
WHERE a.counter_name = 'Worktables From Cache Ratio'
|
|
AND a.OBJECT_NAME = @SQLNAME + ':Access Methods'
|
|
UNION SELECT a.object_name,
|
|
'CacheHitRatio' AS counter_name,
|
|
'_Total' AS instance_name,
|
|
cast(a.cntr_value * ISNULL((100.0 / NULLIF(b.cntr_value,0)),0) AS dec(3, 0)) AS cntr_value
|
|
FROM sys.dm_os_performance_counters a
|
|
JOIN (
|
|
SELECT cntr_value,
|
|
OBJECT_NAME
|
|
FROM sys.dm_os_performance_counters
|
|
WHERE counter_name = 'Cache Hit Ratio Base'
|
|
AND OBJECT_NAME = @SQLNAME + ':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 = @SQLNAME + ':Plan Cache'
|
|
AND instance_name = '_Total'
|
|
username: '{$MSSQL.USER}'
|
|
password: '{$MSSQL.PASSWORD}'
|
|
description: 'The item gets server global status information.'
|
|
timeout: 30s
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: c0400032a74549078448a928f17d3128
|
|
name: 'Get Access Methods counters'
|
|
type: DEPENDENT
|
|
key: mssql.access_methods.raw
|
|
delay: '0'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
description: 'The item gets server information about access methods.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.object_name=~''.*Access Methods'')]'
|
|
error_handler: DISCARD_VALUE
|
|
master_item:
|
|
key: 'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: 9a83826b50ac4928b9a716cf94ed6b61
|
|
name: 'Auto-param attempts per second'
|
|
type: DEPENDENT
|
|
key: mssql.autoparam_attempts_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of auto-parameterization attempts per second. The total should be the sum of the failed, safe, and unsafe auto-parameterizations. Auto-parameterization occurs when an instance of SQL Server tries to parameterize a Transact-SQL request by replacing some literals with parameters so that reuse of the resulting cached execution plan across multiple similar-looking requests is possible. Note that auto-parameterizations are also known as simple parameterizations in the newer versions of SQL Server. This counter does not include forced parameterizations.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Auto-Param Attempts/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.sql_statistics.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
- uuid: 1d49af2907d541719e4482439de3ef40
|
|
name: 'Average latch wait time'
|
|
type: CALCULATED
|
|
key: mssql.average_latch_wait_time
|
|
delay: 0;m0-59s3
|
|
value_type: FLOAT
|
|
units: ms
|
|
params: |
|
|
(last(//mssql.average_latch_wait_time_raw) - last(//mssql.average_latch_wait_time_raw,#2)) /
|
|
(last(//mssql.average_latch_wait_time_base) - last(//mssql.average_latch_wait_time_base,#2) +
|
|
(last(//mssql.average_latch_wait_time_base) - last(//mssql.average_latch_wait_time_base,#2)=0))
|
|
description: 'Average latch wait time (in milliseconds) for latch requests that had to wait.'
|
|
tags:
|
|
- tag: component
|
|
value: wait-time
|
|
- uuid: f9d78cd9c941471287a24ca94005e9ac
|
|
name: 'Average latch wait time base'
|
|
type: DEPENDENT
|
|
key: mssql.average_latch_wait_time_base
|
|
delay: '0'
|
|
description: 'For internal use only.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Average Latch Wait Time Base'')].cntr_value.first()'
|
|
master_item:
|
|
key: mssql.latches_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: wait-time
|
|
- uuid: 7248e70669674ef5a7a85deb20b1fe8e
|
|
name: 'Average latch wait time raw'
|
|
type: DEPENDENT
|
|
key: mssql.average_latch_wait_time_raw
|
|
delay: '0'
|
|
units: ms
|
|
description: 'Average latch wait time (in milliseconds) for latch requests that had to wait.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Average Latch Wait Time (ms)'')].cntr_value.first()'
|
|
master_item:
|
|
key: mssql.latches_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: wait-time
|
|
- uuid: 2915866b5891480aafd73463aca88a21
|
|
name: 'Total average wait time'
|
|
type: CALCULATED
|
|
key: mssql.average_wait_time
|
|
delay: 0;m0-59s3
|
|
value_type: FLOAT
|
|
units: ms
|
|
params: |
|
|
(last(//mssql.average_wait_time_raw) - last(//mssql.average_wait_time_raw,#2)) /
|
|
(last(//mssql.average_wait_time_base) - last(//mssql.average_wait_time_base,#2) +
|
|
(last(//mssql.average_wait_time_base) - last(//mssql.average_wait_time_base,#2)=0))
|
|
description: 'The average wait time, in milliseconds, for each lock request that had to wait.'
|
|
tags:
|
|
- tag: component
|
|
value: wait-time
|
|
triggers:
|
|
- uuid: fad2c1e9d0ba45bcbb47ccbb327f0b98
|
|
expression: 'min(/MSSQL by ODBC/mssql.average_wait_time,5m)>{$MSSQL.AVERAGE_WAIT_TIME.MAX}'
|
|
name: 'MSSQL: Total average wait time for locks is high'
|
|
event_name: 'MSSQL: Total average wait time for locks is high (over {$MSSQL.AVERAGE_WAIT_TIME.MAX}ms for 5m)'
|
|
priority: WARNING
|
|
description: 'An average wait time longer than 500 ms may indicate excessive blocking. This value should generally correlate to "Lock Waits/sec" and move up or down with it accordingly.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: 2a63096aa40a4374bdd7426b9fe0767e
|
|
name: 'Total average wait time base'
|
|
type: DEPENDENT
|
|
key: mssql.average_wait_time_base
|
|
delay: '0'
|
|
description: 'For internal use only.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Average Wait Time Base'')].cntr_value.first()'
|
|
master_item:
|
|
key: mssql.locks_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: wait-time
|
|
- uuid: f0cd7858dabf4b7da93314dc9a8ddeaa
|
|
name: 'Total average wait time raw'
|
|
type: DEPENDENT
|
|
key: mssql.average_wait_time_raw
|
|
delay: '0'
|
|
units: ms
|
|
description: 'Average amount of wait time (in milliseconds) for each lock request that resulted in a wait. Information for all locks.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Average Wait Time (ms)'')].cntr_value.first()'
|
|
master_item:
|
|
key: mssql.locks_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: wait-time
|
|
- uuid: 00b6e46811fd43a396e2a38dd976d220
|
|
name: 'Batch requests per second'
|
|
type: DEPENDENT
|
|
key: mssql.batch_requests_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: rps
|
|
description: 'Number of Transact-SQL command batches received per second. This statistic is affected by all constraints (such as I/O, number of users, cache size, complexity of requests, and so on). High batch requests mean good throughput.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Batch Requests/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.sql_statistics.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
- uuid: ed950d204a1f40a6925856828c7807aa
|
|
name: 'Buffer cache hit ratio'
|
|
type: DEPENDENT
|
|
key: mssql.buffer_cache_hit_ratio
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: '%'
|
|
description: 'Indicates the percentage of pages found in the buffer cache without having to read from the disk. The ratio is the total number of cache hits divided by the total number of cache lookups over the last few thousand page accesses. After a long period of time, the ratio changes very little. Since reading from the cache is much less expensive than reading from the disk, a higher value is preferred for this item. To increase the buffer cache hit ratio, consider increasing the amount of memory available to MSSQL Server or using the buffer pool extension feature.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''BufferCacheHitRatio'')].cntr_value.first()'
|
|
master_item:
|
|
key: mssql.buffer_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: cache
|
|
triggers:
|
|
- uuid: f84fa297023a4aee903e4d32bbad9360
|
|
expression: 'max(/MSSQL by ODBC/mssql.buffer_cache_hit_ratio,5m)<{$MSSQL.BUFFER_CACHE_RATIO.MIN.CRIT}'
|
|
name: 'MSSQL: Percentage of the buffer cache efficiency is low'
|
|
event_name: 'MSSQL: Percentage of the buffer cache efficiency is low (below {$MSSQL.BUFFER_CACHE_RATIO.MIN.CRIT}% for 5m)'
|
|
priority: HIGH
|
|
description: 'Too low buffer cache hit ratio.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: 42e5bf05da06425282134d3cf806d023
|
|
expression: 'max(/MSSQL by ODBC/mssql.buffer_cache_hit_ratio,5m)<{$MSSQL.BUFFER_CACHE_RATIO.MIN.WARN}'
|
|
name: 'MSSQL: Percentage of the buffer cache efficiency is low'
|
|
event_name: 'MSSQL: Percentage of the buffer cache efficiency is low (below {$MSSQL.BUFFER_CACHE_RATIO.MIN.WARN}% for 5m)'
|
|
priority: WARNING
|
|
description: 'Low buffer cache hit ratio.'
|
|
dependencies:
|
|
- name: 'MSSQL: Percentage of the buffer cache efficiency is low'
|
|
expression: 'max(/MSSQL by ODBC/mssql.buffer_cache_hit_ratio,5m)<{$MSSQL.BUFFER_CACHE_RATIO.MIN.CRIT}'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: eadb0b4b4eed412c9a29137a2f4232fd
|
|
name: 'Get Buffer Manager counters'
|
|
type: DEPENDENT
|
|
key: mssql.buffer_manager.raw
|
|
delay: '0'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
description: 'The item gets server information about the buffer pool.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.object_name=~''.*Buffer Manager'')]'
|
|
error_handler: DISCARD_VALUE
|
|
master_item:
|
|
key: 'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: 1900fc83a4d74c599f5f3438450b5d8b
|
|
name: 'Cache hit ratio'
|
|
type: DEPENDENT
|
|
key: mssql.cache_hit_ratio
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: '%'
|
|
description: 'Ratio between cache hits and lookups.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''CacheHitRatio'')].cntr_value.first()'
|
|
master_item:
|
|
key: mssql.cache_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: cache
|
|
- uuid: b9bab8e37b9c46acbf9c64073bfcf0e6
|
|
name: 'Get Cache counters'
|
|
type: DEPENDENT
|
|
key: mssql.cache_info.raw
|
|
delay: '0'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
description: 'The item gets server information about cache.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.object_name=~''.*Plan Cache'' && @.instance_name==''_Total'')]'
|
|
error_handler: DISCARD_VALUE
|
|
master_item:
|
|
key: 'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: 064e1279ae0a4260ab02c08cd3c3f77d
|
|
name: 'Cache objects in use'
|
|
type: DEPENDENT
|
|
key: mssql.cache_objects_in_use
|
|
delay: '0'
|
|
description: 'Number of cache objects in use.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Cache Objects in use'')].cntr_value.first()'
|
|
master_item:
|
|
key: mssql.cache_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: cache
|
|
- uuid: de8c0d7f9c0b42438560dc28e47fdd19
|
|
name: 'Cache object counts'
|
|
type: DEPENDENT
|
|
key: mssql.cache_object_counts
|
|
delay: '0'
|
|
description: 'Number of cache objects in the cache.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Cache Object Counts'')].cntr_value.first()'
|
|
master_item:
|
|
key: mssql.cache_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: cache
|
|
- uuid: ae2d22cd04ed452dbdb04ae3f71b8586
|
|
name: 'Cache pages'
|
|
type: DEPENDENT
|
|
key: mssql.cache_pages
|
|
delay: '0'
|
|
description: 'Number of 8-kilobyte (KB) pages used by cache objects.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Cache Pages'')].cntr_value.first()'
|
|
master_item:
|
|
key: mssql.cache_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: cache
|
|
- tag: component
|
|
value: page
|
|
- uuid: d72b6aadab0c4d6d83bc72da0ce661c3
|
|
name: 'Checkpoint pages per second'
|
|
type: DEPENDENT
|
|
key: mssql.checkpoint_pages_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Indicates the number of pages flushed to the disk per second by a checkpoint or other operation which required all dirty pages to be flushed.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Checkpoint pages/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.buffer_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: page
|
|
- tag: component
|
|
value: performance
|
|
- uuid: 9c1ca822375644669821fe1bea4c5f2a
|
|
name: 'Database pages'
|
|
type: DEPENDENT
|
|
key: mssql.database_pages
|
|
delay: '0'
|
|
description: 'Indicates the number of pages in the buffer pool with database content.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Database pages'')].cntr_value.first()'
|
|
master_item:
|
|
key: mssql.buffer_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: page
|
|
- uuid: 871e3520aac84c18bcaf2f3a0d565b79
|
|
name: 'Total data file size'
|
|
type: DEPENDENT
|
|
key: mssql.data_files_size
|
|
delay: '0'
|
|
units: B
|
|
description: 'Total size of all data files.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Data File(s) Size (KB)'')].cntr_value.first()'
|
|
- type: MULTIPLIER
|
|
parameters:
|
|
- '1024'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: mssql.db_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: application
|
|
- uuid: 9f7bc0b3ae25483cb7290ef6feaa5c84
|
|
name: 'Get DB counters'
|
|
type: DEPENDENT
|
|
key: mssql.db_info.raw
|
|
delay: '0'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
description: 'The item gets summary information about databases.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.object_name=~''.*Databases'' && @.instance_name==''_Total'')]'
|
|
error_handler: DISCARD_VALUE
|
|
master_item:
|
|
key: 'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: 4cdecc74a6e44b57b17f44988d3ffa21
|
|
name: 'Total errors per second'
|
|
type: DEPENDENT
|
|
key: mssql.errors_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of errors per second.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Errors/sec'' && @.instance_name==''_Total'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.sql_errors.raw
|
|
tags:
|
|
- tag: component
|
|
value: error
|
|
- tag: component
|
|
value: performance
|
|
- uuid: 5ed7f6931c42424f8ec3dbe2e6c46f03
|
|
name: 'Failed auto-params per second'
|
|
type: DEPENDENT
|
|
key: mssql.failed_autoparams_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of failed auto-parameterization attempts per second. This number should be small. Note that auto-parameterizations are also known as simple parameterizations in the newer versions of SQL Server.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Failed Auto-Params/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.sql_statistics.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
- uuid: cee8f4d47d41463299c50b5c7925a650
|
|
name: 'Forwarded records per second'
|
|
type: DEPENDENT
|
|
key: mssql.forwarded_records_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of records per second fetched through forwarded record pointers.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Forwarded Records/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.access_methods.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
- uuid: 22d1a41352c4477ba3db5d1a9e038597
|
|
name: 'Free list stalls per second'
|
|
type: DEPENDENT
|
|
key: mssql.free_list_stalls_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: rps
|
|
description: 'Indicates the number of requests per second that had to wait for a free page.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Free list stalls/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.buffer_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
triggers:
|
|
- uuid: f055a294ef3b43dd8958e77c303a5fee
|
|
expression: 'min(/MSSQL by ODBC/mssql.free_list_stalls_sec.rate,5m)>{$MSSQL.FREE_LIST_STALLS.MAX}'
|
|
name: 'MSSQL: Number of rps waiting for a free page is high'
|
|
event_name: 'MSSQL: Number of rps waiting for a free page is high (over {$MSSQL.FREE_LIST_STALLS.MAX} for 5m)'
|
|
priority: WARNING
|
|
description: 'Some requests have to wait for a free page.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: ace67686bb93456c80b40be1f33c173f
|
|
name: 'Full scans per second'
|
|
type: DEPENDENT
|
|
key: mssql.full_scans_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of unrestricted full scans per second. These can be either base-table or full-index scans. Values greater than 1 or 2 indicate that there are table / index page scans. If that is combined with high CPU, this counter requires further investigation, otherwise, if the full scans are on small tables, it can be ignored.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Full Scans/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.access_methods.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
- uuid: 6c17a38a9e9246ecbad3a6b2cb83c6cc
|
|
name: 'Get General Statistics counters'
|
|
type: DEPENDENT
|
|
key: mssql.general_statistics.raw
|
|
delay: '0'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
description: 'The item gets general statistics information.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.object_name=~''.*General Statistics'')]'
|
|
error_handler: DISCARD_VALUE
|
|
master_item:
|
|
key: 'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: f2539791f65a40569bcf6b1c89647904
|
|
name: 'Granted Workspace Memory'
|
|
type: DEPENDENT
|
|
key: mssql.granted_workspace_memory
|
|
delay: '0'
|
|
units: B
|
|
description: 'Specifies the total amount of memory currently granted to executing processes, such as hash, sort, bulk copy, and index creation operations.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Granted Workspace Memory (KB)'')].cntr_value.first()'
|
|
- type: MULTIPLIER
|
|
parameters:
|
|
- '1024'
|
|
master_item:
|
|
key: mssql.mem_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: memory
|
|
- uuid: 585f1a22108143d8b39337900da8860c
|
|
name: 'Index searches per second'
|
|
type: DEPENDENT
|
|
key: mssql.index_searches_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of index searches per second. These are used to start a range scan, reposition a range scan, revalidate a scan point, fetch a single index record, and search down the index to locate where to insert a new row.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Index Searches/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.access_methods.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
- uuid: eafdabca35bd40a58432dd36a210a4e4
|
|
name: 'Errors per second (Info errors)'
|
|
type: DEPENDENT
|
|
key: mssql.info_errors_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of errors per second.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Errors/sec'' && @.instance_name==''Info Errors'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.sql_errors.raw
|
|
tags:
|
|
- tag: component
|
|
value: error
|
|
- tag: component
|
|
value: performance
|
|
- uuid: b68d1f8108d7494b9d4d891911708c72
|
|
name: 'Errors per second (Kill connection errors)'
|
|
type: DEPENDENT
|
|
key: mssql.kill_connection_errors_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of errors per second.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Errors/sec'' && @.instance_name==''Kill Connection Errors'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.sql_errors.raw
|
|
tags:
|
|
- tag: component
|
|
value: error
|
|
- tag: component
|
|
value: performance
|
|
- uuid: 04714203d50645fdb4c69e333facd60e
|
|
name: 'Get Latches counters'
|
|
type: DEPENDENT
|
|
key: mssql.latches_info.raw
|
|
delay: '0'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
description: 'The item gets server information about latches.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.object_name=~''.*Latches'')]'
|
|
error_handler: DISCARD_VALUE
|
|
master_item:
|
|
key: 'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: c1242c5530654057bca59cb9ad12c319
|
|
name: 'Latch waits per second'
|
|
type: DEPENDENT
|
|
key: mssql.latch_waits_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: rps
|
|
description: 'The number of latch requests that could not be granted immediately. Latches are lightweight means of holding a very transient server resource, such as an address in memory.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Latch Waits/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.latches_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
- tag: component
|
|
value: wait-time
|
|
- uuid: 66cc84326bd146ac89f91ddeaa61f63a
|
|
name: 'Lazy writes per second'
|
|
type: DEPENDENT
|
|
key: mssql.lazy_writes_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Indicates the number of buffers written per second by the buffer manager''s lazy writer. The lazy writer is a system process that flushes out batches of dirty, aged buffers (buffers that contain changes that must be written back to the disk before the buffer can be reused for a different page) and makes them available to user processes. The lazy writer eliminates the need to perform frequent checkpoints in order to create available buffers.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Lazy writes/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.buffer_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
triggers:
|
|
- uuid: 8aa547cda82f4dfcaef5ca0467590f64
|
|
expression: 'min(/MSSQL by ODBC/mssql.lazy_writes_sec.rate,5m)>{$MSSQL.LAZY_WRITES.MAX}'
|
|
name: 'MSSQL: Number of buffers written per second by the lazy writer is high'
|
|
event_name: 'MSSQL: Number of buffers written per second by the lazy writer is high (over {$MSSQL.LAZY_WRITES.MAX} for 5m)'
|
|
priority: WARNING
|
|
description: 'The number of buffers written per second by the buffer manager''s lazy writer exceeds the threshold.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: 1b39ade916d243299f6350faaee167c8
|
|
name: 'Get Locks counters'
|
|
type: DEPENDENT
|
|
key: mssql.locks_info.raw
|
|
delay: '0'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
description: 'The item gets server information about locks.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.object_name=~''.*Locks'' && @.instance_name==''_Total'')]'
|
|
error_handler: DISCARD_VALUE
|
|
master_item:
|
|
key: 'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: 1b4c71c016504eb08b1a49146095c231
|
|
name: 'Total lock requests per second'
|
|
type: DEPENDENT
|
|
key: mssql.lock_requests_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: rps
|
|
description: 'Number of new locks and lock conversions per second requested from the lock manager.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Lock Requests/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.locks_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: lock
|
|
- tag: component
|
|
value: performance
|
|
triggers:
|
|
- uuid: be4e0b1aec8d494b984be248424d69cf
|
|
expression: 'min(/MSSQL by ODBC/mssql.lock_requests_sec.rate,5m)>{$MSSQL.LOCK_REQUESTS.MAX}'
|
|
name: 'MSSQL: Total number of locks per second is high'
|
|
event_name: 'MSSQL: Total number of locks per second is high (over {$MSSQL.LOCK_REQUESTS.MAX} for 5m)'
|
|
priority: WARNING
|
|
description: 'Number of new locks and lock conversions per second requested from the lock manager is high.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: 0ac1aab7013249c6a0beb89217bf52c6
|
|
name: 'Total lock requests per second that timed out'
|
|
type: DEPENDENT
|
|
key: mssql.lock_timeouts_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: rps
|
|
description: 'Number of timed out lock requests per second, including requests for NOWAIT locks.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Lock Timeouts/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.locks_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: lock
|
|
- tag: component
|
|
value: performance
|
|
triggers:
|
|
- uuid: 9b62d84b8d9c4209b894151f59c42b27
|
|
expression: 'min(/MSSQL by ODBC/mssql.lock_timeouts_sec.rate,5m)>{$MSSQL.LOCK_TIMEOUTS.MAX}'
|
|
name: 'MSSQL: Total lock requests per second that timed out is high'
|
|
event_name: 'MSSQL: Total lock requests per second that timed out is high (over {$MSSQL.LOCK_TIMEOUTS.MAX} for 5m)'
|
|
priority: WARNING
|
|
description: 'The total number of timed out lock requests per second, including requests for NOWAIT locks, is high.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: 77be28b4732d4665a4e880f2ea309245
|
|
name: 'Total lock requests per second that required waiting'
|
|
type: DEPENDENT
|
|
key: mssql.lock_waits_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: rps
|
|
description: 'Number of lock requests per second that required the caller to wait.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Lock Waits/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.locks_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: lock
|
|
- tag: component
|
|
value: performance
|
|
triggers:
|
|
- uuid: 879748a03c89495398b41fc1dbeba9ed
|
|
expression: 'min(/MSSQL by ODBC/mssql.lock_waits_sec.rate,5m)>0'
|
|
name: 'MSSQL: Some blocking is occurring for 5m'
|
|
priority: AVERAGE
|
|
description: 'Values greater than zero indicate at least some blocking is occurring, while a value of zero can quickly eliminate blocking as a potential root-cause problem.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: bf4ef99559f94147b9b64144f6b795c0
|
|
name: 'Lock wait time'
|
|
type: DEPENDENT
|
|
key: mssql.lock_wait_time
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: ms
|
|
description: 'Average of total wait time (in milliseconds) for locks in the last second.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Lock Wait Time (ms)'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.locks_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: lock
|
|
- uuid: c7856bdb5285421cb79cec0782fa1188
|
|
name: 'Logins per second'
|
|
type: DEPENDENT
|
|
key: mssql.logins_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Total number of logins started per second. This does not include pooled connections. Any value over 2 may indicate insufficient connection pooling.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Logins/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.general_statistics.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
- uuid: cf4af2cbeaff42b2a71c91544286998f
|
|
name: 'Logouts per second'
|
|
type: DEPENDENT
|
|
key: mssql.logouts_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Total number of logout operations started per second. Any value over 2 may indicate insufficient connection pooling.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Logouts/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.general_statistics.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
- uuid: e0620ce52e6d4269b9d12e82c749a146
|
|
name: 'Total log file size'
|
|
type: DEPENDENT
|
|
key: mssql.log_files_size
|
|
delay: '0'
|
|
units: B
|
|
description: 'Total size of all the transaction log files.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Log File(s) Size (KB)'')].cntr_value.first()'
|
|
- type: MULTIPLIER
|
|
parameters:
|
|
- '1024'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: mssql.db_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: log
|
|
- uuid: 636e7c85a79947a39f7b176e8e3c5b82
|
|
name: 'Total log file used size'
|
|
type: DEPENDENT
|
|
key: mssql.log_files_used_size
|
|
delay: '0'
|
|
units: B
|
|
description: 'The cumulative size of all the log files in the database.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Log File(s) Used Size (KB)'')].cntr_value.first()'
|
|
- type: MULTIPLIER
|
|
parameters:
|
|
- '1024'
|
|
master_item:
|
|
key: mssql.db_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: log
|
|
- uuid: 28c1045fb77249aea23f55f48dd89c69
|
|
name: 'Maximum workspace memory'
|
|
type: DEPENDENT
|
|
key: mssql.maximum_workspace_memory
|
|
delay: '0'
|
|
units: B
|
|
description: 'Indicates the maximum amount of memory available for executing processes, such as hash, sort, bulk copy, and index creation operations.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Maximum Workspace Memory (KB)'')].cntr_value.first()'
|
|
- type: MULTIPLIER
|
|
parameters:
|
|
- '1024'
|
|
master_item:
|
|
key: mssql.mem_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: memory
|
|
- uuid: d39c5f2b89de4bc88c7724a8e2b270d7
|
|
name: 'Memory grants outstanding'
|
|
type: DEPENDENT
|
|
key: mssql.memory_grants_outstanding
|
|
delay: '0'
|
|
description: 'Specifies the total number of processes that have successfully acquired a workspace memory grant.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Memory Grants Outstanding'')].cntr_value.first()'
|
|
master_item:
|
|
key: mssql.mem_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: memory
|
|
- uuid: cb468e25c20f43f08fcefd45354ff882
|
|
name: 'Memory grants pending'
|
|
type: DEPENDENT
|
|
key: mssql.memory_grants_pending
|
|
delay: '0'
|
|
description: 'Specifies the total number of processes waiting for a workspace memory grant.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Memory Grants Pending'')].cntr_value.first()'
|
|
master_item:
|
|
key: mssql.mem_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: memory
|
|
- uuid: 982b21186af84be2a34f76e5bccb7a27
|
|
name: 'Get Memory counters'
|
|
type: DEPENDENT
|
|
key: mssql.mem_manager.raw
|
|
delay: '0'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
description: 'The item gets memory information.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.object_name=~''.*Memory Manager'')]'
|
|
error_handler: DISCARD_VALUE
|
|
master_item:
|
|
key: 'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: 18c0d77ded2b4533a559643006a93406
|
|
name: 'Total lock requests per second that have deadlocks'
|
|
type: DEPENDENT
|
|
key: mssql.number_deadlocks_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: rps
|
|
description: 'Number of lock requests per second that resulted in a deadlock.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Number of Deadlocks/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.locks_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: deadlock
|
|
- tag: component
|
|
value: performance
|
|
triggers:
|
|
- uuid: a8f77c32a7194af9b2674f0931c25212
|
|
expression: 'min(/MSSQL by ODBC/mssql.number_deadlocks_sec.rate,5m)>{$MSSQL.DEADLOCKS.MAX}'
|
|
name: 'MSSQL: Number of deadlocks is high'
|
|
event_name: 'MSSQL: Number of deadlocks is high (over {$MSSQL.DEADLOCKS.MAX} for 5m)'
|
|
priority: AVERAGE
|
|
description: 'Too many deadlocks are occurring currently.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: bbb7fcac41b2403ab0b0c76f3437e81f
|
|
name: 'Errors per second (DB offline errors)'
|
|
type: DEPENDENT
|
|
key: mssql.offline_errors_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of errors per second.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Errors/sec'' && @.instance_name==''DB Offline Errors'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.sql_errors.raw
|
|
tags:
|
|
- tag: component
|
|
value: error
|
|
- tag: component
|
|
value: performance
|
|
- uuid: 94a869db684046d9b65db9ee022a9c9e
|
|
name: 'Page life expectancy'
|
|
type: DEPENDENT
|
|
key: mssql.page_life_expectancy
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: s
|
|
description: 'Indicates the number of seconds a page will stay in the buffer pool without references.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Page life expectancy'')].cntr_value.first()'
|
|
master_item:
|
|
key: mssql.buffer_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: page
|
|
triggers:
|
|
- uuid: 4eb155a86f8b4441a6707e8a3816a068
|
|
expression: 'max(/MSSQL by ODBC/mssql.page_life_expectancy,15m)<{$MSSQL.PAGE_LIFE_EXPECTANCY.MIN}'
|
|
name: 'MSSQL: Page life expectancy is low'
|
|
event_name: 'MSSQL: Page life expectancy is low (less {$MSSQL.PAGE_LIFE_EXPECTANCY.MIN}min for 15m)'
|
|
priority: HIGH
|
|
description: 'The page stays in the buffer pool without references for less time than the threshold value.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: 628f787578e04b83882aca052a7f976a
|
|
name: 'Page lookups per second'
|
|
type: DEPENDENT
|
|
key: mssql.page_lookups_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: rps
|
|
description: 'Indicates the number of requests per second to find a page in the buffer pool.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Page lookups/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.buffer_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
- uuid: 433257a5dfe4488c8073db03bfb05315
|
|
name: 'Page reads per second'
|
|
type: DEPENDENT
|
|
key: mssql.page_reads_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Indicates the number of physical database page reads that are issued per second. This statistic displays the total number of physical page reads across all databases. As physical I/O is expensive, you may be able to minimize the cost either by using a larger data cache, intelligent indexes, and more efficient queries, or by changing the database design.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Page reads/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.buffer_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: page
|
|
- tag: component
|
|
value: performance
|
|
triggers:
|
|
- uuid: c0b765b22b264d7c9086edc5cda35592
|
|
expression: 'min(/MSSQL by ODBC/mssql.page_reads_sec.rate,5m)>{$MSSQL.PAGE_READS.MAX}'
|
|
name: 'MSSQL: Number of physical database page reads per second is high'
|
|
event_name: 'MSSQL: Number of physical database page reads per second is high (over {$MSSQL.PAGE_READS.MAX} for 5m)'
|
|
priority: WARNING
|
|
description: 'The physical database page reads are issued too frequently.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: fc3dd6a1bf1242769e2b58f25421a19d
|
|
name: 'Page splits per second'
|
|
type: DEPENDENT
|
|
key: mssql.page_splits_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of page splits per second that occur as a result of overflowing index pages.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Page Splits/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.access_methods.raw
|
|
tags:
|
|
- tag: component
|
|
value: page
|
|
- tag: component
|
|
value: performance
|
|
- uuid: a8b3639fe7b44d88a75e724c083518b5
|
|
name: 'Page writes per second'
|
|
type: DEPENDENT
|
|
key: mssql.page_writes_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Indicates the number of physical database page writes that are issued per second.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Page writes/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.buffer_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: page
|
|
- tag: component
|
|
value: performance
|
|
triggers:
|
|
- uuid: 548941f4a7cc4cadba64d8efd46655da
|
|
expression: 'min(/MSSQL by ODBC/mssql.page_writes_sec.rate,5m)>{$MSSQL.PAGE_WRITES.MAX}'
|
|
name: 'MSSQL: Number of physical database page writes per second is high'
|
|
event_name: 'MSSQL: Number of physical database page writes per second is high (over {$MSSQL.PAGE_WRITES.MAX} for 5m)'
|
|
priority: WARNING
|
|
description: 'The physical database page writes are issued too frequently.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: 76918163a16e4ad786bed2040acc8061
|
|
name: 'Percent of ad hoc queries running'
|
|
type: CALCULATED
|
|
key: mssql.percent_of_adhoc_queries
|
|
delay: 0;m0-59s3
|
|
value_type: FLOAT
|
|
units: '%'
|
|
params: |
|
|
last(//mssql.sql_compilations_sec.rate) * 100 /
|
|
(last(//mssql.batch_requests_sec.rate) + (last(//mssql.batch_requests_sec.rate)=0))
|
|
description: 'The ratio of SQL compilations per second to batch requests per second, in percent.'
|
|
tags:
|
|
- tag: component
|
|
value: application
|
|
triggers:
|
|
- uuid: c5c1840220cd44fcaa7b47e0d52309bb
|
|
expression: 'min(/MSSQL by ODBC/mssql.percent_of_adhoc_queries,15m) > {$MSSQL.PERCENT_COMPILATIONS.MAX}'
|
|
name: 'MSSQL: Percent of ad hoc queries running is high'
|
|
event_name: 'MSSQL: Percent of ad hoc queries running is over {$MSSQL.PERCENT_COMPILATIONS.MAX}% for 15m'
|
|
priority: WARNING
|
|
description: 'The lower this value is, the better. High values often indicate excessive ad hoc querying and should be as low as possible. If excessive ad hoc querying is happening, try rewriting the queries as procedures or invoke the queries using `sp_executeSQL`. When rewriting isn''t possible, consider using a plan guide or setting the database to parameterization forced mode.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: ceab0472883f47b0a18b04fc6f7438ab
|
|
name: 'Percent of Recompiled Transact-SQL Objects'
|
|
type: CALCULATED
|
|
key: mssql.percent_recompilations_to_compilations
|
|
delay: 0;m0-59s3
|
|
value_type: FLOAT
|
|
units: '%'
|
|
params: |
|
|
last(//mssql.sql_recompilations_sec.rate) * 100 /
|
|
(last(//mssql.sql_compilations_sec.rate) + (last(//mssql.sql_compilations_sec.rate)=0))
|
|
description: 'The ratio of SQL re-compilations per second to SQL compilations per second, in percent.'
|
|
tags:
|
|
- tag: component
|
|
value: application
|
|
triggers:
|
|
- uuid: d5bad8ead8704e00bf332032e22ebfdd
|
|
expression: 'min(/MSSQL by ODBC/mssql.percent_recompilations_to_compilations,15m) > {$MSSQL.PERCENT_RECOMPILATIONS.MAX}'
|
|
name: 'MSSQL: Percent of times statement recompiles is high'
|
|
event_name: 'MSSQL: Percent of times statement recompiles is over {$MSSQL.PERCENT_RECOMPILATIONS.MAX}% for 15m'
|
|
priority: WARNING
|
|
description: 'This number should be at or near zero, since recompiles can cause deadlocks and exclusive compile locks. This counter''s value should follow in proportion to "Batch Requests/sec" and "SQL Compilations/sec".'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: 5c0bee24f9494bb49860171ff875caf7
|
|
name: 'Number of blocked processes'
|
|
type: DEPENDENT
|
|
key: mssql.processes_blocked
|
|
delay: '0'
|
|
description: 'Number of currently blocked processes.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Processes blocked'')].cntr_value.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: mssql.general_statistics.raw
|
|
tags:
|
|
- tag: component
|
|
value: application
|
|
- uuid: fc14b0c814234b12a3943eb0435cd28c
|
|
name: 'Read-ahead pages per second'
|
|
type: DEPENDENT
|
|
key: mssql.readahead_pages_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Indicates the number of pages read per second in anticipation of use.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Readahead pages/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.buffer_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: page
|
|
- tag: component
|
|
value: performance
|
|
- uuid: be47be0cbb924982aaf9573c5a233144
|
|
name: 'Safe auto-params per second'
|
|
type: DEPENDENT
|
|
key: mssql.safe_autoparams_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of safe auto-parameterization attempts per second. Safe refers to a determination that a cached execution plan can be shared between different similar-looking Transact-SQL statements. SQL Server makes many auto-parameterization attempts, some of which turn out to be safe and others fail. Note that auto-parameterizations are also known as simple parameterizations in the newer versions of SQL Server. This does not include forced parameterizations.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Safe Auto-Params/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.sql_statistics.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
- uuid: 9ce27df57ebc4ecca1a4741e66c9fba0
|
|
name: 'Full scans to Index searches ratio'
|
|
type: CALCULATED
|
|
key: mssql.scan_to_search
|
|
delay: 0;m0-59s3
|
|
value_type: FLOAT
|
|
params: 'last(//mssql.full_scans_sec.rate) / (last(//mssql.index_searches_sec.rate) + (last(//mssql.index_searches_sec.rate)=0))'
|
|
description: 'The ratio of full scans per second to index searches per second. The threshold recommendation is strictly for OLTP workloads.'
|
|
tags:
|
|
- tag: component
|
|
value: application
|
|
triggers:
|
|
- uuid: 3c22a74a94d641389b57a6013ddd10cb
|
|
expression: 'min(/MSSQL by ODBC/mssql.scan_to_search,15m) > 0.001'
|
|
name: 'MSSQL: Number of index and table scans exceeds index searches in the last 15m'
|
|
priority: WARNING
|
|
description: 'Index searches are preferable to index and table scans. For OLTP applications, optimize for more index searches and less scans (preferably, 1 full scan for every 1000 index searches). Index and table scans are expensive I/O operations.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: e9ea451a38424f6b9da59b8bbb068b51
|
|
name: 'SQL compilations per second'
|
|
type: DEPENDENT
|
|
key: mssql.sql_compilations_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of SQL compilations per second. Indicates the number of times the compile code path is entered. Includes runs caused by statement-level recompilations in SQL Server. After SQL Server user activity is stable, this value reaches a steady state.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''SQL Compilations/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.sql_statistics.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
- uuid: c7e0a26bf403482daec65a4b3cd0fd18
|
|
name: 'Get SQL Errors counters'
|
|
type: DEPENDENT
|
|
key: mssql.sql_errors.raw
|
|
delay: '0'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
description: 'The item gets SQL error information.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.object_name=~''.*SQL Errors'')]'
|
|
error_handler: DISCARD_VALUE
|
|
master_item:
|
|
key: 'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: a72415acabda46309c4dbc692e026cc2
|
|
name: 'SQL re-compilations per second'
|
|
type: DEPENDENT
|
|
key: mssql.sql_recompilations_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of statement recompiles per second. Counts the number of times statement recompiles are triggered. Generally, you want the recompiles to be low.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''SQL Re-Compilations/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.sql_statistics.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
- uuid: ca60d01d14d04566bf1a5e6b3b819538
|
|
name: 'Get SQL Statistics counters'
|
|
type: DEPENDENT
|
|
key: mssql.sql_statistics.raw
|
|
delay: '0'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
description: 'The item gets SQL statistics information.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.object_name=~''.*SQL Statistics'')]'
|
|
error_handler: DISCARD_VALUE
|
|
master_item:
|
|
key: 'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- uuid: 595aa3989b4e41cc8649827525adc0a0
|
|
name: 'Table lock escalations per second'
|
|
type: DEPENDENT
|
|
key: mssql.table_lock_escalations.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of times locks on a table were escalated to the TABLE or HoBT granularity.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Table Lock Escalations/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.access_methods.raw
|
|
tags:
|
|
- tag: component
|
|
value: lock
|
|
- tag: component
|
|
value: performance
|
|
- uuid: 37269b33e05349a1bfd05e4e493da6d4
|
|
name: 'Target pages'
|
|
type: DEPENDENT
|
|
key: mssql.target_pages
|
|
delay: '0'
|
|
description: 'The optimal number of pages in the buffer pool.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Target pages'')].cntr_value.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: mssql.buffer_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: page
|
|
- uuid: 2679f7e843e549ce9468f6cab05a71d5
|
|
name: 'Target server memory'
|
|
type: DEPENDENT
|
|
key: mssql.target_server_memory
|
|
delay: '0'
|
|
units: B
|
|
description: 'Indicates the ideal amount of memory the server can consume.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Target Server Memory (KB)'')].cntr_value.first()'
|
|
- type: MULTIPLIER
|
|
parameters:
|
|
- '1024'
|
|
master_item:
|
|
key: mssql.mem_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: memory
|
|
- uuid: 8c7d63f3689f464394a6035a3cad4009
|
|
name: 'Total latch wait time'
|
|
type: DEPENDENT
|
|
key: mssql.total_latch_wait_time
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: ms
|
|
description: 'Total latch wait time (in milliseconds) for latch requests in the last second. This value should stay stable compared to the number of latch waits per second.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Total Latch Wait Time (ms)'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.latches_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: wait-time
|
|
- uuid: 9112ed9b9d3741018d40f2dc892fa0f6
|
|
name: 'Total server memory'
|
|
type: DEPENDENT
|
|
key: mssql.total_server_memory
|
|
delay: '0'
|
|
units: B
|
|
description: 'Specifies the amount of memory the server has committed using the memory manager.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Total Server Memory (KB)'')].cntr_value.first()'
|
|
- type: MULTIPLIER
|
|
parameters:
|
|
- '1024'
|
|
master_item:
|
|
key: mssql.mem_manager.raw
|
|
tags:
|
|
- tag: component
|
|
value: memory
|
|
- uuid: 273b373d8b41411fafcfd1e93bb8ce2c
|
|
name: 'Total transactions number'
|
|
type: DEPENDENT
|
|
key: mssql.transactions
|
|
delay: '0'
|
|
description: 'The number of currently active transactions of all types.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.object_name=~''.*Transactions'' && @.counter_name==''Transactions'')].cntr_value.first()'
|
|
master_item:
|
|
key: 'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: transaction
|
|
- uuid: 127e1f73ce414eeb87d2cbfef46ccdef
|
|
name: 'Total transactions per second'
|
|
type: DEPENDENT
|
|
key: mssql.transactions_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Total number of transactions started for all databases per second.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Transactions/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.db_info.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
- tag: component
|
|
value: transaction
|
|
- uuid: ef6450dfcb9a476ca1bf97524e05d43d
|
|
name: 'Unsafe auto-params per second'
|
|
type: DEPENDENT
|
|
key: mssql.unsafe_autoparams_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of unsafe auto-parameterization attempts per second. For example, the query has some characteristics that prevent the cached plan from being shared. These are designated as unsafe. This does not count the number of forced parameterizations.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Unsafe Auto-Params/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.sql_statistics.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
- uuid: 27449e795c0a4e4e90c9ee6b6bf3df90
|
|
name: Uptime
|
|
type: DEPENDENT
|
|
key: mssql.uptime
|
|
delay: '0'
|
|
trends: '0'
|
|
units: uptime
|
|
description: 'MSSQL Server uptime in the format "N days, hh:mm:ss".'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Uptime'')].cntr_value.first()'
|
|
master_item:
|
|
key: 'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: application
|
|
triggers:
|
|
- uuid: 0321c9f671394cb180fdce53b4033cf4
|
|
expression: 'nodata(/MSSQL by ODBC/mssql.uptime,30m)=1'
|
|
name: 'MSSQL: Failed to fetch info data'
|
|
event_name: 'MSSQL: Failed to fetch info data (or no data for 30m)'
|
|
priority: INFO
|
|
description: 'Zabbix has not received any data for items for the last 30 minutes.'
|
|
dependencies:
|
|
- name: 'MSSQL: Service is unavailable'
|
|
expression: 'last(/MSSQL by ODBC/net.tcp.service[tcp,{$MSSQL.HOST},{$MSSQL.PORT}])=0'
|
|
tags:
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: aa10f863608a494281f41e12f0d2b1a5
|
|
expression: 'last(/MSSQL by ODBC/mssql.uptime)<10m'
|
|
name: 'MSSQL: Service has been restarted'
|
|
event_name: 'MSSQL: Service has been restarted (uptime < 10m)'
|
|
priority: INFO
|
|
description: 'Uptime is less than 10 minutes.'
|
|
manual_close: 'YES'
|
|
tags:
|
|
- tag: scope
|
|
value: notice
|
|
- uuid: 4e13bfc5927a421fb2996eec7e013448
|
|
name: 'Number of users connected'
|
|
type: DEPENDENT
|
|
key: mssql.user_connections
|
|
delay: '0'
|
|
description: 'Number of users connected to MSSQL Server.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''User Connections'')].cntr_value.first()'
|
|
master_item:
|
|
key: mssql.general_statistics.raw
|
|
tags:
|
|
- tag: component
|
|
value: application
|
|
- uuid: 5942cad838e8430a9ff71293dec52af8
|
|
name: 'Errors per second (User errors)'
|
|
type: DEPENDENT
|
|
key: mssql.user_errors_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of errors per second.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Errors/sec'' && @.instance_name==''User Errors'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.sql_errors.raw
|
|
tags:
|
|
- tag: component
|
|
value: error
|
|
- tag: component
|
|
value: performance
|
|
- uuid: 7013504d4a6c4b40a0aab11dab15d107
|
|
name: Version
|
|
type: DEPENDENT
|
|
key: mssql.version
|
|
delay: '0'
|
|
value_type: CHAR
|
|
trends: '0'
|
|
description: 'MSSQL Server version.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Version'')].instance_name.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1d
|
|
master_item:
|
|
key: 'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: application
|
|
triggers:
|
|
- uuid: 4ece2c9172714c68b8345834cc37d927
|
|
expression: 'last(/MSSQL by ODBC/mssql.version,#1)<>last(/MSSQL by ODBC/mssql.version,#2) and length(last(/MSSQL by ODBC/mssql.version))>0'
|
|
name: 'MSSQL: Version has changed'
|
|
event_name: 'MSSQL: Version has changed (new version value received: {ITEM.VALUE})'
|
|
priority: INFO
|
|
description: 'MSSQL version has changed. Acknowledge to close the problem manually.'
|
|
manual_close: 'YES'
|
|
tags:
|
|
- tag: scope
|
|
value: notice
|
|
- uuid: ea84affd97864dfaad7edf31e16e1300
|
|
name: 'Work files created per second'
|
|
type: DEPENDENT
|
|
key: mssql.workfiles_created_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of work files created per second. For example, work files can be used to store temporary results for hash joins and hash aggregates.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Workfiles Created/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.access_methods.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
triggers:
|
|
- uuid: 2f7e2dd4a94e4e92b468f4e2e2364f71
|
|
expression: 'min(/MSSQL by ODBC/mssql.workfiles_created_sec.rate,5m)>{$MSSQL.WORK_FILES.MAX}'
|
|
name: 'MSSQL: Number of work files created per second is high'
|
|
event_name: 'MSSQL: Number of work files created per second is high (over {$MSSQL.WORK_FILES.MAX} for 5m)'
|
|
priority: AVERAGE
|
|
description: 'Too many work files created per second to store temporary results for hash joins and hash aggregates.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: a38e7fdf994842558227dfad03e9943b
|
|
name: 'Work tables created per second'
|
|
type: DEPENDENT
|
|
key: mssql.worktables_created_sec.rate
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of work tables created per second. For example, work tables can be used to store temporary results for query spool, LOB variables, XML variables, and cursors.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Worktables Created/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: mssql.access_methods.raw
|
|
tags:
|
|
- tag: component
|
|
value: performance
|
|
triggers:
|
|
- uuid: 0214004de3084ce6b35d5bba53a66947
|
|
expression: 'min(/MSSQL by ODBC/mssql.worktables_created_sec.rate,5m)>{$MSSQL.WORK_TABLES.MAX}'
|
|
name: 'MSSQL: Number of work tables created per second is high'
|
|
event_name: 'MSSQL: Number of work tables created per second is high (over {$MSSQL.WORK_TABLES.MAX} for 5m)'
|
|
priority: AVERAGE
|
|
description: 'Too many work tables created per second to store temporary results for query spool, LOB variables, XML variables, and cursors.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: d93779b26c1a4784be31d32130eebf92
|
|
name: 'Worktables from cache ratio'
|
|
type: DEPENDENT
|
|
key: mssql.worktables_from_cache_ratio
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: '%'
|
|
description: 'Percentage of work tables created where the initial two pages of the work table were not allocated but were immediately available from the work table cache.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''WorktablesFromCacheRatio'')].cntr_value.first()'
|
|
master_item:
|
|
key: mssql.access_methods.raw
|
|
tags:
|
|
- tag: component
|
|
value: cache
|
|
triggers:
|
|
- uuid: 98dee2bc42484988b208e100cc25ae05
|
|
expression: 'max(/MSSQL by ODBC/mssql.worktables_from_cache_ratio,5m)<{$MSSQL.WORKTABLES_FROM_CACHE_RATIO.MIN.CRIT}'
|
|
name: 'MSSQL: Percentage of work tables available from the work table cache is low'
|
|
event_name: 'MSSQL: Percentage of work tables available from the work table cache is low (below {$MSSQL.WORKTABLES_FROM_CACHE_RATIO.MIN.CRIT}% for 5m)'
|
|
priority: HIGH
|
|
description: 'A value less than 90% may indicate insufficient memory, since execution plans are being dropped, or, on 32-bit systems, may indicate the need for an upgrade to a 64-bit system.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: c0302965c2574b43955a1d37a8fce9eb
|
|
name: 'Service''s TCP port state'
|
|
type: SIMPLE
|
|
key: 'net.tcp.service[tcp,{$MSSQL.HOST},{$MSSQL.PORT}]'
|
|
delay: 30s
|
|
description: 'Test the availability of MSSQL Server on a TCP port.'
|
|
valuemap:
|
|
name: 'Service state'
|
|
preprocessing:
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 10m
|
|
tags:
|
|
- tag: component
|
|
value: network
|
|
triggers:
|
|
- uuid: 152af5cc3ce04d4aa8ab255c37aed6c6
|
|
expression: 'last(/MSSQL by ODBC/net.tcp.service[tcp,{$MSSQL.HOST},{$MSSQL.PORT}])=0'
|
|
name: 'MSSQL: Service is unavailable'
|
|
priority: DISASTER
|
|
description: 'The TCP port of the MSSQL Server service is currently unavailable.'
|
|
tags:
|
|
- tag: scope
|
|
value: availability
|
|
discovery_rules:
|
|
- uuid: 6261715081794a49938d848c677da657
|
|
name: 'Availability group discovery'
|
|
type: DEPENDENT
|
|
key: mssql.availability.group.discovery
|
|
delay: '0'
|
|
description: 'Discovery of the existing availability groups.'
|
|
item_prototypes:
|
|
- uuid: 381c88cf5b0c47319b036e79265b0a20
|
|
name: 'MSSQL AG ''{#GROUP_NAME}'': Primary replica recovery health'
|
|
type: DEPENDENT
|
|
key: 'mssql.primary_recovery_health["{#GROUP_NAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
Indicates the recovery health of the primary replica:
|
|
0 = In progress
|
|
1 = Online
|
|
2 = Unavailable
|
|
valuemap:
|
|
name: 'MSSQL AG Recovery health'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.group_name==''{#GROUP_NAME}'')].primary_recovery_health.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_availability_group,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: component
|
|
value: availability-group
|
|
trigger_prototypes:
|
|
- uuid: c472a4f8400e451db529d5e12b863216
|
|
expression: 'last(/MSSQL by ODBC/mssql.primary_recovery_health["{#GROUP_NAME}"])=0'
|
|
name: 'MSSQL: AG ''{#GROUP_NAME}'': Primary replica recovery health in progress'
|
|
priority: WARNING
|
|
description: 'The primary replica is in the synchronization process.'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: scope
|
|
value: notice
|
|
- uuid: df51dcd0ef864705bc542e1233c1135b
|
|
name: 'MSSQL AG ''{#GROUP_NAME}'': Primary replica name'
|
|
type: DEPENDENT
|
|
key: 'mssql.primary_replica["{#GROUP_NAME}"]'
|
|
delay: '0'
|
|
value_type: CHAR
|
|
trends: '0'
|
|
description: 'Name of the server instance that is hosting the current primary replica.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.group_name==''{#GROUP_NAME}'')].primary_replica.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 3h
|
|
master_item:
|
|
key: 'db.odbc.get[get_availability_group,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: component
|
|
value: availability-group
|
|
- uuid: d2e76a734bd84484b6bd59b33c921f34
|
|
name: 'MSSQL AG ''{#GROUP_NAME}'': Secondary replica recovery health'
|
|
type: DEPENDENT
|
|
key: 'mssql.secondary_recovery_health["{#GROUP_NAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
Indicates the recovery health of a secondary replica:
|
|
0 = In progress
|
|
1 = Online
|
|
2 = Unavailable
|
|
valuemap:
|
|
name: 'MSSQL AG Recovery health'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.group_name==''{#GROUP_NAME}'')].secondary_recovery_health.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_availability_group,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: component
|
|
value: availability-group
|
|
trigger_prototypes:
|
|
- uuid: 1fbf51eb9f8b432c8e0d9ec3af1cab44
|
|
expression: 'last(/MSSQL by ODBC/mssql.secondary_recovery_health["{#GROUP_NAME}"])=0'
|
|
name: 'MSSQL: AG ''{#GROUP_NAME}'': Secondary replica recovery health in progress'
|
|
priority: WARNING
|
|
description: 'The secondary replica is in the synchronization process.'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: scope
|
|
value: notice
|
|
- uuid: fe59d23d0ba44777b0abf1a35b2c014e
|
|
name: 'MSSQL AG ''{#GROUP_NAME}'': Synchronization health'
|
|
type: DEPENDENT
|
|
key: 'mssql.synchronization_health["{#GROUP_NAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
Reflects a rollup of the `synchronization_health` of all availability replicas in the availability group:
|
|
0 = Not healthy. None of the availability replicas have a healthy synchronization.
|
|
1 = Partially healthy. The synchronization of some, but not all, availability replicas is healthy.
|
|
2 = Healthy. The synchronization of every availability replica is healthy.
|
|
valuemap:
|
|
name: 'MSSQL AG Synchronization health'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.group_name==''{#GROUP_NAME}'')].synchronization_health.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_availability_group,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: component
|
|
value: availability-group
|
|
trigger_prototypes:
|
|
- uuid: 2ee92e5ea41744ac9e27ffaa4313bdec
|
|
expression: 'last(/MSSQL by ODBC/mssql.synchronization_health["{#GROUP_NAME}"])=0'
|
|
name: 'MSSQL: AG ''{#GROUP_NAME}'': All replicas unhealthy'
|
|
priority: DISASTER
|
|
description: 'None of the availability replicas have a healthy synchronization.'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: fd24c78f1db24186932d17d1145b6e92
|
|
expression: 'last(/MSSQL by ODBC/mssql.synchronization_health["{#GROUP_NAME}"])=1'
|
|
name: 'MSSQL: AG ''{#GROUP_NAME}'': Some replicas unhealthy'
|
|
priority: HIGH
|
|
description: 'The synchronization health of some, but not all, availability replicas is healthy.'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: scope
|
|
value: availability
|
|
master_item:
|
|
key: 'db.odbc.get[get_availability_group,"{$MSSQL.DSN}"]'
|
|
lld_macro_paths:
|
|
- lld_macro: '{#GROUP_NAME}'
|
|
path: $.group_name
|
|
preprocessing:
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1d
|
|
- uuid: 2e38ea24c9bf4a5480df0816b7c659b3
|
|
name: 'Database discovery'
|
|
type: DEPENDENT
|
|
key: mssql.database.discovery
|
|
delay: '0'
|
|
filter:
|
|
evaltype: AND
|
|
conditions:
|
|
- macro: '{#DBNAME}'
|
|
value: '{$MSSQL.DBNAME.MATCHES}'
|
|
formulaid: A
|
|
- macro: '{#DBNAME}'
|
|
value: '{$MSSQL.DBNAME.NOT_MATCHES}'
|
|
operator: NOT_MATCHES_REGEX
|
|
formulaid: B
|
|
description: 'Scanning databases in DBMS.'
|
|
item_prototypes:
|
|
- uuid: 9c9dab20d8fa46f888c6aa7c7c9bf1b9
|
|
name: 'MSSQL DB ''{#DBNAME}'': Last diff backup duration'
|
|
type: DEPENDENT
|
|
key: 'mssql.backup.diff.duration["{#DBNAME}"]'
|
|
delay: '0'
|
|
units: s
|
|
description: 'Duration of the last differential backup.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.type==''I'')].duration.first()'
|
|
error_handler: CUSTOM_VALUE
|
|
error_handler_params: '0'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 12h
|
|
master_item:
|
|
key: 'mssql.backup.raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- uuid: bd663e1e9b40469cb4ca430186b049db
|
|
name: 'MSSQL DB ''{#DBNAME}'': Last diff backup (time ago)'
|
|
type: DEPENDENT
|
|
key: 'mssql.backup.diff["{#DBNAME}"]'
|
|
delay: '0'
|
|
units: s
|
|
description: 'The amount of time since the last differential backup.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.type==''I'')].time_since_last_backup.first()'
|
|
error_handler: CUSTOM_VALUE
|
|
error_handler_params: '0'
|
|
master_item:
|
|
key: 'mssql.backup.raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
trigger_prototypes:
|
|
- uuid: 036892c6b9de41c698a4e9acd5457e6a
|
|
expression: 'last(/MSSQL by ODBC/mssql.backup.diff["{#DBNAME}"])>{$MSSQL.BACKUP_DIFF.CRIT:"{#DBNAME}"} and {$MSSQL.BACKUP_DIFF.USED:"{#DBNAME}"}=1'
|
|
name: 'MSSQL: DB ''{#DBNAME}'': Diff backup is old'
|
|
event_name: 'MSSQL: DB ''{#DBNAME}'': Diff backup older than {$MSSQL.BACKUP_DIFF.CRIT:"{#DBNAME}"}'
|
|
opdata: 'Time since last backup: {ITEM.LASTVALUE1}'
|
|
priority: HIGH
|
|
description: 'The differential backup has not been executed for a long time.'
|
|
manual_close: 'YES'
|
|
tags:
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: 9a294163ee034fc6a58f5534a7d79aac
|
|
expression: 'last(/MSSQL by ODBC/mssql.backup.diff["{#DBNAME}"])>{$MSSQL.BACKUP_DIFF.WARN:"{#DBNAME}"} and {$MSSQL.BACKUP_DIFF.USED:"{#DBNAME}"}=1'
|
|
name: 'MSSQL: DB ''{#DBNAME}'': Diff backup is old'
|
|
event_name: 'MSSQL: DB ''{#DBNAME}'': Diff backup older than {$MSSQL.BACKUP_DIFF.WARN:"{#DBNAME}"}'
|
|
opdata: 'Time since last backup: {ITEM.LASTVALUE1}'
|
|
priority: WARNING
|
|
description: 'The differential backup has not been executed for a long time.'
|
|
manual_close: 'YES'
|
|
dependencies:
|
|
- name: 'MSSQL: DB ''{#DBNAME}'': Diff backup is old'
|
|
expression: 'last(/MSSQL by ODBC/mssql.backup.diff["{#DBNAME}"])>{$MSSQL.BACKUP_DIFF.CRIT:"{#DBNAME}"} and {$MSSQL.BACKUP_DIFF.USED:"{#DBNAME}"}=1'
|
|
tags:
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: 52929d988ad34aac945ff2ce9e448261
|
|
name: 'MSSQL DB ''{#DBNAME}'': Last full backup duration'
|
|
type: DEPENDENT
|
|
key: 'mssql.backup.full.duration["{#DBNAME}"]'
|
|
delay: '0'
|
|
units: s
|
|
description: 'Duration of the last full backup.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.type==''D'')].duration.first()'
|
|
error_handler: CUSTOM_VALUE
|
|
error_handler_params: '0'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 12h
|
|
master_item:
|
|
key: 'mssql.backup.raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- uuid: 06d0ac1eeca843a289defa1cf1d728a3
|
|
name: 'MSSQL DB ''{#DBNAME}'': Last full backup (time ago)'
|
|
type: DEPENDENT
|
|
key: 'mssql.backup.full["{#DBNAME}"]'
|
|
delay: '0'
|
|
units: s
|
|
description: 'The amount of time since the last full backup.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.type==''D'')].time_since_last_backup.first()'
|
|
error_handler: CUSTOM_VALUE
|
|
error_handler_params: '0'
|
|
master_item:
|
|
key: 'mssql.backup.raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
trigger_prototypes:
|
|
- uuid: 09c8e059ffa44d0eb1be626b5d820dc9
|
|
expression: 'last(/MSSQL by ODBC/mssql.backup.full["{#DBNAME}"])>{$MSSQL.BACKUP_FULL.CRIT:"{#DBNAME}"} and {$MSSQL.BACKUP_FULL.USED:"{#DBNAME}"}=1'
|
|
name: 'MSSQL: DB ''{#DBNAME}'': Full backup is old'
|
|
event_name: 'MSSQL: DB ''{#DBNAME}'': Full backup older than {$MSSQL.BACKUP_FULL.CRIT:"{#DBNAME}"}'
|
|
opdata: 'Time since last backup: {ITEM.LASTVALUE1}'
|
|
priority: HIGH
|
|
description: 'The full backup has not been executed for a long time.'
|
|
manual_close: 'YES'
|
|
tags:
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: b380d76b1055482ca8cd69c90611f1a5
|
|
expression: 'last(/MSSQL by ODBC/mssql.backup.full["{#DBNAME}"])>{$MSSQL.BACKUP_FULL.WARN:"{#DBNAME}"} and {$MSSQL.BACKUP_FULL.USED:"{#DBNAME}"}=1'
|
|
name: 'MSSQL: DB ''{#DBNAME}'': Full backup is old'
|
|
event_name: 'MSSQL: DB ''{#DBNAME}'': Full backup older than {$MSSQL.BACKUP_FULL.WARN:"{#DBNAME}"}'
|
|
opdata: 'Time since last backup: {ITEM.LASTVALUE1}'
|
|
priority: WARNING
|
|
description: 'The full backup has not been executed for a long time.'
|
|
manual_close: 'YES'
|
|
dependencies:
|
|
- name: 'MSSQL: DB ''{#DBNAME}'': Full backup is old'
|
|
expression: 'last(/MSSQL by ODBC/mssql.backup.full["{#DBNAME}"])>{$MSSQL.BACKUP_FULL.CRIT:"{#DBNAME}"} and {$MSSQL.BACKUP_FULL.USED:"{#DBNAME}"}=1'
|
|
tags:
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: 218241c7c5a648a59021999f97516349
|
|
name: 'MSSQL DB ''{#DBNAME}'': Last log backup duration'
|
|
type: DEPENDENT
|
|
key: 'mssql.backup.log.duration["{#DBNAME}"]'
|
|
delay: '0'
|
|
units: s
|
|
description: 'Duration of the last log backup.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.type==''L'')].duration.first()'
|
|
error_handler: CUSTOM_VALUE
|
|
error_handler_params: '0'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 12h
|
|
master_item:
|
|
key: 'mssql.backup.raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- uuid: c5e796bc904848efb2557c8a9a07b828
|
|
name: 'MSSQL DB ''{#DBNAME}'': Last log backup (time ago)'
|
|
type: DEPENDENT
|
|
key: 'mssql.backup.log["{#DBNAME}"]'
|
|
delay: '0'
|
|
units: s
|
|
description: 'The amount of time since the last log backup.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.type==''L'')].time_since_last_backup.first()'
|
|
error_handler: CUSTOM_VALUE
|
|
error_handler_params: '0'
|
|
master_item:
|
|
key: 'mssql.backup.raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- uuid: a6a276b5a8ce4b00b277e507ea7b15ee
|
|
name: 'MSSQL DB ''{#DBNAME}'': Get last backup'
|
|
type: DEPENDENT
|
|
key: 'mssql.backup.raw["{#DBNAME}"]'
|
|
delay: '0'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
description: 'The item gets information about backup processes for {#DBNAME}.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.dbname==''{#DBNAME}'')]'
|
|
error_handler: DISCARD_VALUE
|
|
master_item:
|
|
key: 'db.odbc.get[get_last_backup,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- uuid: 52a2d8012fea467582b09505e9c880be
|
|
name: 'MSSQL DB ''{#DBNAME}'': Recovery model'
|
|
type: DEPENDENT
|
|
key: 'mssql.backup.recovery_model["{#DBNAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
Recovery model selected:
|
|
1 = Full
|
|
2 = Bulk_logged
|
|
3 = Simple
|
|
valuemap:
|
|
name: 'MSSQL Recovery model'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[0].db_recovery_model'
|
|
error_handler: CUSTOM_VALUE
|
|
error_handler_params: '1'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1d
|
|
master_item:
|
|
key: 'mssql.backup.raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- uuid: f4f218f22d6d48419260003880b38abd
|
|
name: 'MSSQL DB ''{#DBNAME}'': Active transactions'
|
|
type: DEPENDENT
|
|
key: 'mssql.db.active_transactions["{#DBNAME}"]'
|
|
delay: '0'
|
|
description: 'Number of active transactions for the database.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Active Transactions'')].cntr_value.first()'
|
|
master_item:
|
|
key: 'mssql.db.perf_raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- uuid: ff3916f807784639a1821229d4d722d8
|
|
name: 'MSSQL DB ''{#DBNAME}'': Data file size'
|
|
type: DEPENDENT
|
|
key: 'mssql.db.data_files_size["{#DBNAME}"]'
|
|
delay: '0'
|
|
units: B
|
|
description: 'Cumulative size of all the data files in the database including any automatic growth. Monitoring this counter is useful, for example, for determining the correct size of `tempdb`.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Data File(s) Size (KB)'')].cntr_value.first()'
|
|
- type: MULTIPLIER
|
|
parameters:
|
|
- '1024'
|
|
master_item:
|
|
key: 'mssql.db.perf_raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- uuid: 0d7bdf4c18fa49e79926e6f6d078d50f
|
|
name: 'MSSQL DB ''{#DBNAME}'': Log bytes flushed per second'
|
|
type: DEPENDENT
|
|
key: 'mssql.db.log_bytes_flushed_sec.rate["{#DBNAME}"]'
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: Bps
|
|
description: 'Total number of log bytes flushed per second. Useful for determining trends and utilization of the transaction log.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Log Bytes Flushed/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: 'mssql.db.perf_raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- uuid: 85536311e68047a6a8bf13c51c8d0647
|
|
name: 'MSSQL DB ''{#DBNAME}'': Log file size'
|
|
type: DEPENDENT
|
|
key: 'mssql.db.log_files_size["{#DBNAME}"]'
|
|
delay: '0'
|
|
units: B
|
|
description: 'Cumulative size of all the transaction log files in the database.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Log File(s) Size (KB)'')].cntr_value.first()'
|
|
- type: MULTIPLIER
|
|
parameters:
|
|
- '1024'
|
|
master_item:
|
|
key: 'mssql.db.perf_raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- uuid: 75fd2f42e274471c97cb23dc740863b8
|
|
name: 'MSSQL DB ''{#DBNAME}'': Log file used size'
|
|
type: DEPENDENT
|
|
key: 'mssql.db.log_files_used_size["{#DBNAME}"]'
|
|
delay: '0'
|
|
units: B
|
|
description: 'Cumulative size of all the log files in the database.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Log File(s) Used Size (KB)'')].cntr_value.first()'
|
|
- type: MULTIPLIER
|
|
parameters:
|
|
- '1024'
|
|
master_item:
|
|
key: 'mssql.db.perf_raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- uuid: c10b6f364aa547d8b4abe431ae27d70b
|
|
name: 'MSSQL DB ''{#DBNAME}'': Log flushes per second'
|
|
type: DEPENDENT
|
|
key: 'mssql.db.log_flushes_sec.rate["{#DBNAME}"]'
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of log flushes per second.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Log Flushes/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: 'mssql.db.perf_raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- uuid: 5404c8682b294a06a3027dc10d0b43fc
|
|
name: 'MSSQL DB ''{#DBNAME}'': Log flush waits per second'
|
|
type: DEPENDENT
|
|
key: 'mssql.db.log_flush_waits_sec.rate["{#DBNAME}"]'
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of commits per second waiting for the log flush.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Log Flush Waits/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: 'mssql.db.perf_raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
trigger_prototypes:
|
|
- uuid: b351fc1128c24faf88d36717d0a0d1a5
|
|
expression: 'min(/MSSQL by ODBC/mssql.db.log_flush_waits_sec.rate["{#DBNAME}"],5m)>{$MSSQL.LOG_FLUSH_WAITS.MAX:"{#DBNAME}"}'
|
|
name: 'MSSQL: DB ''{#DBNAME}'': Number of commits waiting for the log flush is high'
|
|
event_name: 'MSSQL: DB ''{#DBNAME}'': Number of commits waiting for the log flush is high (over {$MSSQL.LOG_FLUSH_WAITS.MAX:"{#DBNAME}"}/sec for 5m)'
|
|
priority: WARNING
|
|
description: 'Too many commits are waiting for the log flush.'
|
|
tags:
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: f1abdb0800534f6bbf9f1403e7dea9e4
|
|
name: 'MSSQL DB ''{#DBNAME}'': Log flush wait time'
|
|
type: DEPENDENT
|
|
key: 'mssql.db.log_flush_wait_time["{#DBNAME}"]'
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: ms
|
|
description: 'Total wait time (in milliseconds) to flush the log. On an Always On secondary database, this value indicates the wait time for log records to be hardened to disk.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Log Flush Wait Time'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: 'mssql.db.perf_raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
trigger_prototypes:
|
|
- uuid: 80351c56215148b8ab75d95dc81ff0d0
|
|
expression: 'min(/MSSQL by ODBC/mssql.db.log_flush_wait_time["{#DBNAME}"],5m)>{$MSSQL.LOG_FLUSH_WAIT_TIME.MAX:"{#DBNAME}"}'
|
|
name: 'MSSQL: DB ''{#DBNAME}'': Total wait time to flush the log is high'
|
|
event_name: 'MSSQL: DB ''{#DBNAME}'': Total wait time to flush the log is high (over {$MSSQL.LOG_FLUSH_WAIT_TIME.MAX:"{#DBNAME}"}ms for 5m)'
|
|
priority: WARNING
|
|
description: 'The wait time to flush the log is too long.'
|
|
tags:
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: fdb24058e54243e8b5a888ec40d506e9
|
|
name: 'MSSQL DB ''{#DBNAME}'': Log growths'
|
|
type: DEPENDENT
|
|
key: 'mssql.db.log_growths["{#DBNAME}"]'
|
|
delay: '0'
|
|
description: 'Total number of times the transaction log for the database has been expanded.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Log Growths'')].cntr_value.first()'
|
|
master_item:
|
|
key: 'mssql.db.perf_raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- uuid: a1a96d8bfef740ec9c8eadd9704ab605
|
|
name: 'MSSQL DB ''{#DBNAME}'': Log shrinks'
|
|
type: DEPENDENT
|
|
key: 'mssql.db.log_shrinks["{#DBNAME}"]'
|
|
delay: '0'
|
|
description: 'Total number of times the transaction log for the database has been shrunk.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Log Shrinks'')].cntr_value.first()'
|
|
master_item:
|
|
key: 'mssql.db.perf_raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- uuid: 26d1515df1be450e861e4f7a9f76b03d
|
|
name: 'MSSQL DB ''{#DBNAME}'': Log truncations'
|
|
type: DEPENDENT
|
|
key: 'mssql.db.log_truncations["{#DBNAME}"]'
|
|
delay: '0'
|
|
description: 'Number of times the transaction log has been shrunk.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Log Truncations'')].cntr_value.first()'
|
|
master_item:
|
|
key: 'mssql.db.perf_raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- uuid: 9f9cda56992841408ab8307b7019b76f
|
|
name: 'MSSQL DB ''{#DBNAME}'': Percent log used'
|
|
type: DEPENDENT
|
|
key: 'mssql.db.percent_log_used["{#DBNAME}"]'
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
units: '%'
|
|
description: 'Percentage of log space in use.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Percent Log Used'')].cntr_value.first()'
|
|
master_item:
|
|
key: 'mssql.db.perf_raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
trigger_prototypes:
|
|
- uuid: f02958e3ee174c6dbe900e3dd92cacdc
|
|
expression: 'min(/MSSQL by ODBC/mssql.db.percent_log_used["{#DBNAME}"],5m)>{$MSSQL.PERCENT_LOG_USED.MAX:"{#DBNAME}"}'
|
|
name: 'MSSQL: DB ''{#DBNAME}'': Percent of log usage is high'
|
|
event_name: 'MSSQL: DB ''{#DBNAME}'': Percent of log usage is high (over {$MSSQL.PERCENT_LOG_USED.MAX:"{#DBNAME}"}% for 5m)'
|
|
priority: WARNING
|
|
description: 'There''s not enough space left in the log.'
|
|
tags:
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: 74b107e240274c69ae1e8f0632b67e14
|
|
name: 'MSSQL DB ''{#DBNAME}'': Get performance counters'
|
|
type: DEPENDENT
|
|
key: 'mssql.db.perf_raw["{#DBNAME}"]'
|
|
delay: '0'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
description: 'The item gets server status information for {#DBNAME}.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.object_name=~''.*Databases'' && @.instance_name==''{#DBNAME}'')]'
|
|
error_handler: DISCARD_VALUE
|
|
master_item:
|
|
key: 'db.odbc.get[get_status_variables,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- uuid: fe7d7c3e0aab43d79c00eaceb39c8c6c
|
|
name: 'MSSQL DB ''{#DBNAME}'': State'
|
|
type: DEPENDENT
|
|
key: 'mssql.db.state["{#DBNAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
0 = Online
|
|
1 = Restoring
|
|
2 = Recovering | SQL Server 2008 and later
|
|
3 = Recovery_pending | SQL Server 2008 and later
|
|
4 = Suspect
|
|
5 = Emergency | SQL Server 2008 and later
|
|
6 = Offline | SQL Server 2008 and later
|
|
7 = Copying | Azure SQL Database Active Geo-Replication
|
|
10 = Offline_secondary | Azure SQL Database Active Geo-Replication
|
|
valuemap:
|
|
name: 'MSSQL DB state'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''State'')].cntr_value.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 15m
|
|
master_item:
|
|
key: 'mssql.db.perf_raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
trigger_prototypes:
|
|
- uuid: 6b8c8cf8e0d24b7f8d9aa7469a0facd4
|
|
expression: 'last(/MSSQL by ODBC/mssql.db.state["{#DBNAME}"])>1'
|
|
name: 'MSSQL: DB ''{#DBNAME}'': State is {ITEM.VALUE}'
|
|
priority: HIGH
|
|
description: 'The DB has a non-working state.'
|
|
tags:
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: 267b1d54c40641f5aeb4e3e7adfa4680
|
|
name: 'MSSQL DB ''{#DBNAME}'': Transactions per second'
|
|
type: DEPENDENT
|
|
key: 'mssql.db.transactions_sec.rate["{#DBNAME}"]'
|
|
delay: '0'
|
|
value_type: FLOAT
|
|
description: 'Number of transactions started for the database per second.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.counter_name==''Transactions/sec'')].cntr_value.first()'
|
|
- type: CHANGE_PER_SECOND
|
|
parameters:
|
|
- ''
|
|
master_item:
|
|
key: 'mssql.db.perf_raw["{#DBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: database
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
trigger_prototypes:
|
|
- uuid: 4a6e76dc9a5148649d07e35e962b5893
|
|
expression: 'last(/MSSQL by ODBC/mssql.backup.log["{#DBNAME}"])>{$MSSQL.BACKUP_LOG.CRIT:"{#DBNAME}"} and {$MSSQL.BACKUP_LOG.USED:"{#DBNAME}"}=1 and last(/MSSQL by ODBC/mssql.backup.recovery_model["{#DBNAME}"])<>3'
|
|
name: 'MSSQL: DB ''{#DBNAME}'': Log backup is old'
|
|
event_name: 'MSSQL: DB ''{#DBNAME}'': Log backup older than {$MSSQL.BACKUP_LOG.CRIT:"{#DBNAME}"}'
|
|
opdata: 'Time since last backup: {ITEM.LASTVALUE1}'
|
|
priority: HIGH
|
|
description: 'The log backup has not been executed for a long time.'
|
|
manual_close: 'YES'
|
|
tags:
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: 6000420450cf453bae3652c7f7d05e2b
|
|
expression: 'last(/MSSQL by ODBC/mssql.backup.log["{#DBNAME}"])>{$MSSQL.BACKUP_LOG.WARN:"{#DBNAME}"} and {$MSSQL.BACKUP_LOG.USED:"{#DBNAME}"}=1 and last(/MSSQL by ODBC/mssql.backup.recovery_model["{#DBNAME}"])<>3'
|
|
name: 'MSSQL: DB ''{#DBNAME}'': Log backup is old'
|
|
event_name: 'MSSQL: DB ''{#DBNAME}'': Log backup older than {$MSSQL.BACKUP_LOG.WARN:"{#DBNAME}"}'
|
|
opdata: 'Time since last backup: {ITEM.LASTVALUE1}'
|
|
priority: WARNING
|
|
description: 'The log backup has not been executed for a long time.'
|
|
manual_close: 'YES'
|
|
dependencies:
|
|
- name: 'MSSQL: DB ''{#DBNAME}'': Log backup is old'
|
|
expression: 'last(/MSSQL by ODBC/mssql.backup.log["{#DBNAME}"])>{$MSSQL.BACKUP_LOG.CRIT:"{#DBNAME}"} and {$MSSQL.BACKUP_LOG.USED:"{#DBNAME}"}=1 and last(/MSSQL by ODBC/mssql.backup.recovery_model["{#DBNAME}"])<>3'
|
|
tags:
|
|
- tag: database
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: availability
|
|
graph_prototypes:
|
|
- uuid: ac52c952ba954065bbbb5c722630cd0b
|
|
name: 'DB ''{#DBNAME}'': Log size'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: 'mssql.db.log_files_size["{#DBNAME}"]'
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: 'mssql.db.log_files_used_size["{#DBNAME}"]'
|
|
- uuid: 49d3be5c3ce34cfc9c13456a2ae301f6
|
|
name: 'DB ''{#DBNAME}'': Number of Log changes'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: 'mssql.db.log_growths["{#DBNAME}"]'
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: 'mssql.db.log_shrinks["{#DBNAME}"]'
|
|
- sortorder: '2'
|
|
color: 00611C
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: 'mssql.db.log_truncations["{#DBNAME}"]'
|
|
- uuid: 9553b9169f5e441abecfde1c07ca1a4e
|
|
name: 'DB ''{#DBNAME}'': Size'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: 'mssql.db.data_files_size["{#DBNAME}"]'
|
|
master_item:
|
|
key: 'db.odbc.get[get_database,"{$MSSQL.DSN}"]'
|
|
lld_macro_paths:
|
|
- lld_macro: '{#DBNAME}'
|
|
path: $.dbname
|
|
- lld_macro: '{#RECOVERY_MODEL}'
|
|
path: $.recovery_model
|
|
preprocessing:
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1d
|
|
overrides:
|
|
- name: 'Log backup'
|
|
step: '1'
|
|
filter:
|
|
conditions:
|
|
- macro: '{#RECOVERY_MODEL}'
|
|
value: '3'
|
|
formulaid: A
|
|
operations:
|
|
- operationobject: TRIGGER_PROTOTYPE
|
|
operator: LIKE
|
|
value: 'Log backup is old'
|
|
discover: NO_DISCOVER
|
|
- uuid: 6d972c575a944d49badfc2e6a614f686
|
|
name: 'Job discovery'
|
|
type: DEPENDENT
|
|
key: mssql.job.discovery
|
|
delay: '0'
|
|
filter:
|
|
evaltype: AND
|
|
conditions:
|
|
- macro: '{#JOBNAME}'
|
|
value: '{$MSSQL.JOB.MATCHES}'
|
|
formulaid: A
|
|
- macro: '{#JOBNAME}'
|
|
value: '{$MSSQL.JOB.NOT_MATCHES}'
|
|
operator: NOT_MATCHES_REGEX
|
|
formulaid: B
|
|
description: 'Scanning jobs in DBMS.'
|
|
item_prototypes:
|
|
- uuid: 4e1e21603b01420f9b25e85f5987e7ec
|
|
name: 'MSSQL Job ''{#JOBNAME}'': Enabled'
|
|
type: DEPENDENT
|
|
key: 'mssql.job.enabled["{#JOBNAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
The possible values of the job status:
|
|
0 = Disabled
|
|
1 = Enabled
|
|
valuemap:
|
|
name: 'MSSQL - Yes / No'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- $.enabled
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 12h
|
|
master_item:
|
|
key: 'mssql.job.status_raw["{#JOBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: mssql-job
|
|
- tag: mssql-job
|
|
value: '{#JOBNAME}'
|
|
- uuid: 3ceba63a77e24628a2ee9439d574bac5
|
|
name: 'MSSQL Job ''{#JOBNAME}'': Last run date-time'
|
|
type: DEPENDENT
|
|
key: 'mssql.job.lastrundatetime["{#JOBNAME}"]'
|
|
delay: '0'
|
|
units: unixtime
|
|
description: 'The last date-time of the job run.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- $.last_run_date_time
|
|
- type: JAVASCRIPT
|
|
parameters:
|
|
- 'return value == ''null'' ? 0 : Math.floor(new Date(value) / 1000);'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 12h
|
|
master_item:
|
|
key: 'mssql.job.status_raw["{#JOBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: mssql-job
|
|
- tag: mssql-job
|
|
value: '{#JOBNAME}'
|
|
- uuid: b9984c1238554a559b444853e9f66653
|
|
name: 'MSSQL Job ''{#JOBNAME}'': Last run status message'
|
|
type: DEPENDENT
|
|
key: 'mssql.job.lastrunstatusmessage["{#JOBNAME}"]'
|
|
delay: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
description: 'An informational message about the last run of the job.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- $.last_run_status_message
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 12h
|
|
master_item:
|
|
key: 'mssql.job.status_raw["{#JOBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: mssql-job
|
|
- tag: mssql-job
|
|
value: '{#JOBNAME}'
|
|
- uuid: 1c725adad98f44158b43127d4c5fb0f3
|
|
name: 'MSSQL Job ''{#JOBNAME}'': Next run date-time'
|
|
type: DEPENDENT
|
|
key: 'mssql.job.nextrundatetime["{#JOBNAME}"]'
|
|
delay: '0'
|
|
units: unixtime
|
|
description: 'The next date-time of the job run.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- $.next_run_date_time
|
|
- type: JAVASCRIPT
|
|
parameters:
|
|
- 'return value == ''null'' ? 0 : Math.floor(new Date(value) / 1000);'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 12h
|
|
master_item:
|
|
key: 'mssql.job.status_raw["{#JOBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: mssql-job
|
|
- tag: mssql-job
|
|
value: '{#JOBNAME}'
|
|
- uuid: eaa43a261e114979af236698ea7ae32c
|
|
name: 'MSSQL Job ''{#JOBNAME}'': Run status'
|
|
type: DEPENDENT
|
|
key: 'mssql.job.runstatus["{#JOBNAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
The possible values of the job status:
|
|
0 ⇒ Failed
|
|
1 ⇒ Succeeded
|
|
2 ⇒ Retry
|
|
3 ⇒ Canceled
|
|
4 ⇒ Running
|
|
valuemap:
|
|
name: 'MSSQL Job Run Status'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- $.run_status
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 15m
|
|
master_item:
|
|
key: 'mssql.job.status_raw["{#JOBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: mssql-job
|
|
- tag: mssql-job
|
|
value: '{#JOBNAME}'
|
|
trigger_prototypes:
|
|
- uuid: 304f468605164fd2baa1905cedf2c897
|
|
expression: 'last(/MSSQL by ODBC/mssql.job.runstatus["{#JOBNAME}"])=0'
|
|
name: 'MSSQL: Job ''{#JOBNAME}'': Failed to run'
|
|
priority: WARNING
|
|
description: 'The last run of the job has failed.'
|
|
manual_close: 'YES'
|
|
tags:
|
|
- tag: mssql-job
|
|
value: '{#JOBNAME}'
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: 69da58fe4c7e42028fa96388df526103
|
|
name: 'MSSQL Job ''{#JOBNAME}'': Run duration'
|
|
type: DEPENDENT
|
|
key: 'mssql.job.run_duration["{#JOBNAME}"]'
|
|
delay: '0'
|
|
units: s
|
|
description: 'Duration of the last-run job.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- $.run_duration
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 15m
|
|
master_item:
|
|
key: 'mssql.job.status_raw["{#JOBNAME}"]'
|
|
tags:
|
|
- tag: component
|
|
value: mssql-job
|
|
- tag: mssql-job
|
|
value: '{#JOBNAME}'
|
|
trigger_prototypes:
|
|
- uuid: 7e45cb8f41114388ac8772006d929da2
|
|
expression: 'last(/MSSQL by ODBC/mssql.job.run_duration["{#JOBNAME}"])>{$MSSQL.JOB_DURATION.WARN:"{#JOBNAME}"}'
|
|
name: 'MSSQL: Job ''{#JOBNAME}'': Job duration is high'
|
|
event_name: 'MSSQL: Job ''{#JOBNAME}'': Job duration is greater than {$MSSQL.JOB_DURATION.WARN:"{#JOBNAME}"}'
|
|
opdata: 'Job duration: {ITEM.LASTVALUE1}'
|
|
priority: WARNING
|
|
description: 'The job is taking too long.'
|
|
manual_close: 'YES'
|
|
tags:
|
|
- tag: mssql-job
|
|
value: '{#JOBNAME}'
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: 3660503b46be4c92a7b1d5040bc8cd0d
|
|
name: 'MSSQL Job ''{#JOBNAME}'': Get job status'
|
|
type: DEPENDENT
|
|
key: 'mssql.job.status_raw["{#JOBNAME}"]'
|
|
delay: '0'
|
|
history: '0'
|
|
value_type: TEXT
|
|
trends: '0'
|
|
description: 'The item gets the status of SQL agent job {#JOBNAME}.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.job_name==''{#JOBNAME}'')].first()'
|
|
error_handler: DISCARD_VALUE
|
|
master_item:
|
|
key: 'db.odbc.get[get_job_status,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: raw
|
|
- tag: mssql-job
|
|
value: '{#JOBNAME}'
|
|
master_item:
|
|
key: 'db.odbc.get[get_job_status,"{$MSSQL.DSN}"]'
|
|
lld_macro_paths:
|
|
- lld_macro: '{#JOBNAME}'
|
|
path: $.job_name
|
|
preprocessing:
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1d
|
|
- uuid: af25f87b29c24c05a57a82f7b1347ad4
|
|
name: 'Local database discovery'
|
|
type: DEPENDENT
|
|
key: mssql.local.db.discovery
|
|
delay: '0'
|
|
description: 'Discovery of the local availability databases.'
|
|
item_prototypes:
|
|
- uuid: 7e4baf2802394b93bf5770f4f72d7f05
|
|
name: 'MSSQL AG ''{#GROUP_NAME}'' Local DB ''{#DBNAME}'': Suspended'
|
|
type: DEPENDENT
|
|
key: 'mssql.local_db.is_suspended["{#DBNAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
Database state:
|
|
0 = Resumed
|
|
1 = Suspended
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.dbname==''{#DBNAME}'' && @.group_name==''{#GROUP_NAME}'')].is_suspended.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_local_db,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: component
|
|
value: local-db
|
|
- tag: local-db
|
|
value: '{#DBNAME}'
|
|
- uuid: f8111429599e49bd947a12b9771d1a3d
|
|
name: 'MSSQL AG ''{#GROUP_NAME}'' Local DB ''{#DBNAME}'': State'
|
|
type: DEPENDENT
|
|
key: 'mssql.local_db.state["{#DBNAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
0 = Online
|
|
1 = Restoring
|
|
2 = Recovering
|
|
3 = Recovery pending
|
|
4 = Suspect
|
|
5 = Emergency
|
|
6 = Offline
|
|
valuemap:
|
|
name: 'MSSQL DB state'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.dbname==''{#DBNAME}'' && @.group_name==''{#GROUP_NAME}'')].database_state.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_local_db,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: component
|
|
value: local-db
|
|
- tag: local-db
|
|
value: '{#DBNAME}'
|
|
trigger_prototypes:
|
|
- uuid: 78923c9c52e74c80a77d418afe9e438d
|
|
expression: 'last(/MSSQL by ODBC/mssql.local_db.state["{#DBNAME}"])>0'
|
|
name: 'MSSQL: AG ''{#GROUP_NAME}'' Local DB ''{#DBNAME}'': "{#DBNAME}" is {ITEM.VALUE}'
|
|
priority: WARNING
|
|
description: 'The local availability database has a non-working state.'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: local-db
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: e7d7fbac95e94c45b6515c8e1b7baf1a
|
|
name: 'MSSQL AG ''{#GROUP_NAME}'' Local DB ''{#DBNAME}'': Synchronization health'
|
|
type: DEPENDENT
|
|
key: 'mssql.local_db.synchronization_health["{#DBNAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
Reflects the intersection of the synchronization state of a database that is joined to the availability group on the availability replica and the availability mode of the availability replica (synchronous-commit or asynchronous-commit mode):
|
|
0 = Not healthy. The synchronization_state of the database is 0 ("Not synchronizing").
|
|
1 = Partially healthy. A database on a synchronous-commit availability replica is considered partially healthy if synchronization_state is 1 ("Synchronizing").
|
|
2 = Healthy. A database on an synchronous-commit availability replica is considered healthy if synchronization_state is 2 ("Synchronized"), and a database on an asynchronous-commit availability replica is considered healthy if synchronization_state is 1 ("Synchronizing").
|
|
valuemap:
|
|
name: 'MSSQL AG Synchronization health'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.dbname==''{#DBNAME}'' && @.group_name==''{#GROUP_NAME}'')].synchronization_health.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_local_db,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: component
|
|
value: local-db
|
|
- tag: local-db
|
|
value: '{#DBNAME}'
|
|
trigger_prototypes:
|
|
- uuid: e51c10f9d86d458da9fdb7e21b4e0102
|
|
expression: 'last(/MSSQL by ODBC/mssql.local_db.synchronization_health["{#DBNAME}"])=0'
|
|
name: 'MSSQL: AG ''{#GROUP_NAME}'' Local DB ''{#DBNAME}'': "{#DBNAME}" is Not healthy'
|
|
priority: HIGH
|
|
description: 'The synchronization state of the local availability database is "Not synchronizing".'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: local-db
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: dc636f10f12c4f1ead597eca778561ec
|
|
expression: 'last(/MSSQL by ODBC/mssql.local_db.synchronization_health["{#DBNAME}"])=1'
|
|
name: 'MSSQL: AG ''{#GROUP_NAME}'' Local DB ''{#DBNAME}'': "{#DBNAME}" is Partially healthy'
|
|
priority: AVERAGE
|
|
description: 'A database on a synchronous-commit availability replica is considered partially healthy if synchronization state is "Synchronizing".'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: local-db
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: availability
|
|
master_item:
|
|
key: 'db.odbc.get[get_local_db,"{$MSSQL.DSN}"]'
|
|
lld_macro_paths:
|
|
- lld_macro: '{#DBNAME}'
|
|
path: $.dbname
|
|
- lld_macro: '{#GROUP_NAME}'
|
|
path: $.group_name
|
|
preprocessing:
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1d
|
|
- uuid: d247eb5485f145ff9487424371a802b7
|
|
name: 'Mirroring discovery'
|
|
type: DEPENDENT
|
|
key: mssql.mirroring.discovery
|
|
delay: '0'
|
|
description: 'To see the row for a database other than master or tempdb, you must either be the database owner or have at least ALTER ANY DATABASE or VIEW ANY DATABASE server-level permission or CREATE DATABASE permission in the master database. To see non-NULL values on a mirror database, you must be a member of the sysadmin fixed server role.'
|
|
item_prototypes:
|
|
- uuid: faccb61c8215482ab375c03444a0e8fd
|
|
name: 'MSSQL Mirroring ''{#DBNAME}'': Role'
|
|
type: DEPENDENT
|
|
key: 'mssql.mirroring.role["{#DBNAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
Current role of the local database plays in the database mirroring session.
|
|
1 = Principal
|
|
2 = Mirror
|
|
valuemap:
|
|
name: 'MSSQL Mirroring Role'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.dbname==''{#DBNAME}'')].mirroring_role.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_db_mirroring,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: mssql-mirroring
|
|
- tag: mssql-mirroring
|
|
value: '{#DBNAME}'
|
|
- uuid: 19cdd945ced447d28b7480c07c1f61bc
|
|
name: 'MSSQL Mirroring ''{#DBNAME}'': Role sequence'
|
|
type: DEPENDENT
|
|
key: 'mssql.mirroring.role_sequence["{#DBNAME}"]'
|
|
delay: '0'
|
|
description: 'The number of times that mirroring partners have switched the principal and mirror roles due to a failover or forced service.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.dbname==''{#DBNAME}'')].mirroring_role_sequence.first()'
|
|
- type: SIMPLE_CHANGE
|
|
parameters:
|
|
- ''
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_db_mirroring,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: mssql-mirroring
|
|
- tag: mssql-mirroring
|
|
value: '{#DBNAME}'
|
|
- uuid: bedb23d788074424afdb2751c8a7e2b7
|
|
name: 'MSSQL Mirroring ''{#DBNAME}'': Safety level'
|
|
type: DEPENDENT
|
|
key: 'mssql.mirroring.safety_level["{#DBNAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
Safety setting for updates on the mirror database:
|
|
0 = Unknown state
|
|
1 = Off [asynchronous]
|
|
2 = Full [synchronous]
|
|
valuemap:
|
|
name: 'MSSQL Safety level'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.dbname==''{#DBNAME}'')].mirroring_safety_level.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_db_mirroring,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: mssql-mirroring
|
|
- tag: mssql-mirroring
|
|
value: '{#DBNAME}'
|
|
- uuid: 5df06f00a6194242af45ff5cccf43988
|
|
name: 'MSSQL Mirroring ''{#DBNAME}'': State'
|
|
type: DEPENDENT
|
|
key: 'mssql.mirroring.state["{#DBNAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
State of the mirror database and of the database mirroring session.
|
|
0 = Suspended
|
|
1 = Disconnected from the other partner
|
|
2 = Synchronizing
|
|
3 = Pending failover
|
|
4 = Synchronized
|
|
5 = The partners are not synchronized. Failover is not possible now.
|
|
6 = The partners are synchronized. Failover is potentially possible. For information about the requirements for the failover, see Database Mirroring Operating Modes: https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-operating-modes?view=sql-server-ver16.
|
|
valuemap:
|
|
name: 'MSSQL Mirroring State'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.dbname==''{#DBNAME}'')].mirroring_state.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_db_mirroring,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: mssql-mirroring
|
|
- tag: mssql-mirroring
|
|
value: '{#DBNAME}'
|
|
trigger_prototypes:
|
|
- uuid: 323543b0df0e474abf9190a9630242b1
|
|
expression: 'last(/MSSQL by ODBC/mssql.mirroring.state["{#DBNAME}"])=3'
|
|
name: 'MSSQL: Mirroring ''{#DBNAME}'': "{#DBNAME}" is {ITEM.VALUE}'
|
|
priority: WARNING
|
|
description: 'The state of the mirror database and of the database mirroring session is "Pending failover".'
|
|
tags:
|
|
- tag: mssql-mirroring
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: 9a2bcdfb32654a55b8a75c8bf4331950
|
|
expression: 'last(/MSSQL by ODBC/mssql.mirroring.state["{#DBNAME}"])=5'
|
|
name: 'MSSQL: Mirroring ''{#DBNAME}'': "{#DBNAME}" is {ITEM.VALUE}'
|
|
priority: HIGH
|
|
description: 'The state of the mirror database and of the database mirroring session is "Not synchronized". The partners are not synchronized. A failover is not possible now.'
|
|
tags:
|
|
- tag: mssql-mirroring
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: c673a7c4820840528a4aa1771a5a56d4
|
|
expression: 'last(/MSSQL by ODBC/mssql.mirroring.state["{#DBNAME}"])>=0 and last(/MSSQL by ODBC/mssql.mirroring.state["{#DBNAME}"])<=2'
|
|
name: 'MSSQL: Mirroring ''{#DBNAME}'': "{#DBNAME}" is {ITEM.VALUE}'
|
|
priority: INFO
|
|
description: 'The state of the mirror database and of the database mirroring session is "Suspended", "Disconnected from the other partner", or "Synchronizing".'
|
|
tags:
|
|
- tag: mssql-mirroring
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: 7e4763d493a745e99082ca71a54c23c2
|
|
name: 'MSSQL Mirroring ''{#DBNAME}'': Witness state'
|
|
type: DEPENDENT
|
|
key: 'mssql.mirroring.witness_state["{#DBNAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
State of the witness in the database mirroring session of the database:
|
|
0 = Unknown
|
|
1 = Connected
|
|
2 = Disconnected
|
|
valuemap:
|
|
name: 'MSSQL Witness state'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.dbname==''{#DBNAME}'')].mirroring_witness_state.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_db_mirroring,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: mssql-mirroring
|
|
- tag: mssql-mirroring
|
|
value: '{#DBNAME}'
|
|
trigger_prototypes:
|
|
- uuid: ec374424b79346c99f1d815e0815c799
|
|
expression: 'last(/MSSQL by ODBC/mssql.mirroring.witness_state["{#DBNAME}"])=2'
|
|
name: 'MSSQL: Mirroring ''{#DBNAME}'': "{#DBNAME}" Witness is disconnected'
|
|
priority: WARNING
|
|
description: 'The state of the witness in the database mirroring session of the database is "Disconnected".'
|
|
tags:
|
|
- tag: mssql-mirroring
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: availability
|
|
graph_prototypes:
|
|
- uuid: 9bf9203bd4b449b39e288fbb79f9d627
|
|
name: 'Mirroring ''{#DBNAME}'': States'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: 'mssql.mirroring.state["{#DBNAME}"]'
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: 'mssql.mirroring.witness_state["{#DBNAME}"]'
|
|
master_item:
|
|
key: 'db.odbc.get[get_db_mirroring,"{$MSSQL.DSN}"]'
|
|
lld_macro_paths:
|
|
- lld_macro: '{#DBNAME}'
|
|
path: $.dbname
|
|
preprocessing:
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1d
|
|
- uuid: dcf4eae19c7043fc85f7ec414502ef84
|
|
name: 'Non-local database discovery'
|
|
type: DEPENDENT
|
|
key: mssql.non.local.db.discovery
|
|
delay: '0'
|
|
description: 'Discovery of the non-local (not local to SQL Server instance) availability databases.'
|
|
item_prototypes:
|
|
- uuid: 32d3797d6b22413e98aa206d3092947f
|
|
name: 'MSSQL AG ''{#GROUP_NAME}'' Non-Local DB ''*{#REPLICA_NAME}*{#DBNAME}'': Log queue size'
|
|
type: DEPENDENT
|
|
key: 'mssql.non-local_db.log_send_queue_size["{#GROUP_NAME}*{#REPLICA_NAME}*{#DBNAME}"]'
|
|
delay: '0'
|
|
units: B
|
|
description: 'Amount of the log records of the primary database that has not been sent to the secondary databases.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.dbname==''{#DBNAME}'' && @.group_name==''{#GROUP_NAME}'' && @.replica_name==''{#REPLICA_NAME}'')].log_send_queue_size.first()'
|
|
- type: MULTIPLIER
|
|
parameters:
|
|
- '1024'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_non_local_db,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: component
|
|
value: non-local-db
|
|
- tag: non-local-db
|
|
value: '{#DBNAME}'
|
|
trigger_prototypes:
|
|
- uuid: def3b1d47dac4d039bffcc78f955b433
|
|
expression: 'last(/MSSQL by ODBC/mssql.non-local_db.log_send_queue_size["{#GROUP_NAME}*{#REPLICA_NAME}*{#DBNAME}"],#1)>last(/MSSQL by ODBC/mssql.non-local_db.log_send_queue_size["{#GROUP_NAME}*{#REPLICA_NAME}*{#DBNAME}"],#2) and last(/MSSQL by ODBC/mssql.non-local_db.log_send_queue_size["{#GROUP_NAME}*{#REPLICA_NAME}*{#DBNAME}"],#2)>last(/MSSQL by ODBC/mssql.non-local_db.log_send_queue_size["{#GROUP_NAME}*{#REPLICA_NAME}*{#DBNAME}"],#3)'
|
|
name: 'MSSQL: AG ''{#GROUP_NAME}'' Non-Local DB ''*{#REPLICA_NAME}*{#DBNAME}'': Log queue size is growing'
|
|
priority: HIGH
|
|
description: 'The log records of the primary database are not sent to the secondary databases.'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: non-local-db
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: capacity
|
|
- uuid: 66d9735553754192bb9acf7b27c1501f
|
|
name: 'MSSQL AG ''{#GROUP_NAME}'' Non-Local DB ''*{#REPLICA_NAME}*{#DBNAME}'': Redo log queue size'
|
|
type: DEPENDENT
|
|
key: 'mssql.non-local_db.redo_queue_size["{#GROUP_NAME}*{#REPLICA_NAME}*{#DBNAME}"]'
|
|
delay: '0'
|
|
units: B
|
|
description: 'Amount of log records in the log files of the secondary replica that has not yet been redone.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.dbname==''{#DBNAME}'' && @.group_name==''{#GROUP_NAME}'' && @.replica_name==''{#REPLICA_NAME}'')].redo_queue_size.first()'
|
|
- type: MULTIPLIER
|
|
parameters:
|
|
- '1024'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_non_local_db,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: component
|
|
value: non-local-db
|
|
- tag: non-local-db
|
|
value: '{#DBNAME}'
|
|
trigger_prototypes:
|
|
- uuid: 2102f244f58b4fe981d569fe0fe599a9
|
|
expression: 'last(/MSSQL by ODBC/mssql.non-local_db.redo_queue_size["{#GROUP_NAME}*{#REPLICA_NAME}*{#DBNAME}"],#1)>last(/MSSQL by ODBC/mssql.non-local_db.redo_queue_size["{#GROUP_NAME}*{#REPLICA_NAME}*{#DBNAME}"],#2) and last(/MSSQL by ODBC/mssql.non-local_db.redo_queue_size["{#GROUP_NAME}*{#REPLICA_NAME}*{#DBNAME}"],#2)>last(/MSSQL by ODBC/mssql.non-local_db.redo_queue_size["{#GROUP_NAME}*{#REPLICA_NAME}*{#DBNAME}"],#3)'
|
|
name: 'MSSQL: AG ''{#GROUP_NAME}'' Non-Local DB ''*{#REPLICA_NAME}*{#DBNAME}'': Redo log queue size is growing'
|
|
priority: HIGH
|
|
description: 'The log records in the log files of the secondary replica have not yet been redone.'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: non-local-db
|
|
value: '{#DBNAME}'
|
|
- tag: scope
|
|
value: capacity
|
|
master_item:
|
|
key: 'db.odbc.get[get_non_local_db,"{$MSSQL.DSN}"]'
|
|
lld_macro_paths:
|
|
- lld_macro: '{#DBNAME}'
|
|
path: $.dbname
|
|
- lld_macro: '{#GROUP_NAME}'
|
|
path: $.group_name
|
|
- lld_macro: '{#REPLICA_NAME}'
|
|
path: $.replica_name
|
|
preprocessing:
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1d
|
|
- uuid: baed17bf5a4846439e720388c9ae7c17
|
|
name: 'Quorum discovery'
|
|
type: DEPENDENT
|
|
key: mssql.quorum.discovery
|
|
delay: '0'
|
|
filter:
|
|
evaltype: AND
|
|
conditions:
|
|
- macro: '{#CLUSTER_NAME}'
|
|
value: ^.+$
|
|
formulaid: A
|
|
description: 'Discovery of the quorum of the WSFC cluster.'
|
|
item_prototypes:
|
|
- uuid: 6c30ce79872d4bb2b27965acd277e1e9
|
|
name: 'MSSQL Cluster ''{#CLUSTER_NAME}'': Quorum state'
|
|
type: DEPENDENT
|
|
key: 'mssql.quorum.state.[{#CLUSTER_NAME}]'
|
|
delay: '0'
|
|
description: |
|
|
State of the WSFC quorum, one of:
|
|
0 = Unknown quorum state
|
|
1 = Normal quorum
|
|
2 = Forced quorum
|
|
valuemap:
|
|
name: 'MSSQL Quorum state'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.cluster_name==''{#CLUSTER_NAME}'')].quorum_state.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_quorum,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: cluster-name
|
|
value: '{#CLUSTER_NAME}'
|
|
- tag: component
|
|
value: quorum
|
|
- uuid: 4d7f29272d1c4889bae0aa50bcd31e9c
|
|
name: 'MSSQL Cluster ''{#CLUSTER_NAME}'': Quorum type'
|
|
type: DEPENDENT
|
|
key: 'mssql.quorum.type.[{#CLUSTER_NAME}]'
|
|
delay: '0'
|
|
description: |
|
|
Type of quorum used by this WSFC cluster, one of:
|
|
0 = Node Majority. This quorum configuration can sustain failures of half the nodes (rounding up) minus one.
|
|
1 = Node and Disk Majority. If the disk witness remains on line, this quorum configuration can sustain failures of half the nodes (rounding up).
|
|
2 = Node and File Share Majority. This quorum configuration works in a similar way to Node and Disk Majority, but uses a file-share witness instead of a disk witness.
|
|
3 = No Majority: Disk Only. If the quorum disk is online, this quorum configuration can sustain failures of all nodes except one.
|
|
4 = Unknown Quorum. Unknown quorum for the cluster.
|
|
5 = Cloud Witness. Cluster utilizes Microsoft Azure for quorum arbitration. If the cloud witness is available, the cluster can sustain the failure of half the nodes (rounding up).
|
|
valuemap:
|
|
name: 'MSSQL Quorum type'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.cluster_name==''{#CLUSTER_NAME}'')].quorum_type.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1d
|
|
master_item:
|
|
key: 'db.odbc.get[get_quorum,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: cluster-name
|
|
value: '{#CLUSTER_NAME}'
|
|
- tag: component
|
|
value: quorum
|
|
master_item:
|
|
key: 'db.odbc.get[get_quorum,"{$MSSQL.DSN}"]'
|
|
lld_macro_paths:
|
|
- lld_macro: '{#CLUSTER_NAME}'
|
|
path: $.cluster_name
|
|
preprocessing:
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1d
|
|
- uuid: 1ea73914e10e407abe3b4e715f6759b4
|
|
name: 'Quorum members discovery'
|
|
type: DEPENDENT
|
|
key: mssql.quorum.member.discovery
|
|
delay: '0'
|
|
filter:
|
|
evaltype: AND
|
|
conditions:
|
|
- macro: '{#CLUSTER_NAME}'
|
|
value: ^.+$
|
|
formulaid: A
|
|
- macro: '{#MEMBER_NAME}'
|
|
value: '{$MSSQL.QUORUM.MEMBER.DISCOVERY.NAME.MATCHES}'
|
|
formulaid: B
|
|
- macro: '{#MEMBER_NAME}'
|
|
value: '{$MSSQL.QUORUM.MEMBER.DISCOVERY.NAME.NOT_MATCHES}'
|
|
operator: NOT_MATCHES_REGEX
|
|
formulaid: C
|
|
description: 'Discovery of the quorum members of the WSFC cluster.'
|
|
item_prototypes:
|
|
- uuid: 0dc6b43da4854b1cbb41ad0d5c5079a9
|
|
name: 'MSSQL Cluster member ''{#MEMBER_NAME}'': Member state'
|
|
type: DEPENDENT
|
|
key: 'mssql.quorum_members.member_state.[{#MEMBER_NAME}]'
|
|
delay: '0'
|
|
description: |
|
|
The member state, one of:
|
|
0 = Offline
|
|
1 = Online
|
|
valuemap:
|
|
name: 'MSSQL Quorum members state'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.member_name==''{#MEMBER_NAME}'')].member_state.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_quorum_member,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: 'quorum members'
|
|
- tag: quorum-members-name
|
|
value: '{#MEMBER_NAME}'
|
|
- uuid: 337cd4bfedc846048fc366bb7cf1bebd
|
|
name: 'MSSQL Cluster member ''{#MEMBER_NAME}'': Member type'
|
|
type: DEPENDENT
|
|
key: 'mssql.quorum_members.member_type.[{#MEMBER_NAME}]'
|
|
delay: '0'
|
|
description: |
|
|
The type of member, one of:
|
|
0 = WSFC node
|
|
1 = Disk witness
|
|
2 = File share witness
|
|
3 = Cloud Witness
|
|
valuemap:
|
|
name: 'MSSQL Quorum members type'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.member_name==''{#MEMBER_NAME}'')].member_type.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1d
|
|
master_item:
|
|
key: 'db.odbc.get[get_quorum_member,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: 'quorum members'
|
|
- tag: quorum-members-name
|
|
value: '{#MEMBER_NAME}'
|
|
- uuid: d8fff68f86494892bbe1cb2c6b281075
|
|
name: 'MSSQL Cluster member ''{#MEMBER_NAME}'': Number of quorum votes'
|
|
type: DEPENDENT
|
|
key: 'mssql.quorum_members.number_of_quorum_votes.[{#MEMBER_NAME}]'
|
|
delay: '0'
|
|
description: 'Number of quorum votes possessed by this quorum member.'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.member_name==''{#MEMBER_NAME}'')].number_of_quorum_votes.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1d
|
|
master_item:
|
|
key: 'db.odbc.get[get_quorum_member,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: component
|
|
value: 'quorum members'
|
|
- tag: quorum-members-name
|
|
value: '{#MEMBER_NAME}'
|
|
master_item:
|
|
key: 'db.odbc.get[get_quorum_member,"{$MSSQL.DSN}"]'
|
|
lld_macro_paths:
|
|
- lld_macro: '{#CLUSTER_NAME}'
|
|
path: $.cluster_name
|
|
- lld_macro: '{#MEMBER_NAME}'
|
|
path: $.member_name
|
|
preprocessing:
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1d
|
|
- uuid: 024656ffe254416781cb4ad52ae456ea
|
|
name: 'Replication discovery'
|
|
type: DEPENDENT
|
|
key: mssql.replica.discovery
|
|
delay: '0'
|
|
description: 'Discovery of the database replicas.'
|
|
item_prototypes:
|
|
- uuid: 7403de703b024d428edfc59f3aa9181b
|
|
name: 'MSSQL AG ''{#GROUP_NAME}'' Replica ''{#REPLICA_NAME}'': Connected state'
|
|
type: DEPENDENT
|
|
key: 'mssql.replica.connected_state["{#GROUP_NAME}_{#REPLICA_NAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
Whether a secondary replica is currently connected to the primary replica:
|
|
0 = Disconnected. The response of an availability replica to the "Disconnected" state depends on its role:
|
|
On the primary replica, if a secondary replica is disconnected, its secondary databases are marked as "Not synchronized" on the primary replica, which waits for the secondary to reconnect;
|
|
On a secondary replica, upon detecting that it is disconnected, the secondary replica attempts to reconnect to the primary replica.
|
|
1 = Connected. Each primary replica tracks the connection state for every secondary replica in the same availability group. Secondary replicas track the connection state of only the primary replica.
|
|
valuemap:
|
|
name: 'MSSQL AG Connected state'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.replica_name==''{#REPLICA_NAME}'' && @.group_name==''{#GROUP_NAME}'')].connected_state.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_replica,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: component
|
|
value: replica
|
|
- tag: replica
|
|
value: '{#REPLICA_NAME}'
|
|
- uuid: 51cb8f4fe5954c0ea21eff6102034862
|
|
name: 'MSSQL AG ''{#GROUP_NAME}'' Replica ''{#REPLICA_NAME}'': Is local'
|
|
type: DEPENDENT
|
|
key: 'mssql.replica.is_local["{#GROUP_NAME}_{#REPLICA_NAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
Whether the replica is local:
|
|
0 = Indicates a remote secondary replica in an availability group whose primary replica is hosted by the local server instance. This value occurs only on the primary replica location.
|
|
1 = Indicates a local replica. On secondary replicas, this is the only available value for the availability group to which the replica belongs.
|
|
valuemap:
|
|
name: 'MSSQL - Yes / No'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.replica_name==''{#REPLICA_NAME}'' && @.group_name==''{#GROUP_NAME}'')].is_local.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_replica,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: component
|
|
value: replica
|
|
- tag: replica
|
|
value: '{#REPLICA_NAME}'
|
|
- uuid: ec1b71f75c4247c28d5830bd8004f0ac
|
|
name: 'MSSQL AG ''{#GROUP_NAME}'' Replica ''{#REPLICA_NAME}'': Join state'
|
|
type: DEPENDENT
|
|
key: 'mssql.replica.join_state["{#GROUP_NAME}_{#REPLICA_NAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
0 = Not joined
|
|
1 = Joined, standalone instance
|
|
2 = Joined, failover cluster instance
|
|
valuemap:
|
|
name: 'MSSQL AG Join state'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.replica_name==''{#REPLICA_NAME}'' && @.group_name==''{#GROUP_NAME}'')].join_state.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_replica,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: component
|
|
value: replica
|
|
- tag: replica
|
|
value: '{#REPLICA_NAME}'
|
|
- uuid: ad3e6d5bf510496eb050b9a05f8341e1
|
|
name: 'MSSQL AG ''{#GROUP_NAME}'' Replica ''{#REPLICA_NAME}'': Operational state'
|
|
type: DEPENDENT
|
|
key: 'mssql.replica.operational_state["{#GROUP_NAME}_{#REPLICA_NAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
Current operational state of the replica:
|
|
0 = Pending failover
|
|
1 = Pending
|
|
2 = Online
|
|
3 = Offline
|
|
4 = Failed
|
|
5 = Failed, no quorum
|
|
6 = Not local
|
|
valuemap:
|
|
name: 'MSSQL AG Operational state'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.replica_name==''{#REPLICA_NAME}'' && @.group_name==''{#GROUP_NAME}'')].operational_state.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_replica,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: component
|
|
value: replica
|
|
- tag: replica
|
|
value: '{#REPLICA_NAME}'
|
|
trigger_prototypes:
|
|
- uuid: 0192e9d5795a4d46acb8de29f710edcd
|
|
expression: 'last(/MSSQL by ODBC/mssql.replica.operational_state["{#GROUP_NAME}_{#REPLICA_NAME}"])=0 or last(/MSSQL by ODBC/mssql.replica.operational_state["{#GROUP_NAME}_{#REPLICA_NAME}"])=1 or last(/MSSQL by ODBC/mssql.replica.operational_state["{#GROUP_NAME}_{#REPLICA_NAME}"])=3'
|
|
name: 'MSSQL: AG ''{#GROUP_NAME}'' Replica ''{#REPLICA_NAME}'': {#REPLICA_NAME} is {ITEM.VALUE}'
|
|
priority: WARNING
|
|
description: 'The operational state of the replica in a given availability group is "Pending" or "Offline".'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: replica
|
|
value: '{#REPLICA_NAME}'
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: 3f2006b9ae114e01b442b34fec9373d3
|
|
expression: 'last(/MSSQL by ODBC/mssql.replica.operational_state["{#GROUP_NAME}_{#REPLICA_NAME}"])=4'
|
|
name: 'MSSQL: AG ''{#GROUP_NAME}'' Replica ''{#REPLICA_NAME}'': {#REPLICA_NAME} is {ITEM.VALUE}'
|
|
priority: AVERAGE
|
|
description: 'The operational state of the replica in a given availability group is "Failed".'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: replica
|
|
value: '{#REPLICA_NAME}'
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: 94ea79848a5048d8ae417df709b8d612
|
|
expression: 'last(/MSSQL by ODBC/mssql.replica.operational_state["{#GROUP_NAME}_{#REPLICA_NAME}"])=5'
|
|
name: 'MSSQL: AG ''{#GROUP_NAME}'' Replica ''{#REPLICA_NAME}'': {#REPLICA_NAME} is {ITEM.VALUE}'
|
|
priority: HIGH
|
|
description: 'The operational state of the replica in a given availability group is "Failed, no quorum".'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: replica
|
|
value: '{#REPLICA_NAME}'
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: 2a58cab2197348d997bdb69bb128414d
|
|
name: 'MSSQL AG ''{#GROUP_NAME}'' Replica ''{#REPLICA_NAME}'': Recovery health'
|
|
type: DEPENDENT
|
|
key: 'mssql.replica.recovery_health["{#GROUP_NAME}_{#REPLICA_NAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
Rollup of the "database_state" column of the `sys.dm_hadr_database_replica_states` dynamic management view:
|
|
0 = In progress. At least one joined database has a database state other than "Online" (database_state is not "0").
|
|
1 = Online. All the joined databases have a database state of "Online" (database_state is "0").
|
|
valuemap:
|
|
name: 'MSSQL AG Recovery health'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.replica_name==''{#REPLICA_NAME}'' && @.group_name==''{#GROUP_NAME}'')].recovery_health.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_replica,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: component
|
|
value: replica
|
|
- tag: replica
|
|
value: '{#REPLICA_NAME}'
|
|
trigger_prototypes:
|
|
- uuid: 58f508b855364d769e2be977793a03c8
|
|
expression: 'last(/MSSQL by ODBC/mssql.replica.recovery_health["{#GROUP_NAME}_{#REPLICA_NAME}"])=0'
|
|
name: 'MSSQL: AG ''{#GROUP_NAME}'' Replica ''{#REPLICA_NAME}'': {#REPLICA_NAME} Recovery in progress'
|
|
priority: INFO
|
|
description: 'At least one joined database has a database state other than "Online".'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: replica
|
|
value: '{#REPLICA_NAME}'
|
|
- tag: scope
|
|
value: notice
|
|
- uuid: 5139a4cef5ac46d2ab34cd8bda1c84ac
|
|
name: 'MSSQL AG ''{#GROUP_NAME}'' Replica ''{#REPLICA_NAME}'': Role'
|
|
type: DEPENDENT
|
|
key: 'mssql.replica.role["{#GROUP_NAME}_{#REPLICA_NAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
Current Always On availability group role of a local replica or a connected remote replica:
|
|
0 = Resolving
|
|
1 = Primary
|
|
2 = Secondary
|
|
valuemap:
|
|
name: 'MSSQL AG Role'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.replica_name==''{#REPLICA_NAME}'' && @.group_name==''{#GROUP_NAME}'')].role.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_replica,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: component
|
|
value: replica
|
|
- tag: replica
|
|
value: '{#REPLICA_NAME}'
|
|
- uuid: 5655f95df0e043acba246f65e4905b0d
|
|
name: 'MSSQL AG ''{#GROUP_NAME}'' Replica ''{#REPLICA_NAME}'': Sync health'
|
|
type: DEPENDENT
|
|
key: 'mssql.replica.synchronization_health["{#GROUP_NAME}_{#REPLICA_NAME}"]'
|
|
delay: '0'
|
|
description: |
|
|
Reflects a rollup of the database synchronization state (synchronization_state) of all joined availability databases (also known as replicas) and the availability mode of the replica (synchronous-commit or asynchronous-commit mode). The rollup will reflect the least healthy accumulated state of the databases on the replica:
|
|
0 = Not healthy. At least one joined database is in the "Not synchronizing" state.
|
|
1 = Partially healthy. Some replicas are not in the target synchronization state: synchronous-commit replicas should be synchronized, and asynchronous-commit replicas should be synchronizing.
|
|
2 = Healthy. All replicas are in the target synchronization state: synchronous-commit replicas are synchronized, and asynchronous-commit replicas are synchronizing.
|
|
valuemap:
|
|
name: 'MSSQL AG Synchronization health'
|
|
preprocessing:
|
|
- type: JSONPATH
|
|
parameters:
|
|
- '$[?(@.replica_name==''{#REPLICA_NAME}'' && @.group_name==''{#GROUP_NAME}'')].synchronization_health.first()'
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1h
|
|
master_item:
|
|
key: 'db.odbc.get[get_replica,"{$MSSQL.DSN}"]'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: component
|
|
value: replica
|
|
- tag: replica
|
|
value: '{#REPLICA_NAME}'
|
|
trigger_prototypes:
|
|
- uuid: f2f4d198ad2242088d867abe6fac4ecd
|
|
expression: 'last(/MSSQL by ODBC/mssql.replica.synchronization_health["{#GROUP_NAME}_{#REPLICA_NAME}"])=0'
|
|
name: 'MSSQL: AG ''{#GROUP_NAME}'' Replica ''{#REPLICA_NAME}'': {#REPLICA_NAME} is Not healthy'
|
|
priority: AVERAGE
|
|
description: 'At least one joined database is in the "Not synchronizing" state.'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: replica
|
|
value: '{#REPLICA_NAME}'
|
|
- tag: scope
|
|
value: availability
|
|
- uuid: 070af612aa7248588fc058d8f9fb9929
|
|
expression: 'last(/MSSQL by ODBC/mssql.replica.synchronization_health["{#GROUP_NAME}_{#REPLICA_NAME}"])=1'
|
|
name: 'MSSQL: AG ''{#GROUP_NAME}'' Replica ''{#REPLICA_NAME}'': {#REPLICA_NAME} is Partially healthy'
|
|
priority: WARNING
|
|
description: 'Some replicas are not in the target synchronization state: synchronous-commit replicas should be synchronized, and asynchronous-commit replicas should be synchronizing.'
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: replica
|
|
value: '{#REPLICA_NAME}'
|
|
- tag: scope
|
|
value: availability
|
|
trigger_prototypes:
|
|
- uuid: 7c6afc5596e94af0979bd20d5ef542ea
|
|
expression: 'last(/MSSQL by ODBC/mssql.replica.connected_state["{#GROUP_NAME}_{#REPLICA_NAME}"])=0 and last(/MSSQL by ODBC/mssql.replica.role["{#GROUP_NAME}_{#REPLICA_NAME}"])=2'
|
|
name: 'MSSQL: AG ''{#GROUP_NAME}'' Replica ''{#REPLICA_NAME}'': {#REPLICA_NAME} is disconnected'
|
|
priority: WARNING
|
|
description: |
|
|
The response of an availability replica to the "Disconnected" state depends on its role:
|
|
On the primary replica, if a secondary replica is disconnected, its secondary databases are marked as "Not synchronized" on the primary replica, which waits for the secondary to reconnect;
|
|
On a secondary replica, upon detecting that it is disconnected, the secondary replica attempts to reconnect to the primary replica.
|
|
tags:
|
|
- tag: availability-group
|
|
value: '{#GROUP_NAME}'
|
|
- tag: replica
|
|
value: '{#REPLICA_NAME}'
|
|
- tag: scope
|
|
value: availability
|
|
master_item:
|
|
key: 'db.odbc.get[get_replica,"{$MSSQL.DSN}"]'
|
|
lld_macro_paths:
|
|
- lld_macro: '{#GROUP_NAME}'
|
|
path: $.group_name
|
|
- lld_macro: '{#REPLICA_NAME}'
|
|
path: $.replica_name
|
|
preprocessing:
|
|
- type: DISCARD_UNCHANGED_HEARTBEAT
|
|
parameters:
|
|
- 1d
|
|
tags:
|
|
- tag: class
|
|
value: database
|
|
- tag: target
|
|
value: mssql
|
|
macros:
|
|
- macro: '{$MSSQL.AVERAGE_WAIT_TIME.MAX}'
|
|
value: '500'
|
|
description: 'The maximum average wait time, in milliseconds - for the trigger expression.'
|
|
- macro: '{$MSSQL.BACKUP_DIFF.CRIT}'
|
|
value: 6d
|
|
description: 'The maximum of days without a differential backup - for the High trigger expression.'
|
|
- macro: '{$MSSQL.BACKUP_DIFF.USED}'
|
|
value: '1'
|
|
description: 'The flag for checking the age of a differential backup. If set to a value other than "1", the trigger expression for the differential backup age will not fire. Can be used with context for database name.'
|
|
- macro: '{$MSSQL.BACKUP_DIFF.WARN}'
|
|
value: 3d
|
|
description: 'The maximum of days without a differential backup - for the Warning trigger expression.'
|
|
- macro: '{$MSSQL.BACKUP_FULL.CRIT}'
|
|
value: 10d
|
|
description: 'The maximum of days without a full backup - for the High trigger expression.'
|
|
- macro: '{$MSSQL.BACKUP_FULL.USED}'
|
|
value: '1'
|
|
description: 'The flag for checking the age of a full backup. If set to a value other than "1", the trigger expression for the full backup age will not fire. Can be used with context for database name.'
|
|
- macro: '{$MSSQL.BACKUP_FULL.WARN}'
|
|
value: 9d
|
|
description: 'The maximum of days without a full backup - for the Warning trigger expression.'
|
|
- macro: '{$MSSQL.BACKUP_LOG.CRIT}'
|
|
value: 8h
|
|
description: 'The maximum of days without a log backup - for the High trigger expression.'
|
|
- macro: '{$MSSQL.BACKUP_LOG.USED}'
|
|
value: '1'
|
|
description: 'The flag for checking the age of a log backup. If set to a value other than "1", the trigger expression for the log backup age will not fire. Can be used with context for database name.'
|
|
- macro: '{$MSSQL.BACKUP_LOG.WARN}'
|
|
value: 4h
|
|
description: 'The maximum of days without a log backup - for the Warning trigger expression.'
|
|
- macro: '{$MSSQL.BUFFER_CACHE_RATIO.MIN.CRIT}'
|
|
value: '30'
|
|
description: 'The minimum buffer cache hit ratio, in percent - for the High trigger expression.'
|
|
- macro: '{$MSSQL.BUFFER_CACHE_RATIO.MIN.WARN}'
|
|
value: '50'
|
|
description: 'The minimum buffer cache hit ratio, in percent - for the Warning trigger expression.'
|
|
- macro: '{$MSSQL.DBNAME.MATCHES}'
|
|
value: '.*'
|
|
description: 'This macro is used in database discovery. It can be overridden on the host or linked template level.'
|
|
- macro: '{$MSSQL.DBNAME.NOT_MATCHES}'
|
|
value: master|tempdb|model|msdb
|
|
description: 'This macro is used in database discovery. It can be overridden on the host or linked template level.'
|
|
- macro: '{$MSSQL.DEADLOCKS.MAX}'
|
|
value: '1'
|
|
description: 'The maximum deadlocks per second - for the trigger expression.'
|
|
- macro: '{$MSSQL.DSN}'
|
|
value: '<Put your DSN here>'
|
|
description: 'System data source name.'
|
|
- macro: '{$MSSQL.FREE_LIST_STALLS.MAX}'
|
|
value: '2'
|
|
description: 'The maximum free list stalls per second - for the trigger expression.'
|
|
- macro: '{$MSSQL.HOST}'
|
|
value: localhost
|
|
description: 'The hostname or IP address of the MSSQL instance.'
|
|
- macro: '{$MSSQL.JOB.MATCHES}'
|
|
value: '.*'
|
|
description: 'This macro is used in job discovery. It can be overridden on the host or linked template level.'
|
|
- macro: '{$MSSQL.JOB.NOT_MATCHES}'
|
|
value: CHANGE_IF_NEEDED
|
|
description: 'This macro is used in job discovery. It can be overridden on the host or linked template level.'
|
|
- macro: '{$MSSQL.JOB_DURATION.WARN}'
|
|
value: 1h
|
|
description: 'The maximum job duration - for the Warning trigger expression.'
|
|
- macro: '{$MSSQL.LAZY_WRITES.MAX}'
|
|
value: '20'
|
|
description: 'The maximum lazy writes per second - for the trigger expression.'
|
|
- macro: '{$MSSQL.LOCK_REQUESTS.MAX}'
|
|
value: '1000'
|
|
description: 'The maximum lock requests per second - for the trigger expression.'
|
|
- macro: '{$MSSQL.LOCK_TIMEOUTS.MAX}'
|
|
value: '1'
|
|
description: 'The maximum lock timeouts per second - for the trigger expression.'
|
|
- macro: '{$MSSQL.LOG_FLUSH_WAITS.MAX}'
|
|
value: '1'
|
|
description: 'The maximum log flush waits per second - for the trigger expression.'
|
|
- macro: '{$MSSQL.LOG_FLUSH_WAIT_TIME.MAX}'
|
|
value: '1'
|
|
description: 'The maximum log flush wait time, in milliseconds - for the trigger expression.'
|
|
- macro: '{$MSSQL.PAGE_LIFE_EXPECTANCY.MIN}'
|
|
value: '300'
|
|
description: 'The minimum page life expectancy - for the trigger expression.'
|
|
- macro: '{$MSSQL.PAGE_READS.MAX}'
|
|
value: '90'
|
|
description: 'The maximum page reads per second - for the trigger expression.'
|
|
- macro: '{$MSSQL.PAGE_WRITES.MAX}'
|
|
value: '90'
|
|
description: 'The maximum page writes per second - for the trigger expression.'
|
|
- macro: '{$MSSQL.PASSWORD}'
|
|
value: '<Put your password here>'
|
|
description: 'MSSQL user password.'
|
|
- macro: '{$MSSQL.PERCENT_COMPILATIONS.MAX}'
|
|
value: '10'
|
|
description: 'The maximum percentage of Transact-SQL compilations - for the trigger expression.'
|
|
- macro: '{$MSSQL.PERCENT_LOG_USED.MAX}'
|
|
value: '80'
|
|
description: 'The maximum percentage of log used - for the trigger expression.'
|
|
- macro: '{$MSSQL.PERCENT_READAHEAD.MAX}'
|
|
value: '20'
|
|
description: 'The maximum percentage of pages read per second in anticipation of use - for the trigger expression.'
|
|
- macro: '{$MSSQL.PERCENT_RECOMPILATIONS.MAX}'
|
|
value: '10'
|
|
description: 'The maximum percentage of Transact-SQL recompilations - for the trigger expression.'
|
|
- macro: '{$MSSQL.PORT}'
|
|
value: '1433'
|
|
description: 'MSSQL TCP port.'
|
|
- macro: '{$MSSQL.QUORUM.MEMBER.DISCOVERY.NAME.MATCHES}'
|
|
value: '.*'
|
|
description: 'Filter to include discovered quorum member by name.'
|
|
- macro: '{$MSSQL.QUORUM.MEMBER.DISCOVERY.NAME.NOT_MATCHES}'
|
|
value: CHANGE_IF_NEEDED
|
|
description: 'Filter to exclude discovered quorum member by name.'
|
|
- macro: '{$MSSQL.USER}'
|
|
value: '<Put your username here>'
|
|
description: 'MSSQL username.'
|
|
- macro: '{$MSSQL.WORKTABLES_FROM_CACHE_RATIO.MIN.CRIT}'
|
|
value: '90'
|
|
description: 'The minimum percentage of work tables from the cache ratio - for the High trigger expression.'
|
|
- macro: '{$MSSQL.WORK_FILES.MAX}'
|
|
value: '20'
|
|
description: 'The maximum number of work files created per second - for the trigger expression.'
|
|
- macro: '{$MSSQL.WORK_TABLES.MAX}'
|
|
value: '20'
|
|
description: 'The maximum number of work tables created per second - for the trigger expression.'
|
|
dashboards:
|
|
- uuid: 168d5d7b54374d8a951818ba619e0319
|
|
name: 'MSSQL: Overview'
|
|
pages:
|
|
- name: Main
|
|
widgets:
|
|
- type: graph
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Errors'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAA
|
|
- type: graph
|
|
'y': '5'
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Server memory'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAC
|
|
- type: graph
|
|
'y': '10'
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Waits per second'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAE
|
|
- type: graph
|
|
'y': '15'
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Database pages'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAG
|
|
- type: graph
|
|
'y': '20'
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Transactions'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAI
|
|
- type: graph
|
|
'y': '25'
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Pages'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAK
|
|
- type: graph
|
|
'y': '30'
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Memory grants outstanding and pending'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAM
|
|
- type: graph
|
|
'y': '35'
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Access methods'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAO
|
|
- type: graph
|
|
'y': '40'
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Logins per second and Logouts per second'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAQ
|
|
- type: graph
|
|
x: '36'
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Locks'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAB
|
|
- type: graph
|
|
x: '36'
|
|
'y': '5'
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Memory/cache load'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAD
|
|
- type: graph
|
|
x: '36'
|
|
'y': '10'
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Wait times'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAF
|
|
- type: graph
|
|
x: '36'
|
|
'y': '15'
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Cache objects'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAH
|
|
- type: graph
|
|
x: '36'
|
|
'y': '20'
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Compilations'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAJ
|
|
- type: graph
|
|
x: '36'
|
|
'y': '25'
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Page life expectancy'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAL
|
|
- type: graph
|
|
x: '36'
|
|
'y': '30'
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Processes blocked'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAN
|
|
- type: graph
|
|
x: '36'
|
|
'y': '35'
|
|
width: '36'
|
|
height: '5'
|
|
fields:
|
|
- type: GRAPH
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'MSSQL: Auto-params'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAP
|
|
- name: Databases
|
|
widgets:
|
|
- type: graphprototype
|
|
width: '36'
|
|
height: '15'
|
|
fields:
|
|
- type: INTEGER
|
|
name: columns
|
|
value: '1'
|
|
- type: GRAPH_PROTOTYPE
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'DB ''{#DBNAME}'': Size'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAR
|
|
- type: INTEGER
|
|
name: rows
|
|
value: '3'
|
|
- type: graphprototype
|
|
x: '36'
|
|
width: '36'
|
|
height: '15'
|
|
fields:
|
|
- type: INTEGER
|
|
name: columns
|
|
value: '1'
|
|
- type: GRAPH_PROTOTYPE
|
|
name: graphid.0
|
|
value:
|
|
host: 'MSSQL by ODBC'
|
|
name: 'DB ''{#DBNAME}'': Log size'
|
|
- type: STRING
|
|
name: reference
|
|
value: AAAAS
|
|
- type: INTEGER
|
|
name: rows
|
|
value: '3'
|
|
valuemaps:
|
|
- uuid: c66ee50a8b3f4b6788b732bb8a6b514e
|
|
name: 'MSSQL - Yes / No'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: 'No'
|
|
- value: '1'
|
|
newvalue: 'Yes'
|
|
- uuid: bfbb00eb85d747dabeda2954a5d659b7
|
|
name: 'MSSQL AG Connected state'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: Disconnected
|
|
- value: '1'
|
|
newvalue: Connected
|
|
- uuid: 47413c135c094cdc8b59f18a9b195a78
|
|
name: 'MSSQL AG Join state'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: 'Not joined'
|
|
- value: '1'
|
|
newvalue: 'Joined, standalone instance'
|
|
- value: '2'
|
|
newvalue: 'Joined, failover cluster instance'
|
|
- uuid: a570f341d9a8470797aecfebd376b2b8
|
|
name: 'MSSQL AG Operational state'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: 'Pending failover'
|
|
- value: '1'
|
|
newvalue: Pending
|
|
- value: '2'
|
|
newvalue: Online
|
|
- value: '3'
|
|
newvalue: Offline
|
|
- value: '4'
|
|
newvalue: Failed
|
|
- value: '5'
|
|
newvalue: 'Failed, no quorum'
|
|
- value: '6'
|
|
newvalue: 'Replica is not local'
|
|
- uuid: 4ec6a96ff6c64756b2f27e47972fd9f6
|
|
name: 'MSSQL AG Recovery health'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: 'In progress'
|
|
- value: '1'
|
|
newvalue: Online
|
|
- value: '2'
|
|
newvalue: 'Replica is not local'
|
|
- uuid: 596556fba6064efb8155233371eb2950
|
|
name: 'MSSQL AG Role'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: Resolving
|
|
- value: '1'
|
|
newvalue: Primary
|
|
- value: '2'
|
|
newvalue: Secondary
|
|
- uuid: 2ab0923b3ce14ffc9dd454114f6b15be
|
|
name: 'MSSQL AG Synchronization health'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: 'Not healthy'
|
|
- value: '1'
|
|
newvalue: 'Partially healthy'
|
|
- value: '2'
|
|
newvalue: Healthy
|
|
- uuid: bac1a10dad134d50add5bc9550e2ebf3
|
|
name: 'MSSQL DB state'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: ONLINE
|
|
- value: '1'
|
|
newvalue: RESTORING
|
|
- value: '2'
|
|
newvalue: RECOVERING
|
|
- value: '3'
|
|
newvalue: RECOVERY_PENDING
|
|
- value: '4'
|
|
newvalue: SUSPECT
|
|
- value: '5'
|
|
newvalue: EMERGENCY
|
|
- value: '6'
|
|
newvalue: OFFLINE
|
|
- value: '7'
|
|
newvalue: COPYING
|
|
- value: '10'
|
|
newvalue: OFFLINE_SECONDARY
|
|
- uuid: eefa6e0e6f8b4facbc12004437b7ee8f
|
|
name: 'MSSQL Job Run Status'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: Failed
|
|
- value: '1'
|
|
newvalue: Succeeded
|
|
- value: '2'
|
|
newvalue: Retry
|
|
- value: '3'
|
|
newvalue: Canceled
|
|
- value: '4'
|
|
newvalue: Running
|
|
- value: '5'
|
|
newvalue: Unknown
|
|
- uuid: 09aaa82ccd6a444b80d5d3bb182fbafd
|
|
name: 'MSSQL Mirroring Role'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: 'Database is inaccessible or is not mirrored'
|
|
- value: '1'
|
|
newvalue: Principal
|
|
- value: '2'
|
|
newvalue: Mirror
|
|
- uuid: f1c105c262c5476798a93eaa4c46fbda
|
|
name: 'MSSQL Mirroring State'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: Suspended
|
|
- value: '1'
|
|
newvalue: 'Disconnected from the other partner'
|
|
- value: '2'
|
|
newvalue: Synchronizing
|
|
- value: '3'
|
|
newvalue: 'Pending failover'
|
|
- value: '4'
|
|
newvalue: Synchronized
|
|
- value: '5'
|
|
newvalue: 'The partners are not synchronized. Failover is not possible now.'
|
|
- value: '6'
|
|
newvalue: 'The partners are synchronized. Failover is potentially possible.'
|
|
- value: '7'
|
|
newvalue: 'Database is inaccessible or is not mirrored'
|
|
- uuid: 1454643e9cad46f8953f4c054509151c
|
|
name: 'MSSQL Quorum members state'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: Offline
|
|
- value: '1'
|
|
newvalue: Online
|
|
- uuid: caa789916da048c691f8eef5a71a856e
|
|
name: 'MSSQL Quorum members type'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: 'WSFC node'
|
|
- value: '1'
|
|
newvalue: 'Disk witness'
|
|
- value: '2'
|
|
newvalue: 'File share witness'
|
|
- value: '3'
|
|
newvalue: 'Cloud Witness'
|
|
- uuid: 23c9fede4c9a4a21854b3fc83de459a7
|
|
name: 'MSSQL Quorum state'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: 'Unknown quorum state'
|
|
- value: '1'
|
|
newvalue: 'Normal quorum'
|
|
- value: '2'
|
|
newvalue: 'Forced quorum'
|
|
- uuid: fbf296f404ad45d5b02e3c20104b9b9e
|
|
name: 'MSSQL Quorum type'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: 'Node Majority'
|
|
- value: '1'
|
|
newvalue: 'Node and Disk Majority'
|
|
- value: '2'
|
|
newvalue: 'Node and File Share Majority'
|
|
- value: '3'
|
|
newvalue: 'No Majority: Disk Only'
|
|
- value: '4'
|
|
newvalue: 'Unknown Quorum'
|
|
- value: '5'
|
|
newvalue: 'Cloud Witness'
|
|
- uuid: f59af5b534994dccaebb4afc3266520b
|
|
name: 'MSSQL Recovery model'
|
|
mappings:
|
|
- value: '1'
|
|
newvalue: FULL
|
|
- value: '2'
|
|
newvalue: BULK_LOGGED
|
|
- value: '3'
|
|
newvalue: SIMPLE
|
|
- uuid: e70421b2c3db4ce1937791bdb432040e
|
|
name: 'MSSQL Safety level'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: 'Unknown state'
|
|
- value: '1'
|
|
newvalue: 'Off [asynchronous]'
|
|
- value: '2'
|
|
newvalue: 'Full [synchronous]'
|
|
- value: '3'
|
|
newvalue: 'Database is inaccessible or is not mirrored'
|
|
- uuid: aa9de6e21d08486db04f29e10d11a94c
|
|
name: 'MSSQL Witness state'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: Unknown
|
|
- value: '1'
|
|
newvalue: Connected
|
|
- value: '2'
|
|
newvalue: Disconnected
|
|
- value: '3'
|
|
newvalue: 'No witness exists'
|
|
- uuid: 03cde96b90a641598db572a662dc2064
|
|
name: 'Service state'
|
|
mappings:
|
|
- value: '0'
|
|
newvalue: Down
|
|
- value: '1'
|
|
newvalue: Up
|
|
triggers:
|
|
- uuid: a1397bd4e9334a24a34dba0687b263e6
|
|
expression: 'last(/MSSQL by ODBC/mssql.forwarded_records_sec.rate) * 100 > 10 * last(/MSSQL by ODBC/mssql.batch_requests_sec.rate)'
|
|
name: 'MSSQL: Too frequently using pointers'
|
|
priority: WARNING
|
|
description: 'Rows with VARCHAR columns can experience expansion when VARCHAR values are updated with a longer string. In the case where the row cannot fit in the existing page, the row migrates, and access to the row will traverse a pointer. This only happens on heaps (tables without clustered indexes). In cases where clustered indexes cannot be used, drop non-clustered indexes, build a clustered index to reorg pages and rows, drop the clustered index, then recreate non-clustered indexes.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
- uuid: 2a58729eb605461b853ec4e855878226
|
|
expression: 'last(/MSSQL by ODBC/mssql.readahead_pages_sec.rate) > {$MSSQL.PERCENT_READAHEAD.MAX} / 100 * last(/MSSQL by ODBC/mssql.page_reads_sec.rate)'
|
|
name: 'MSSQL: Too many physical reads occurring'
|
|
priority: WARNING
|
|
description: 'If this value makes up even a sizeable minority of the total "Page Reads/sec" (say, greater than 20% of the total page reads), you may have too many physical reads occurring.'
|
|
tags:
|
|
- tag: scope
|
|
value: performance
|
|
graphs:
|
|
- uuid: d2db0e08beb6445a8b5dd9fb46249265
|
|
name: 'MSSQL: Access methods'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.forwarded_records_sec.rate
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.full_scans_sec.rate
|
|
- sortorder: '2'
|
|
color: 00611C
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.index_searches_sec.rate
|
|
- sortorder: '3'
|
|
color: F7941D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.page_splits_sec.rate
|
|
- sortorder: '4'
|
|
color: FC6EA3
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.workfiles_created_sec.rate
|
|
- sortorder: '5'
|
|
color: 6C59DC
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.worktables_created_sec.rate
|
|
- uuid: f22186d86d9346fe9e1f3292e1f0017c
|
|
name: 'MSSQL: Auto-params'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.autoparam_attempts_sec.rate
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.failed_autoparams_sec.rate
|
|
- sortorder: '2'
|
|
color: 00611C
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.safe_autoparams_sec.rate
|
|
- sortorder: '3'
|
|
color: F7941D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.unsafe_autoparams_sec.rate
|
|
- uuid: 24ef7368fb364616892c4928ba6167c6
|
|
name: 'MSSQL: Cache objects'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.cache_object_counts
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.cache_objects_in_use
|
|
- uuid: 1f713999d38745b6bb2828e397a32970
|
|
name: 'MSSQL: Compilations'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.sql_compilations_sec.rate
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.sql_recompilations_sec.rate
|
|
- uuid: 91af398cfb86472ba4a69e3df0816be7
|
|
name: 'MSSQL: Database pages'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.database_pages
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.target_pages
|
|
- uuid: 0c9aaf223d6f4ba9bd17b0289af4bbe2
|
|
name: 'MSSQL: Errors'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.errors_sec.rate
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.offline_errors_sec.rate
|
|
- sortorder: '2'
|
|
color: 00611C
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.info_errors_sec.rate
|
|
- sortorder: '3'
|
|
color: F7941D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.kill_connection_errors_sec.rate
|
|
- sortorder: '4'
|
|
color: FC6EA3
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.user_errors_sec.rate
|
|
- uuid: e35985317393409b9a764adcb05f07d0
|
|
name: 'MSSQL: Locks'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.lock_requests_sec.rate
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.lock_timeouts_sec.rate
|
|
- sortorder: '2'
|
|
color: 00611C
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.number_deadlocks_sec.rate
|
|
- sortorder: '3'
|
|
color: F7941D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.lock_waits_sec.rate
|
|
- sortorder: '4'
|
|
color: FC6EA3
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.average_wait_time
|
|
- uuid: 8796ad9cc04f48cdbe8ef2cb5f429997
|
|
name: 'MSSQL: Logins per second and Logouts per second'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.logins_sec.rate
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.logouts_sec.rate
|
|
- uuid: bc588f8933d846a19accdb66eb29ccb9
|
|
name: 'MSSQL: Memory/cache load'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.cache_hit_ratio
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.buffer_cache_hit_ratio
|
|
- uuid: 3d431f6cc6d34ef5820f2a24f3872b41
|
|
name: 'MSSQL: Memory grants outstanding and pending'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.memory_grants_outstanding
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.memory_grants_pending
|
|
- uuid: 0f0bc58e0c18421591d265f81c000021
|
|
name: 'MSSQL: Page life expectancy'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.page_life_expectancy
|
|
- uuid: 2b1d0e42f61a4312998775d21f7cabb7
|
|
name: 'MSSQL: Pages'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.page_lookups_sec.rate
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.page_reads_sec.rate
|
|
- sortorder: '2'
|
|
color: 00611C
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.page_writes_sec.rate
|
|
- sortorder: '3'
|
|
color: F7941D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.readahead_pages_sec.rate
|
|
- sortorder: '4'
|
|
color: FC6EA3
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.checkpoint_pages_sec.rate
|
|
- sortorder: '5'
|
|
color: 6C59DC
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.free_list_stalls_sec.rate
|
|
- sortorder: '6'
|
|
color: C7A72D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.lazy_writes_sec.rate
|
|
- uuid: 2e9273b0472c4a3e9049bea3548301d5
|
|
name: 'MSSQL: Processes blocked'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.processes_blocked
|
|
- uuid: f124ce3778bd4722bf0d2d598ac31b0f
|
|
name: 'MSSQL: Server memory'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.target_server_memory
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.total_server_memory
|
|
- sortorder: '2'
|
|
color: 00611C
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.granted_workspace_memory
|
|
- sortorder: '3'
|
|
color: F7941D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.maximum_workspace_memory
|
|
- uuid: 59ffd52c5c334d20870fc209c6c61398
|
|
name: 'MSSQL: Transactions'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.transactions_sec.rate
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.batch_requests_sec.rate
|
|
- uuid: 6979c0bcba0e4ac08c6bfefa0c648d40
|
|
name: 'MSSQL: Waits per second'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.latch_waits_sec.rate
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.lock_waits_sec.rate
|
|
- uuid: 41cc736d93ce45d6aeb6dede77c97f08
|
|
name: 'MSSQL: Wait times'
|
|
graph_items:
|
|
- color: 199C0D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.average_latch_wait_time
|
|
- sortorder: '1'
|
|
color: F63100
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.total_latch_wait_time
|
|
- sortorder: '2'
|
|
color: 00611C
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.average_wait_time
|
|
- sortorder: '3'
|
|
color: F7941D
|
|
item:
|
|
host: 'MSSQL by ODBC'
|
|
key: mssql.lock_wait_time
|