Visualize data from Azure Data Explorer in Grafana

Article
09/09/2024

Grafana is an analytics platform where you can query and visualize data, and then create and share dashboards based on your visualizations. Grafana provides an Azure Data Explorer plug-in, which enables you to connect to and visualize data from Azure Data Explorer. The plug-in works with both Azure Managed Grafana and self-hosted Grafana.

In this article, you learn how to configure your cluster as a data source for Grafana and visualize data in Grafana for Azure Managed Grafana and self-hosted Grafana. The StormEvents sample dataset contains weather-related data from the National Centers for Environmental Information.

Prerequisites

For Azure Managed Grafana, an Azure account and Azure Managed Grafana instance.
For self-hosted Grafana, Grafana version 5.3.0 or later for your operating system and the Azure Data Explorer plug-in for Grafana. You need plug-in version 3.0.5 or later to use the Grafana query builder.
An Azure Data Explorer cluster and database. You can create a full cluster.

Configure the data source

To configure Azure Data Explorer as a data source, follow the steps for your Grafana environment.

Azure Managed Grafana
Self-hosted Grafana

Add the managed identity to the Viewer role

Managed Grafana creates a system-assigned managed identity for each new workspace, by default. You can use it to access your Azure Data Explorer cluster.

In the Azure portal, go to your Azure Data Explorer cluster.
In the Overview section, select the database that has the StormEvents sample data.
Select Permissions > Add > Viewer.
In the search box, enter your Managed Grafana workspace name.
In the search results, select the result that matches your workspace name, and then choose Select.

Set up Azure Data Explorer as a Grafana data source

Managed Grafana workspaces come with the Azure Data Explorer plug-in preinstalled.

In the Azure portal, go to your Managed Grafana workspace.
Under Overview, select the Endpoint link to open the Grafana UI.
In Grafana, on the left menu, select the gear icon. Then select Data Sources.
Select Azure Data Explorer Datasource.
In Connection Details, enter your Azure Data Explorer cluster URL.
Select Save & Test.

Create a service principal

You can create the service principal in the Azure portal or by using the Azure CLI command-line experience. After you create the service principal, you get values for four connection properties that you'll use in later steps.

Azure portal

Follow the instructions in the Azure portal documentation. Use this specific information:
1. In the Assign the application to a role section, assign a role type of Reader to your Azure Data Explorer cluster.
2. In the Get values for signing in section, copy the values for the three properties covered in the steps: Directory ID (tenant ID), Application ID, and Password.
In the Azure portal, select Subscriptions. Then copy the ID for the subscription in which you created the service principal.

Azure CLI

Use the following command to create a service principal. Set an appropriate scope and a role type of reader.

az ad sp create-for-rbac --name "https://{UrlToYourDashboard}:{PortNumber}" --role "reader" \
                         --scopes /subscriptions/{SubID}/resourceGroups/{ResourceGroupName}

For more information, see Create an Azure service principal with the Azure CLI.

The command returns a result set like the following example. Copy the values for the appId, password, and tenant properties.

{
  "appId": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
  "displayName": "{UrlToYourDashboard}:{PortNumber}",
  "name": "https://{UrlToYourDashboard}:{PortNumber}",
  "password": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
  "tenant": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"
}

Get a list of your subscriptions:
```
az account list --output table
```
Copy the appropriate subscription ID.

Add the service principal to the Viewer role

Now that you have a service principal, you add it to the Viewer role in the Azure Data Explorer database. You can perform this task under Permissions in the Azure portal, or under Query by using a management command.

Azure portal: Permissions

In the Azure portal, go to your Azure Data Explorer cluster.
In the Overview section, select the database that has the StormEvents sample data.
Select Permissions > Add.
Under Add Database Permissions, select the Viewer role, and then choose Select principals.
Search for the service principal that you created. Select the principal, and then choose Select.
Select Save.

Management command: Query

In the Azure portal, go to your Azure Data Explorer cluster, and then select Query.
Run the following command in the query window. Use the application ID and tenant ID from the Azure portal or the Azure CLI.
```
.add database {TestDatabase} viewers ('aadapp={ApplicationID};{TenantID}')
```
The command returns a result set. In the following example, the first row is for an existing user in the database. The second row is for the service principal that you just added.

Specify properties and test the connection

With the service principal assigned to the Viewer role, you now specify properties in your instance of Grafana and test the connection to Azure Data Explorer.

In Grafana, on the left menu, select the gear icon. Then select Data Sources.
Select Add data source.
On the Data Sources / New page, enter a name for the data source, and then select the type Azure Data Explorer Datasource.
In Settings > Connection details, enter the name of your cluster in the form https://{ClusterName}.{Region}.kusto.chinacloudapi.cn. Enter the other values from the Azure portal or the Azure CLI. Use the following mapping information as a guide.

Grafana UI Azure portal Azure CLI

Subscription Id SUBSCRIPTION ID SubscriptionId

Tenant Id Directory ID tenant

Client Id Application ID appId

Client secret Password password
Select Save & Test.

If the test is successful, go to the next section. If you have any problems, check the values that you specified in Grafana and review the previous steps.

Grafana UI	Azure portal	Azure CLI
Subscription Id	SUBSCRIPTION ID	`SubscriptionId`
Tenant Id	Directory ID	`tenant`
Client Id	Application ID	`appId`
Client secret	Password	`password`

Optimize queries

You can use two features for query optimization:

Optimize dashboard query rendering performance
Enable weak consistency

To perform the optimization, in Data Sources > Settings > Query Optimizations, make the needed changes.

Screenshot that shows properties that can be configured on the query optimization pane.

Optimize dashboard query rendering performance

When a dashboard or visual is rendered more than once by one or more users, Grafana sends at least one query to Azure Data Explorer by default. Enable Query results caching to improve dashboard rendering performance and to reduce load on the Azure Data Explorer cluster.

During the specified time range, Azure Data Explorer will use the results cache to retrieve the previous results and won't run an unnecessary query. This capability is especially effective in reducing load on resources and improving performance when multiple users are using the same dashboard.

To enable results cache rendering, do the following on the Query Optimizations pane:

Turn off Use dynamic caching.
In Cache Max Age, enter the number of minutes during which you want to use cached results.

Enable weak consistency

Clusters are configured with strong consistency. This default configuration guarantees that query results are up to date with all changes in the cluster.

When you enable weak consistency, query results can have a lag of 1 to 2 minutes after cluster alterations. However, weak consistency might boost visual rendering time. If immediate consistency isn't critical and performance is marginal, enable weak consistency to improve performance. For more information, see Query consistency.

To enable weak consistency, on the Query Optimizations pane, select Data consistency > Weak.

Visualize data

You finished configuring Azure Data Explorer as a data source for Grafana. Now it's time to visualize data.

The following basic example uses both the query builder mode and the raw mode of the query editor. We recommend that you view write queries for Azure Data Explorer for examples of other queries to run against the dataset.

In Grafana, on the left menu, select the plus icon. Then select Dashboard.
Under the Add tab, select Graph.
On the graph pane, select Panel Title > Edit.
At the bottom of the pane, select Data Source, and then select the data source that you configured.

Query builder mode

Use query builder mode to define your query.

Below the data source, select Database and choose your database from the dropdown list.
Select From and choose your table from the dropdown list.
Now that the table is defined, filter the data:
1. Select + to the right of Where (filter) to select one or more columns in your table.
2. For each filter, define the values by using the applicable operator. This selection is similar to using the where operator in Kusto Query Language.
Select the values to present in the table:
1. Select + to the right of Value columns to select the value columns that will appear on the pane.
2. For each value column, set the aggregation type.
  
  You can set one or more value columns. This selection is equivalent to using the summarize operator.
Select + to the right of Group by (summarize) to select one or more columns that will be used to arrange the values into groups. This selection is equivalent to the group expression in the summarize operator.
Select Run Query.

Tip

While you're finalizing the settings in the query builder, a Kusto Query Language query is created. This query shows the logic that you constructed by using the graphical query editor.
Select Edit KQL to move to raw mode. Edit your query by using the flexibility and power of the Kusto Query Language.

Screenshot of a raw query in the query builder.

Raw mode

Use raw mode to edit your query.

On the query pane, paste the following query, and then select Run. The query buckets the count of events by day for the sample dataset.
```
StormEvents
| summarize event_count=count() by bin(StartTime, 1d)
```
The graph doesn't show any results because it's scoped (by default) to data from the last six hours. On the top menu, select Last 6 hours.
Specify a custom range that covers 2007, the year included in the StormEvents sample dataset. Then select Apply.

Now the graph shows the data from 2007, bucketed by day.
On the top menu, select the save icon: .

To switch to the query builder mode, select Switch to builder. Grafana will convert the query to the available logic in the query builder. The query builder logic is limited, so you might lose manual changes that you made to the query.

Screenshot of the query window, with the button for switching to the builder highlighted.

Create alerts

In Home Dashboard, select Alerting > Notification channels to create a new notification channel.
Enter a name and type under New Notification Channel, and then select Save.
On the dashboard, select Edit from the dropdown list.
Select the alert bell icon to open the Alert pane. Select Create Alert, and then complete the properties for the alert.
Select the Save dashboard icon to save your changes.

Write queries for Azure Data Explorer