zabbix_export: version: '7.0' template_groups: - uuid: c2c162144c2d4c5491c8801193af4945 name: Templates/Cloud templates: - uuid: 9d7871c093f9489293421396b9fea668 template: 'GCP Cloud SQL MySQL Replica by HTTP' name: 'GCP Cloud SQL MySQL Replica by HTTP' description: | Get GCP Cloud SQL MySQL monitoring for read-only replicas 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-2 groups: - name: Templates/Cloud items: - uuid: 17aa9eb4749e46be9b0b0f93ef011fca name: 'Last I/O thread error number' type: DEPENDENT key: gcp.cloudsql.mysql.repl.last_io_errno delay: '0' description: 'The error number of the most recent error that caused the I/O thread to stop.' preprocessing: - type: JSONPATH parameters: - $.last_io_errno master_item: key: gcp.cloudsql.mysql.repl.metrics.get tags: - tag: component value: replication - uuid: 52c11965a2fc4d37a97ebccc04ee7b80 name: 'Last SQL thread error number' type: DEPENDENT key: gcp.cloudsql.mysql.repl.last_sql_errno delay: '0' description: 'The error number of the most recent error that caused the SQL thread to stop.' preprocessing: - type: JSONPATH parameters: - $.last_sql_errno master_item: key: gcp.cloudsql.mysql.repl.metrics.get tags: - tag: component value: replication - uuid: 470200d2cca348bb8a670a2be3a270e3 name: 'Replica metrics get' type: SCRIPT key: gcp.cloudsql.mysql.repl.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/replication/', mysql_path = 'cloudsql.googleapis.com/database/mysql/replication/', raw_metrics, obj = {}, base_paths = ['network_lag', 'replica_lag'].map(function (path) { return base_path + path; }), mysql_paths = [ 'last_io_errno', 'last_sql_errno', 'slave_io_running', 'slave_sql_running'].map(function (path) { return mysql_path + path; }).concat( ['network_lag', 'replica_lag'].map(function (path) { return base_path + path; })); 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)), 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', metric_raw = getHttpData(url, params.token, { query: '{' + mysql_paths.join(';') + '}' + j_filter }), metric_keys = getField(metric_raw, 'timeSeriesDescriptor.pointDescriptors'), metric_values = getField(metric_raw, 'timeSeriesData.0.pointData.0.values'), re = /(^t_\d+\.value\.)/g; for (i in metric_keys) { var value_types = ['int64Value', 'doubleValue', 'stringValue', 'boolValue']; for (v in value_types) { if (typeof metric_values[i][value_types[v]] !== 'undefined') { obj[getField(metric_keys[i], 'key').replace(re, "")] = metric_values[i][value_types[v]]; } } } var state_raw = getHttpData(url, params.token, { query: base_path + 'state' + filter }), state_keys = getField(state_raw, 'timeSeriesDescriptor.pointDescriptors'), state_tsd = getField(state_raw, 'timeSeriesData'); for (i in state_keys) { var state_key = getField(state_keys[i], 'key').replace(/value\./g, ''); obj[state_key] = 'undefined'; for (t in state_tsd) { if (getField(state_tsd[t], 'pointData.0.values.' + i + '.boolValue') == true) { obj[state_key] = getField(state_tsd[t], 'labelValues.3.stringValue'); break; } } } return JSON.stringify(obj); description: 'MySQL replication metrics 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 MySQL Replica"}' - name: token value: '{$GCP.AUTH.TOKEN}' tags: - tag: component value: raw - uuid: e84ac3a2537848b29024659ab5e3a0e9 name: 'Network lag' type: DEPENDENT key: gcp.cloudsql.mysql.repl.network_lag delay: '0' units: s description: 'Indicates time taken from primary binary log to IO thread on replica.' preprocessing: - type: JSONPATH parameters: - $.network_lag master_item: key: gcp.cloudsql.mysql.repl.metrics.get tags: - tag: component value: replication - uuid: b5f9960de9414975ac48357844a15860 name: 'Replication lag' type: DEPENDENT key: gcp.cloudsql.mysql.repl.replica_lag delay: '0' units: s description: 'Number of seconds the read replica is behind its primary (approximation).' preprocessing: - type: JSONPATH parameters: - $.replica_lag master_item: key: gcp.cloudsql.mysql.repl.metrics.get tags: - tag: component value: replication - uuid: 0c093a0416f24afb96fc28a7abb283d4 name: 'Slave I/O thread running' type: DEPENDENT key: gcp.cloudsql.mysql.repl.slave_io_running delay: '0' description: | Indicates whether the I/O thread for reading the primary's binary log is running. Possible values are Yes, No and Connecting. valuemap: name: 'Thread state' preprocessing: - type: JSONPATH parameters: - $.slave_io_running - type: JAVASCRIPT parameters: - | const idx = [ 'No', 'Yes', 'Connecting', ].indexOf(value); return idx !== -1 ? idx : 10; master_item: key: gcp.cloudsql.mysql.repl.metrics.get tags: - tag: component value: replication - uuid: 994d92340dd146a381f2e6bbebd1c3f2 name: 'Slave SQL thread running' type: DEPENDENT key: gcp.cloudsql.mysql.repl.slave_sql_running delay: '0' description: 'Indicates whether the SQL thread for executing events in the relay log is running.' valuemap: name: 'Thread state' preprocessing: - type: JSONPATH parameters: - $.slave_sql_running - type: BOOL_TO_DECIMAL parameters: - '' master_item: key: gcp.cloudsql.mysql.repl.metrics.get tags: - tag: component value: replication - uuid: 90a9154072164b5c89d715fa77e7e2b0 name: 'Replication state' type: DEPENDENT key: gcp.cloudsql.mysql.repl.state delay: '0' description: | The current serving state of replication. This metric is only available for the MySQL/PostgreSQL instances. valuemap: name: 'Replication state' preprocessing: - type: JSONPATH parameters: - $.state - type: JAVASCRIPT parameters: - | const idx = [ 'Running', 'Syncing', 'Unsynced', 'Stopped', 'Error', ].indexOf(value); return idx !== -1 ? idx : 10; master_item: key: gcp.cloudsql.mysql.repl.metrics.get tags: - tag: component value: replication - tag: component value: status tags: - tag: class value: cloud - tag: target value: cloudsql - tag: target value: gcp - tag: target value: google-cloud-platform - tag: target value: mysql - tag: target value: replica macros: - 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 MySQL Replica", e.g. {$HTTP.TLS.VERIFY:"GCP Cloud SQL MySQL Replica"}.' dashboards: - uuid: 76eda12212f045388bef22cb382eff6b name: 'MySQL replica' pages: - name: 'MySQL replica' widgets: - type: graph width: '72' height: '5' fields: - type: GRAPH name: graphid.0 value: host: 'GCP Cloud SQL MySQL Replica by HTTP' name: 'GCP Cloud SQL MySQL: Lags' - type: STRING name: reference value: AAAAA - type: item name: 'I/O Thread State' 'y': '5' width: '18' height: '3' fields: - type: ITEM name: itemid.0 value: host: 'GCP Cloud SQL MySQL Replica by HTTP' key: gcp.cloudsql.mysql.repl.slave_io_running - type: INTEGER name: show.0 value: '2' - type: item name: 'I/O Thread Error' x: '18' 'y': '5' width: '18' height: '3' fields: - type: INTEGER name: decimal_size value: '1' - type: ITEM name: itemid.0 value: host: 'GCP Cloud SQL MySQL Replica by HTTP' key: gcp.cloudsql.mysql.repl.last_io_errno - type: INTEGER name: show.0 value: '2' - type: item name: 'SQL Thread State' x: '36' 'y': '5' width: '18' height: '3' fields: - type: INTEGER name: decimal_size value: '1' - type: ITEM name: itemid.0 value: host: 'GCP Cloud SQL MySQL Replica by HTTP' key: gcp.cloudsql.mysql.repl.slave_sql_running - type: INTEGER name: show.0 value: '2' - type: item name: 'SQL Thread Error' x: '54' 'y': '5' width: '18' height: '3' fields: - type: INTEGER name: decimal_size value: '1' - type: ITEM name: itemid.0 value: host: 'GCP Cloud SQL MySQL Replica by HTTP' key: gcp.cloudsql.mysql.repl.last_sql_errno - type: INTEGER name: show.0 value: '2' valuemaps: - uuid: ea83090a16d94eb3bb643009c784d820 name: 'Replication state' mappings: - value: '0' newvalue: Running - value: '1' newvalue: Syncing - value: '2' newvalue: Unsynced - value: '3' newvalue: Stopped - value: '4' newvalue: Error - value: '10' newvalue: Unknown - uuid: b1acfa66a401499cae56777db6c7750b name: 'Thread state' mappings: - value: '0' newvalue: 'No' - value: '1' newvalue: 'Yes' - value: '2' newvalue: Connecting - value: '10' newvalue: Unknown graphs: - uuid: e94b7e8241b544e2beb4ea95db3e139f name: 'GCP Cloud SQL MySQL: Lags' graph_items: - color: 199C0D item: host: 'GCP Cloud SQL MySQL Replica by HTTP' key: gcp.cloudsql.mysql.repl.network_lag - sortorder: '1' color: F63100 item: host: 'GCP Cloud SQL MySQL Replica by HTTP' key: gcp.cloudsql.mysql.repl.replica_lag