Transformation functions

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.

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:

Query B:

After you concatenate the fields, the data frame would be:

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

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

Config query

Output query (same as input, but now with config on the value field)

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

In the field mapping, specify:

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:

For example consider the following query that could be modified by selecting the time field, as Time, and Date Format as YYYY.

The result:

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:

Consider the following dataset:

Dataset Example

You could prepare the data to be used by a Time series panel with this configuration:

This will generate the following output:

Transformed Data

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:

For example, if you have this data:

Dataset Example

With this configuration:

You’ll get the following output:

Transformed Data

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.

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:

Additional available conditions for number fields are:

Consider the following dataset:

If you Include the data points that have a temperature below 30°C, the configuration will look as follows:

And you will get the following result, where only the temperatures below 30°C are included:

Transformed Data

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:

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:

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:

This transformation provides a convenient way to standardize and tailor the presentation of string data for better visualization and analysis.

Format time

Use this transformation to customize the output of a time field. Output can be formatted using Moment.js format strings. 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:

After applying 'YYYY-MM-DD HH:mm:ss':

This transformation lets you tailor the time representation in your visualizations, providing flexibility and precision in displaying temporal data.

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.

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:

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:

And we can add more than one calculation. For instance:

We would then get:

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

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

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.

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:

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:

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.

Y Bucket

This setting determines how the y-axis is split into buckets.

Assume you have the following dataset:

Histogram

Use this transformation to generate a histogram based on input data, allowing you to visualize the distribution of values.

Original data

Series 1:

Series 2:

Output

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:

Query B:

The result after applying the inner join transformation looks like the following:

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:

Query B:

The result after applying the outer join transformation looks like the following:

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.

Example

Input 1: series1{what='Temp', cluster='A', job='J1'}

Input 2: series2{what='Temp', cluster='B', job='J1'}

Input 3: series3{what='Speed', cluster='B', job='J1'}

Config:

value: 'what'

Output:

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:

In Columns mode, the result looks like this:

In “Rows” mode, the result has a table for each series and show each label value like this:

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.

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.

This will first result in these two tables:

After merge:

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:

Here is the result after adding a Limit transformation with a value of ‘3’:

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:

Query B:

Here is the result after applying the Merge transformation:

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:

Example

Given this initial query result:

You could apply a rename field override to create:

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.

This is particularly useful when using a metrics SQL table, as in this example:

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'

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:

Could be transformed to:

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:

Could be transformed to:

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:

Query B:

The reduce transformer has two modes:

For example, if you used the First and Last calculation with a Series to rows transformation, then the result wouldbe:

The Reduce fields with the Last calculation, results in two frames, each with one row:

Query A:

Query B:

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:

Useful when visualizing data in:

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:

Output:

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:

Output:

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:

Query B:

Here is the result after applying the Series to rows transformation.

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.

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, 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: