clickhouse-grafana icon indicating copy to clipboard operation
clickhouse-grafana copied to clipboard

Published 20 hours ago • verified publisher icon Altinity

$from and $to macros use UI time range in alert queries

Open alexandrpaliy opened this issue 2 years ago • 0 comments

Latest grafana 9.3.0 with legacy alerting.

According to https://grafana.com/grafana/plugins/vertamedia-clickhouse-datasource/, "Macros support":

$from - replaced with (timestamp with ms)/1000 value of UI selected "Time Range:From" $to - replaced with (timestamp with ms)/1000 value of UI selected "Time Range:To" ... $timeFilter - replaced with currently selected "Time Range". Requires Column:Date and Column:DateTime or Column:TimeStamp to be selected.

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 has query(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:

logger=plugin.vertamedia-clickhouse-datasource t=2022-12-01T12:55:03.832005233Z level=debug msg="queryResponse: SELECT\n    (intDiv(toUInt32(time), 60) * 60) * 1000 AS t,\n    count()\nFROM db1.table1 AS t1\nWHERE time >= toDateTime(1669898403) AND time <= toDateTime(1669899303)\n    AND country = 'country1'\nGROUP BY t\nORDER BY t FORMAT JSON /* alerts query */; returns 0 frames"

logger=plugin.vertamedia-clickhouse-datasource t=2022-12-01T12:55:48.832409075Z level=debug msg="queryResponse: 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 FORMAT JSON /* alerts query */; returns 0 frames"

For panel1, alert's query has WHERE time >= toDateTime(1669898403) AND time <= toDateTime(1669899303), where: 1669898403 is "Thu Dec 01 2022 12:40:03 GMT+0000" 1669899303 is "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: 1669849200 is "Wed Nov 30 2022 23:00:00 GMT+0000" 1669935599 is "Thu Dec 01 2022 22:59:59 GMT+0000" , which is incorrect from alert's perspective, but correct from UI perspective.

alexandrpaliy avatar Dec 01 '22 13:12 alexandrpaliy