Closed alexandrpaliy closed 6 months ago
Slach
commented
6 months ago @alexandrpaliy sorry for really late reply I tried to reproduce behavior, but now in grafana 10.x all looks good, query passed to backend part of plugin, and time ranges replaced correctly
Slach
commented
6 months ago Now panel2 query $from and $to replaced alert's query has time >= toDateTime(1669849200) and time <= toDateTime(1669935599) with the same manner as $timeFilter
instead of time >= 1669849200 and time <= 1669935599
Slach
commented
6 months ago hm ;( issue shall reproduces, but looks like this is related to other bug in development version of plugin https://github.com/Altinity/clickhouse-grafana/issues/546#issuecomment-2094567779
Slach
commented
6 months ago @alexandrpaliy use toDateTime($from) and toDateTime($to) for time range properly as workaround
Latest grafana 9.3.0 with legacy alerting.
According to https://grafana.com/grafana/plugins/vertamedia-clickhouse-datasource/, "Macros support":
Maybe I misinterpret the docs, but I always considered these options as "the same", where $from and $to let you have more "manual control", and $timeFilter just generates the whole condition by itself. Turns out, when these macros are used in alert query, they behave differently: $timeFilter respects alert query time limitations, and $from and $to do not.
Example
I have created 2 almost equal panels at the same dashboard with grafana (with equal alert rules), the only difference is: panel1 uses
WHERE $timeFilter, panel2 -WHERE time >= $from and time <= $to. Dashboards UI time range is set to "today". Alert hasquery(A, 15m, now)in it's settings.panel1 JSON
``` { "id": 6, "gridPos": { "h": 8, "w": 12, "x": 0, "y": 9 }, "type": "timeseries", "title": "test panel1", "alert": { "alertRuleTags": {}, "conditions": [ { "evaluator": { "params": [ 999 ], "type": "gt" }, "operator": { "type": "and" }, "query": { "params": [ "A", "15m", "now" ] }, "reducer": { "params": [], "type": "avg" }, "type": "query" } ], "executionErrorState": "alerting", "for": "1m", "frequency": "1m", "handler": 1, "name": "test panel1 alert", "noDataState": "ok", "notifications": [] }, "datasource": { "type": "vertamedia-clickhouse-datasource", "uid": "7udY81xxx" }, "thresholds": [ { "colorMode": "critical", "op": "gt", "value": 999, "visible": true } ], "description": "", "fieldConfig": { "defaults": { "custom": { "drawStyle": "line", "lineInterpolation": "linear", "barAlignment": 0, "lineWidth": 1, "fillOpacity": 0, "gradientMode": "none", "spanNulls": false, "showPoints": "auto", "pointSize": 5, "stacking": { "mode": "none", "group": "A" }, "axisPlacement": "auto", "axisLabel": "", "axisColorMode": "text", "scaleDistribution": { "type": "linear" }, "axisCenteredZero": false, "hideFrom": { "tooltip": false, "viz": false, "legend": false }, "thresholdsStyle": { "mode": "off" } }, "color": { "mode": "palette-classic" }, "mappings": [], "thresholds": { "mode": "absolute", "steps": [ { "color": "green", "value": null }, { "color": "red", "value": 80 } ] } }, "overrides": [] }, "hideTimeOverride": false, "options": { "tooltip": { "mode": "single", "sort": "none" }, "legend": { "showLegend": true, "displayMode": "list", "placement": "bottom", "calcs": [] } }, "targets": [ { "database": "db1", "datasource": { "type": "vertamedia-clickhouse-datasource", "uid": "7udY81xxx" }, "dateColDataType": "", "dateLoading": false, "dateTimeColDataType": "time", "dateTimeType": "DATETIME", "datetimeLoading": false, "extrapolate": false, "format": "time_series", "formattedQuery": "SELECT $timeSeries as t, count() FROM $table WHERE $timeFilter GROUP BY t ORDER BY t", "hide": false, "interval": "", "intervalFactor": 1, "query": "SELECT\n $timeSeries AS t,\n count()\nFROM $table AS t1\nWHERE $timeFilter\n AND country = 'country1'\nGROUP BY t\nORDER BY t\n", "rawQuery": "SELECT\n (intDiv(toUInt32(time), 60) * 60) * 1000 AS t,\n count()\nFROM db1.table1 AS t1\nWHERE time >= toDateTime(1669849200) AND time <= toDateTime(1669935599)\n AND country = 'country1'\nGROUP BY t\nORDER BY t", "refId": "A", "round": "0s", "skip_comments": true, "table": "table1", "tableLoading": false } ] } ```panel2 JSON
``` { "id": 7, "gridPos": { "h": 8, "w": 12, "x": 12, "y": 9 }, "type": "timeseries", "title": "test panel2", "alert": { "alertRuleTags": {}, "conditions": [ { "evaluator": { "params": [ 999 ], "type": "gt" }, "operator": { "type": "and" }, "query": { "params": [ "A", "15m", "now" ] }, "reducer": { "params": [], "type": "avg" }, "type": "query" } ], "executionErrorState": "alerting", "for": "1m", "frequency": "1m", "handler": 1, "name": "test panel2 alert", "noDataState": "ok", "notifications": [] }, "datasource": { "type": "vertamedia-clickhouse-datasource", "uid": "7udY81xxx" }, "thresholds": [ { "colorMode": "critical", "op": "gt", "value": 999, "visible": true } ], "description": "", "fieldConfig": { "defaults": { "custom": { "drawStyle": "line", "lineInterpolation": "linear", "barAlignment": 0, "lineWidth": 1, "fillOpacity": 0, "gradientMode": "none", "spanNulls": false, "showPoints": "auto", "pointSize": 5, "stacking": { "mode": "none", "group": "A" }, "axisPlacement": "auto", "axisLabel": "", "axisColorMode": "text", "scaleDistribution": { "type": "linear" }, "axisCenteredZero": false, "hideFrom": { "tooltip": false, "viz": false, "legend": false }, "thresholdsStyle": { "mode": "off" } }, "color": { "mode": "palette-classic" }, "mappings": [], "thresholds": { "mode": "absolute", "steps": [ { "color": "green", "value": null }, { "color": "red", "value": 80 } ] } }, "overrides": [] }, "hideTimeOverride": false, "options": { "tooltip": { "mode": "single", "sort": "none" }, "legend": { "showLegend": true, "displayMode": "list", "placement": "bottom", "calcs": [] } }, "targets": [ { "database": "db1", "datasource": { "type": "vertamedia-clickhouse-datasource", "uid": "7udY81xxx" }, "dateColDataType": "", "dateLoading": false, "dateTimeColDataType": "time", "dateTimeType": "DATETIME", "datetimeLoading": false, "extrapolate": false, "format": "time_series", "formattedQuery": "SELECT $timeSeries as t, count() FROM $table WHERE $timeFilter GROUP BY t ORDER BY t", "hide": false, "interval": "", "intervalFactor": 1, "query": "SELECT\n $timeSeries AS t,\n count()\nFROM $table AS t1\nWHERE time >= $from and time <= $to\n AND country = 'country2'\nGROUP BY t\nORDER BY t\n", "rawQuery": "SELECT\n (intDiv(toUInt32(time), 60) * 60) * 1000 AS t,\n count()\nFROM db1.table1 AS t1\nWHERE time >= 1669849200 and time <= 1669935599\n AND country = 'country2'\nGROUP BY t\nORDER BY t", "refId": "A", "round": "0s", "skip_comments": true, "table": "table1", "tableLoading": false } ] } ```Now, from grafana debug logs:
For panel1, alert's query has
WHERE time >= toDateTime(1669898403) AND time <= toDateTime(1669899303), where:1669898403is "Thu Dec 01 2022 12:40:03 GMT+0000"1669899303is "Thu Dec 01 2022 12:55:03 GMT+0000" , which is correct from alert's perspective.For panel2, alert's query has
WHERE time >= 1669849200 and time <= 1669935599, where:1669849200is "Wed Nov 30 2022 23:00:00 GMT+0000"1669935599is "Thu Dec 01 2022 22:59:59 GMT+0000" , which is incorrect from alert's perspective, but correct from UI perspective.