This post has been republished via RSS; it originally appeared at: Azure Data Explorer Blog articles.

A common table design pattern in Kusto is the use of narrow tables.

In a schema design, narrow tables are characterized by having a few data values colums, so for each row in a table, different types of information being stored. A narrow table can still have multiple columns that serve other purpose. For instance, columns could be present for various dimensions, such as a device or a sensor.

Let's consider an example of a narrow table with a fixed schema. The metric column includes values like temperature, pressure, or vibration. The respective measurements for these metrics are stored in the sensor_reading_real column:

The design pattern is very flexible. For new metrics no schema design change has to be made.

How to achieve having multiple metrics in one visualization, with the additional flexibility to let users select what metrics to be visualized? The envisioned outcome is a Grafana dashboard of a certain design:

Grafana dashboard - envisioned outcome

For this blog-post we are using Azure Managed Grafana for the visualization. As a pre-requisite we assume that you have Azure Managed Grafana and a Kusto database running.

In Kusto we use the following table definition to store the data:

.create table narrow_measurement_real (
measurement_ts        : datetime, 
device_id             : string,
metric                : string,
unit                  : string,
sensor_reading_real   : real 
) with (docstring ='narrow table with data values with datatype real', folder ='narrow')

The function definition looks as follows:

.create-or-alter function with (docstring = "wide view on narrow table",folder = "transformation") wide_measurement_real(_timestamp_from:datetime,_timestamp_to:datetime, _bin:timespan,_device_id:dynamic, _metric:dynamic) {
narrow_measurement_real
| where measurement_ts  between (_from .. _to) and metric in (_metric) and device_id in (_device_id)
| summarize sensor_reading=max(sensor_reading_real) by metric, device_id, bin(measurement_ts, _bin)
| evaluate pivot(metric, max(sensor_reading))
| order by measurement_ts
}

With this we are done on the database side and can start with the setup in Azure Managed Grafana. First we will set up a connection to our KQL database (for the detailed steps, please see also the documentation Visualize data from Azure Data Explorer in Grafana ). As a connection type we choose Azure Data Explorer Datasource. For the connection setup to the Kusto database in Azure Managed Grafana you need the cluser URI (for Fabric this is the URI provided as a Query URI under the database details). We are using the current user authentication method here, passing through the current user in Azure Managed Grafana to the Kusto database. You have to make sure that the users have at least viewer-permissons on the database. As of writing this blog article current user authentication method is in experimental phase, so it is not recommended yet using this in production deployments. 

The connection will look similar to:

Now we can start with the dashboard creation. In Grafana go to the dashboards menu, create a new dashboard and add a visualization, and select the data source you've defined in the previous step. In the query window, switch from the builder to direct KQL, as the query builder currently does not support functions.

The KQL in the query window should look like this:

let _from=$__timeFrom;
let _to=$__timeTo;
let _bin=$__timeInterval; //The $__interval is calculated using the time range and the width of the graph (the number of pixels).
let _device_id=dynamic([$v_device_id]);
let _metric=dynamic([$v_metric]);
wide_measurement_real(_from, _to, _bin, _device_id, _metric)

Next, you'll need to define the two variables referenced in the query for the drop-down selection:

1. for the selection of the devices (include the time filter so users can only select devices for the current time-filtering),
2. and for the selection of the metrics.

Apply the current dashboard configuration and switch to the dashboard settings to define these two variables as query types. Define the name, the label, select your data source, and choose Kusto Query as the query type. You can either use the builder or direct KQL entry with the query definition that looks as follows for both variables:

Devices: v_device_id

narrow_measurement_real
| where measurement_ts between ($__timeFrom .. $__timeTo) 
| distinct device_id
| order by device_id asc 

Metrics: v_metric

narrow_measurement_real
| distinct metric
| order by metric asc 

Make sure to change the refresh to On time range change for devices if you do the time selection in the query for the variable v_device_id. Enable multi-value selection for both variables, as we want to plot multiple metrics in the chart and multiple devices on the dashboards. This is also important, as the variables are defined with the dynamic datatype, so an array of senor ids and device ids is expected.

Almost done! Here are a few more edits:

• Add v_device_id under repeat options to get the timeseries per device.
• Define overrides for fields with regular expressions (this requires metrics to follow certain naming conventions). This way, you can configure the metrics displayed with different units based on the name. For more information on overriding the units of the metrics, check out the Grafana documentation
• Add the legend as a table with several calculations and include calculations like minima, maxima, mean values.

With this we are done, and have created a simple and very flexible dashboard! If new metrics are inserted ot the table, no change is required, you will be able to visualize them as they will appear in the dropdown list for the selection:

Grafana dashboard with the metric selection

Conclusion

In this blog article, you have learned how to use narrow tables in Kusto, how to create a function to get a wide view on a narrow table definition, and how to visualize multiple metrics in one visualization with the flexibility for users to select the metrics they want to see. You've also learned how to define variables in Grafana for a more interactive and customized visualization experience.