# Query and transform data
****
This documentation topic is designed for Grafana workspaces that support **Grafana version 12.x**.
For Grafana workspaces that support Grafana version 10.x, see [Working in Grafana version 10](using-grafana-v10.md).
For Grafana workspaces that support Grafana version 9.x, see [Working in Grafana version 9](using-grafana-v9.md).
For Grafana workspaces that support Grafana version 8.x, see [Working in Grafana version 8](using-grafana-v8.md).
Amazon Managed Grafana supports many types of [data sources](AMG-data-sources.md). Data source *queries* return data that Grafana can *transform* and visualize. Each data source uses its own query language, and data source plugins each implement a query-building user interface called a query editor.
**Topics**
+ [About queries](#v12-panels-query-xform-about)
+ [Navigate the query tab](#v12-panels-query-xform-navigate)
+ [Adding a query](#v12-panels-query-xform-add)
+ [Manage queries](#v12-panels-query-xform-manage)
+ [Query options](#v12-panels-query-xform-options)
+ [Write expression queries](v12-panels-query-xform-expressions.md)
+ [Share query results with another panel](v12-panels-query-share.md)
+ [Transform data](v12-panels-xform.md)
+ [Troubleshoot queries](v12-panels-query-troubleshoot.md)
+ [Calculation types](v12-panels-calculation-types.md)
## About queries
Grafana panels communicate with data sources via queries, which retrieve data for the visualization. A query is a question written in the query language used by the data source.
You can configure query frequency and data collection limits in the panel’s data source options. Grafana supports up to 26 queries per panel.
You can find more information about each data source’s query language in the [Data sources](AMG-data-sources.md) section.
**Query editors**
Each data source’s *query editor* provides a customized user interface that helps you write queries that take advantage of its unique capabilities.
Because of the differences between query languages, each data source query editor looks and functions differently. Depending on your data source, the query editor might provide auto-completion features, metric names, variable suggestions, or a visual query-building interface.
For details on a specific data source’s unique query editor features, including information about queries and syntax, see the data source documentation:
+ For data sources included with Amazon Managed Grafana, see [Built-in data sources](AMG-data-sources-builtin.md).
+ For data sources included with Grafana Enterprise, see [Connect to Enterprise data sources](AMG-data-sources-enterprise.md).
+ For other data source plugins that you install via the [Find plugins with the plugin catalog](grafana-plugins.md#plugin-catalog), the documentation is linked within the listing in the plugin catalog.
**Query syntax**
Data sources use different query languages to request data. For details on a specific data source’s unique query language, refer to its documentation.
**PostgreSQL example:**
```
SELECT hostname FROM host WHERE region IN($region)
```
**PromQL example:**
```
query_result(max_over_time([${__range_s}s]) != )
```
**Special data sources**
Grafana also includes three special data sources: **Grafana**, **Mixed**, and **Dashboard**. For more information, see [Connect to data sources](AMG-data-sources.md).
## Navigate the query tab
A panel’s **Query** tab consists of the following elements:
+ **Data source selector** – Selects the data source to query.
+ **Query options** – Sets maximum data retrieval parameters and query run time intervals.
+ **Query inspector button** – Opens the query inspector panel, where you can view and optimize your query.
+ **Query editor list** – Lists the queries you’ve written.
+ **Expressions** – Uses the expression builder to create alert expressions. For more information about expressions, see [Write expression queries](v12-panels-query-xform-expressions.md).
## Adding a query
A query returns data that Grafana visualizes in dashboard panels. When you create a panel, Grafana automatically selects the default data source.
**To add a query**
1. Edit the panel to which you’re adding a query.
1. Choose the **Query** tab.
1. Choose the **Data source** dropdown menu and select a data source.
1. Choose **Query options** to configure the maximum number of data points you need. For more information about query options, see [Query options](#v12-panels-query-xform-options).
1. Write the query using the query editor.
1. Choose **Apply**.
Grafana queries the data source and visualizes the data.
## Manage queries
Grafana organizes queries in collapsible query rows. Each query row contains a query editor and is identified with a letter (A, B, C, and so on).
To manage your queries, you can copy queries, hide queries, remove queries, reorder queries, and toggle help for the query editor.
## Query options
Choose **Query options** next to the data source selector to see settings for the selected data source. Changes you make here affect only queries made in this panel.
Grafana sets defaults that are shown in dark gray text. Changes are displayed in white text. To return a field to the default setting, delete the white text from the field.
Panel data source query options include:
+ **Max data points** – If the data source supports it, this sets the maximum number of data points for each series returned. If the query returns more data points than the max data points setting, then the data source reduces the number of points returned by aggregating them together by average, max, or another function.
You can limit the number of points to improve query performance or smooth the visualized line. The default value is the width (or number of pixels) of the graph, because you can only visualize as many data points as the graph panel has room to display.
With streaming data, Grafana uses the max data points value for the rolling buffer. Streaming is a continuous flow of data, and buffering divides the stream into chunks.
+ **Min interval** – Sets a minimum limit for the automatically calculated interval, which is typically the minimum scrape interval. If a data point is saved every 15 seconds, you don’t benefit from having an interval lower than that. You can also set this to a higher minimum than the scrape interval to retrieve queries that are more coarse-grained and well-functioning.
+ **Interval** – Sets a time span that you can use when aggregating or grouping data points by time.
Grafana automatically calculates an appropriate interval that you can use as a variable in templated queries. The variable is measured in either seconds (`$__interval`) or milliseconds (`$__interval_ms`).
Intervals are typically used in aggregation functions like sum or average. For example, this is a Prometheus query that uses the interval variable: `rate(http_requests_total[$__interval])`.
This automatic interval is calculated based on the width of the graph. As the user zooms out on a visualization, the interval grows, resulting in a more coarse-grained aggregation. Likewise, if the user zooms in, the interval decreases, resulting in a more fine-grained aggregation.
For more information, see [Global variables](v12-dash-variable-add.md#v12-dash-variable-add-global).
+ **Relative time** – Overrides the relative time range for individual panels, which causes them to be different than what is selected in the dashboard time picker in the top-right corner of the dashboard. You can use this to show metrics from different time periods or days on the same dashboard.
**Note**
Panel time overrides have no effect when the dashboard’s time range is absolute.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/grafana/latest/userguide/v12-panels-query-xform.html)
+ **Time shift** – Overrides the time range for individual panels by shifting its start and end relative to the time picker. For example, you can shift the time range for the panel to be two hours earlier than the dashboard time picker.
**Note**
Panel time overrides have no effect when the dashboard's time range is absolute.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/grafana/latest/userguide/v12-panels-query-xform.html)
+ **Cache timeout** – *(Visible only if available in the data source)* Overrides the default cache timeout if your time series store has a query cache. Specify this value as a numeric value in seconds.
# Write expression queries
****
This documentation topic is designed for Grafana workspaces that support **Grafana version 12.x**.
For Grafana workspaces that support Grafana version 10.x, see [Working in Grafana version 10](using-grafana-v10.md).
For Grafana workspaces that support Grafana version 9.x, see [Working in Grafana version 9](using-grafana-v9.md).
For Grafana workspaces that support Grafana version 8.x, see [Working in Grafana version 8](using-grafana-v8.md).
Server-side expressions enable you to manipulate data returned from queries with math and other operations. Expressions create new data and do not manipulate the data returned by data sources.
## About expressions
Server-side expressions allow you to manipulate data returned from queries with math and other operations. Expressions create new data and do not manipulate the data returned by data sources, aside from some minor data restructuring to make the data acceptable input for expressions.
**Using expressions**
Expressions are most commonly used by [Grafana alerting](v12-alerts.md). The processing is done server-side, so expressions can operate without a browser session. However, expressions can also be used with backend data sources and visualization.
**Note**
Expressions do not work with legacy dashboard alerts.
Expressions are meant to augment data sources by enabling queries from different data sources to be combined or by providing operations unavailable in a data source.
**Note**
When possible, you should do data processing inside the data source. Copying data from storage to the Grafana server for processing is inefficient, so expressions are targeted at lightweight data processing.
Expressions work with data source queries that return time series or numeric data. They also operate on [multiple-dimensional data](getting-started-grafanaui.md#time-series-dimensions). For example, a query that returns multiple series, where each series is identified by labels or tags.
An individual expression takes one or more queries or other expressions as input and adds data to the result. Each individual expression or query is represented by a variable that is a named identifier known as its RefID (e.g., the default letter `A` or `B`).
To reference the output of an individual expression or a data source query in another expression, this identifier is used as a variable.
**Types of expressions**
Expressions work with two types of data.
+ A collection of time series.
+ A collection of numbers, where each number is an item.
Each collection is returned from a single data source query or expression and represented by the RefID. Each collection is a set, where each item in the set is uniquely identified by its dimensions which are stored as [labels](getting-started-grafanaui.md#labels) or key-value pairs.
**Data source queries**
Server-side expressions only support data source queries for backend data sources. The data is generally assumed to be labeled time series data.
Data source queries, when used with expressions, are run by the expression engine. When it does this, it restructures data to be either one time series or one number per data frame. So for example if using a data source that returns multiple series on one frame in the table view, you might notice it looks different when run with expressions.
Currently, the only non-time series format (number) is supported when using data frames are you have a table response that returns a data frame with no time, string columns, and one number column:
The following example table produces a number that works with expressions. The string columns become labels and the number column the corresponding value. For example `{"Loc": "MIA", "Host": "A"}` with a value of 1.
| Loc | Host | Avg\$1CPU |
| --- | --- | --- |
| MIA | A | 1 |
| NYC | B | 2 |
**Operations**
You can use the following operations in expressions: math, reduce, and resample.
**Math**
Math is for free-form math formulas on time series or number data. Math operations take numbers and time series as input and changes them to different numbers and time series.
Data from other queries or expressions are referenced with the RefID prefixed with a dollar sign, for example `$A`. If the variable has spaces in the name, then you can use a brace syntax like `${my variable}`.
Numeric constants can be in decimal (`2.24`), octal (with a leading zero like `072`), or hex (with a leading 0x like `0x2A`). Exponentials and signs are also supported (for example, `-0.8e-2`).
**Operators**
The arithmetic (`+`, binary and unary `-`, `*`, `/`, `%`, exponent `**`), relational (`<`, `>`, `==`, `!=`, `>=`, `<=`), and logical (`&&`, `||`, and unary `!`) operators are supported.
How the operation behaves with data depends on if it is a number or time series data.
With binary operations, such as `$A + $B` or `$A || $B`, the operator is applied in the following ways depending on the type of data:
+ If both `$A` and `$B` are a number, then the operation is performed between the two numbers.
+ If one variable is a number, and the other variable is a time series, then the operation between the value of each point in the time series and the number is performed.
+ If both `$A` and `$B` are time series data, then the operation between each value in the two series is performed for each time stamp that exists in both `$A` and `$B`. The `Resample` operation can be used to line up time stamps.
Summary:
+ Number number = number
+ Number series = series
+ Series series = series
Because expressions work with multiple series or numbers represented by a single variable, binary operations also perform a union (join) between the two variables. This is done based on the identifying labels associated with each individual series or number.
So if you have numbers with labels like `{host=web01}` in `$A` and another number in `$B` with the same labels then the operation is performed between those two items within each variable, and the result will share the same labels. The rules for the behavior of this union are as follows:
+ An item with no labels will join to anything.
+ If both `$A` and `$B` each contain only one item (one series, or one number), they will join.
+ If labels are exact match they will join.
+ If labels are a subset of the other, for example an item in `$A` is labeled `{host=A,dc=MIA}` and an item in `$B` is labeled `{host=A}` they will join.
+ If within a variable such as `$A` there are different tag keys for each item, the join behavior is undefined.
The relational and logical operators return 0 for false 1 for true.
**Math Functions**
While most functions exist in the own expression operations, the math operation does have some functions that similar to math operators or symbols. When functions can take either numbers or series, than the same type as the argument will be returned. When it is a series, the operation of performed for the value of each point in the series.
*abs*
abs returns the absolute value of its argument which can be a number or a series. For example `abs(-1)` or `abs($A)`.
*is\$1inf*
is\$1inf takes a number or a series and returns `1` for `Inf` values (negative or positive) and `0` for other values. For example `is_inf($A)`.
**Note**
If you need to specifically check for negative infinity for example, you can do a comparison like `$A == infn()`.
*is\$1nan*
is\$1nan takes a number or a series and returns `1` for `NaN` values and `0` for other values. For example `is_nan($A)`. This function is required for this check because `NaN` is not equal to `NaN`.
*is\$1null*
is\$1null takes a number or a series and returns `1` for `null` values and `0` for other values. For example `is_null($A)`.
*is\$1number*
is\$1number takes a number or a series and returns `1` for all real number values and `0` for other values (which are `null`, `Inf+`, `Inf-`, and `NaN`). For example `is_number($A)`.
*log*
Log returns the natural logarithm of its argument which can be a number or a series. If the value is less than 0, `NaN` is returned. For example `log(-1)` or `log($A)`.
*inf, infn, nan, and null*
The inf, infn, nan, and null functions all return a single value of the name. They primarily exist for testing. Example: `null()`.
*round*
Round returns a rounded integer value. For example, `round(3.123)` or `round($A)`.
*ceil*
Ceil rounds the number up to the nearest integer value. For example, `ceil(3.123)` returns `4`.
*floor*
Floor rounds the number down to the nearest integer value. For example, `floor(3.123`) returns `3`.
**Reduce**
Reduce takes one or more time series returned from a query or an expression and turns each series into a single number. The labels of the time series are kept as labels on each outputted reduced number.
*Fields:*
+ **Function** – The reduction function to use
+ **Input** – The variable (refID (such as `A`)) to resample
+ **Mode** – Allows control behavior of reduction function when a series contains non-numerical values (null, NaN, \$1-Inf)
**Reduction Functions**
*Count*
Count returns the number of points in each series.
*Mean*
Mean returns the total of all values in each series divided by the number of points in that series. In `strict` mode if any values in the series are null or nan, or if the series is empty, NaN is returned.
*Min and Max*
Min and Max return the smallest or largest value in the series respectively. In `strict` mode if any values in the series are null or nan, or if the series is empty, NaN is returned.
*Sum*
Sum returns the total of all values in the series. If series is of zero length, the sum will be 0. In `strict` mode if there are any NaN or Null values in the series, NaN is returned.
*Last*
Last returns the last number in the series. If the series has no values then returns NaN.
**Reduction Modes**
*Strict*
In Strict mode the input series is processed as is. If any values in the series are non-numeric (null, NaN or \$1-Inf), NaN is returned.
*Drop Non-Numeric*
In this mode all non-numeric values (null, NaN or \$1-Inf) in the input series are filtered out before running the reduction function.
*Replace Non-Numeric*
In this mode all non-numeric values are replaced by a pre-defined value.
**Resample**
Resample changes the time stamps in each time series to have a consistent time interval. The main use case is so you can resample time series that do not share the same timestamps so math can be performed between them. This can be done by resample each of the two series, and then in a Math operation referencing the resampled variables.
*Fields:*
+ **Input** – The variable of time series data (refID (such as `A`)) to resample
+ **Resample** to – The duration of time to resample to, for example `10s`. Units can be `s` for seconds, `m` for minutes, `h` for hours, `d` for days, `w` for weeks, and `y` for years.
+ **Downsample** – The reduction function to use when there are more than one data point per window sample. See the reduction operation for behavior details.
+ **Upsample** – The method to use to fill a window sample that has no data points.
+ **pad** fills with the last knowm value
+ **backfill** with next known value
+ **fillna** to fill empty sample windows with NaNs
## Write an expression
If your data source supports them, then Grafana displays the **Expression** button and shows any existing expressions in the query editor list.
**To write an expression**
1. Open the panel.
1. Below the query, choose **Expression**.
1. In the **Operation** field, select the type of expression you want to write.
1. Write the expression.
1. Choose **Apply**.
## Special cases
When any queried data source returns no series or numbers, the expression engine returns `NoData`. For example, if a request contains two data source queries that are merged by an expression, if `NoData` is returned by at least one of the data source queries, then the returned result for the entire query is `NoData`. For more information about how Grafana Alerting processes `NoData` results, see [Configure Grafana managed alert rules](v12-alerting-configure-grafanamanaged.md).
In the case of using an expression on multiple queries, the expression engine requires that all of the queries return an identical timestamp. For example, if using math to combine the result of multiple SQL queries which each use `SELECT NOW() AS "time"`, the expression will only work if all queries evaluate `NOW()` to an identical timestamp, which does not always happen. To resolve this, you can replace `NOW()` with an arbitrary time, such as `SELECT 1 AS "time"`, or any other valid UNIX timestamp.
# Share query results with another panel
****
This documentation topic is designed for Grafana workspaces that support **Grafana version 12.x**.
For Grafana workspaces that support Grafana version 10.x, see [Working in Grafana version 10](using-grafana-v10.md).
For Grafana workspaces that support Grafana version 9.x, see [Working in Grafana version 9](using-grafana-v9.md).
For Grafana workspaces that support Grafana version 8.x, see [Working in Grafana version 8](using-grafana-v8.md).
Grafana lets you use the query result from one panel for any other panel in the dashboard. Sharing query results across panels reduces the number of queries made to your data source, which can improve the performance of your dashboard.
The *Dashboard* data source lets you select a panel in your dashboard that contains the queries you want to share the results for. Instead of sending a separate query for each panel, Grafana sends one query and other panels use the query results to construct visualizations.
This strategy can drastically reduce the number of queries being made when you for example have several panels visualizing the same data.
**To share query results**
1. [Create a dashboard](v12-dash-creating.md).
1. Change the title to `Source panel`. You'll use this panel as a source for the other panels.
1. Define the query or queries that you want to share.
If you don't have a data source available, use the **Grafana** data source, which returns a random time series that you can use for testing.
1. Add a second panel and select the **Dashboard** data source in the query editor.
1. In the **Use results from panel list**, select the first panel you created.
All queries defined in the source panel are now available to the new panel. Queries made in the source panel can be shared with multiple panels.
You can click on any of the queries to go to the panel where they are defined.
# Transform data
****
This documentation topic is designed for Grafana workspaces that support **Grafana version 12.x**.
For Grafana workspaces that support Grafana version 10.x, see [Working in Grafana version 10](using-grafana-v10.md).
For Grafana workspaces that support Grafana version 9.x, see [Working in Grafana version 9](using-grafana-v9.md).
For Grafana workspaces that support Grafana version 8.x, see [Working in Grafana version 8](using-grafana-v8.md).
Transformations are a powerful way to manipulate data returned by a query before the system applies a visualization. Using transformations, you can:
+ Rename fields
+ Join time series data
+ Perform mathematical operations across queries
+ Use the output of one transformation as the input to another transformation
For users that rely on multiple views of the same dataset, transformations offer an efficient method of creating and maintaining numerous dashboards.
You can also use the output of one transformation as the input to another transformation, which results in a performance gain.
**Note**
Sometimes the system cannot graph transformed data. When that happens, click the **Table view** toggle above the visualization to switch to a table view of the data. This can help you understand the final result of your transformations.
## Transformation types
Grafana provides a number of ways that you can transform data. There is a complete list of transformation functions below.
## Order of transformations
When there are multiple transformations, Grafana applies them in the order they are listed. Each transformation creates a result set that then passes on to the next transformation in the processing pipeline.
The order in which Grafana applies transformations directly impacts the results. For example, if you use a Reduce transformation to condense all the results of one column into a single value, then you can only apply transformations to that single value.
## Add a transformation function to data
The following steps guide you in adding a transformation to data. This documentation does not include steps for each type of transformation.
**To add a transformation to a panel**
1. Navigate to the panel where you want to add one or more transformations.
1. Hover over any part of the panel to display the actions menu on the top right corner.
1. From the actions menu choose **Edit**.
1. Select the **Transform** tab.
1. Select a transformation. A transformation row appears where you configure the transformation options.
1. To apply another transformation, Choose **Add transformation**. This transformation acts on the result set returned by the previous transformation.
## Debug a transformation
To see the input and the output result sets of the transformation, choose the debug (bug) icon on the right side of the transformation row. This will display the input data, and the result of the transformation as output.
The input and output results sets can help you debug a transformation.
## Disable a transformation
You can disable or hide a transformation by choosing the show (eye) icon on the top right of the transformation row. This disables the applied actions of that specific transformation and can help to identify issues when you change several transformations one after another.
## Filter a transformation
If your transformation uses more than one query, you can filter these, and apply the selected transformation to only one of the queries. To do this, choose the filter icon on the top right of the transformation row. This opens a dropdown with a list of queries used on the panel. From here, you can select the query you want to transform.
You can also filter by annotations (which includes exemplars) to apply transformations to them. When you do so, the list of fields changes to reflect those in the annotation or exemplar tooltip.
The filter icon is always displayed if your panel has more than one query or source of data (that is panel or annotation data), but it may not work if previous transformations for merging the queries' outputs are applied. This is because one transformation takes the output of the previous one.
## Delete a transformation
We recommend that you remove transformations that you don’t need. When you delete a transformation, you remove the data from the visualization.
Prerequisites:
Identify all dashboards that rely on the transformation and inform impacted dashboard users.
**To delete a transformation**
1. Open a panel for editing.
1. Select the **Transform** tab.
1. Choose the trash icon next to the transformation you want to delete.
# Transformation functions
****
This documentation topic is designed for Grafana workspaces that support **Grafana version 12.x**.
For Grafana workspaces that support Grafana version 10.x, see [Working in Grafana version 10](using-grafana-v10.md).
For Grafana workspaces that support Grafana version 9.x, see [Working in Grafana version 9](using-grafana-v9.md).
For Grafana workspaces that support Grafana version 8.x, see [Working in Grafana version 8](using-grafana-v8.md).
You can perform the following transformations on your data.
## Add field from calculation
Use this transformation to add a new field calculated from two other fields. Each transformation allows you to add one new field.
+ **Mode** - Select a mode:
+ **Reduce row** – Apply selected calculation on each row of selected fields independently.
+ **Binary operation** – Apply basic binary operations (for example, sum or multiply) on values in a single row from two selected fields.
+ **Unary operation** – Apply basic unary operations on values in a single row from a selected field. The available operations are:
+ **Absolute value (abs)** – Returns the absolute value of a given expression. It represents the distance from zero as a positive number.
+ **Natural exponential (exp)** – Returns *e* raised to the power of a given expression.
+ **Natural logarithm (ln)** – Returns the natural logarithm of a given expression.
+ **Floor (floor)** – Returns the largest integer less than or equal to a given expression.
+ **Ceiling (ceil)** – returns the smallest integer greater than or equal to a given expression.
+ **Cumulative functions** – Apply functions on teh current row and all preceding rows.
+ **Total** – Calculates the cumulative total up to and including the current row.
+ **Mean** – Calculates the mean up to and including the current row.
+ **Window functions** – Apply window functions. The window can either be *trailing* or *centered*. With a trailing window the current row will be the last row in the window. With a centered window, the window will be centered on the current row. For even windows sizes, the window will be centered between the current row and the previous row.
+ **Mean** – Calculates the moving mean or running average.
+ **** – Calculates the moving standard deviation.
+ **Variance** – Calculate the moving variance.
+ **Row index** – Insert a field with the row index.
+ **Field name** – Select the names of fields you want to use in the calculation for the new field.
+ **Calculation** – If you select **Reduce row** mode, then the **Calculation** field appears. Select the field to see a list of calculation choices you can use to create the new field. For information about available calculations, refer to [Calculation types](v12-panels-calculation-types.md).
+ **Operation** – If you select **Binary operation** or **Unary operation** mode, then the **Operation** fields appear. These fields allow you to do basic math operations on values in a single row from two selected fields. You can also use numerical values for binary operations.
+ **As percentile** – If you select **Row index** mode, then the **As percentile** switch appears. This switch allows you to transform the row index as a percentage of the total number of rows.
+ **Alias** – (Optional) Enter the name of your new field. If you leave this blank, then the field will be named to match the calculation.
+ **Replace all fields** – (Optional) Select this option if you want to hide all other fields and display only your calculated field in the visualization.
**Note**
**Cumulative functions** and **Window functions** are current in public preview. Grafana Labs offers limited support, and breaking changes might occur prior to the feature being made generally available.
## Concatenate fields
**Concatenate fields**
Use this transformation to combines all fields from all frames into one result.
For example, if you have separate queries retrieving temperature and uptime data (query A) and air quality index and error information (query b), applying the concatenate transformation yields a consolidated data frame with all relevant information in one view.
Consider these two following.
*Query A:*
| Temp | Uptime |
| --- | --- |
| 15.4 | 1230233 |
*Query B:*
| AQI | Errors |
| --- | --- |
| 3.2 | 5 |
After you concatenate the fields, the data frame would be:
| Temp | Uptime | AQI | Errors |
| --- | --- | --- | --- |
| 15.4 | 1230233 | 3.2 | 5 |
This transformation simplifies the process of merging data from different sources, providing a comprehensive view for analysis and visualization.
## Config from query results
**Config from query results**
Use this transformation to select one query and extract standard options like **Min**, **Max**, **Unit** and **Thresholds** and apply them to other query results. This enables dynamic visualization configuration, based on the data returned by a specific query.
**Options**
+ **Config query** – Select the query that returns the data you want to use as configuration.
+ **Apply to** – Select the fields or series to which the configuration should be applied.
+ **Apply to options** – Specify a field type or use a field name regex, depending on your selection in **Apply to**.
**Field mapping table**
Below the configuration options, you'll find the field mapping table. This table lists all fields found in the data returned by the config query, along with the **Use as** and **Select** options. It provides control over mapping fields to config properties, and for multiple rows, it allows you to choose which value to select.
The following example shows an input query and a query used as field configuration.
*Input query*
| Time | Value |
| --- | --- |
| 1626178119127 | 10 |
| 1626178119129 | 30 |
*Config query*
| Time | Value |
| --- | --- |
| 1626178119127 | 100 |
| 1626178119129 | 100 |
*Output query (same as input, but now with config on the value field)*
| Time | Value (config: Max=100) |
| --- | --- |
| 1626178119127 | 10 |
| 1626178119129 | 30 |
Each field now has a maximum configuration option set. Options such as **Min**, **Max**, **Unit**, and **Thresholds** are part of teh field configuration. If set, they are used by the visualization instead of any options manually configured in the panel editor options pane.
**Value mappings**
You can also transform a query result into value mappings. With this option, every row in the configuration query result defines a single value mapping row. See the following example.
*Config query result*
| Value | Text | Color |
| --- | --- | --- |
| L | Low | blue |
| M | Medium | green |
| H | High | red |
*In the field mapping, specify:*
| Field | Use as | Select |
| --- | --- | --- |
| Value | Value mappings / Value | All values |
| Text | Value mappings / Text | All values |
| Color | Value mappings / Color | All values |
Grafana builds value mappings from your query result and applies them to the real data query results. You should see values being mapped and colored according to the config query results.
## Convert field type
Use this transformation to modify the field type of the specified field.
This transformation has the following options:
+ **Field** – Select from available fields.
+ **as** – Select the FieldType to convert to.
+ **Numeric** – attempts to make the values numbers.
+ **String** – will make the values strings.
+ **Time** – attempts to parse the values as time.
+ Will show an option to specify a DateFormat as input by a string, like `yyyy-mm-dd` or `DD MM YYYY hh:mm:ss`.
+ **Boolean** – will make the values Boolean.
+ **Enum** – will make the values enums.
+ Will show a table to manage the enums.
+ **Other** – attempts to parse the values as json.
For example consider the following query that could be modified by selecting the time field, as **Time**, and Date Format as `YYYY`.
| Time | Mark | Value |
| --- | --- | --- |
| 2017-07-01 | above | 25 |
| 2018-08-02 | below | 22 |
| 2019-09-02 | below | 29 |
| 2020-10-04 | above | 22 |
The result:
| Time | Mark | Value |
| --- | --- | --- |
| 2017-01-01 00:00:00 | above | 25 |
| 2018-01-01 00:00:00 | below | 22 |
| 2019-01-01 00:00:00 | below | 29 |
| 2020-01-01 00:00:00 | above | 22 |
This transformation allows you to flexibly adapt your data types, ensuring compatibility and consistency in your visualizations.
## Extract Fields
Use this transformation to select a source of data and extract content from it in different formats. This transformation has the following fields:
+ **Source** – Select the field for the source of data.
+ **Format** – Choose one of the following:
+ **JSON** – Parse JSON content from the source.
+ **Key\$1value pairs** – Parse content in the format `a=b` or `c:d` from the source.
+ **Auto** – Discover fields automatically.
+ **Regex** – Extract fields using regular expressions for more powerful field extraction from text data.
+ **Replace All Fields** – (Optional) Select this option to hide all other fields and display only your calculated field in the visualization.
+ **Keep Time** - (Optional) Available only if **Replace All Fields** is true. Keeps the time field in the output.
Consider the following dataset:
**Dataset Example**
| Timestamp | json\$1data |
| --- | --- |
| 1636678740000000000 | \$1"value": 1\$1 |
| 1636678680000000000 | \$1"value": 5\$1 |
| 1636678620000000000 | \$1"value": 12\$1 |
You could prepare the data to be used by a [Time series panel](v12-panels-time-series.md) with this configuration:
+ Source: json\$1data
+ Format: JSON
+ Field: value
+ Alias: my\$1value
+ Replace all fields: true
+ Keep time: true
This will generate the following output:
**Transformed Data**
| Timestamp | my\$1value |
| --- | --- |
| 1636678740000000000 | 1 |
| 1636678680000000000 | 5 |
| 1636678620000000000 | 12 |
With this transformation, you can extract and format data in various ways. You can customize the extraction format based on your specific data needs.
## Lookup fields from resource
Use this transformation to enrich a field value by looking up additional fields from an external source.
This transformation has the following fields:
+ **Field** – Select a text field from your dataset.
+ **Lookup** – Choose from **Countries**, **USA States**, and **Airports**.
**Note**
This transformation only supports spatial data.
For example, if you have this data:
**Dataset Example**
| Location | Values |
| --- | --- |
| AL | 0 |
| AK | 10 |
| Arizona | 5 |
| Arkansas | 1 |
| Somewhere | 5 |
With this configuration:
+ Field: location
+ Lookup: USA States
You’ll get the following output:
**Transformed Data**
| Location | ID | Name | Lng | Lat | Values |
| --- | --- | --- | --- | --- | --- |
| AL | AL | Alabama | -80.891064 | 12.448457 | 0 |
| AK | AK | Arkansas | -100.891064 | 24.448457 | 10 |
| Arizona | | | | | 5 |
| Arkansas | | | | | 1 |
| Somewhere | | | | | 5 |
This transformation lets you augment your data by fetching additional information from external sources, providing a more comprehensive dataset for analysis and visualization.
## Filter data by query refId
Use this transformation to hide one or more queries in panels that have multiple queries.
Grafana displays the query identification letters in dark gray text. Choose a query identifier to toggle filtering. If the query letter is white, then the results are displayed. If the query letter is dark, then the results are hidden.
**Note**
This transformation is not available for Graphite because this data source does not support correlating returned data with queries.
## Filter data by values
Use this transformation to selectively filter data points directly within your visualization. This transformation provides options to include or exclude data based on one or more conditions applied to a selected field.
This transformation is very useful if your data source does not natively filter by values. You might also use this to narrow values to display if you are using a shared query.
The available conditions for all fields are:
+ **Regex** – Match a regex expression.
+ **Is Null** – Match if the value is null.
+ **Is Not Null** – Match if the value is not null.
+ **Equal** – Match if the value is equal to the specified value.
+ **Different** – Match if the value is different than the specified value.
+ **Substring** – Match if the value contains the specified text.
Additional available conditions for number fields are:
+ **Greater** – Match if the value is greater than the specified value.
+ **Lower** – Match if the value is lower than the specified value.
+ **Greater or equal** – Match if the value is greater or equal.
+ **Lower or equal** – Match if the value is lower or equal.
+ **Range** – Match a range between a specified minimum and maximum, min and max included.
Consider the following dataset:
| Time | Temperature | Altitude |
| --- | --- | --- |
| 2020-07-07 11:34:23 | 32 | 101 |
| 2020-07-07 11:34:22 | 28 | 125 |
| 2020-07-07 11:34:21 | 26 | 110 |
| 2020-07-07 11:34:20 | 23 | 98 |
| 2020-07-07 10:32:24 | 31 | 95 |
| 2020-07-07 10:31:22 | 20 | 85 |
| 2020-07-07 09:30:57 | 19 | 101 |
If you **Include** the data points that have a temperature below 30°C, the configuration will look as follows:
+ Filter Type: 'Include'
+ Condition: Rows where 'Temperature' matches 'Lower Than' '30'
And you will get the following result, where only the temperatures below 30°C are included:
**Transformed Data**
| Time | Temperature | Altitude |
| --- | --- | --- |
| 2020-07-07 11:34:22 | 28 | 125 |
| 2020-07-07 11:34:21 | 26 | 110 |
| 2020-07-07 11:34:20 | 23 | 98 |
| 2020-07-07 10:31:22 | 20 | 85 |
| 2020-07-07 09:30:57 | 19 | 101 |
You can add more than one condition to the filter. For example, you might want to include the data only if the altitude is greater than 100. To do so, add that condition to the following configuration:
+ Filter type: 'Include' rows that 'Match All' conditions
+ Condition 1: Rows where 'Temperature' matches 'Lower' than '30'
+ Condition 2: Rows where 'Altitude' matches 'Greater' than '100'
When you have more than one condition, you can choose if you want the action (include/exclude) to be applied on rows that **Match all** conditions or **Match any** of the conditions you added.
In the example above, we chose **Match all** because we wanted to include the rows that have a temperature lower than 30°C *AND* an altitude higher than 100. If we wanted to include the rows that have a temperature lower than 30°C *OR* an altitude higher than 100 instead, then we would select **Match any**. This would include the first row in the original data, which has a temperature of 32°C (does not match the first condition) but an altitude of 101 (which matches the second condition), so it is included.
Conditions that are invalid or incompletely configured are ignored.
This versatile data filtering transformation lets you to selectively include or exclude data points based on specific conditions. Customize the criteria to tailor your data presentation to meet your unique analytical needs.
## Filter fields by name
Use this transformation to remove parts of your query results. There are three ways to filter field names:
+ Enter a regular expression.
+ Manually select included fields.
+ Use a dashboard variable.
*Use a regular expression*
When you filter using a regular expression, field names that match the regular expression are included. For example, using the regular expression `'prod.*'` would return only the fields that start with `prod`
The regular expression can include an interpolated dashboard variable using the `${variableName}` syntax.
*Manually select included fields*
Select or deselect field names to remove them from the result. If a regular expression is also included, fields that are matched by the expression are included, even if they're unchecked.
*Use a dashboard variable*
Select **From variable** to let you select a dashboard variable that's used to include fields. By setting up a dashboard variable with multiple choices, the same fields can be displayed across multiple visualizations.
This transformation provides flexibility in tailoring your query results to focus on the specific fields you need for effective analysis and visualization.
## Format string
Use this transformation to customize the output of a string field. This transformation has the following fields:
+ **Upper case** – Formats the entire string in uppercase characters.
+ **Lower case** – Formats the entire string in lowercase characters.
+ **Sentence case** – Formats the first character of the string in uppercase.
+ **Title case** – Formats the first character of each word in the string in uppercase.
+ **Pascal case** – Formats the first character of each word in the string in uppercase and doesn’t include spaces between words.
+ **Camel case** – Formats the first character of each word in the string in uppercase, except the first word, and doesn’t include spaces between words.
+ **Snake case** – Formats all characters in the string in lowercase and uses underscores instead of spaces between words.
+ **Kebab case** – Formats all characters in the string in lowercase and uses dashes instead of spaces between words.
+ **Trim** – Removes all leading and trailing spaces from the string.
+ **Substring** – Returns a substring of the string, using the specified start and end positions.
This transformation provides a convenient way to standardize and tailor the presentation of string data for better visualization and analysis.
**Note**
This transformation is currently in public preview. Grafana Labs offers limited support, and breaking changes might occur prior to the feature being made generally available.
## Format time
Use this transformation to customize the output of a time field. Output can be formatted using [Moment.js format strings](https://momentjs.com/docs/#/displaying/). For example, if you want to display only the year of a time field, the format string 'YYYY' can be used to show the calendar year (for example, 1999 or 2012).
**Before Transformation:**
| Timestamp | Event |
| --- | --- |
| 1636678740000000000 | System Start |
| 1636678680000000000 | User Login |
| 1636678620000000000 | Data Updated |
**After applying 'YYYY-MM-DD HH:mm:ss':**
| Timestamp | Event |
| --- | --- |
| 2021-11-12 14:25:40 | System Start |
| 2021-11-12 14:24:40 | User Login |
| 2021-11-12 14:23:40 | Data Updated |
This transformation lets you tailor the time representation in your visualizations, providing flexibility and precision in displaying temporal data.
**Note**
This transformation is available in workspaces compatbile with Grafana v12 as an alpha feature.
## Group by
This transformation groups the data by a specified field (column) value and processes calculations on each group. Select to see a list of calculation choices.
Here’s an example of original data.
| Time | Server ID | CPU Temperature | Server Status |
| --- | --- | --- | --- |
| 7/7/2020 11:34:20 AM | server 1 | 80 | Shutdown |
| 7/7/2020 11:34:20 AM | server 3 | 62 | OK |
| 7/7/2020 10:32:20 AM | server 2 | 90 | Overload |
| 7/7/2020 10:31:22 AM | server 3 | 55 | OK |
| 7/7/2020 9:30:57 AM | server 3 | 62 | Rebooting |
| 7/7/2020 9:30:05 AM | server 2 | 88 | OK |
| 7/7/2020 9:28:06 AM | server 1 | 80 | OK |
| 7/7/2020 9:25:05 AM | server 2 | 88 | OK |
| 7/7/2020 9:23:07 AM | server 1 | 86 | OK |
This transformation goes in two steps. First you specify one or multiple fields to group the data by. This will group all the same values of those fields together, as if you sorted them. For instance if we group by the Server ID field, then it would group the data this way:
| Time | Server ID | CPU Temperature | Server Status |
| --- | --- | --- | --- |
| 7/7/2020 11:34:20 AM | server 1 | 80 | Shutdown |
| 7/7/2020 9:28:06 AM | server 1 | 80 | OK |
| 7/7/2020 9:23:07 AM | server 1 | 86 | OK |
| 7/7/2020 10:32:20 AM | server 2 | 90 | Overload |
| 7/7/2020 9:30:05 AM | server 2 | 88 | OK |
| 7/7/2020 9:25:05 AM | server 2 | 88 | OK |
| 7/7/2020 11:34:20 AM | server 3 | 62 | OK |
| 7/7/2020 10:31:22 AM | server 3 | 55 | OK |
| 7/7/2020 9:30:57 AM | server 3 | 62 | Rebooting |
All rows with the same value of Server ID are grouped together.
After choosing which field you want to group your data by, you can add various calculations on the other fields, and apply the calculation to each group of rows. For instance, we could want to calculate the average CPU temperature for each of those servers. So we can add the *mean* calculation applied on the CPU Temperature field to get the following:
| Server ID | CPU Temperature (mean) |
| --- | --- |
| server 1 | 82 |
| server 2 | 88.6 |
| server 3 | 59.6 |
And we can add more than one calculation. For instance:
+ For field Time, we can calculate the *Last* value, to know when the last data point was received for each server
+ For field Server Status, we can calculate the *Last* value to know what is the last state value for each server
+ For field Temperature, we can also calculate the *Last* value to know what is the latest monitored temperature for each server
We would then get:
| Server ID | CPU Temperature (mean) | CPU Temperature (last) | Time (last) | Server Status (last) |
| --- | --- | --- | --- | --- |
| server 1 | 82 | 80 | 7/7/2020 11:34:20 AM | Shutdown |
| server 2 | 88.6 | 90 | 7/7/2020 10:32:20 AM | Overload |
| server 3 | 59.6 | 62 | 7/7/2020 11:34:20 AM | OK |
This transformation enables you to extract key information from your time series and display it in a convenient way.
## Grouping to matrix
Use this transformation to combine three fields—which are used as input for the **Column**, **Row**, and **Cell value** fields from the query output—and generate a matrix. The matrix is calculated as follows:
**Original data**
| Server ID | CPU Temperature | Server Status |
| --- | --- | --- |
| server 1 | 82 | OK |
| server 2 | 88.6 | OK |
| server 3 | 59.6 | Shutdown |
We can generate a matrix using the values of `Server Status` as column names, the `Server ID` values as row names, and the `CPU Temperature` as content of each cell. The content of each cell will appear for the existing column (`Server Status`) and row combination (`Server ID`). For the rest of the cells, you can select which value to display between: **Null**, **True**, **False**, or **Empty**.
**Output**
| Server IDServer Status | OK | Shutdown |
| --- | --- | --- |
| server 1 | 82 | |
| server 2 | 88.6 | |
| server 3 | | 59.6 |
Use this transformation to construct a matrix by specifying fields from your query results. The matrix output reflects the relationships between the unique values in these fields. This helps you present complex relationships in a clear and structured matrix format.
## Group to nested table
Use this transformation to group the data by a specified field (column) value and process calculation on each group. Records are generated that share the same grouped field value, to be displayed in a nested table.
To calculate a statistic for a field, select the box next to the field and choose the **Calculate** option. This will add another selection box with statistics to be selected.
The following table shows sample data.
| Time | Server ID | CPU Temperature | Server Status |
| --- | --- | --- | --- |
| 7/7/2020 11:34:20 AM | server 1 | 80 | Shutdown |
| 7/7/2020 11:34:20 AM | server 3 | 62 | OK |
| 7/7/2020 10:32:20 AM | server 2 | 90 | Overload |
| 7/7/2020 10:31:22 AM | server 3 | 55 | OK |
| 7/7/2020 9:30:57 AM | server 3 | 62 | Rebooting |
| 7/7/2020 9:30:05 AM | server 2 | 88 | OK |
| 7/7/2020 9:28:06 AM | server 1 | 80 | OK |
| 7/7/2020 9:25:05 AM | server 2 | 88 | OK |
| 7/7/2020 9:23:07 AM | server 1 | 86 | OK |
This transformation has two steps. First, specify one or more fields by which to group the data. This groups all the same values of those fields together, as if you sorted them For instance, if you group by the `Server ID` field, Grafana groups the data this way:
| Server ID | Data |
| --- | --- |
| Time | CPU Temperature | Server Status |
| --- | --- | --- |
| Time | CPU Temperature | Server Status |
| --- | --- | --- |
| Time | CPU Temperature | Server Status |
| --- | --- | --- |
| server 1 | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/grafana/latest/userguide/v12-panels-xform-functions.html) |
| server 2 | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/grafana/latest/userguide/v12-panels-xform-functions.html) |
| server 3 | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/grafana/latest/userguide/v12-panels-xform-functions.html) |
| 7/7/2020 11:34:20 AM | 80 | Shutdown |
| 7/7/2020 9:28:06 AM | 80 | OK |
| 7/7/2020 9:23:07 AM | 86 | OK |
| 7/7/2020 10:32:20 AM | 90 | Overload |
| 7/7/2020 9:30:05 AM | 88 | OK |
| 7/7/2020 9:25:05 AM | 88 | OK |
| 7/7/2020 11:34:20 AM | 62 | OK |
| 7/7/2020 10:31:22 AM | 55 | OK |
| 7/7/2020 9:30:57 AM | 62 | Rebooting |
After choosing the field by which you want to group your data, you can add various calculations on the other fields and apply the calculation to each group of rows. For instance, you might want to calculate the average CPU temperature for each of those servers. To do so, add the mean calculation applied on the CPU Temperature field to get the following result:
| Server ID | CPU Temperatute (mean) | |
| --- | --- | --- |
| Time | Server Status |
| --- | --- |
| Time | Server Status |
| --- | --- |
| Time | Server Status |
| --- | --- |
| server 1 | 82 | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/grafana/latest/userguide/v12-panels-xform-functions.html) |
| server 2 | 88.6 | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/grafana/latest/userguide/v12-panels-xform-functions.html) |
| server 3 | 59.6 | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/grafana/latest/userguide/v12-panels-xform-functions.html) |
| 7/7/2020 11:34:20 AM | Shutdown |
| 7/7/2020 9:28:06 AM | OK |
| 7/7/2020 9:23:07 AM | OK |
| 7/7/2020 10:32:20 AM | Overload |
| 7/7/2020 9:30:05 AM | OK |
| 7/7/2020 9:25:05 AM | OK |
| 7/7/2020 11:34:20 AM | OK |
| 7/7/2020 10:31:22 AM | OK |
| 7/7/2020 9:30:57 AM | Rebooting |
## Create heatmap
Use this transformation to prepare histogram data for visualizing trends over time. Similar to the heatmap visualization, this transformation converts histogram metrics into temporal buckets.
**X Bucket**
This setting determines how the x-axis is split into buckets.
+ **Size** – Specify a time interval in the input field. For example, a time range of `1h` creates cells one hour wide on the x-axis.
+ **Count** – For non-time-related series, use this option to define the number of elements in a bucket.
**Y Bucket**
This setting determines how the y-axis is split into buckets.
+ **Linear**
+ **Logarithmic** – Choose between log base 2 or log base 10.
+ **Symlog** – Uses a symmetrical logarithmic scale. Choose between log base 2 or log base 10, allowing for negative values.
Assume you have the following dataset:
| Timestamp | Value |
| --- | --- |
| 2023-01-01 12:00:00 | 5 |
| 2023-01-01 12:15:00 | 10 |
| 2023-01-01 12:30:00 | 15 |
| 2023-01-01 12:45:00 | 8 |
+ With X Bucket set to `Size: 15m` and Y Bucket as `Linear`, the histogram organizes values into time intervals of 15 minutes on the x-axis and linearly on the y-axis.
+ For X Bucket as `Count: 2` and Y Bucket as `Logarithmic (base 10)`, the histogram groups values into buckets of two on the x-axis and use a logarithmic scale on the y-axis.
## Histogram
Use this transformation to generate a histogram based on input data, allowing you to visualize the distribution of values.
+ **Bucket size** – The range between the lowest and highest items in a bucket (xMin to xMax).
+ **Bucket offset** – The offset for non-zero-based buckets.
+ **Combine series** – Create a unified histogram using all available series.
**Original data**
Series 1:
| A | B | C |
| --- | --- | --- |
| 1 | 3 | 5 |
| 2 | 4 | 6 |
| 3 | 5 | 7 |
| 4 | 6 | 8 |
| 5 | 7 | 9 |
Series 2:
| C |
| --- |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
**Output**
| xMin | xMax | A | B | C | C |
| --- | --- | --- | --- | --- | --- |
| 1 | 2 | 1 | 0 | 0 | 0 |
| 2 | 3 | 1 | 0 | 0 | 0 |
| 3 | 4 | 1 | 1 | 0 | 0 |
| 4 | 5 | 1 | 1 | 0 | 0 |
| 5 | 6 | 1 | 1 | 1 | 1 |
| 6 | 7 | 0 | 1 | 1 | 1 |
| 7 | 8 | 0 | 1 | 1 | 1 |
| 8 | 9 | 0 | 0 | 1 | 1 |
| 9 | 10 | 0 | 0 | 1 | 1 |
Visualize the distribution of values using the generated histogram, providing insights into the data’s spread and density.
## Join by field
Use this transformation to merge multiple results into a single table, enabling the consolidation of data from different queries.
The is especially useful for converting multiple time series results into a single wide table with a shared time field.
**Inner join**
An inner join merges data from multiple tables where all tables share the same value from the selected field. This type of join excludes data where values do not match in every result.
Use this transformation to combine the results from multiple queries (combining on a passed join field or the first time column) into one result, and drop rows where a successful join cannot occur.
In the following example, two queries return table data. It is visualized as two separate tables before applying the inner join transformation.
Query A:
| Time | Job | Uptime |
| --- | --- | --- |
| 7/7/2020 11:34:20 AM | node | 25260122 |
| 7/7/2020 11:24:20 AM | postgre | 123001233 |
| 7/7/2020 11:14:20 AM | postgre | 345001233 |
Query B:
| Time | Server | Errors |
| --- | --- | --- |
| 7/7/2020 11:34:20 AM | server 1 | 15 |
| 7/7/2020 11:24:20 AM | server 2 | 5 |
| 7/7/2020 11:04:20 AM | server 3 | 10 |
The result after applying the inner join transformation looks like the following:
| Time | Job | Uptime | Server | Errors |
| --- | --- | --- | --- | --- |
| 7/7/2020 11:34:20 AM | node | 25260122 | server 1 | 15 |
| 7/7/2020 11:24:20 AM | postgre | 123001233 | server 2 | 5 |
*Outer join*
An outer join includes all data from an inner join and rows where values do not match in every input. While the inner join joins Query A and Query B on the time field, the outer join includes all rows that don’t match on the time field.
In the following example, two queries return table data. It is visualized as two tables before applying the outer join transformation.
Query A:
| Time | Job | Uptime |
| --- | --- | --- |
| 7/7/2020 11:34:20 AM | node | 25260122 |
| 7/7/2020 11:24:20 AM | postgre | 123001233 |
| 7/7/2020 11:14:20 AM | postgre | 345001233 |
Query B:
| Time | Server | Errors |
| --- | --- | --- |
| 7/7/2020 11:34:20 AM | server 1 | 15 |
| 7/7/2020 11:24:20 AM | server 2 | 5 |
| 7/7/2020 11:04:20 AM | server 3 | 10 |
The result after applying the outer join transformation looks like the following:
| Time | Job | Uptime | Server | Errors |
| --- | --- | --- | --- | --- |
| 7/7/2020 11:04:20 AM | | | server 3 | 10 |
| 7/7/2020 11:14:20 AM | postgre | 345001233 | | |
| 7/7/2020 11:34:20 AM | node | 25260122 | server 1 | 15 |
| 7/7/2020 11:24:20 AM | postgre | 123001233 | server 2 | 5 |
## Join by labels
Use this transformation to join multiple results into a single table.
This is especially useful for converting multiple time series results into a single wide table with a shared **Label** field.
+ **Join** – Select the label to join by between the labels available or common across all time series.
+ **Value** – The name for the output result.
**Example**
Input 1: `series1{what='Temp', cluster='A', job='J1'}`
| Time | Value |
| --- | --- |
| 1 | 10 |
| 2 | 200 |
Input 2: `series2{what='Temp', cluster='B', job='J1'}`
| Time | Value |
| --- | --- |
| 1 | 10 |
| 2 | 200 |
Input 3: `series3{what='Speed', cluster='B', job='J1'}`
| Time | Value |
| --- | --- |
| 22 | 22 |
| 28 | 77 |
Config:
```
value: 'what'
```
Output:
| cluster | job | Temp | Speed |
| --- | --- | --- | --- |
| A | J1 | 10 | |
| A | J1 | 200 | |
| B | J1 | 10 | 22 |
| B | J1 | 200 | 77 |
Combine and organize time series data effectively with this transformation for comprehensive insights.
## Labels to fields
Use this transformation to convert time series results with labels or tags into a table, including each label's keys and values in the result. Display labels as either columns or row values for enhanced data visualization.
Given a query result of two time series:
+ Series 1 – labels `Server=Server A`, `Datacenter=EU`
+ Series 2 – labels `Server=Server B`, `Datacenter=EU`
In **Columns** mode, the result looks like this:
| Time | Server | Datacenter | Value |
| --- | --- | --- | --- |
| 7/7/2020 11:34:20 AM | Server A | EU | 1 |
| 7/7/2020 11:34:20 AM | Server B | EU | 2 |
In “Rows” mode, the result has a table for each series and show each label value like this:
| label | value |
| --- | --- |
| Server | Server A |
| Datacenter | EU |
| label | value |
| --- | --- |
| Server | Server B |
| Datacenter | EU |
**Value field name**
If you selected Server as the **Value field name**, then you would get one field for every value of the Server label.
| Time | Datacenter | Server A | Server B |
| --- | --- | --- | --- |
| 7/7/2020 11:34:20 AM | EU | 1 | 2 |
**Merging behavior**
The labels to fields transformer is internally two separate transformations. The first acts on single series and extracts labels to fields. The second is the merge transformation that joins all the results into a single table. The merge transformation tries to join on all matching fields. This merge step is required and cannot be turned off.
To illustrate this, here is an example where you have two queries that return time series with no overlapping labels.
+ Series 1 – labels `Server=ServerA`
+ Series 2 – labels `Datacenter=EU`
This will first result in these two tables:
| Time | Server | Value |
| --- | --- | --- |
| 7/7/2020 11:34:20 AM | ServerA | 10 |
| Time | Datacenter | Value |
| --- | --- | --- |
| 7/7/2020 11:34:20 AM | EU | 20 |
After merge:
| Time | Server | Value | Datacenter |
| --- | --- | --- | --- |
| 7/7/2020 11:34:20 AM | ServerA | 10 | |
| 7/7/2020 11:34:20 AM | | 20 | EU |
## Limit
Use this transformation to restrict the number of rows displayed, providing a more focused view of your data. This is particularly useful when dealing with large datasets.
The following is an example illustrating the impact of the **Limit** transformation on a response from a data source:
| Time | Metric | Value |
| --- | --- | --- |
| 7/7/2020 11:34:20 AM | Temperature | 25 |
| 7/7/2020 11:34:20 AM | Humidity | 22 |
| 7/7/2020 10:32:20 AM | Humidity | 29 |
| 7/7/2020 10:31:22 AM | Temperature | 22 |
| 7/7/2020 9:30:57 AM | Humidity | 33 |
| 7/7/2020 9:30:05 AM | Temperature | 19 |
Here is the result after adding a Limit transformation with a value of ‘3’:
| Time | Metric | Value |
| --- | --- | --- |
| 7/7/2020 11:34:20 AM | Temperature | 25 |
| 7/7/2020 11:34:20 AM | Humidity | 22 |
| 7/7/2020 10:32:20 AM | Humidity | 29 |
This transformation helps you tailor the visual presentation of your data to focus on the most relevant data.
## Merge series/tables
Use this transformation to combine the result from multiple queries into a single result, which is particularly useful when using the table panel visualization. The transformation merges values into the same row if shared fields contain the same data.
Here's an exampleillustration the impact of the **Merge series/tables** transformation on two queries returning table data:
*Query A:*
| Time | Job | Uptime |
| --- | --- | --- |
| 7/7/2020 11:34:20 AM | node | 25260122 |
| 7/7/2020 11:24:20 AM | postgre | 123001233 |
Query B:
| Time | Job | Errors |
| --- | --- | --- |
| 7/7/2020 11:34:20 AM | node | 15 |
| 7/7/2020 11:24:20 AM | postgre | 5 |
Here is the result after applying the Merge transformation:
| Time | Job | Errors | Uptime |
| --- | --- | --- | --- |
| 7/7/2020 11:34:20 AM | node | 15 | 25260122 |
| 7/7/2020 11:24:20 AM | postgre | 5 | 123001233 |
This transformation combines values from Query A and Query B into a unified table, enhancingthe presentation for better insights.
## Organize fields by name
Use this transformation to rename, reorder, or hide fields returned by a single query in your panel. This transformation only works in panels with a single query. If your panel has multiple queries, then you must either apply an *Outer join* transformation or remove the extra queries.
**Transforming fields**
Grafana displays a list of fields returned by the query. You can:
+ **Change field order** – Drag a field to a new location in the list.
+ **Hide or show a field** – Use the eye icon next to the field name to toggle the visibility of a field.
+ **Rename fields** – Type a new name into the **Rename** box.
**Example**
Given this initial query result:
| Time | Metric | Value |
| --- | --- | --- |
| 2020-07-07 11:34:20 | Temperature | 25 |
| 2020-07-07 11:34:20 | Humidity | 22 |
| 2020-07-07 10:32:20 | Humidity | 29 |
You could apply a rename field override to create:
| Time | Sensor | Reading |
| --- | --- | --- |
| 2020-07-07 11:34:20 | Temperature | 25 |
| 2020-07-07 11:34:20 | Humidity | 22 |
| 2020-07-07 10:32:20 | Humidity | 29 |
This transformation lets you tailor the display of query results, ensuring a clear and insightful representation of your data in Grafana.
## Partition by values
Use this transformation to streamline the process of graphing multiple series without the need for multiple queries with different `WHERE` clauses.
**Note**
This function is available in workspaces compatible with Grafana version 9 and above.
This is particularly useful when using a metrics SQL table, as in this example:
| Time | Region | Value |
| --- | --- | --- |
| 10/20/2022 12:00:00 PM | US | 1520 |
| 10/20/2022 12:00:00 PM | EU | 2936 |
| 10/20/2022 1:00:00 AM | US | 1327 |
| 10/20/2022 1:00:00 AM | EU | 912 |
With the *Partition by values* transformer, you can issue a single query and split the results by unique values in one or more columns (fields) of your choosing. The following example uses `Region`.
```
SELECT Time, Region, Value FROM metrics WHERE Time > '2022-10-20'
```
| Time | Region | Value |
| --- | --- | --- |
| 10/20/2022 12:00:00 PM | US | 1520 |
| 10/20/2022 1:00:00 AM | US | 1327 |
| Time | Region | Value |
| --- | --- | --- |
| 10/20/2022 12:00:00 PM | EU | 2936 |
| 10/20/2022 1:00:00 AM | EU | 912 |
This transformation simplifies the process and enhances the flexibility of visualizing multiple series within the same time series visualization.
## Prepare times series
Use this transformation to address issues when a data source returns time series data in a format that isn’t compatible with the desired visualization. This transformation allows you to convert time series data from between wide and long formats.
**Multi-frame time series**
Use this option to transform the time series data frame from the wide format to the long format. This is particularly helpful when your data source delivers time series information in a format that needs to be reshaped for optimal compatibility with your visualization.
*Example*
This input:
| Timestamp | Value1 | Value2 |
| --- | --- | --- |
| 2023-01-01 00:00:00 | 10 | 20 |
| 2023-01-01 01:00:00 | 15 | 25 |
Could be transformed to:
| Timestamp | Variable | Value |
| --- | --- | --- |
| 2023-01-01 00:00:00 | Value1 | 10 |
| 2023-01-01 00:00:00 | Value2 | 20 |
| 2023-01-01 01:00:00 | Value1 | 15 |
| 2023-01-01 01:00:00 | Value2 | 25 |
**Wide time series**
Use this option to transform the time series data frame from the long format to the wide format. This is particularly helpful when your data source delivers time series data in a long format and your visualization requires a wide format.
*Example*
This input:
| Timestamp | Variable | Value |
| --- | --- | --- |
| 2023-01-01 00:00:00 | Value1 | 10 |
| 2023-01-01 00:00:00 | Value2 | 20 |
| 2023-01-01 01:00:00 | Value1 | 15 |
| 2023-01-01 01:00:00 | Value2 | 25 |
Could be transformed to:
| Timestamp | Value1 | Value2 |
| --- | --- | --- |
| 2023-01-01 00:00:00 | 10 | 20 |
| 2023-01-01 01:00:00 | 15 | 25 |
## Reduce
Use this transformation applies a calculation to each field in the data frame and return a single value. This transformation is particularly useful for consolidating mulitiple time series data into a more compact, summarized format. Time fields are removed when applying this transformation.
Consider the input:
Query A:
| Time | Temp | Uptime |
| --- | --- | --- |
| 2020-07-07 11:34:20 | 12.3 | 256122 |
| 2020-07-07 11:24:20 | 15.4 | 1230233 |
Query B:
| Time | AQI | Errors |
| --- | --- | --- |
| 2020-07-07 11:34:20 | 6.5 | 15 |
| 2020-07-07 11:24:20 | 3.2 | 5 |
The reduce transformer has two modes:
+ **Series to rows** – Creates a row for each field and a column for each calculation.
+ **Reduce fields** – Keeps the existing frame structure, but collapses each field into a single value.
For example, if you used the **First** and **Last** calculation with a **Series to rows** transformation, then the result wouldbe:
| Field | First | Last |
| --- | --- | --- |
| Temp | 12.3 | 15.4 |
| Uptime | 256122 | 1230233 |
| AQI | 6.5 | 3.2 |
| Errors | 15 | 5 |
The **Reduce fields** with the **Last** calculation, results in two frames, each with one row:
Query A:
| Temp | Uptime |
| --- | --- |
| 15.4 | 1230233 |
Query B:
| AQI | Errors |
| --- | --- |
| 3.2 | 5 |
## Rename by regex
Use this transformation to rename parts of the query results using a regular expression and replacement pattern.
You can specify a regular expression, which is only applied to matches, along with a replacement pattern that support back references. For example, let’s imagine you’re visualizing CPU usage per host and you want to remove the domain name. You could set the regex to `([^\.]+)\..+` and the replacement pattern to `$1`, `web-01.example.com` would become `web-01`.
This transformation lets you tailor your data to meet your visualization needs, making your dashboards more informative and user-friendly.
## Rows to fields
Use this transformation to convert rows into separate fields. This can be useful because fields can be styled and configured individually. It can also use additional fields as sources for dynamic field configuration or map them to field labels. The additional labels can then be used to define better display names for the resulting fields.
This transformation includes a field table which lists all fields in the data returned by the configuration query. This table gives you control over what field should be mapped to each configuration property (the **Use as** option). You can also choose which value to select if there are multiple rows in the returned data.
This transformation requires:
+ One field to use as the source of field names.
By default, the transform uses the first string field as the source. You can override this default setting by selecting **Field name** in the **Use as** column for the field you want to use instead.
+ One field to use as the source of values.
By default, the transform uses the first number field as the source. But you can override this default setting by selecting **Field value** in the **Use as** column for the field you want to use instead.
Useful when visualizing data in:
+ Gauge
+ Stat
+ Pie chart
**Map extra fields to labels**
If a field does not map to config property Grafana will automatically use it as source for a label on the output field.
Example:
| Name | DataCenter | Value |
| --- | --- | --- |
| ServerA | US | 100 |
| ServerB | EU | 200 |
Output:
| ServerA (labels: DataCenter: US) | ServerB (labels: DataCenter: EU) |
| --- | --- |
| 100 | 200 |
The extra labels can now be used in the field display name to provide more complete field names.
If you want to extract config from one query and apply it to another you should use the *config from query results* transformation.
**Example**
Input:
| Name | Value | Max |
| --- | --- | --- |
| ServerA | 10 | 100 |
| ServerB | 20 | 200 |
| ServerC | 30 | 300 |
Output:
| ServerA (config: max=100) | ServerB (config: max=200) | ServerC (config: max=300) |
| --- | --- | --- |
| 10 | 20 | 30 |
As you can see each row in the source data becomes a separate field. Each field now also has a max config option set. Options like **Min**, **Max**, **Unit** and **Thresholds** are all part of field configuration and if set like this will be used by the visualization instead of any options manually configured in the panel editor options pane.
This transformation enables the conversion of rows into individual fields, facilitates dynamic field configuration, and maps additional fields to labels.
## Series to rows
Use this transformation to combine the result from multiple time series data queries into one single result. This is helpful when using the table panel visualization.
The result from this transformation will contain three columns: Time, Metric, and Value. The Metric column is added so you easily can see from which query the metric originates from. Customize this value by defining Label on the source query.
In the example below, we have two queries returning time series data. It is visualized as two separate tables before applying the transformation.
*Query A:*
| Time | Temperature |
| --- | --- |
| 2020-07-07 11:34:20 | 25 |
| 2020-07-07 10:31:22 | 22 |
| 2020-07-07 09:30:05 | 19 |
*Query B:*
| Time | Humidity |
| --- | --- |
| 2020-07-07 11:34:20 | 24 |
| 2020-07-07 10:32:20 | 29 |
| 2020-07-07 09:30:57 | 33 |
Here is the result after applying the Series to rows transformation.
| Time | Metric | Value |
| --- | --- | --- |
| 2020-07-07 11:34:20 | Temperature | 25 |
| 2020-07-07 11:34:20 | Humidity | 22 |
| 2020-07-07 10:32:20 | Humidity | 29 |
| 2020-07-07 10:31:22 | Temperature | 22 |
| 2020-07-07 09:30:57 | Humidity | 33 |
| 2020-07-07 09:30:05 | Temperature | 19 |
This transformation facilitates the consolidation of results from multiple time series queries, providing a streamlined and unified dataset for efficient analysis and visualization in a tabular format.
## Sort by
Use this transformation to sort each frame within a query result based on a specified field, making your data easier to understand and analyze. By configuring the desired field for sorting, you can control the order in which the data is presented in the table or visualization.
Use the **Reverse** switch to inversely order the values within the specified field. This functionality is particularly useful when you want to quickly toggle between ascending and descending order to suit your analytical needs.
For example, in a scenario where time-series data is retrieved from a data source, the **Sort by** transformation can be applied to arrange the data frames based on the timestamp, either in ascending or descending order, depending on the analytical requirements. This capability ensures that you can easily navigate and interpret time-series data, gaining valuable insights from the organized and visually coherent presentation.
## Spatial
Use this transformation to apply spatial operations to query results.
+ **Action** – Select an action:
+ **Prepare spatial field** – Set a geometry field based on the results of other fields.
+ **Location mode** – Select a location mode (these options are shared by the **Calculate value** and **Transform** modes):
+ **Auto** – Automatically identify location data based on default field names.
+ **Coords** – Specify latitude and longitude fields.
+ **Geohash** – Specify a geohash field.
+ **Lookup** – Specify Gazetteer location fields.
+ **Calculate value** – Use the geometry to define a new field (heading/distance/area).
+ **Function** – Choose a mathematical operation to apply to the geometry:
+ **Heading** – Calculate the heading (direction) between two points.
+ **Area** – Calculate the area enclosed by a polygon defined by the geometry.
+ **Distance** – Calculate the distance between two points.
+ **Transform** – Apply spatial operations to the geometry.
+ **Operation** – Choose an operation to apply to the geometry:
+ **As line** – Create a single line feature with a vertex at each row.
+ **Line builder** – Create a line between two points.
This transformation allows you to manipulate and analyze geospatial data, enabling operations such as creating lines between points, calculating spatial properties, and more.
## Time series to table transform
Use this transformation to convert time series results into a table, transforming a time series data frame into a **Trend** field. The **Trend** field can then be rendered using the [sparkline cell type](v12-panels-table.md#v12-panels-table-cell-type), generating an inline sparkline for each table row. If there are multiple time series queries, each will result in a separate table data frame. These can be joined using join or merge transforms to produce a single table with multiple sparklines per row.
For each generated **Trend** field value, a calculation function can be selected. The default is **Last non-null value**. This value is displayed next to the sparkline and used for sorting table rows.
## Regression analysis
Use this transformation to create a new data frame containing values predicted by a statistical model. This is useful for finding a trend in chaotic data. It works by fitting a mathematical function to the data, using either linear or polynomial regression. The data frame can then be used in a visualization to display a trendline.
There are two different models:
+ **Linear regression** – Fits a linear function to the data.
+ **Polynomial regression** – Fits a polynomial function to the data.
**Note**
This transformation is currently in public preview. Grafana Labs offers limited support, and breaking changes might occur prior to the feature being made generally available.
# Troubleshoot queries
****
This documentation topic is designed for Grafana workspaces that support **Grafana version 12.x**.
For Grafana workspaces that support Grafana version 10.x, see [Working in Grafana version 10](using-grafana-v10.md).
For Grafana workspaces that support Grafana version 9.x, see [Working in Grafana version 9](using-grafana-v9.md).
For Grafana workspaces that support Grafana version 8.x, see [Working in Grafana version 8](using-grafana-v8.md).
This page provides information to solve common dashboard problems.
**I get different results when I rearrange my functions**
Function order is very important. Just like in math, the order that you place your functions can affect the result.
**Inspect your query request and response**
The most common problems are related to the query and response from your data source. Even if it looks like a bug or visualization issue in Grafana, it is almost always a problem with the data source query or the data source response. Start by inspecting your panel query and response.
For more information, refer to [Inspect request and response data](v12-panels-panel-inspector.md).
**My query is slow**
How many data points is your query returning? A query that returns lots of data points will be slow. Try this:
+ In **Query options**, limit the **Max data points** returned.
+ In **Query options**, increase the **Min interval** time.
+ In your query, use a `group by` function.
# Calculation types
****
This documentation topic is designed for Grafana workspaces that support **Grafana version 12.x**.
For Grafana workspaces that support Grafana version 10.x, see [Working in Grafana version 10](using-grafana-v10.md).
For Grafana workspaces that support Grafana version 9.x, see [Working in Grafana version 9](using-grafana-v9.md).
For Grafana workspaces that support Grafana version 8.x, see [Working in Grafana version 8](using-grafana-v8.md).
The following table contains a list of calculations you can perform in Grafana. You can find these calculations in the **Transform** tab and in the bar gauge, gauge, and stat visualizations.
| Calculation | Description |
| --- | --- |
| All nulls | True when all values are null |
| All values | Array with all values |
| All unique values | Array with all unique values |
| All zeros | True when all values are 0 |
| Change count | Number of times the field’s value changes |
| Count | Number of values in a field |
| Delta | Cumulative change in value, only counts increments |
| Difference | Difference between first and last value of a field |
| Difference percent | Percentage change between first and last value of a field |
| Distinct count | Number of unique values in a field |
| First | First value in a field |
| First\$1 (not null) | First, not null value in a field (also excludes NaNs) |
| Last | Last value in a field |
| Last\$1 (not null) | Last, not null value in a field (also excludes NaNs) |
| Max | Maximum value of a field |
| Mean | Mean value of all values in a field |
| Variance | Variance of all values in a field |
| StdDev | Standard deviation of all values in a field |
| Min | Minimum value of a field |
| Min (above zero) | Minimum, positive value of a field |
| Range | Difference between maximum and minimum values of a field |
| Step | Minimal interval between values of a field |
| Total | Sum of all values in a field |