query editors

46 notes, read out of this brain and free to use. Each one was extracted from a source and is re-checked against its exam.

predict_linear PromQL function syntax and arguments

The predict_linear function takes two arguments: predict_linear(<range-vector>, <seconds>). The <range-vector> provides the historical data used to calculate the trend. The <seconds> specifies how far into the future to predict, in seconds.

predict_linear uses linear regression to extrapolate trends

predict_linear performs a linear regression over the values in a range vector and extrapolates the resulting trend into the future. The calculation uses the formula: predicted_value = value_at_evaluation_time + slope * time. The result is a single predicted value at the requested future time, rather than a forecast series.

MSSQL time column requirement for time series

Ensure your query includes a column named time when using the Time series format. Use the $__time() macro to rename your date column. The time column must be of a valid SQL date/time type (datetime, datetime2, date) or contain Unix epoch values. The result set must be sorted by the time column using ORDER BY.

MSSQL macro syntax requirements

Verify macro syntax uses $__timeFilter(column) not $_timeFilter(column). Macros don't work inside stored procedures; use explicit date parameters instead. Check that the column name passed to macros exists in your table. View the expanded query by clicking Generated SQL after running the query to debug macro expansion.

MSSQL time macros always expand to UTC

Time macros ($__time, $__timeFilter, etc.) always expand to UTC values. Do not pass timezone parameters to time macros as they are not supported.

MSSQL timezone conversion for local timestamps

If timestamps are stored in local time, convert them to UTC in your query using: SELECT your_datetime_column AT TIME ZONE 'Your Local Timezone' AT TIME ZONE 'UTC' AS time, value FROM your_table

MSSQL system databases excluded from query editor

The query editor automatically excludes tempdb, model, msdb, and master from the database dropdown.

MSSQL time macros with aggregation

Use the $__timeGroup() macro to aggregate data into time intervals. This macro also supports the FILL option to handle missing data points.

MSSQL stored procedure requirements

Ensure stored procedures use SELECT statements, not just variable assignments. Remove SET NOCOUNT ON if present, or ensure it's followed by a SELECT statement. Verify stored procedure parameters are being passed correctly. Test the stored procedure directly in SQL Server Management Studio with the same parameters.

MSSQL query result size limit handling

Add time filters using $__timeFilter(column) to limit data to the dashboard time range. Use aggregations (AVG, SUM, COUNT) with GROUP BY instead of returning raw rows. Add a TOP clause to limit results: SELECT TOP 1000 .... Use the $__timeGroup() macro to aggregate data into time intervals.

FlightSQL error codes and meanings

FlightSQL errors are prefixed with 'flightsql:' followed by a gRPC error description. InvalidArgument indicates invalid SQL query syntax. PermissionDenied indicates the token lacks access to the requested resource. NotFound indicates the requested table or database doesn't exist. Unavailable indicates the InfluxDB server is unreachable. Unauthenticated indicates the token is missing, invalid, or expired.

InfluxQL alias patterns documentation reference

InfluxQL supports alias patterns such as $tag_hostname in the ALIAS field to create unique display names for each series. This prevents legend and tooltip color mismatches when multiple series have the same name.

SQL $__timeFilter macro requirement

For SQL queries in InfluxDB, the $__timeFilter(time) macro must be included so the query uses the dashboard time range. Without this macro, the query may not return the correct data based on the dashboard's selected time range.

MySQL query editor modes

The MySQL query editor has two modes: Builder and Code. Builder mode helps you build a query using a visual interface. Code mode allows for advanced querying and offers support for complex SQL query writing.

MySQL Builder mode Format option

The Format option in MySQL Builder mode allows you to select the response format from a dropdown. The default is Table. If you use the Time series format option, one of the columns must be named time.

MySQL Builder mode Dataset and Table selection

In MySQL Builder mode, the Dataset component allows you to select a database to query from a dropdown. The Table component allows you to select a table from a dropdown, with tables corresponding to the chosen database.

MySQL Builder mode Data operations

Data operations in MySQL Builder mode is an optional component that allows you to select an aggregation from a dropdown. You can add multiple data operations by clicking the plus sign. Click the X to remove a data operation. Click the garbage can icon to remove the entire column.

MySQL Builder mode Column and Alias

The Column component in MySQL Builder mode allows you to select a column on which to run the aggregation. The Alias component is optional and allows you to add an alias from a dropdown or type your own alias in the box and click Enter. Remove an alias by clicking the X.

MySQL Builder mode Filter by column value

The Filter component in MySQL Builder mode is optional and can be toggled to add filters. You can add a column to filter by from a dropdown. To filter on more columns, click the plus sign to the right of the condition dropdown. You can choose various operators from the dropdown next to the condition. When multiple filters are added, you can use an AND operator to display all true conditions or an OR operator to display any true conditions. To remove a filter, click the X button next to that filter's dropdown. After selecting a date type column, you can choose Macros from the operators list and select timeFilter, which adds the $__timeFilter macro to the query with the selected date column.

MySQL Builder mode Group by column

The Group component in MySQL Builder mode is optional and can be toggled to add a Group by column. You can select a column to filter by from a dropdown. Click the plus sign to filter by multiple columns. Click the X to remove a filter.

MySQL Builder mode Order by and Limit

The Order component in MySQL Builder mode is optional and can be toggled to add an ORDER BY statement. You can select a column to order by from a dropdown and select ascending (ASC) or descending (DESC) order. The Limit component allows you to add an optional limit on the number of retrieved results, with a default of 50.

MySQL Builder mode Preview

The Preview component in MySQL Builder mode toggles a preview of the SQL query generated by the query builder. Preview is toggled on by default.

MySQL Code mode auto-completion

MySQL Code mode supports auto-completion of tables, columns, SQL keywords, standard SQL functions, Grafana template variables, and Grafana macros. Columns cannot be completed before a table has been specified.

MySQL Code mode format and keyboard shortcut

In MySQL Code mode, you can select Table or Time Series as the format. Click the {} in the bottom right to format the query. Click the downward caret to expand the Code mode editor. CTRL/CMD + Return serves as a keyboard shortcut to execute the query.

MySQL Code mode query changes not transferred to Builder mode

Changes made to a query in Code mode do not transfer to Builder mode and are discarded. Grafana prompts you to copy your code to the clipboard to save any changes.

MySQL reserved word and prohibited character handling

If a table or database name contains a reserved word or a prohibited character, the MySQL query editor puts quotes around the name. For example, the name table-name is quoted with backticks - `table-name`.

MySQL $__time macro

The $__time(dateColumn) macro replaces the value with an expression to convert to a UNIX timestamp and renames the column to time_sec. It helps recognize the time column as required in Time Series format. Example: UNIX_TIMESTAMP(dateColumn) AS time_sec.

MySQL $__timeEpoch macro

The $__timeEpoch(dateColumn) macro replaces the value with an expression to convert to a UNIX Epoch timestamp and renames the column to time_sec. Example: UNIX_TIMESTAMP(dateColumn) AS time_sec.

MySQL $__timeFilter macro

The $__timeFilter(dateColumn) macro applies a time range filter using the specified column name and fetches only data that falls within the currently active time range. Example: dateColumn BETWEEN FROM_UNIXTIME(1494410783) AND FROM_UNIXTIME(1494410983).

MySQL $timeFrom and $timeTo macros

The $__timeFrom() macro replaces the value with the start of the currently active time selection. Example: FROM_UNIXTIME(1494410783). The $__timeTo() macro replaces the value with the end of the currently active time selection. Example: FROM_UNIXTIME(1494410983).

MySQL $__timeGroup macro with fill parameter

The $__timeGroup(dateColumn,'5m') macro replaces the value with an expression suitable for use in a GROUP BY clause and creates bucket timestamps at a fixed interval. Example: cast(cast(UNIX_TIMESTAMP(dateColumn)/(300) as signed)*300 as signed). The $__timeGroup(dateColumn,'5m', 0) macro includes a fill parameter to ensure missing points in the series are added by Grafana using 0 as the default value (applies only to time series queries). The $__timeGroup(dateColumn,'5m', NULL) macro uses NULL as the value for missing points (applies only to time series queries). The $__timeGroup(dateColumn,'5m', previous) macro uses the previous value in the series as the fill value; if no previous value exists, NULL will be used (applies only to time series queries).

MySQL $__timeGroupAlias macro

The $__timeGroupAlias(dateColumn,'5m') macro replaces the value identical to $__timeGroup but with an added column alias.

MySQL $__unixEpochNanoFilter macro

The $__unixEpochNanoFilter(dateColumn) macro replaces the value with a time range filter using the specified column name with time represented as a nanosecond timestamp. Example: dateColumn > 1494410783152415214 AND dateColumn < 1494497183142514872.

MySQL $unixEpochNanoFrom and $unixEpochNanoTo macros

The $__unixEpochNanoFrom() macro replaces the value with the start of the currently active time selection as nanosecond timestamp. Example: 1494410783152415214. The $__unixEpochNanoTo() macro replaces the value with the end of the currently active time selection as nanosecond timestamp. Example: 1494497183142514872.

MySQL $unixEpochGroup and $unixEpochGroupAlias macros

The $__unixEpochGroup(dateColumn,'5m', [fillmode]) macro works the same as $__timeGroup but for times stored as Unix timestamp. Note that fillMode only works with time series queries. The $__unixEpochGroupAlias(dateColumn,'5m', [fillmode]) macro works the same as $__timeGroup but also adds a column alias. Note that fillMode only works with time series queries.

MySQL fill resampling safeguards in Grafana 13.0

As of Grafana 13.0, fill resampling in $__timeGroup and $__unixEpochGroup includes additional safeguards to prevent incorrect data points when the query returns no rows or the time range falls outside the data boundaries.

MySQL Table format SQL queries

If the Format option is set to Table, you can execute virtually any type of SQL query. The Table panel automatically displays the resulting columns and rows from your query. You can change or customize the name of a Table panel column by using the SQL keyword AS syntax.

MySQL time series query requirements

To run a time series query in MySQL you must include a column named time that returns either a SQL datetime value or a numeric datatype representing the UNIX epoch time in seconds. Additionally, the query results must be sorted by the time column for proper visualization in panels.

MySQL time series query metric column backward compatibility

For backward compatibility, queries returning three columns including a string column named metric are handled as an exception. Instead of converting the metric column into field labels, it is used as the field name, and the series name is set to the value of the metric column.

MySQL query editor access locations

The MySQL query editor is located on the Explore page. You can also access the MySQL query editor from a dashboard panel by clicking the ellipsis in the upper right of the panel and selecting Edit.

MySQL annotations documentation link

For detailed information on creating annotations with MySQL, refer to the MySQL annotations documentation.

Example MySQL Table query with JOIN and time filter

SELECT title as 'Title', user.login as 'Created By', dashboard.created as 'Created On' FROM dashboard INNER JOIN user on user.id = dashboard.created_by WHERE $__timeFilter(dashboard.created)

Example MySQL time series query with $__timeFilter macro

SELECT $__time(time_date_time), value_double FROM my_data WHERE $__timeFilter(time_date_time) ORDER BY time_date_time

Example MySQL time series query with $__timeGroup macro

SELECT $__timeGroup(time_date_time, '5m') AS time, sum(value_double) AS sum_value FROM my_data WHERE $__timeFilter(time_date_time) GROUP BY time ORDER BY time

Example MySQL time series query with $__timeGroupAlias and fill parameter

SELECT $__timeGroupAlias(createdAt,'5m',0), sum(value_double) as value, hostname FROM my_data WHERE $__timeFilter(createdAt) GROUP BY time, hostname ORDER BY time

Example MySQL time series query with $__timeGroupAlias and multiple columns

SELECT $__timeGroupAlias(time_date_time,'5m'), min(value_double) as min_value, max(value_double) as max_value FROM my_data WHERE $__timeFilter(time_date_time) GROUP BY time ORDER BY time

Give your agent this brain