Azure Data Explorer plugin: 400 Bad Request on GUI-designed query due to missing escaping of "time" column

[sandersaares]

What happened:

test1
| where time >= datetime(2000-01-01T00:00:00Z) and time <= datetime(2000-01-01T01:00:00Z)
| order by time asc

I suspect this is be due to lack of required keyword-escaping of time.

What you expected to happen:

A working query should have been created.

How to reproduce it (as minimally and precisely as possible):

Make Azure Data Explorer table with timestamp in time column.

Sample data: metrics.json.gz

Anything else we need to know?:

Environment:

• Grafana version: 9.3.2.2 Azure Managed Grafana
• Data source type & version: Azure Data Explorer 4.2.0
• OS Grafana is installed on: Azure
• User OS & Browser: Windows 11, Edge

{
  "request": {
    "url": "api/ds/query",
    "method": "POST",
    "data": {
      "queries": [
        {
          "query": "test1\n| where $__timeFilter(time)\n| order by time asc",
          "querySource": "raw",
          "expression": {
            "where": {
              "type": "and",
              "expressions": []
            },
            "groupBy": {
              "type": "and",
              "expressions": []
            },
            "reduce": {
              "type": "and",
              "expressions": []
            },
            "from": {
              "type": "property",
              "property": {
                "type": "string",
                "name": "test1"
              }
            }
          },
          "pluginVersion": "4.2.0",
          "refId": "A",
          "datasource": {
            "type": "grafana-azure-data-explorer-datasource",
            "uid": "u4kH7I14k"
          },
          "rawMode": false,
          "resultFormat": "table",
          "database": "feb23",
          "key": "Q-f91d34fa-732c-47f7-a588-307afe14369a-0",
          "datasourceId": 3,
          "intervalMs": 2000,
          "maxDataPoints": 1861
        }
      ],
      "range": {
        "from": "2000-01-01T00:00:00.000Z",
        "to": "2000-01-01T01:00:00.000Z",
        "raw": {
          "from": "2000-01-01T00:00:00.000Z",
          "to": "2000-01-01T01:00:00.000Z"
        }
      },
      "from": "946684800000",
      "to": "946688400000"
    },
    "hideFromInspector": false
  },
  "response": {
    "results": {
      "A": {
        "error": "Azure HTTP \"400 BadRequest\": \"Request is invalid and cannot be processed: Syntax error: SYN0002: A recognition error occurred. [line:position=2:8]\"",
        "status": 500,
        "frames": [
          {
            "schema": {
              "refId": "A",
              "meta": {
                "executedQueryString": "test1\n| where time >= datetime(2000-01-01T00:00:00Z) and time <= datetime(2000-01-01T01:00:00Z)\n| order by time asc"
              },
              "fields": []
            },
            "data": {
              "values": []
            }
          }
        ],
        "refId": "A"
      }
    }
  }
}

[zuchka]

Thanks for creating this issue, @sandersaares

generally it is better to use Grafana's built-in time-macros for filtering by time:

https://grafana.com/docs/grafana/latest/datasources/azure-monitor/query-editor/#use-macros-in-resource-graph-queries

have you tried these instead?

[sandersaares]

As you can see on my screenshot, I am creating a query using the GUI designer. Presumably this already implies that the relevant macros are used. You can also see the macro in the query inspector output I pasted.

[zuchka]

could you please copy and paste all relevant data using the panel --> more --> get help workflow in the panel's dropdown menu? This will help up mock up your data and reproduce your issue. It is important to our teams that issues get verified as reproducible before we formally mark them as bugs. Thank you!

here are more details and steps for copying your data over to this issue:

https://grafana.com/docs/grafana/latest/troubleshooting/send-panel-to-grafana-support/

[sandersaares]

Key	Value
Panel	timeseries @ 9.3.2.2 (67a213dc85)
Grafana	9.3.2.2 (67a213dc85) // Pro

Panel debug snapshot dashboard

{
  "panels": [
    {
      "id": 2,
      "gridPos": {
        "h": 13,
        "w": 15,
        "x": 0,
        "y": 0
      },
      "type": "timeseries",
      "title": "Reproduced with embedded data",
      "targets": [
        {
          "refId": "A",
          "datasource": {
            "type": "grafana",
            "uid": "grafana"
          },
          "queryType": "snapshot",
          "snapshot": [
            {
              "schema": {
                "refId": "A",
                "meta": {
                  "executedQueryString": "grafana63304\n| where time >= datetime(2023-02-15T01:52:33.943Z) and time <= datetime(2023-02-15T07:52:33.943Z)\n| order by time asc"
                },
                "fields": []
              },
              "data": {
                "values": []
              }
            }
          ]
        }
      ],
      "options": {
        "tooltip": {
          "mode": "single",
          "sort": "none"
        },
        "legend": {
          "showLegend": true,
          "displayMode": "list",
          "placement": "bottom",
          "calcs": []
        }
      },
      "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": [
              {
                "value": null,
                "color": "green"
              },
              {
                "value": 80,
                "color": "red"
              }
            ]
          }
        },
        "overrides": []
      },
      "datasource": {
        "type": "grafana",
        "uid": "grafana"
      }
    },
    {
      "gridPos": {
        "h": 7,
        "w": 9,
        "x": 15,
        "y": 0
      },
      "id": 5,
      "options": {
        "content": "<table width=\"100%\">\n    <tr>\n      <th width=\"2%\">Panel</th>\n      <td >timeseries @ 9.3.2.2 (67a213dc85)</td>\n    </tr>\n    <tr>\n      <th>Queries</th>\n      <td>A[grafana-azure-data-explorer-datasource]</td>\n    </tr>\n    \n    <tr><th>Data</th><td>Error 1 frames, 0 fields, 0 rows </td></tr>\n    \n    <tr>\n      <th>Grafana</th>\n      <td>9.3.2.2 (67a213dc85) // Pro</td>\n    </tr>\n  </table>",
        "mode": "html"
      },
      "title": "Debug info",
      "type": "text"
    },
    {
      "id": 6,
      "title": "Original Panel JSON",
      "type": "text",
      "gridPos": {
        "h": 13,
        "w": 9,
        "x": 15,
        "y": 7
      },
      "options": {
        "content": "{\n  \"id\": 2,\n  \"gridPos\": {\n    \"x\": 0,\n    \"y\": 0,\n    \"w\": 12,\n    \"h\": 9\n  },\n  \"type\": \"timeseries\",\n  \"title\": \"Panel Title\",\n  \"targets\": [\n    {\n      \"query\": \"grafana63304\\n| where $__timeFilter(time)\\n| order by time asc\",\n      \"querySource\": \"raw\",\n      \"expression\": {\n        \"where\": {\n          \"type\": \"and\",\n          \"expressions\": []\n        },\n        \"groupBy\": {\n          \"type\": \"and\",\n          \"expressions\": []\n        },\n        \"reduce\": {\n          \"type\": \"and\",\n          \"expressions\": []\n        },\n        \"from\": {\n          \"type\": \"property\",\n          \"property\": {\n            \"type\": \"string\",\n            \"name\": \"grafana63304\"\n          }\n        }\n      },\n      \"pluginVersion\": \"4.2.0\",\n      \"datasource\": {\n        \"type\": \"grafana-azure-data-explorer-datasource\",\n        \"uid\": \"u4kH7I14k\"\n      },\n      \"refId\": \"A\",\n      \"rawMode\": false,\n      \"resultFormat\": \"table\",\n      \"database\": \"feb23\"\n    }\n  ],\n  \"options\": {\n    \"tooltip\": {\n      \"mode\": \"single\",\n      \"sort\": \"none\"\n    },\n    \"legend\": {\n      \"showLegend\": true,\n      \"displayMode\": \"list\",\n      \"placement\": \"bottom\",\n      \"calcs\": []\n    }\n  },\n  \"fieldConfig\": {\n    \"defaults\": {\n      \"custom\": {\n        \"drawStyle\": \"line\",\n        \"lineInterpolation\": \"linear\",\n        \"barAlignment\": 0,\n        \"lineWidth\": 1,\n        \"fillOpacity\": 0,\n        \"gradientMode\": \"none\",\n        \"spanNulls\": false,\n        \"showPoints\": \"auto\",\n        \"pointSize\": 5,\n        \"stacking\": {\n          \"mode\": \"none\",\n          \"group\": \"A\"\n        },\n        \"axisPlacement\": \"auto\",\n        \"axisLabel\": \"\",\n        \"axisColorMode\": \"text\",\n        \"scaleDistribution\": {\n          \"type\": \"linear\"\n        },\n        \"axisCenteredZero\": false,\n        \"hideFrom\": {\n          \"tooltip\": false,\n          \"viz\": false,\n          \"legend\": false\n        },\n        \"thresholdsStyle\": {\n          \"mode\": \"off\"\n        }\n      },\n      \"color\": {\n        \"mode\": \"palette-classic\"\n      },\n      \"mappings\": [],\n      \"thresholds\": {\n        \"mode\": \"absolute\",\n        \"steps\": [\n          {\n            \"value\": null,\n            \"color\": \"green\"\n          },\n          {\n            \"value\": 80,\n            \"color\": \"red\"\n          }\n        ]\n      }\n    },\n    \"overrides\": []\n  },\n  \"datasource\": {\n    \"uid\": \"u4kH7I14k\",\n    \"type\": \"grafana-azure-data-explorer-datasource\"\n  }\n}",
        "mode": "code",
        "code": {
          "language": "json",
          "showLineNumbers": true,
          "showMiniMap": true
        }
      }
    },
    {
      "id": 3,
      "title": "Data from panel above",
      "type": "table",
      "datasource": {
        "type": "datasource",
        "uid": "-- Dashboard --"
      },
      "gridPos": {
        "h": 7,
        "w": 15,
        "x": 0,
        "y": 13
      },
      "options": {
        "showTypeIcons": true
      },
      "targets": [
        {
          "datasource": {
            "type": "datasource",
            "uid": "-- Dashboard --"
          },
          "panelId": 2,
          "withTransforms": true,
          "refId": "A"
        }
      ]
    }
  ],
  "schemaVersion": 37,
  "title": "Debug: Panel Title // 2023-02-15 07:52:51",
  "tags": [
    "debug",
    "debug-timeseries"
  ],
  "time": {
    "from": "2023-02-15T01:52:51.634Z",
    "to": "2023-02-15T07:52:51.634Z"
  }
}

[alyssabull]

Hi @sandersaares - I can replicate this issue as mentioned. We are looking into how to handle reserved keywords in the query builder. In the meantime there are two workarounds you can use to run a successful query:

1. Create a new column called Time:

test1
| extend Time = time
| where Time >= datetime(2000-01-01T00:00:00Z) and Time <= datetime(2000-01-01T01:00:00Z)
| order by Time asc

2. Create a new column using the reserved keyword escaping called ["time"]:

test1
| extend ["time"] = time
| where ["time"] >= datetime(2000-01-01T00:00:00Z) and ["time"] <= datetime(2000-01-01T01:00:00Z)
| order by ["time"] asc