Created
May 10, 2025 16:48
-
-
Save iamshreeram/2fad89d8fcaef93794e53cd7a8c5a357 to your computer and use it in GitHub Desktop.
simple dashboard json
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "annotations": { | |
| "list": [ | |
| { | |
| "builtIn": 1, | |
| "datasource": { | |
| "type": "grafana", | |
| "uid": "-- Grafana --" | |
| }, | |
| "enable": true, | |
| "hide": true, | |
| "iconColor": "rgba(0, 211, 255, 1)", | |
| "name": "Annotations & Alerts", | |
| "type": "dashboard" | |
| } | |
| ] | |
| }, | |
| "editable": true, | |
| "fiscalYearStartMonth": 0, | |
| "graphTooltip": 0, | |
| "id": 2, | |
| "links": [], | |
| "panels": [ | |
| { | |
| "collapsed": false, | |
| "gridPos": { | |
| "h": 1, | |
| "w": 24, | |
| "x": 0, | |
| "y": 0 | |
| }, | |
| "id": 22, | |
| "panels": [], | |
| "title": "Stats", | |
| "type": "row" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "thresholds" | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "waterlevel" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "#F2CC0C", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 4, | |
| "w": 24, | |
| "x": 0, | |
| "y": 1 | |
| }, | |
| "id": 5, | |
| "options": { | |
| "colorMode": "value", | |
| "graphMode": "area", | |
| "justifyMode": "auto", | |
| "orientation": "auto", | |
| "percentChangeColorMode": "standard", | |
| "reduceOptions": { | |
| "calcs": [ | |
| "lastNotNull" | |
| ], | |
| "fields": "", | |
| "values": false | |
| }, | |
| "showPercentChange": false, | |
| "textMode": "auto", | |
| "wideLayout": true | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT \n time as latest_time,\n temperature,\n waterlevel,\n luminance,\n phvalue\nFROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`\nWHERE \n time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)\nORDER BY \n time DESC\nLIMIT 1", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "", | |
| "transparent": true, | |
| "type": "stat" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "continuous-BlPu" | |
| }, | |
| "custom": { | |
| "axisPlacement": "auto", | |
| "fillOpacity": 70, | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "lineWidth": 1 | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "waterlevel" | |
| }, | |
| "properties": [] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 9, | |
| "w": 24, | |
| "x": 0, | |
| "y": 5 | |
| }, | |
| "id": 16, | |
| "options": { | |
| "colWidth": 0.9, | |
| "legend": { | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": true | |
| }, | |
| "rowHeight": 0.9, | |
| "showValue": "auto", | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n TIMESTAMP_TRUNC(time, DAY) as day,\n MIN(temperature) as min_temp,\n MAX(temperature) as max_temp,\n MAX(temperature) - MIN(temperature) as temp_range,\n MIN(phvalue) as min_ph,\n MAX(phvalue) as max_ph,\n MAX(phvalue) - MIN(phvalue) as ph_range,\n MIN(waterlevel) as min_wl,\n MAX(waterlevel) as max_wl,\n MAX(waterlevel) - MIN(waterlevel) as wl_range,\n MIN(luminance) as min_lumi,\n MAX(luminance) as max_lumi,\n MAX(luminance) - MIN(luminance) as lumi_range,\n\nFROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`\nWHERE \n time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)\nGROUP BY \n day\nORDER BY \n day", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Min/Max per day", | |
| "transparent": true, | |
| "type": "status-history" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "axisBorderShow": false, | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "barAlignment": 0, | |
| "barWidthFactor": 0.6, | |
| "drawStyle": "line", | |
| "fillOpacity": 0, | |
| "gradientMode": "none", | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "insertNulls": false, | |
| "lineInterpolation": "linear", | |
| "lineWidth": 1, | |
| "pointSize": 5, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| }, | |
| "showPoints": "auto", | |
| "spanNulls": false, | |
| "stacking": { | |
| "group": "A", | |
| "mode": "none" | |
| }, | |
| "thresholdsStyle": { | |
| "mode": "off" | |
| } | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [] | |
| }, | |
| "gridPos": { | |
| "h": 5, | |
| "w": 6, | |
| "x": 0, | |
| "y": 14 | |
| }, | |
| "id": 1, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": true | |
| }, | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT time, temperature FROM `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Temprature", | |
| "transparent": true, | |
| "type": "timeseries" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "axisBorderShow": false, | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "barAlignment": 0, | |
| "barWidthFactor": 0.6, | |
| "drawStyle": "line", | |
| "fillOpacity": 0, | |
| "gradientMode": "none", | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "insertNulls": false, | |
| "lineInterpolation": "linear", | |
| "lineWidth": 1, | |
| "pointSize": 5, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| }, | |
| "showPoints": "auto", | |
| "spanNulls": false, | |
| "stacking": { | |
| "group": "A", | |
| "mode": "none" | |
| }, | |
| "thresholdsStyle": { | |
| "mode": "off" | |
| } | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "waterlevel" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "orange", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 5, | |
| "w": 6, | |
| "x": 6, | |
| "y": 14 | |
| }, | |
| "id": 2, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": true | |
| }, | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT time, waterlevel FROM `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Water Level", | |
| "transparent": true, | |
| "type": "timeseries" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "axisBorderShow": false, | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "barAlignment": 0, | |
| "barWidthFactor": 0.6, | |
| "drawStyle": "line", | |
| "fillOpacity": 0, | |
| "gradientMode": "none", | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "insertNulls": false, | |
| "lineInterpolation": "linear", | |
| "lineWidth": 1, | |
| "pointSize": 5, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| }, | |
| "showPoints": "auto", | |
| "spanNulls": false, | |
| "stacking": { | |
| "group": "A", | |
| "mode": "none" | |
| }, | |
| "thresholdsStyle": { | |
| "mode": "off" | |
| } | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "waterlevel" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "semi-dark-purple", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "luminance" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "light-blue", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 5, | |
| "w": 6, | |
| "x": 12, | |
| "y": 14 | |
| }, | |
| "id": 3, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": true | |
| }, | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT time, luminance FROM `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "luminance", | |
| "transparent": true, | |
| "type": "timeseries" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "axisBorderShow": false, | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "barAlignment": 0, | |
| "barWidthFactor": 0.6, | |
| "drawStyle": "line", | |
| "fillOpacity": 0, | |
| "gradientMode": "none", | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "insertNulls": false, | |
| "lineInterpolation": "linear", | |
| "lineWidth": 1, | |
| "pointSize": 5, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| }, | |
| "showPoints": "auto", | |
| "spanNulls": false, | |
| "stacking": { | |
| "group": "A", | |
| "mode": "none" | |
| }, | |
| "thresholdsStyle": { | |
| "mode": "off" | |
| } | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "waterlevel" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "semi-dark-purple", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "luminance" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "super-light-red", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "phvalue" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "super-light-red", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 5, | |
| "w": 6, | |
| "x": 18, | |
| "y": 14 | |
| }, | |
| "id": 20, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": true | |
| }, | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT time, phvalue FROM `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "phvalues", | |
| "type": "timeseries" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "axisBorderShow": false, | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "barAlignment": 0, | |
| "barWidthFactor": 0.6, | |
| "drawStyle": "line", | |
| "fillOpacity": 0, | |
| "gradientMode": "none", | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "insertNulls": false, | |
| "lineInterpolation": "linear", | |
| "lineWidth": 1, | |
| "pointSize": 5, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| }, | |
| "showPoints": "auto", | |
| "spanNulls": false, | |
| "stacking": { | |
| "group": "A", | |
| "mode": "none" | |
| }, | |
| "thresholdsStyle": { | |
| "mode": "off" | |
| } | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [] | |
| }, | |
| "gridPos": { | |
| "h": 6, | |
| "w": 6, | |
| "x": 0, | |
| "y": 19 | |
| }, | |
| "id": 17, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": false | |
| }, | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n TIMESTAMP_TRUNC(time, HOUR) AS hour_interval,\n AVG(temperature) AS avg_temp,\n MIN(temperature) AS min_temp,\n MAX(temperature) AS max_temp,\n STDDEV(temperature) AS temp_stddev,\n COUNT(*) AS readings_count\nFROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`\nGROUP BY \n hour_interval\nORDER BY \n hour_interval", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Temprature", | |
| "transparent": true, | |
| "type": "timeseries" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "axisBorderShow": false, | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "barAlignment": 0, | |
| "barWidthFactor": 0.6, | |
| "drawStyle": "line", | |
| "fillOpacity": 0, | |
| "gradientMode": "none", | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "insertNulls": false, | |
| "lineInterpolation": "linear", | |
| "lineWidth": 1, | |
| "pointSize": 5, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| }, | |
| "showPoints": "auto", | |
| "spanNulls": false, | |
| "stacking": { | |
| "group": "A", | |
| "mode": "none" | |
| }, | |
| "thresholdsStyle": { | |
| "mode": "off" | |
| } | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "waterlevel" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "orange", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 6, | |
| "w": 6, | |
| "x": 6, | |
| "y": 19 | |
| }, | |
| "id": 18, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": false | |
| }, | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n TIMESTAMP_TRUNC(time, HOUR) AS hour_interval,\n AVG(waterlevel) AS avg_temp,\n MIN(waterlevel) AS min_temp,\n MAX(waterlevel) AS max_temp,\n STDDEV(waterlevel) AS temp_stddev,\n COUNT(*) AS readings_count\nFROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`\nGROUP BY \n hour_interval\nORDER BY \n hour_interval", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Water Level", | |
| "transparent": true, | |
| "type": "timeseries" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "axisBorderShow": false, | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "barAlignment": 0, | |
| "barWidthFactor": 0.6, | |
| "drawStyle": "line", | |
| "fillOpacity": 0, | |
| "gradientMode": "none", | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "insertNulls": false, | |
| "lineInterpolation": "linear", | |
| "lineWidth": 1, | |
| "pointSize": 5, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| }, | |
| "showPoints": "auto", | |
| "spanNulls": false, | |
| "stacking": { | |
| "group": "A", | |
| "mode": "none" | |
| }, | |
| "thresholdsStyle": { | |
| "mode": "off" | |
| } | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "waterlevel" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "semi-dark-purple", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 6, | |
| "w": 6, | |
| "x": 12, | |
| "y": 19 | |
| }, | |
| "id": 19, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": false | |
| }, | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n TIMESTAMP_TRUNC(time, HOUR) AS hour_interval,\n AVG(luminance) AS avg_lumi,\n MIN(luminance) AS min_lumi,\n MAX(luminance) AS max_lumi,\n STDDEV(luminance) AS lumi_stddev\nFROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`\nGROUP BY \n hour_interval\nORDER BY \n hour_interval", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "luminance", | |
| "transparent": true, | |
| "type": "timeseries" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "axisBorderShow": false, | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "barAlignment": 0, | |
| "barWidthFactor": 0.6, | |
| "drawStyle": "line", | |
| "fillOpacity": 0, | |
| "gradientMode": "none", | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "insertNulls": false, | |
| "lineInterpolation": "linear", | |
| "lineWidth": 1, | |
| "pointSize": 5, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| }, | |
| "showPoints": "auto", | |
| "spanNulls": false, | |
| "stacking": { | |
| "group": "A", | |
| "mode": "none" | |
| }, | |
| "thresholdsStyle": { | |
| "mode": "off" | |
| } | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "waterlevel" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "semi-dark-purple", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 6, | |
| "w": 6, | |
| "x": 18, | |
| "y": 19 | |
| }, | |
| "id": 21, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": false | |
| }, | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n TIMESTAMP_TRUNC(time, HOUR) AS hour_interval,\n AVG(phvalue) AS avg_phvalue,\n MIN(phvalue) AS min_phvalue,\n MAX(phvalue) AS max_phvalue,\n STDDEV(phvalue) AS phvalue_stddev\nFROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`\nGROUP BY \n hour_interval\nORDER BY \n hour_interval", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "phvalue", | |
| "transparent": true, | |
| "type": "timeseries" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "thresholds" | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [] | |
| }, | |
| "gridPos": { | |
| "h": 5, | |
| "w": 5, | |
| "x": 0, | |
| "y": 25 | |
| }, | |
| "id": 4, | |
| "options": { | |
| "colorMode": "value", | |
| "graphMode": "area", | |
| "justifyMode": "auto", | |
| "orientation": "auto", | |
| "percentChangeColorMode": "standard", | |
| "reduceOptions": { | |
| "calcs": [ | |
| "lastNotNull" | |
| ], | |
| "fields": "", | |
| "values": false | |
| }, | |
| "showPercentChange": false, | |
| "textMode": "auto", | |
| "wideLayout": true | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT macaddr, count(DISTINCT macaddr) FROM `sensor-data-id.aust_bigquery_sensor_tf.sensor_data` \ngroup by macaddr\n", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Total sensors", | |
| "transparent": true, | |
| "type": "stat" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "thresholds" | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [] | |
| }, | |
| "gridPos": { | |
| "h": 5, | |
| "w": 5, | |
| "x": 5, | |
| "y": 25 | |
| }, | |
| "id": 25, | |
| "options": { | |
| "colorMode": "value", | |
| "graphMode": "area", | |
| "justifyMode": "auto", | |
| "orientation": "auto", | |
| "percentChangeColorMode": "standard", | |
| "reduceOptions": { | |
| "calcs": [ | |
| "lastNotNull" | |
| ], | |
| "fields": "", | |
| "values": false | |
| }, | |
| "showPercentChange": false, | |
| "textMode": "auto", | |
| "wideLayout": true | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT macaddr, count(macaddr) FROM `sensor-data-id.aust_bigquery_sensor_tf.sensor_data` \ngroup by macaddr\n", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Total data", | |
| "transparent": true, | |
| "type": "stat" | |
| }, | |
| { | |
| "collapsed": false, | |
| "gridPos": { | |
| "h": 1, | |
| "w": 24, | |
| "x": 0, | |
| "y": 30 | |
| }, | |
| "id": 23, | |
| "panels": [], | |
| "title": "Analytics", | |
| "type": "row" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "axisBorderShow": false, | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "fillOpacity": 50, | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "pointShape": "circle", | |
| "pointSize": { | |
| "fixed": 5 | |
| }, | |
| "pointStrokeWidth": 1, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| }, | |
| "show": "points" | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "waterlevel" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "#F2CC0C", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 6, | |
| "w": 6, | |
| "x": 0, | |
| "y": 31 | |
| }, | |
| "id": 6, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": false | |
| }, | |
| "mapping": "auto", | |
| "series": [ | |
| {} | |
| ], | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT \n temperature,\n waterlevel,\n luminance,\n phvalue\nFROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`\nWHERE \n time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Correlation Analysis", | |
| "transparent": true, | |
| "type": "xychart" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "axisBorderShow": false, | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "fillOpacity": 80, | |
| "gradientMode": "none", | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "lineWidth": 1, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| }, | |
| "thresholdsStyle": { | |
| "mode": "off" | |
| } | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "waterlevel" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "#F2CC0C", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 6, | |
| "w": 6, | |
| "x": 6, | |
| "y": 31 | |
| }, | |
| "id": 12, | |
| "options": { | |
| "barRadius": 0, | |
| "barWidth": 0.97, | |
| "fullHighlight": false, | |
| "groupWidth": 0.7, | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": false | |
| }, | |
| "orientation": "auto", | |
| "showValue": "auto", | |
| "stacking": "none", | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| }, | |
| "xTickLabelRotation": 0, | |
| "xTickLabelSpacing": 0 | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n TIMESTAMP_TRUNC(time, DAY) as day,\n COUNT(*) as readings_count,\n COUNT(DISTINCT CASE WHEN temperature IS NULL THEN 1 END) as temp_null_count,\n COUNT(DISTINCT CASE WHEN waterlevel IS NULL THEN 1 END) as water_null_count,\n COUNT(DISTINCT CASE WHEN luminance IS NULL THEN 1 END) as lum_null_count,\n COUNT(DISTINCT CASE WHEN phvalue IS NULL THEN 1 END) as ph_null_count\nFROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`\nWHERE \n time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)\nGROUP BY \n day\nORDER BY \n day", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Sensor Health", | |
| "transparent": true, | |
| "type": "barchart" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic-by-name" | |
| }, | |
| "custom": { | |
| "axisBorderShow": false, | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "barAlignment": 0, | |
| "barWidthFactor": 0.6, | |
| "drawStyle": "points", | |
| "fillOpacity": 0, | |
| "gradientMode": "none", | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "insertNulls": false, | |
| "lineInterpolation": "linear", | |
| "lineWidth": 1, | |
| "pointSize": 3, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| }, | |
| "showPoints": "auto", | |
| "spanNulls": false, | |
| "stacking": { | |
| "group": "A", | |
| "mode": "none" | |
| }, | |
| "thresholdsStyle": { | |
| "mode": "off" | |
| } | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "waterlevel" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "#F2CC0C", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 6, | |
| "w": 6, | |
| "x": 12, | |
| "y": 31 | |
| }, | |
| "id": 11, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": false | |
| }, | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "multi", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n time,\n temperature,\n AVG(temperature) OVER (ORDER BY time ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) as temp_12h_avg,\n waterlevel,\n AVG(waterlevel) OVER (ORDER BY time ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) as water_12h_avg,\n phvalue,\n AVG(phvalue) OVER (ORDER BY time ROWS BETWEEN 11 PRECEDING AND CURRENT ROW) as phvalue_12h_avg \nFROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`\nWHERE \n time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 20 DAY)\nORDER BY \n time", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Rolling Average (metrics in y-axis)", | |
| "transparent": true, | |
| "type": "timeseries" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "axisBorderShow": false, | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "barAlignment": 0, | |
| "barWidthFactor": 0.6, | |
| "drawStyle": "line", | |
| "fillOpacity": 0, | |
| "gradientMode": "none", | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "insertNulls": false, | |
| "lineInterpolation": "linear", | |
| "lineWidth": 1, | |
| "pointSize": 5, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| }, | |
| "showPoints": "auto", | |
| "spanNulls": false, | |
| "stacking": { | |
| "group": "A", | |
| "mode": "none" | |
| }, | |
| "thresholdsStyle": { | |
| "mode": "off" | |
| } | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "waterlevel" | |
| }, | |
| "properties": [] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 6, | |
| "w": 6, | |
| "x": 18, | |
| "y": 31 | |
| }, | |
| "id": 15, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": true | |
| }, | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n time,\n temperature,\n temperature - LAG(temperature) OVER (ORDER BY time) as temp_change,\n waterlevel - LAG(waterlevel) OVER (ORDER BY time) as water_change\nFROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`\nWHERE \n time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)\nORDER BY \n time", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Rate of change", | |
| "transparent": true, | |
| "type": "timeseries" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "thresholds" | |
| }, | |
| "custom": { | |
| "axisPlacement": "auto", | |
| "fillOpacity": 70, | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "insertNulls": false, | |
| "lineWidth": 0, | |
| "spanNulls": false | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "waterlevel" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "#F2CC0C", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 5, | |
| "w": 6, | |
| "x": 0, | |
| "y": 37 | |
| }, | |
| "id": 9, | |
| "options": { | |
| "alignValue": "left", | |
| "legend": { | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": true | |
| }, | |
| "mergeValues": true, | |
| "rowHeight": 0.9, | |
| "showValue": "auto", | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n time,\n CASE WHEN temperature > 30 THEN 'High Temp' \n WHEN temperature < 10 THEN 'Low Temp' \n ELSE NULL END as temp_status,\n CASE WHEN waterlevel > 90 THEN 'High Water' \n WHEN waterlevel < 10 THEN 'Low Water' \n ELSE NULL END as water_status,\n CASE WHEN phvalue > 8.5 THEN 'High pH' \n WHEN phvalue < 6.5 THEN 'Low pH' \n ELSE NULL END as ph_status\nFROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`\nWHERE \n time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY) AND\n (temperature > 30 OR temperature < 10 OR\n waterlevel > 90 OR waterlevel < 10 OR\n phvalue > 8.5 OR phvalue < 6.5)\nORDER BY \n time", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Threshold Violation", | |
| "transparent": true, | |
| "type": "state-timeline" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "fillOpacity": 44, | |
| "gradientMode": "hue", | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "lineWidth": 1, | |
| "stacking": { | |
| "group": "A", | |
| "mode": "none" | |
| } | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "hour_of_day" | |
| }, | |
| "properties": [] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 6, | |
| "w": 6, | |
| "x": 6, | |
| "y": 37 | |
| }, | |
| "id": 10, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": false | |
| }, | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n EXTRACT(HOUR FROM time) as hour_of_day,\n AVG(temperature) as avg_temp,\n AVG(waterlevel) as avg_water,\n AVG(luminance) as avg_lum,\n AVG(phvalue) as avg_ph\nFROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`\nWHERE \n time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)\nGROUP BY \n hour_of_day\nORDER BY \n hour_of_day", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Threshold Violation (needs fix)", | |
| "transparent": true, | |
| "type": "histogram" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "custom": { | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| } | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "waterlevel" | |
| }, | |
| "properties": [] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 6, | |
| "w": 6, | |
| "x": 12, | |
| "y": 37 | |
| }, | |
| "id": 14, | |
| "options": { | |
| "calculate": false, | |
| "cellGap": 1, | |
| "color": { | |
| "exponent": 0.5, | |
| "fill": "dark-orange", | |
| "mode": "scheme", | |
| "reverse": false, | |
| "scale": "exponential", | |
| "scheme": "Oranges", | |
| "steps": 64 | |
| }, | |
| "exemplars": { | |
| "color": "rgba(255,0,255,0.7)" | |
| }, | |
| "filterValues": { | |
| "le": 1e-9 | |
| }, | |
| "legend": { | |
| "show": true | |
| }, | |
| "rowsFrame": { | |
| "layout": "auto" | |
| }, | |
| "tooltip": { | |
| "mode": "single", | |
| "showColorScale": false, | |
| "yHistogram": false | |
| }, | |
| "yAxis": { | |
| "axisPlacement": "left", | |
| "reverse": false | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "SELECT\n TIMESTAMP_TRUNC(time, HOUR) as hour,\n COUNT(*) as readings,\n 100 * COUNT(*) / MAX(COUNT(*)) OVER () as availability_percent\nFROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`\nWHERE \n time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)\nGROUP BY \n hour\nORDER BY \n hour", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Sensor Availability", | |
| "transparent": true, | |
| "type": "heatmap" | |
| }, | |
| { | |
| "collapsed": false, | |
| "gridPos": { | |
| "h": 1, | |
| "w": 24, | |
| "x": 0, | |
| "y": 43 | |
| }, | |
| "id": 24, | |
| "panels": [], | |
| "title": "Forecast", | |
| "type": "row" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "axisBorderShow": false, | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "barAlignment": 0, | |
| "barWidthFactor": 0.6, | |
| "drawStyle": "line", | |
| "fillOpacity": 0, | |
| "gradientMode": "none", | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "insertNulls": false, | |
| "lineInterpolation": "linear", | |
| "lineWidth": 1, | |
| "pointSize": 5, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| }, | |
| "showPoints": "auto", | |
| "spanNulls": false, | |
| "stacking": { | |
| "group": "A", | |
| "mode": "none" | |
| }, | |
| "thresholdsStyle": { | |
| "mode": "off" | |
| } | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "waterlevel" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "#F2CC0C", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 6, | |
| "w": 6, | |
| "x": 0, | |
| "y": 44 | |
| }, | |
| "id": 8, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": false | |
| }, | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "WITH time_series AS (\n SELECT\n time,\n temperature,\n waterlevel,\n luminance,\n phvalue,\n UNIX_SECONDS(time) as seconds\n FROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`\n WHERE \n time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)\n)\n\nSELECT\n time,\n temperature,\n waterlevel,\n luminance,\n phvalue,\n -- Temperature forecast\n (SELECT AVG(temperature) FROM time_series) + \n (SELECT COVAR_POP(temperature, seconds)/VAR_POP(seconds) FROM time_series) * \n (seconds - (SELECT AVG(seconds) FROM time_series)) as temp_forecast,\n \n -- Water level forecast\n (SELECT AVG(waterlevel) FROM time_series) + \n (SELECT COVAR_POP(waterlevel, seconds)/VAR_POP(seconds) FROM time_series) * \n (seconds - (SELECT AVG(seconds) FROM time_series)) as water_forecast\nFROM \n time_series\nORDER BY \n time", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Sensor Data Forecast", | |
| "transparent": true, | |
| "type": "timeseries" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic" | |
| }, | |
| "custom": { | |
| "axisBorderShow": false, | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "fillOpacity": 50, | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "pointShape": "circle", | |
| "pointSize": { | |
| "fixed": 5 | |
| }, | |
| "pointStrokeWidth": 1, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| }, | |
| "show": "points" | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [] | |
| }, | |
| "gridPos": { | |
| "h": 6, | |
| "w": 6, | |
| "x": 6, | |
| "y": 44 | |
| }, | |
| "id": 7, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": false | |
| }, | |
| "mapping": "auto", | |
| "series": [ | |
| {} | |
| ], | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "WITH stats AS (\n SELECT\n AVG(temperature) as mean_temp,\n STDDEV(temperature) as std_temp,\n AVG(waterlevel) as mean_water,\n STDDEV(waterlevel) as std_water,\n AVG(luminance) as mean_lum,\n STDDEV(luminance) as std_lum,\n AVG(phvalue) as mean_ph,\n STDDEV(phvalue) as std_ph\n FROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`\n WHERE \n time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)\n)\n\nSELECT \n time,\n temperature,\n waterlevel,\n luminance,\n phvalue,\n CASE WHEN ABS(temperature - mean_temp) > 3*std_temp THEN 1 ELSE 0 END as temp_anomaly,\n CASE WHEN ABS(waterlevel - mean_water) > 3*std_water THEN 1 ELSE 0 END as water_anomaly,\n CASE WHEN ABS(luminance - mean_lum) > 3*std_lum THEN 1 ELSE 0 END as lum_anomaly,\n CASE WHEN ABS(phvalue - mean_ph) > 3*std_ph THEN 1 ELSE 0 END as ph_anomaly\nFROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`, stats\nWHERE \n time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)\nORDER BY \n time", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Anomaly Detection", | |
| "transparent": true, | |
| "type": "xychart" | |
| }, | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "fieldConfig": { | |
| "defaults": { | |
| "color": { | |
| "mode": "palette-classic-by-name" | |
| }, | |
| "custom": { | |
| "axisBorderShow": false, | |
| "axisCenteredZero": false, | |
| "axisColorMode": "text", | |
| "axisLabel": "", | |
| "axisPlacement": "auto", | |
| "barAlignment": 0, | |
| "barWidthFactor": 0.6, | |
| "drawStyle": "line", | |
| "fillOpacity": 0, | |
| "gradientMode": "none", | |
| "hideFrom": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": false | |
| }, | |
| "insertNulls": false, | |
| "lineInterpolation": "linear", | |
| "lineWidth": 1, | |
| "pointSize": 5, | |
| "scaleDistribution": { | |
| "type": "linear" | |
| }, | |
| "showPoints": "auto", | |
| "spanNulls": false, | |
| "stacking": { | |
| "group": "A", | |
| "mode": "none" | |
| }, | |
| "thresholdsStyle": { | |
| "mode": "off" | |
| } | |
| }, | |
| "mappings": [], | |
| "thresholds": { | |
| "mode": "absolute", | |
| "steps": [ | |
| { | |
| "color": "green" | |
| }, | |
| { | |
| "color": "red", | |
| "value": 80 | |
| } | |
| ] | |
| } | |
| }, | |
| "overrides": [ | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "waterlevel" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "color", | |
| "value": { | |
| "fixedColor": "#F2CC0C", | |
| "mode": "fixed" | |
| } | |
| } | |
| ] | |
| }, | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "temp_z_score" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "custom.axisPlacement", | |
| "value": "right" | |
| } | |
| ] | |
| }, | |
| { | |
| "matcher": { | |
| "id": "byName", | |
| "options": "water_z_score" | |
| }, | |
| "properties": [ | |
| { | |
| "id": "custom.axisPlacement", | |
| "value": "right" | |
| } | |
| ] | |
| }, | |
| { | |
| "__systemRef": "hideSeriesFrom", | |
| "matcher": { | |
| "id": "byNames", | |
| "options": { | |
| "mode": "exclude", | |
| "names": [ | |
| "temp_anomaly" | |
| ], | |
| "prefix": "All except:", | |
| "readOnly": true | |
| } | |
| }, | |
| "properties": [ | |
| { | |
| "id": "custom.hideFrom", | |
| "value": { | |
| "legend": false, | |
| "tooltip": false, | |
| "viz": true | |
| } | |
| } | |
| ] | |
| } | |
| ] | |
| }, | |
| "gridPos": { | |
| "h": 6, | |
| "w": 6, | |
| "x": 12, | |
| "y": 44 | |
| }, | |
| "id": 13, | |
| "options": { | |
| "legend": { | |
| "calcs": [], | |
| "displayMode": "list", | |
| "placement": "bottom", | |
| "showLegend": false | |
| }, | |
| "tooltip": { | |
| "hideZeros": false, | |
| "mode": "single", | |
| "sort": "none" | |
| } | |
| }, | |
| "pluginVersion": "12.0.0", | |
| "targets": [ | |
| { | |
| "datasource": { | |
| "type": "grafana-bigquery-datasource", | |
| "uid": "celgybumowkjke" | |
| }, | |
| "editorMode": "code", | |
| "format": 1, | |
| "location": "", | |
| "project": "sensor-data-id", | |
| "rawQuery": true, | |
| "rawSql": "WITH stats AS (\n SELECT\n AVG(temperature) as mean_temp,\n STDDEV(temperature) as std_temp,\n AVG(waterlevel) as mean_water,\n STDDEV(waterlevel) as std_water\n FROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`\n WHERE \n time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)\n),\n\nz_scores AS (\n SELECT\n time,\n temperature,\n (temperature - mean_temp)/NULLIF(std_temp, 0) as temp_z_score,\n waterlevel,\n (waterlevel - mean_water)/NULLIF(std_water, 0) as water_z_score\n FROM \n `sensor-data-id.aust_bigquery_sensor_tf.sensor_data`, stats\n WHERE \n time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)\n)\n\nSELECT\n time,\n temperature,\n temp_z_score,\n waterlevel,\n water_z_score,\n CASE WHEN ABS(temp_z_score) > 3 THEN 1 ELSE 0 END as temp_anomaly,\n CASE WHEN ABS(water_z_score) > 3 THEN 1 ELSE 0 END as water_anomaly\nFROM \n z_scores\nORDER BY \n time", | |
| "refId": "A", | |
| "sql": { | |
| "columns": [ | |
| { | |
| "parameters": [], | |
| "type": "function" | |
| } | |
| ], | |
| "groupBy": [ | |
| { | |
| "property": { | |
| "type": "string" | |
| }, | |
| "type": "groupBy" | |
| } | |
| ], | |
| "limit": 50 | |
| } | |
| } | |
| ], | |
| "title": "Anomaly Detection (z-score)", | |
| "transparent": true, | |
| "type": "timeseries" | |
| } | |
| ], | |
| "preload": false, | |
| "refresh": "10s", | |
| "schemaVersion": 41, | |
| "tags": [], | |
| "templating": { | |
| "list": [] | |
| }, | |
| "time": { | |
| "from": "2025-04-25T00:00:00.000Z", | |
| "to": "2025-05-09T00:00:00.000Z" | |
| }, | |
| "timepicker": {}, | |
| "timezone": "browser", | |
| "title": "Sensor Data - Dup", | |
| "uid": "76a7cfba-fe75-42ff-9238-e6527077a0ac", | |
| "version": 46 | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment