zabbix_export: version: '7.0' template_groups: - uuid: c2c162144c2d4c5491c8801193af4945 name: Templates/Cloud templates: - uuid: 507d70db18554a4e9ee5e09bb29bc85f template: 'GCP Cloud SQL MSSQL by HTTP' name: 'GCP Cloud SQL MSSQL by HTTP' description: | Get GCP Cloud SQL MSSQL instances monitoring with script item usage to perform HTTP requests to Google Cloud Platform Monitoring API. This template will be automatically connected to discovered entities with all their required parameters pre-defined. You can discuss this template or leave feedback on our forum https://www.zabbix.com/forum/zabbix-suggestions-and-feedback. Generated by official Zabbix template tool "Templator" vendor: name: Zabbix version: 7.0-3 groups: - name: Templates/Cloud items: - uuid: a8d79cab89864c03a7614bd59f734942 name: 'Audits size' type: DEPENDENT key: gcp.cloudsql.mssql.audits_size delay: '0' units: B description: | Tracks the size in bytes of stored SQLServer audit files on an instance. Empty value if there are no audits enabled. preprocessing: - type: JSONPATH parameters: - $.base_audits_size error_handler: DISCARD_VALUE master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: security - uuid: 3026bc61670c496a942a94ab50b614df name: 'Audits successfully uploaded' type: DEPENDENT key: gcp.cloudsql.mssql.audits_upload_count delay: '0' description: | Tracks the size in bytes of stored SQLServer audit files on an instance. Empty value if there are no audits enabled. preprocessing: - type: JSONPATH parameters: - $.mssql_success error_handler: DISCARD_VALUE master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: security - uuid: d512c542f5514681acbb11431a0a6f5a name: 'Connection resets' type: DEPENDENT key: gcp.cloudsql.mssql.conn.connection_reset_count delay: '0' description: 'Total number of login operations started from the connection pool since the last restart of SQL Server service.' preprocessing: - type: JSONPATH parameters: - $.mssql_connection_reset_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: connections - tag: component value: network - uuid: 300a6cb53fb44176bdf99eba86a976bd name: 'Login attempts' type: DEPENDENT key: gcp.cloudsql.mssql.conn.login_attempt_count delay: '0' description: | Total number of login attempts since the last restart of SQL Server service. This does not include pooled connections. preprocessing: - type: JSONPATH parameters: - $.mssql_login_attempt_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: connections - tag: component value: network - uuid: 622c237ed12e44c7b2415676ec23bd2d name: Logouts type: DEPENDENT key: gcp.cloudsql.mssql.conn.logout_count delay: '0' description: 'Total number of logout operations since the last restart of SQL Server service.' preprocessing: - type: JSONPATH parameters: - $.mssql_logout_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: connections - tag: component value: network - uuid: 6ba672cbf4c242eb8e6fb80eba4c90d8 name: 'Processes blocked' type: DEPENDENT key: gcp.cloudsql.mssql.conn.processes_blocked delay: '0' description: 'Current number of blocked processes.' preprocessing: - type: JSONPATH parameters: - $.mssql_processes_blocked master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: connections - tag: component value: network - uuid: 38de8ae591854d5fbc4ee4946a2e189a name: 'Reserved CPU cores' type: DEPENDENT key: gcp.cloudsql.mssql.cpu.reserved_cores delay: '0' value_type: FLOAT description: 'Number of cores reserved for the database.' preprocessing: - type: JSONPATH parameters: - $.base_reserved_cores master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: cpu - uuid: cd1f9ab326ac4694b3aeea6cb4ec56ab name: 'CPU usage time' type: DEPENDENT key: gcp.cloudsql.mssql.cpu.usage_time delay: '0' value_type: FLOAT units: s description: 'Cumulative CPU usage time in seconds.' preprocessing: - type: JSONPATH parameters: - $.base_usage_time master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: cpu - uuid: 42d51e38b96f472996eff1e0f9d7b931 name: 'CPU utilization' type: DEPENDENT key: gcp.cloudsql.mssql.cpu.utilization delay: '0' value_type: FLOAT units: '%' description: 'Current CPU utilization represented as a percentage of the reserved CPU that is currently in use.' preprocessing: - type: JSONPATH parameters: - $.base_utilization - type: MULTIPLIER parameters: - '100' master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: cpu triggers: - uuid: b8280e45fea8470e9f36f7f08e4a77e1 expression: 'min(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.cpu.utilization,5m) >= {$CLOUD_SQL.MSSQL.CPU.UTIL.MAX}' name: 'GCP MSSQL: High CPU utilization' opdata: 'Current utilization: {ITEM.LASTVALUE}' priority: AVERAGE description: 'The CPU utilization is too high. The system might be slow to respond.' tags: - tag: scope value: performance - uuid: ecdd1774ef744bcb93e8e4094e29772f name: 'Databases get' type: SCRIPT key: gcp.cloudsql.mssql.db.get delay: '{$GCP.TIME.WINDOW}' history: '0' value_type: TEXT trends: '0' params: | const CTlsConfig = function (raw_value) { raw_value = String(raw_value).trim().toLowerCase(); if (['none', 'peer', 'full'].indexOf(raw_value) === -1) { raw_value = 'full'; } this.options = { SSLVerifyPeer: (raw_value === 'peer' || raw_value === 'full'), SSLVerifyHost: (raw_value === 'full') }; this.enabled = (raw_value !== 'none'); this.checkURL = function (url) { if (this.enabled && !/^https:\/\//i.test(url)) { throw 'TLS certificate verification is enabled by {$' + 'HTTP.TLS.VERIFY}, but the URL uses plain HTTP: ' + url + '. Use an "https://" URL, or set {$' + 'HTTP.TLS.VERIFY} to `none` to disable verification.'; } return url; }; } var base_path = 'cloudsql.googleapis.com/database/sqlserver/transactions/', arr = [], base_paths = [ 'log_bytes_flushed_count', 'transaction_count' ]; function getField(data, path) { var steps = path.split('.'); for (var i = 0; i < steps.length; i++) { var step = steps[i]; if (typeof data !== 'object' || typeof data[step] === 'undefined') { throw 'Required field "' + path + '" is not present in data received.'; } data = data[step]; } return data; } function checkParams(params) { ['project_id', 'time_window', 'database_id', 'token'].forEach(function (field) { if (typeof params !== 'object' || typeof params[field] === 'undefined' || params[field] === '') { throw 'Required param is not set: ' + field + '.'; } }); if (!params.time_window.match(/^[0-9]+(m|h)$/)) { throw 'Incorrect update interval for parameter time_window. Valid ranges 1m-23h.'; } return params; } function getHttpData(url, token, body) { var tls = new CTlsConfig(params.tls_verify), request = new HttpRequest(tls.options); if (typeof params.proxy !== 'undefined' && params.proxy !== '') { request.setProxy(params.proxy); }; request.addHeader('Authorization: Bearer ' + token); request.addHeader('Content-Type: application/json'); var response = request.post(url, JSON.stringify(body)); Zabbix.log(4, '[ GCP API ] [ ' + url + ' ] Received response with status code ' + request.getStatus() + ': ' + response); if (response !== null) { try { response = JSON.parse(response); } catch (error) { throw 'Failed to parse response received from GCP API. Check debug log for more information.'; } } if (typeof response !== 'object' || response === null) { throw 'Cannot process response data: received data is not an object. Check debug log for more information.'; } if (request.getStatus() !== 200) { if (typeof (response.error && response.error.message) !== 'undefined') { throw response.error.message; } else { throw 'Failed to receive data: invalid response status code. Check debug log for more information.'; } } return response; }; var params = checkParams(JSON.parse(value)), j_filter = ' | within ' + params.time_window + " | filter database_id = '" + params.database_id + "'" + ' | join', url = 'https://monitoring.googleapis.com/v3/projects/' + params.project_id + '/timeSeries:query', raw = getHttpData(url, params.token, { query: '{' + base_paths.map(function (path) { return base_path + path; }).join(';') + '}' + j_filter }), metrics = getField(raw, 'timeSeriesDescriptor.pointDescriptors'), time_series = getField(raw, 'timeSeriesData'); for (t in time_series) { data = { database: getField(time_series[t], 'labelValues.3.stringValue'), metrics: {} }; for (i in metrics) { re = /(^t_\d+\.value(_|\.))/g; data['metrics'][getField(metrics[i], 'key').replace(re, '')] = getField(time_series[t], 'pointData.0.values.' + i + '.int64Value'); } arr.push(data); } return JSON.stringify(arr); description: 'MSSQL databases data in raw format.' preprocessing: - type: CHECK_NOT_SUPPORTED parameters: - '-1' timeout: '{$GCP.DATA.TIMEOUT}' parameters: - name: database_id value: '{$GCP.PROJECT.ID}:{HOST.NAME}' - name: project_id value: '{$GCP.PROJECT.ID}' - name: proxy value: '{$GCP.PROXY}' - name: time_window value: '{$GCP.TIME.WINDOW}' - name: tls_verify value: '{$HTTP.TLS.VERIFY:"GCP Cloud SQL MSSQL"}' - name: token value: '{$GCP.AUTH.TOKEN}' tags: - tag: component value: raw - uuid: 9806c645a10a4e1d8fbb39eb1d3ac993 name: 'DB engine state' type: HTTP_AGENT key: gcp.cloudsql.mssql.db.state description: 'GCP Cloud SQL MSSQL DB Engine State.' valuemap: name: 'Database availability' preprocessing: - type: CHECK_NOT_SUPPORTED parameters: - '-1' - type: JSONPATH parameters: - '$.timeSeriesData[0].pointData[0].values[0].int64Value' error_handler: DISCARD_VALUE - type: DISCARD_UNCHANGED_HEARTBEAT parameters: - 10m timeout: '{$GCP.DATA.TIMEOUT}' url: 'https://monitoring.googleapis.com/v3/projects/{$GCP.PROJECT.ID}/timeSeries:query' status_codes: '' posts: '{"query":"cloudsql.googleapis.com/database/up | within 5m | filter database_id = ''{$GCP.PROJECT.ID}:{HOST.NAME}''"}' headers: - name: Content-Type value: application/json - name: Authorization value: 'Bearer {$GCP.AUTH.TOKEN}' request_method: POST tags: - tag: component value: status triggers: - uuid: b3c7b8e2e38a42fe9d2ae3c84b637e53 expression: 'last(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.db.state)=0' name: 'GCP MSSQL: Database engine is down' priority: AVERAGE description: | Database engine is down. If an instance experiences unplanned (non-maintenance) downtime, the instance state will still be RUNNING, but the database engine state metric will report 0. dependencies: - name: 'GCP MSSQL: Failed to get the instance state' expression: 'last(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.inst.state) = 10' - name: 'GCP MSSQL: Instance is in failed state' expression: 'last(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.inst.state) = 5' - name: 'GCP MSSQL: Instance is in maintenance' expression: 'last(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.inst.state) = 4' - name: 'GCP MSSQL: Instance is in suspended state' expression: 'last(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.inst.state) = 1' - name: 'GCP MSSQL: Instance is in unknown state' expression: 'last(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.inst.state) = 6' - name: 'GCP MSSQL: Instance is stopped by the owner' expression: 'last(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.inst.state) = 2' tags: - tag: scope value: availability - uuid: cd6cf3870a514ff59bd1f40987764642 name: 'Disk bytes used' type: DEPENDENT key: gcp.cloudsql.mssql.disk.bytes_used delay: '0' units: B description: 'Data utilization in bytes.' preprocessing: - type: JSONPATH parameters: - $.mssql_bytes_used master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: storage - uuid: 86bdbf20c9b4466c9ea00e26cb0f0652 name: 'Disk size' type: DEPENDENT key: gcp.cloudsql.mssql.disk.quota delay: '0' units: B description: 'Maximum data disk size in bytes.' preprocessing: - type: JSONPATH parameters: - $.mssql_quota master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: storage - uuid: 400a7318203f40928ebf0b023da65848 name: 'Disk read I/O' type: DEPENDENT key: gcp.cloudsql.mssql.disk.read_ops_count delay: '0' description: 'Delta count of data disk read I/O operations.' preprocessing: - type: JSONPATH parameters: - $.base_read_ops_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: storage - uuid: 1d0c09441d9d4333b7be32c3d69bad47 name: 'Disk utilization' type: DEPENDENT key: gcp.cloudsql.mssql.disk.utilization delay: '0' value_type: FLOAT units: '%' description: | The fraction of the disk quota that is currently in use. Shown as percentage. preprocessing: - type: JSONPATH parameters: - $.mssql_utilization - type: MULTIPLIER parameters: - '100' master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: storage triggers: - uuid: 2cbc7e3008f24197963df4ea920ab5a8 expression: 'last(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.disk.utilization) >= {$CLOUD_SQL.MSSQL.DISK.UTIL.CRIT}' name: 'GCP MSSQL: Disk space is critically low' opdata: 'Current utilization: {ITEM.LASTVALUE}' priority: AVERAGE description: 'Critical utilization of the disk space.' tags: - tag: scope value: capacity - uuid: 2569abbb94ca4df38cdd4f9d5a5df9fc expression: 'last(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.disk.utilization) >= {$CLOUD_SQL.MSSQL.DISK.UTIL.WARN}' name: 'GCP MSSQL: Disk space is low' opdata: 'Current utilization: {ITEM.LASTVALUE}' priority: WARNING description: 'High utilization of the storage space.' dependencies: - name: 'GCP MSSQL: Disk space is critically low' expression: 'last(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.disk.utilization) >= {$CLOUD_SQL.MSSQL.DISK.UTIL.CRIT}' tags: - tag: scope value: capacity - uuid: 819078cd99af46368eb33d76dadf4861 name: 'Disk write I/O' type: DEPENDENT key: gcp.cloudsql.mssql.disk.write_ops_count delay: '0' description: 'Delta count of data disk write I/O operations.' preprocessing: - type: JSONPATH parameters: - $.base_write_ops_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: storage - uuid: 556c96cf1bf342f99c69dc47813f221e name: 'Instance state' type: HTTP_AGENT key: gcp.cloudsql.mssql.inst.state description: 'GCP Cloud SQL MSSQL Current instance state.' valuemap: name: 'Instance state' preprocessing: - type: JSONPATH parameters: - '$.timeSeriesData[0].pointData[0].values[0].stringValue' error_handler: CUSTOM_VALUE error_handler_params: '10' - type: JAVASCRIPT parameters: - | const idx = [ 'RUNNING', 'SUSPENDED', 'RUNNABLE', 'PENDING_CREATE', 'MAINTENANCE', 'FAILED', 'UNKNOWN_STATE' ].indexOf(value); return idx !== -1 ? idx : 10; - type: DISCARD_UNCHANGED_HEARTBEAT parameters: - 10m timeout: '{$GCP.DATA.TIMEOUT}' url: 'https://monitoring.googleapis.com/v3/projects/{$GCP.PROJECT.ID}/timeSeries:query' status_codes: '' posts: '{"query":"cloudsql.googleapis.com/database/state | within 5m | filter database_id = ''{$GCP.PROJECT.ID}:{HOST.NAME}''"}' headers: - name: Content-Type value: application/json - name: Authorization value: 'Bearer {$GCP.AUTH.TOKEN}' request_method: POST tags: - tag: component value: status triggers: - uuid: dff9067ad7de4ab1a4f83c9d482e6b71 expression: 'last(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.inst.state) = 10' name: 'GCP MSSQL: Failed to get the instance state' priority: AVERAGE description: | Failed to get the instance state. Check access permissions to GCP API or service account. tags: - tag: scope value: availability - uuid: 3d7e054340e94b09b085e508b9b76841 expression: 'last(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.inst.state) = 5' name: 'GCP MSSQL: Instance is in failed state' priority: AVERAGE description: 'The instance creation failed, or an operation left the instance in an own bad state.' tags: - tag: scope value: availability - uuid: dd395fd3003f4fce847cd72309cdf7ec expression: 'last(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.inst.state) = 4' name: 'GCP MSSQL: Instance is in maintenance' priority: INFO description: 'The instance is down for maintenance.' tags: - tag: scope value: availability - uuid: 3c080bba511e44aab1139b66f00451ee expression: 'last(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.inst.state) = 1' name: 'GCP MSSQL: Instance is in suspended state' priority: WARNING description: | The instance is in suspended state. It is not available, for example, due to problems with billing. tags: - tag: scope value: availability - uuid: 378b4d62582d416db7cb7fd6ed099ac9 expression: 'last(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.inst.state) = 6' name: 'GCP MSSQL: Instance is in unknown state' priority: AVERAGE description: 'The state of the instance is unknown.' tags: - tag: scope value: availability - uuid: 72ecba08f6424573a36b816da9695b89 expression: 'last(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.inst.state) = 2' name: 'GCP MSSQL: Instance is stopped by the owner' priority: INFO description: | The instance has been stopped by the owner. It is not currently running, but it's ready to be restarted. tags: - tag: scope value: availability - uuid: a29584de7ab142309f0bb6f57b52af85 name: 'Buffer cache hit ratio' type: DEPENDENT key: gcp.cloudsql.mssql.memory.buffer_cache_hit_ratio delay: '0' value_type: FLOAT units: '%' description: | Current percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups. preprocessing: - type: JSONPATH parameters: - $.mssql_buffer_cache_hit_ratio master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: memory - uuid: f8460bbac21c44a98a31a49daf7f692f name: 'Checkpoint pages' type: DEPENDENT key: gcp.cloudsql.mssql.memory.checkpoint_page_count delay: '0' description: 'Total number of pages flushed to disk by a checkpoint or other operation that requires all dirty pages to be flushed.' preprocessing: - type: JSONPATH parameters: - $.mssql_checkpoint_page_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: memory - uuid: cc6a5230c7a2447e8038cdbe52105da4 name: 'Free list stalls' type: DEPENDENT key: gcp.cloudsql.mssql.memory.free_list_stall_count delay: '0' description: 'Total number of requests that had to wait for a free page.' preprocessing: - type: JSONPATH parameters: - $.mssql_free_list_stall_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: memory - uuid: d86e328fc5fa4c3d938a4959075299b5 name: 'Lazy writes' type: DEPENDENT key: gcp.cloudsql.mssql.memory.lazy_write_count delay: '0' description: | Total number of buffers written 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 disk before the buffer can be reused for a different page) and makes them available to user processes. preprocessing: - type: JSONPATH parameters: - $.mssql_lazy_write_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: memory - uuid: 2fa0744221884109951ce62d3bb9aaaf name: 'Memory grants pending' type: DEPENDENT key: gcp.cloudsql.mssql.memory.memory_grants_pending delay: '0' description: 'Current number of processes waiting for a workspace memory grant.' preprocessing: - type: JSONPATH parameters: - $.mssql_memory_grants_pending master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: memory - uuid: 3dcad151d0c04f8eb0e8e81c00de23c8 name: 'Page life expectancy' type: DEPENDENT key: gcp.cloudsql.mssql.memory.page_life_expectancy delay: '0' units: s description: 'Current number of seconds a page will stay in the buffer pool without references.' preprocessing: - type: JSONPATH parameters: - $.mssql_page_life_expectancy master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: memory - uuid: 4d87a50d490348afbaf751857beb5b61 name: 'Read page operations' type: DEPENDENT key: gcp.cloudsql.mssql.memory.page_ops.read delay: '0' description: | Total number of physical database page reads. This metric counts physical page reads across all databases. preprocessing: - type: JSONPATH parameters: - $.mssql_read master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: memory - uuid: 41654bd730b744acbc0ea6de07c992f3 name: 'Write age operations' type: DEPENDENT key: gcp.cloudsql.mssql.memory.page_ops.write delay: '0' description: | Total number of physical database page writes. This metric counts physical page writes across all databases. preprocessing: - type: JSONPATH parameters: - $.mssql_write master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: memory - uuid: db943300803847718c52d0d33988d81e name: 'Memory size' type: DEPENDENT key: gcp.cloudsql.mssql.memory.quota delay: '0' units: B description: 'Maximum RAM size in bytes.' preprocessing: - type: JSONPATH parameters: - $.base_quota master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: memory - uuid: 21b075a5f95d4c93888b7a0766fdcea5 name: 'Memory used by DB engine' type: DEPENDENT key: gcp.cloudsql.mssql.memory.total_usage delay: '0' units: B description: | Total RAM usage in bytes. This metric reports the RAM usage of the database process, including the buffer/cache. preprocessing: - type: JSONPATH parameters: - $.base_total_usage master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: memory - uuid: 05d1ff573371416dad9ee028247b1cf3 name: 'Memory usage' type: DEPENDENT key: gcp.cloudsql.mssql.memory.usage delay: '0' units: B description: | The RAM usage in bytes. This metric reports the RAM usage of the server, excluding the buffer/cache. preprocessing: - type: JSONPATH parameters: - $.base_usage master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: memory - uuid: 4e11944fb7624ffdb02b5d1bb5bedf7a name: 'Memory utilization' type: DEPENDENT key: gcp.cloudsql.mssql.memory.utilization delay: '0' value_type: FLOAT units: '%' description: | The fraction of the memory quota that is currently in use. Shown as percentage. preprocessing: - type: JSONPATH parameters: - $.base_ram_pused master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: memory triggers: - uuid: c5003533f3524b8b868a0c2f86b1fa9b expression: 'min(/GCP Cloud SQL MSSQL by HTTP/gcp.cloudsql.mssql.memory.utilization,5m) >= {$CLOUD_SQL.MSSQL.RAM.UTIL.MAX}' name: 'GCP MSSQL: High memory utilization' opdata: 'Current utilization: {ITEM.LASTVALUE}' priority: HIGH description: 'RAM utilization is too high. The system might be slow to respond.' tags: - tag: scope value: performance - uuid: 6de08b07cecc47a88bde22de5d1b4e5a name: 'Metrics get' type: SCRIPT key: gcp.cloudsql.mssql.metrics.get delay: '{$GCP.TIME.WINDOW}' history: '0' value_type: TEXT trends: '0' params: | const CTlsConfig = function (raw_value) { raw_value = String(raw_value).trim().toLowerCase(); if (['none', 'peer', 'full'].indexOf(raw_value) === -1) { raw_value = 'full'; } this.options = { SSLVerifyPeer: (raw_value === 'peer' || raw_value === 'full'), SSLVerifyHost: (raw_value === 'full') }; this.enabled = (raw_value !== 'none'); this.checkURL = function (url) { if (this.enabled && !/^https:\/\//i.test(url)) { throw 'TLS certificate verification is enabled by {$' + 'HTTP.TLS.VERIFY}, but the URL uses plain HTTP: ' + url + '. Use an "https://" URL, or set {$' + 'HTTP.TLS.VERIFY} to `none` to disable verification.'; } return url; }; } var base_path = 'cloudsql.googleapis.com/database/', mssql_path = 'cloudsql.googleapis.com/database/sqlserver/', obj = {}, base_paths = [ 'cpu/reserved_cores', 'cpu/usage_time', 'cpu/utilization', 'disk/read_ops_count', 'disk/write_ops_count', 'memory/quota', 'memory/total_usage', 'memory/usage', 'network/connections', 'network/received_bytes_count', 'network/sent_bytes_count | map ignore [destination]' ].map(function (path) { return base_path + path; }), mssql_paths = [ 'connections/connection_reset_count', 'connections/login_attempt_count', 'connections/logout_count', 'connections/processes_blocked', 'memory/buffer_cache_hit_ratio', 'memory/checkpoint_page_count', 'memory/lazy_write_count', 'memory/memory_grants_pending', 'memory/page_life_expectancy', 'memory/free_list_stall_count', 'transactions/batch_request_count', 'transactions/forwarded_record_count', 'transactions/full_scan_count', 'transactions/page_split_count', 'transactions/probe_scan_count', 'transactions/sql_compilation_count', 'transactions/sql_recompilation_count' ].map(function (path) { return mssql_path + path; }).concat( ['disk/bytes_used', 'disk/utilization', 'disk/quota'].map(function (path) { return base_path + path; })), value_types = ['int64Value', 'doubleValue', 'stringValue', 'boolValue']; function getField(data, path) { var steps = path.split('.'); for (var i = 0; i < steps.length; i++) { var step = steps[i]; if (typeof data !== 'object' || typeof data[step] === 'undefined') { throw 'Required field "' + path + '" is not present in data received.'; } data = data[step]; } return data; } function getFieldBool(data, path) { var steps = path.split('.'); for (var i = 0; i < steps.length; i++) { var step = steps[i]; if (typeof data !== 'object' || typeof data[step] === 'undefined') { return false; } data = data[step]; } return true; } function checkParams(params) { ['project_id', 'time_window', 'database_id', 'token', 'audited'].forEach(function (field) { if (typeof params !== 'object' || typeof params[field] === 'undefined' || params[field] === '') { throw 'Required param is not set: ' + field + '.'; } }); if (!params.time_window.match(/^[0-9]+(m|h)$/)) { throw 'Incorrect update interval for parameter time_window. Valid ranges 1m-23h.'; } if (params.audited !== 'true' && params.audited !== 'false') { throw 'Incorrect audit definition type. Valid definitions: true/false'; } return params; } function getHttpData(url, token, body) { var tls = new CTlsConfig(params.tls_verify), request = new HttpRequest(tls.options); if (typeof params.proxy !== 'undefined' && params.proxy !== '') { request.setProxy(params.proxy); }; request.addHeader('Authorization: Bearer ' + token); request.addHeader('Content-Type: application/json'); var response = request.post(url, JSON.stringify(body)); Zabbix.log(4, '[ GCP API ] [ ' + url + ' ] Received response with status code ' + request.getStatus() + ': ' + response); if (response !== null) { try { response = JSON.parse(response); } catch (error) { throw 'Failed to parse response received from GCP API. Check debug log for more information.'; } } if (typeof response !== 'object' || response === null) { throw 'Cannot process response data: received data is not an object. Check debug log for more information.'; } if (request.getStatus() !== 200) { if (typeof (response.error && response.error.message) !== 'undefined') { throw response.error.message; } else { throw 'Failed to receive data: invalid response status code. Check debug log for more information.'; } } return response; }; function rawMap(raw, prefix) { var keys = getField(raw, 'timeSeriesDescriptor.pointDescriptors'), values = getField(raw, 'timeSeriesData.0.pointData.0.values'), re = /(^t_\d+\.value\.)/g; for (i in keys) { var key = getField(keys[i], 'key').replace(re, ''); for (v in value_types) { if (typeof values[i][value_types[v]] !== 'undefined') { if (prefix) { obj[prefix + key] = values[i][value_types[v]]; } else { obj[key] = values[i][value_types[v]]; } } } } return obj; } function singleMetrics(tsd) { var result = {}; for (t in tsd) { for (v in value_types) { if (getFieldBool(tsd[t], 'pointData.0.values.0.' + [value_types[v]])) { result['mssql_' + getField(tsd[t], 'labelValues.3.stringValue').toLowerCase()] = tsd[t].pointData[0].values[0][value_types[v]]; break; } } } return result; } var params = checkParams(JSON.parse(value)); if (params.audited === 'true') { base_paths.push(mssql_path + 'audits_size'); }; var filter = ' | within ' + params.time_window + " | filter database_id = '" + params.database_id + "'", j_filter = filter + ' | join', url = 'https://monitoring.googleapis.com/v3/projects/' + params.project_id + '/timeSeries:query', queries = [ { name: 'base_', data: { query: '{' + base_paths.join(';') + '}' + j_filter } }, { name: 'mssql_', data: { query: '{' + mssql_paths.join(';') + '}' + j_filter } } ]; for (q in queries) { raw = getHttpData(url, params.token, queries[q].data); obj = Object.assign(obj, rawMap(raw, queries[q].name)); } obj.base_ram_pused = Math.floor(getField(obj, 'base_usage') / getField(obj, 'base_quota') * 100); var single_queries = [ { query: mssql_path + 'memory/page_operation_count' + filter } ]; if (params.audited === 'true') { single_queries.push({ query: mssql_path + 'audits_upload_count' + filter }); } for (q in single_queries) { raw = getHttpData(url, params.token, single_queries[q]); Object.assign(obj, singleMetrics(getField(raw, 'timeSeriesData'))); } return JSON.stringify(obj); description: 'MSSQL metrics data in raw format.' preprocessing: - type: CHECK_NOT_SUPPORTED parameters: - '-1' timeout: '{$GCP.DATA.TIMEOUT}' parameters: - name: audited value: '{$CLOUD_SQL.MSSQL.AUDITED}' - name: database_id value: '{$GCP.PROJECT.ID}:{HOST.NAME}' - name: project_id value: '{$GCP.PROJECT.ID}' - name: proxy value: '{$GCP.PROXY}' - name: time_window value: '{$GCP.TIME.WINDOW}' - name: tls_verify value: '{$HTTP.TLS.VERIFY:"GCP Cloud SQL MSSQL"}' - name: token value: '{$GCP.AUTH.TOKEN}' tags: - tag: component value: raw - uuid: 393b6117008d4925be9474afc9c7cdf9 name: Connections type: DEPENDENT key: gcp.cloudsql.mssql.network.connections delay: '0' description: 'Number of connections to the databases on the Cloud SQL instance.' preprocessing: - type: JSONPATH parameters: - $.base_connections master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: connections - tag: component value: network - uuid: 297744bfcdd84565b828e1c6912beb46 name: 'Network: Received bytes' type: DEPENDENT key: gcp.cloudsql.mssql.network.received_bytes_count delay: '0' units: B description: 'Delta count of bytes received through the network.' preprocessing: - type: JSONPATH parameters: - $.base_received_bytes_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: network - tag: component value: traffic - uuid: b362d14169b34f00ab3fa94b89a1b362 name: 'Network: Sent bytes' type: DEPENDENT key: gcp.cloudsql.mssql.network.sent_bytes_count delay: '0' units: B description: 'Delta count of bytes sent through the network.' preprocessing: - type: JSONPATH parameters: - $.base_sent_bytes_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: network - tag: component value: traffic - uuid: 7ee117702e1f40faadd901faa9d333ff name: 'Resources get' type: SCRIPT key: gcp.cloudsql.mssql.resources.get delay: '{$GCP.TIME.WINDOW}' history: '0' value_type: TEXT trends: '0' params: | const CTlsConfig = function (raw_value) { raw_value = String(raw_value).trim().toLowerCase(); if (['none', 'peer', 'full'].indexOf(raw_value) === -1) { raw_value = 'full'; } this.options = { SSLVerifyPeer: (raw_value === 'peer' || raw_value === 'full'), SSLVerifyHost: (raw_value === 'full') }; this.enabled = (raw_value !== 'none'); this.checkURL = function (url) { if (this.enabled && !/^https:\/\//i.test(url)) { throw 'TLS certificate verification is enabled by {$' + 'HTTP.TLS.VERIFY}, but the URL uses plain HTTP: ' + url + '. Use an "https://" URL, or set {$' + 'HTTP.TLS.VERIFY} to `none` to disable verification.'; } return url; }; } var base_path = 'cloudsql.googleapis.com/database/sqlserver/transactions/', arr = [], base_paths = [ 'deadlock_count', 'lock_wait_count', 'lock_wait_time' ]; function getField(data, path) { var steps = path.split('.'); for (var i = 0; i < steps.length; i++) { var step = steps[i]; if (typeof data !== 'object' || typeof data[step] === 'undefined') { throw 'Required field "' + path + '" is not present in data received.'; } data = data[step]; } return data; } function checkParams(params) { ['project_id', 'time_window', 'database_id', 'token'].forEach(function (field) { if (typeof params !== 'object' || typeof params[field] === 'undefined' || params[field] === '') { throw 'Required param is not set: ' + field + '.'; } }); if (!params.time_window.match(/^[0-9]+(m|h)$/)) { throw 'Incorrect update interval for parameter time_window. Valid ranges 1m-23h.'; } return params; } function getHttpData(url, token, body) { var tls = new CTlsConfig(params.tls_verify), request = new HttpRequest(tls.options); if (typeof params.proxy !== 'undefined' && params.proxy !== '') { request.setProxy(params.proxy); }; request.addHeader('Authorization: Bearer ' + token); request.addHeader('Content-Type: application/json'); var response = request.post(url, JSON.stringify(body)); Zabbix.log(4, '[ GCP API ] [ ' + url + ' ] Received response with status code ' + request.getStatus() + ': ' + response); if (response !== null) { try { response = JSON.parse(response); } catch (error) { throw 'Failed to parse response received from GCP API. Check debug log for more information.'; } } if (typeof response !== 'object' || response === null) { throw 'Cannot process response data: received data is not an object. Check debug log for more information.'; } if (request.getStatus() !== 200) { if (typeof (response.error && response.error.message) !== 'undefined') { throw response.error.message; } else { throw 'Failed to receive data: invalid response status code. Check debug log for more information.'; } } return response; }; var params = checkParams(JSON.parse(value)), j_filter = ' | within ' + params.time_window + " | filter database_id = '" + params.database_id + "'" + ' | join', url = 'https://monitoring.googleapis.com/v3/projects/' + params.project_id + '/timeSeries:query', raw = getHttpData(url, params.token, { query: '{' + base_paths.map(function (path) { return base_path + path; }).join(';') + '}' + j_filter }), metrics = getField(raw, 'timeSeriesDescriptor.pointDescriptors'), time_series = getField(raw, 'timeSeriesData'); for (t in time_series) { data = { resource: getField(time_series[t], 'labelValues.3.stringValue'), metrics: {} }; for (i in metrics) { re = /(^t_\d+\.value(_|\.))/g; data['metrics'][getField(metrics[i], 'key').replace(re, '')] = getField(time_series[t], 'pointData.0.values.' + i + '.int64Value'); } arr.push(data); } return JSON.stringify(arr); description: 'MSSQL resources data in raw format.' preprocessing: - type: CHECK_NOT_SUPPORTED parameters: - '-1' timeout: '{$GCP.DATA.TIMEOUT}' parameters: - name: database_id value: '{$GCP.PROJECT.ID}:{HOST.NAME}' - name: project_id value: '{$GCP.PROJECT.ID}' - name: proxy value: '{$GCP.PROXY}' - name: time_window value: '{$GCP.TIME.WINDOW}' - name: tls_verify value: '{$HTTP.TLS.VERIFY:"GCP Cloud SQL MSSQL"}' - name: token value: '{$GCP.AUTH.TOKEN}' tags: - tag: component value: raw - uuid: 3c14882329b94b8e88ca5d85e21bb7c8 name: 'Schedulers get' type: SCRIPT key: gcp.cloudsql.mssql.schedulers.get delay: '{$GCP.TIME.WINDOW}' history: '0' value_type: TEXT trends: '0' params: | const CTlsConfig = function (raw_value) { raw_value = String(raw_value).trim().toLowerCase(); if (['none', 'peer', 'full'].indexOf(raw_value) === -1) { raw_value = 'full'; } this.options = { SSLVerifyPeer: (raw_value === 'peer' || raw_value === 'full'), SSLVerifyHost: (raw_value === 'full') }; this.enabled = (raw_value !== 'none'); this.checkURL = function (url) { if (this.enabled && !/^https:\/\//i.test(url)) { throw 'TLS certificate verification is enabled by {$' + 'HTTP.TLS.VERIFY}, but the URL uses plain HTTP: ' + url + '. Use an "https://" URL, or set {$' + 'HTTP.TLS.VERIFY} to `none` to disable verification.'; } return url; }; } var base_path = 'cloudsql.googleapis.com/database/sqlserver/schedulers/', arr = [], base_paths = ['active_workers', 'current_tasks', 'current_workers', 'pending_disk_io', 'runnable_tasks', 'work_queue']; function getField(data, path) { var steps = path.split('.'); for (var i = 0; i < steps.length; i++) { var step = steps[i]; if (typeof data !== 'object' || typeof data[step] === 'undefined') { throw 'Required field "' + path + '" is not present in data received.'; } data = data[step]; } return data; } function checkParams(params) { ['project_id', 'time_window', 'database_id', 'token'].forEach(function (field) { if (typeof params !== 'object' || typeof params[field] === 'undefined' || params[field] === '') { throw 'Required param is not set: ' + field + '.'; } }); if (!params.time_window.match(/^[0-9]+(m|h)$/)) { throw 'Incorrect update interval for parameter time_window. Valid ranges 1m-23h.'; } return params; } function getHttpData(url, token, body) { var tls = new CTlsConfig(params.tls_verify), request = new HttpRequest(tls.options); if (typeof params.proxy !== 'undefined' && params.proxy !== '') { request.setProxy(params.proxy); }; request.addHeader('Authorization: Bearer ' + token); request.addHeader('Content-Type: application/json'); var response = request.post(url, JSON.stringify(body)); Zabbix.log(4, '[ GCP API ] [ ' + url + ' ] Received response with status code ' + request.getStatus() + ': ' + response); if (response !== null) { try { response = JSON.parse(response); } catch (error) { throw 'Failed to parse response received from GCP API. Check debug log for more information.'; } } if (typeof response !== 'object' || response === null) { throw 'Cannot process response data: received data is not an object. Check debug log for more information.'; } if (request.getStatus() !== 200) { if (typeof (response.error && response.error.message) !== 'undefined') { throw response.error.message; } else { throw 'Failed to receive data: invalid response status code. Check debug log for more information.'; } } return response; }; var params = checkParams(JSON.parse(value)), j_filter = ' | within ' + params.time_window + " | filter database_id = '" + params.database_id + "'" + ' | join', url = 'https://monitoring.googleapis.com/v3/projects/' + params.project_id + '/timeSeries:query', raw = getHttpData(url, params.token, { query: '{' + base_paths.map(function (path) { return base_path + path; }).join(';') + '}' + j_filter }), metrics = getField(raw, 'timeSeriesDescriptor.pointDescriptors'), time_series = getField(raw, 'timeSeriesData'); for (t in time_series) { data = { scheduler: getField(time_series[t], 'labelValues.3.stringValue'), metrics: {} }; for (i in metrics) { re = /(^t_\d+\.value(_|\.))/g; data['metrics'][getField(metrics[i], 'key').replace(re, '')] = getField(time_series[t], 'pointData.0.values.' + i + '.int64Value'); } arr.push(data); } return JSON.stringify(arr); description: 'MSSQL schedulers data in raw format.' preprocessing: - type: CHECK_NOT_SUPPORTED parameters: - '-1' timeout: '{$GCP.DATA.TIMEOUT}' parameters: - name: database_id value: '{$GCP.PROJECT.ID}:{HOST.NAME}' - name: project_id value: '{$GCP.PROJECT.ID}' - name: proxy value: '{$GCP.PROXY}' - name: time_window value: '{$GCP.TIME.WINDOW}' - name: tls_verify value: '{$HTTP.TLS.VERIFY:"GCP Cloud SQL MSSQL"}' - name: token value: '{$GCP.AUTH.TOKEN}' tags: - tag: component value: raw - uuid: 7d2c28a537e44ee284e4b5d186b935d6 name: 'Batch requests' type: DEPENDENT key: gcp.cloudsql.mssql.trans.batch_request_count delay: '0' description: 'Total number of Transact-SQL command batches received.' preprocessing: - type: JSONPATH parameters: - $.mssql_batch_request_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: transactions - uuid: 774cffa1a1f24d778f346bea24926690 name: 'Forwarded records' type: DEPENDENT key: gcp.cloudsql.mssql.trans.forwarded_record_count delay: '0' description: 'Total number of records fetched through forwarded record pointers.' preprocessing: - type: JSONPATH parameters: - $.mssql_forwarded_record_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: transactions - uuid: f80a924d20c4470496202d3e32261e7d name: 'Full scans' type: DEPENDENT key: gcp.cloudsql.mssql.trans.full_scan_count delay: '0' description: | Total number of unrestricted full scans. These can be either base-table or full-index scans. preprocessing: - type: JSONPATH parameters: - $.mssql_full_scan_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: transactions - uuid: cd6f91964b7041bb929c6f6f3430cc75 name: 'Page splits' type: DEPENDENT key: gcp.cloudsql.mssql.trans.page_split_count delay: '0' description: 'Total number of page splits that occur as the result of overflowing index pages.' preprocessing: - type: JSONPATH parameters: - $.mssql_page_split_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: transactions - uuid: 0b431016ca8245dea8e33c252a9a7c9b name: 'Probe scans' type: DEPENDENT key: gcp.cloudsql.mssql.trans.probe_scan_count delay: '0' description: 'Total number of probe scans that are used to find at least one single qualified row in an index or base table directly.' preprocessing: - type: JSONPATH parameters: - $.mssql_probe_scan_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: transactions - uuid: bf4eb81de038452cb3af16b4efcdb4c5 name: 'SQL compilations' type: DEPENDENT key: gcp.cloudsql.mssql.trans.sql_compilation_count delay: '0' description: 'Total number of SQL compilations.' preprocessing: - type: JSONPATH parameters: - $.mssql_sql_compilation_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: transactions - uuid: 39c350f5d1f94726b03c435e9e5e3110 name: 'SQL recompilations' type: DEPENDENT key: gcp.cloudsql.mssql.trans.sql_recompilation_count delay: '0' description: 'Total number of SQL recompilations.' preprocessing: - type: JSONPATH parameters: - $.mssql_sql_recompilation_count master_item: key: gcp.cloudsql.mssql.metrics.get tags: - tag: component value: transactions discovery_rules: - uuid: 2dad3e63931942f8937b8720c9477a66 name: 'Databases discovery' type: DEPENDENT key: gcp.cloudsql.db.discovery delay: '0' filter: evaltype: AND conditions: - macro: '{#DB.NAME}' value: '{$CLOUD_SQL.MSSQL.DB.NAME.MATCHES}' formulaid: A - macro: '{#DB.NAME}' value: '{$CLOUD_SQL.MSSQL.DB.NAME.NOT_MATCHES}' operator: NOT_MATCHES_REGEX formulaid: B description: 'Databases discovery.' item_prototypes: - uuid: 8f6deafa9efd416797ea58d1fce41c29 name: 'Database [{#DB.NAME}]: Log bytes flushed' type: DEPENDENT key: 'gcp.cloudsql.mssql.db.log_bytes_flushed_count[{#DB.NAME}]' delay: '0' units: B description: 'Total number of log bytes flushed for the [{#DB.NAME}] database.' preprocessing: - type: JSONPATH parameters: - $.log_bytes_flushed_count master_item: key: 'gcp.cloudsql.mssql.db.raw[{#DB.NAME}]' tags: - tag: component value: database - tag: database value: '{#DB.NAME}' - uuid: b52f757bc2b341e891f60e15795088ca name: 'Database [{#DB.NAME}]: Raw data' type: DEPENDENT key: 'gcp.cloudsql.mssql.db.raw[{#DB.NAME}]' delay: '0' history: '0' value_type: TEXT trends: '0' description: 'Data in raw format for the [{#DB.NAME}] database.' preprocessing: - type: JSONPATH parameters: - '$[?(@.database == "{#DB.NAME}")].metrics.first()' master_item: key: gcp.cloudsql.mssql.db.get tags: - tag: component value: raw - tag: database value: '{#DB.NAME}' - uuid: 9f8d37a25e1041c087bbea8d670cce46 name: 'Database [{#DB.NAME}]: Transactions started' type: DEPENDENT key: 'gcp.cloudsql.mssql.db.transaction_count[{#DB.NAME}]' delay: '0' description: 'Total number of transactions started for the [{#DB.NAME}] database.' preprocessing: - type: JSONPATH parameters: - $.transaction_count master_item: key: 'gcp.cloudsql.mssql.db.raw[{#DB.NAME}]' tags: - tag: component value: database - tag: database value: '{#DB.NAME}' master_item: key: gcp.cloudsql.mssql.db.get lld_macro_paths: - lld_macro: '{#DB.NAME}' path: $.database preprocessing: - type: DISCARD_UNCHANGED_HEARTBEAT parameters: - 3h - uuid: d47a9164fdf54309ab96f48f6dc3c037 name: 'Resources discovery' type: DEPENDENT key: gcp.cloudsql.resources.discovery delay: '0' filter: evaltype: AND conditions: - macro: '{#RESOURCE.NAME}' value: '{$CLOUD_SQL.MSSQL.RES.NAME.MATCHES}' formulaid: A - macro: '{#RESOURCE.NAME}' value: '{$CLOUD_SQL.MSSQL.RES.NAME.NOT_MATCHES}' operator: NOT_MATCHES_REGEX formulaid: B description: 'Resources discovery.' item_prototypes: - uuid: 55bb12000dca46e096045da83a46edea name: 'Resource [{#RESOURCE.NAME}]: Deadlocks' type: DEPENDENT key: 'gcp.cloudsql.mssql.resource.deadlock_count[{#RESOURCE.NAME}]' delay: '0' description: 'Total number of lock requests that resulted in a deadlock for the [{#RESOURCE.NAME}] resource.' preprocessing: - type: JSONPATH parameters: - $.deadlock_count master_item: key: 'gcp.cloudsql.mssql.resource.raw[{#RESOURCE.NAME}]' tags: - tag: component value: resource - tag: resource value: '{#RESOURCE.NAME}' - uuid: 0117dba440f34461959490efaff059e2 name: 'Resource [{#RESOURCE.NAME}]: Lock waits' type: DEPENDENT key: 'gcp.cloudsql.mssql.resource.lock_wait_count[{#RESOURCE.NAME}]' delay: '0' description: 'Total number of lock requests that required the caller to wait for the [{#RESOURCE.NAME}] resource.' preprocessing: - type: JSONPATH parameters: - $.lock_wait_count master_item: key: 'gcp.cloudsql.mssql.resource.raw[{#RESOURCE.NAME}]' tags: - tag: component value: resource - tag: resource value: '{#RESOURCE.NAME}' - uuid: 6e8b5646d70b470fa18960c7b43a9aff name: 'Resource [{#RESOURCE.NAME}]: Lock wait time' type: DEPENDENT key: 'gcp.cloudsql.mssql.resource.lock_wait_time[{#RESOURCE.NAME}]' delay: '0' units: s description: 'Total time lock requests were waiting for locks for the [{#RESOURCE.NAME}] resource.' preprocessing: - type: JSONPATH parameters: - $.lock_wait_time master_item: key: 'gcp.cloudsql.mssql.resource.raw[{#RESOURCE.NAME}]' tags: - tag: component value: resource - tag: resource value: '{#RESOURCE.NAME}' - uuid: 0cc16595d1e044db947084a44786aaaa name: 'Resource [{#RESOURCE.NAME}]: Raw data' type: DEPENDENT key: 'gcp.cloudsql.mssql.resource.raw[{#RESOURCE.NAME}]' delay: '0' history: '0' value_type: TEXT trends: '0' description: 'Data in raw format for the [{#RESOURCE.NAME}] resource.' preprocessing: - type: JSONPATH parameters: - '$[?(@.resource == "{#RESOURCE.NAME}")].metrics.first()' master_item: key: gcp.cloudsql.mssql.resources.get tags: - tag: component value: raw - tag: resource value: '{#RESOURCE.NAME}' master_item: key: gcp.cloudsql.mssql.resources.get lld_macro_paths: - lld_macro: '{#RESOURCE.NAME}' path: $.resource preprocessing: - type: DISCARD_UNCHANGED_HEARTBEAT parameters: - 3h - uuid: 31667d0e9c32458d9c895367b3ca7042 name: 'Schedulers discovery' type: DEPENDENT key: gcp.cloudsql.schedulers.discovery delay: '0' filter: evaltype: AND conditions: - macro: '{#SCHEDULER.ID}' value: '{$CLOUD_SQL.MSSQL.SCHEDULER.ID.MATCHES}' formulaid: A - macro: '{#SCHEDULER.ID}' value: '{$CLOUD_SQL.MSSQL.SCHEDULER.ID.NOT_MATCHES}' operator: NOT_MATCHES_REGEX formulaid: B description: 'Schedulers discovery.' item_prototypes: - uuid: 67bde61c128e4dfe9e47d8a3a6e3ceec name: 'Scheduler [{#SCHEDULER.ID}]: Active workers' type: DEPENDENT key: 'gcp.cloudsql.mssql.scheduler.active_workers[{#SCHEDULER.ID}]' delay: '0' description: | Current number of active workers associated with the scheduler that goes by its ID [{#SCHEDULER.ID}]. An active worker is never preemptive, must have an associated task, and is either running, runnable, or suspended. preprocessing: - type: JSONPATH parameters: - $.active_workers master_item: key: 'gcp.cloudsql.mssql.scheduler.raw[{#SCHEDULER.ID}]' tags: - tag: component value: scheduler - tag: scheduler value: '{#SCHEDULER.ID}' - uuid: fc5113b2b5f64205ad9d100bd480adea name: 'Scheduler [{#SCHEDULER.ID}]: Current tasks' type: DEPENDENT key: 'gcp.cloudsql.mssql.scheduler.current_tasks[{#SCHEDULER.ID}]' delay: '0' description: | Current number of present tasks associated with the scheduler that goes by its ID [{#SCHEDULER.ID}]. This count includes tasks that are waiting for a worker to execute them and tasks that are currently waiting or running (in SUSPENDED or RUNNABLE state). preprocessing: - type: JSONPATH parameters: - $.current_tasks master_item: key: 'gcp.cloudsql.mssql.scheduler.raw[{#SCHEDULER.ID}]' tags: - tag: component value: scheduler - tag: scheduler value: '{#SCHEDULER.ID}' - uuid: 6602f8d26a9a46fb8ed74a40d10f36e4 name: 'Scheduler [{#SCHEDULER.ID}]: Current workers' type: DEPENDENT key: 'gcp.cloudsql.mssql.scheduler.current_workers[{#SCHEDULER.ID}]' delay: '0' description: | Current number of workers associated with the scheduler that goes by its ID [{#SCHEDULER.ID}]. It includes workers that are not assigned any task. preprocessing: - type: JSONPATH parameters: - $.current_workers master_item: key: 'gcp.cloudsql.mssql.scheduler.raw[{#SCHEDULER.ID}]' tags: - tag: component value: scheduler - tag: scheduler value: '{#SCHEDULER.ID}' - uuid: 2022a85bb7c04e73a75136e55162084a name: 'Scheduler [{#SCHEDULER.ID}]: Pending I/O operations' type: DEPENDENT key: 'gcp.cloudsql.mssql.scheduler.pending_disk_io[{#SCHEDULER.ID}]' delay: '0' description: | Current number of pending I/Os waiting to be completed that are associated with the scheduler that goes by its ID [{#SCHEDULER.ID}]. Each scheduler has a list of pending I/Os that are checked to determine whether they have been completed every time there is a context switch. The count is incremented when the request is inserted. This count is decremented when the request is completed. This number does not indicate the state of the I/Os. preprocessing: - type: JSONPATH parameters: - $.pending_disk_io master_item: key: 'gcp.cloudsql.mssql.scheduler.raw[{#SCHEDULER.ID}]' tags: - tag: component value: scheduler - tag: scheduler value: '{#SCHEDULER.ID}' - uuid: c3e337845c344f4cb0d14a5e7d22cbb7 name: 'Scheduler [{#SCHEDULER.ID}]: Raw data' type: DEPENDENT key: 'gcp.cloudsql.mssql.scheduler.raw[{#SCHEDULER.ID}]' delay: '0' history: '0' value_type: TEXT trends: '0' description: 'Data in raw format associated with the scheduler that goes by its ID [{#SCHEDULER.ID}].' preprocessing: - type: JSONPATH parameters: - '$[?(@.scheduler == "{#SCHEDULER.ID}")].metrics.first()' master_item: key: gcp.cloudsql.mssql.schedulers.get tags: - tag: component value: raw - tag: scheduler value: '{#SCHEDULER.ID}' - uuid: a9852ac0cdcc423fad6b305eb57eedf1 name: 'Scheduler [{#SCHEDULER.ID}]: Runnable tasks' type: DEPENDENT key: 'gcp.cloudsql.mssql.scheduler.runnable_tasks[{#SCHEDULER.ID}]' delay: '0' description: 'Current number of workers that are associated with the scheduler that goes by its ID [{#SCHEDULER.ID}] and have assigned tasks waiting to be scheduled on the runnable queue.' preprocessing: - type: JSONPATH parameters: - $.runnable_tasks master_item: key: 'gcp.cloudsql.mssql.scheduler.raw[{#SCHEDULER.ID}]' tags: - tag: component value: scheduler - tag: scheduler value: '{#SCHEDULER.ID}' - uuid: eaae59fb97124471b1c7c30c601dba1f name: 'Scheduler [{#SCHEDULER.ID}]: Work queue' type: DEPENDENT key: 'gcp.cloudsql.mssql.scheduler.work_queue[{#SCHEDULER.ID}]' delay: '0' description: | Current number of tasks in the pending queue associated with the scheduler that goes by its ID [{#SCHEDULER.ID}]. These tasks are waiting for a worker to pick them up. preprocessing: - type: JSONPATH parameters: - $.work_queue master_item: key: 'gcp.cloudsql.mssql.scheduler.raw[{#SCHEDULER.ID}]' tags: - tag: component value: scheduler - tag: scheduler value: '{#SCHEDULER.ID}' master_item: key: gcp.cloudsql.mssql.schedulers.get lld_macro_paths: - lld_macro: '{#SCHEDULER.ID}' path: $.scheduler preprocessing: - type: DISCARD_UNCHANGED_HEARTBEAT parameters: - 3h tags: - tag: class value: cloud - tag: target value: cloudsql - tag: target value: gcp - tag: target value: google-cloud-platform - tag: target value: mssql macros: - macro: '{$CLOUD_SQL.MSSQL.CPU.UTIL.MAX}' value: '95' description: 'GCP Cloud SQL MSSQL instance CPU usage threshold.' - macro: '{$CLOUD_SQL.MSSQL.DB.NAME.MATCHES}' value: '.*' description: 'The filter to include GCP Cloud SQL MSSQL databases by namespace.' - macro: '{$CLOUD_SQL.MSSQL.DB.NAME.NOT_MATCHES}' value: CHANGE_IF_NEEDED description: 'The filter to exclude GCP Cloud SQL MSSQL databases by namespace.' - macro: '{$CLOUD_SQL.MSSQL.DISK.UTIL.CRIT}' value: '90' description: 'GCP Cloud SQL MSSQL instance critical disk usage threshold.' - macro: '{$CLOUD_SQL.MSSQL.DISK.UTIL.WARN}' value: '80' description: 'GCP Cloud SQL MSSQL instance warning disk usage threshold.' - macro: '{$CLOUD_SQL.MSSQL.RAM.UTIL.MAX}' value: '90' description: 'GCP Cloud SQL MSSQL instance RAM usage threshold.' - macro: '{$CLOUD_SQL.MSSQL.RES.NAME.MATCHES}' value: '.*' description: 'The filter to include GCP Cloud SQL MSSQL resources by namespace.' - macro: '{$CLOUD_SQL.MSSQL.RES.NAME.NOT_MATCHES}' value: CHANGE_IF_NEEDED description: 'The filter to exclude GCP Cloud SQL MSSQL resources by namespace.' - macro: '{$CLOUD_SQL.MSSQL.SCHEDULER.ID.MATCHES}' value: '.*' description: 'The filter to include GCP Cloud SQL MSSQL schedulers by namespace.' - macro: '{$CLOUD_SQL.MSSQL.SCHEDULER.ID.NOT_MATCHES}' value: CHANGE_IF_NEEDED description: 'The filter to exclude GCP Cloud SQL MSSQL schedulers by namespace.' - macro: '{$GCP.DATA.TIMEOUT}' value: 15s description: 'A response timeout for an API.' - macro: '{$GCP.PROXY}' description: 'Sets HTTP proxy value. If this macro is empty then no proxy is used.' - macro: '{$GCP.TIME.WINDOW}' value: 5m description: | Time interval for the data requests. Supported usage type: 1. The default update interval for most of the items. 2. The minimal time window for the data requested in the Monitoring Query Language REST API request. - macro: '{$HTTP.TLS.VERIFY}' value: full description: 'TLS certificate verification for script items: "none" - disabled, "peer" - verify the certificate chain and expiration, "full" - full verification. Any other value enables full verification. To override the setting for this template only, define the macro with the context "GCP Cloud SQL MSSQL", e.g. {$HTTP.TLS.VERIFY:"GCP Cloud SQL MSSQL"}.' dashboards: - uuid: 4233430037fc4570b3a5eb3f23ef02e6 name: 'MSSQL instance' pages: - name: 'MSSQL instance' widgets: - type: graph width: '36' height: '5' fields: - type: ITEM name: itemid.0 value: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.cpu.usage_time - type: STRING name: reference value: AAAAA - type: INTEGER name: source_type value: '1' - type: graph 'y': '5' width: '36' height: '5' fields: - type: GRAPH name: graphid.0 value: host: 'GCP Cloud SQL MSSQL by HTTP' name: 'GCP Cloud SQL MSSQL: Memory usage' - type: STRING name: reference value: AAAAC - type: graph 'y': '10' width: '36' height: '5' fields: - type: ITEM name: itemid.0 value: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.disk.utilization - type: STRING name: reference value: AAAAE - type: INTEGER name: source_type value: '1' - type: graph 'y': '15' width: '36' height: '5' fields: - type: GRAPH name: graphid.0 value: host: 'GCP Cloud SQL MSSQL by HTTP' name: 'GCP Cloud SQL MSSQL: Network traffic' - type: STRING name: reference value: AAAAG - type: graph 'y': '20' width: '36' height: '5' fields: - type: GRAPH name: graphid.0 value: host: 'GCP Cloud SQL MSSQL by HTTP' name: 'GCP Cloud SQL MSSQL: Transactions' - type: STRING name: reference value: AAAAI - type: graph 'y': '25' width: '36' height: '5' fields: - type: GRAPH name: graphid.0 value: host: 'GCP Cloud SQL MSSQL by HTTP' name: 'GCP Cloud SQL MSSQL: Memory workflow' - type: STRING name: reference value: AAAAK - type: graph x: '36' width: '36' height: '5' fields: - type: ITEM name: itemid.0 value: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.cpu.utilization - type: STRING name: reference value: AAAAB - type: INTEGER name: source_type value: '1' - type: graph x: '36' 'y': '5' width: '36' height: '5' fields: - type: ITEM name: itemid.0 value: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.memory.utilization - type: STRING name: reference value: AAAAD - type: INTEGER name: source_type value: '1' - type: graph x: '36' 'y': '10' width: '36' height: '5' fields: - type: GRAPH name: graphid.0 value: host: 'GCP Cloud SQL MSSQL by HTTP' name: 'GCP Cloud SQL MSSQL: Disk read/write operations' - type: STRING name: reference value: AAAAF - type: graph x: '36' 'y': '15' width: '36' height: '5' fields: - type: ITEM name: itemid.0 value: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.network.connections - type: STRING name: reference value: AAAAH - type: INTEGER name: source_type value: '1' - type: graph x: '36' 'y': '20' width: '36' height: '5' fields: - type: ITEM name: itemid.0 value: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.memory.buffer_cache_hit_ratio - type: STRING name: reference value: AAAAJ - type: INTEGER name: source_type value: '1' - type: graph x: '36' 'y': '25' width: '36' height: '5' fields: - type: GRAPH name: graphid.0 value: host: 'GCP Cloud SQL MSSQL by HTTP' name: 'GCP Cloud SQL MSSQL: Memory Read/Writes' - type: STRING name: reference value: AAAAL valuemaps: - uuid: d2aa57a09cbb43e8b53b11316193f013 name: 'Database availability' mappings: - value: '0' newvalue: Down - value: '1' newvalue: Up - uuid: 1c26d58d111b4689a2e7acd23a38f1ca name: 'Instance state' mappings: - value: '0' newvalue: Running - value: '1' newvalue: Suspended - value: '2' newvalue: Runnable - value: '3' newvalue: 'Pending create' - value: '4' newvalue: Maintenance - value: '5' newvalue: Failed - value: '6' newvalue: 'Unknown state' - value: '10' newvalue: 'Data retrievement error' graphs: - uuid: 1ff7d04868284c07829382c44301fe0e name: 'GCP Cloud SQL MSSQL: Disk read/write operations' graph_items: - color: 199C0D item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.disk.read_ops_count - sortorder: '1' color: F63100 item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.disk.write_ops_count - uuid: 0ef4b456168d4ecd87c3dc68092c673d name: 'GCP Cloud SQL MSSQL: Disk usage' graph_items: - drawtype: BOLD_LINE color: 199C0D calc_fnc: MIN item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.disk.quota - sortorder: '1' drawtype: GRADIENT_LINE color: F63100 item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.disk.bytes_used - uuid: 09b009aa17f1410bb926043b4c7c39e4 name: 'GCP Cloud SQL MSSQL: Memory Read/Writes' graph_items: - color: 199C0D item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.memory.page_ops.read - sortorder: '1' color: F63100 item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.memory.page_ops.write - uuid: 3273bee668e84b26911307ea4c1f620f name: 'GCP Cloud SQL MSSQL: Memory usage' graph_items: - drawtype: BOLD_LINE color: 199C0D calc_fnc: MIN item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.memory.quota - sortorder: '1' drawtype: GRADIENT_LINE color: F63100 item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.memory.usage - uuid: 4d5afffa67804f4da73474d2ee26bd5d name: 'GCP Cloud SQL MSSQL: Memory workflow' graph_items: - color: 199C0D item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.memory.checkpoint_page_count - sortorder: '1' color: F63100 item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.memory.free_list_stall_count - sortorder: '2' color: 00611C item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.memory.lazy_write_count - sortorder: '3' color: F7941D item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.memory.memory_grants_pending - uuid: 558283815ab2414f8da6387724a784a3 name: 'GCP Cloud SQL MSSQL: Network traffic' type: STACKED graph_items: - color: 199C0D item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.network.received_bytes_count - sortorder: '1' color: F63100 item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.network.sent_bytes_count - uuid: 922d35bf685b473ca9848a9b9e7e34c3 name: 'GCP Cloud SQL MSSQL: Transactions' graph_items: - color: 199C0D item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.trans.batch_request_count - sortorder: '1' color: F63100 item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.trans.forwarded_record_count - sortorder: '2' color: 00611C item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.trans.full_scan_count - sortorder: '3' color: F7941D item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.trans.page_split_count - sortorder: '4' color: FC6EA3 item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.trans.probe_scan_count - sortorder: '5' color: 6C59DC item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.trans.sql_compilation_count - sortorder: '6' color: C7A72D item: host: 'GCP Cloud SQL MSSQL by HTTP' key: gcp.cloudsql.mssql.trans.sql_recompilation_count