Azure Data Explorer as a data source for Azure Managed Grafana dashboards

Azure Data Explorer, Grafana 5 Minutes

Internet of Things solutions must provide insights, based on data provided by devices and other resources.

Adding dashboards to an IoT solution is the most common way to add that value.

There are a number of popular platforms for custom dashboarding, including Grafana.

Their solution is offered both in a commercial way and as an open-source version.

I have already written a couple of blog posts about working with Grafana, both in the cloud and on the edge.

The cloud version was initially based on VM technology in Azure.

Recently, Azure started offering a managed PaaS version. It takes away the ‘pain’ of managing the environment.

Due to security concerns, Azure Managed Grafana doesn’t currently support custom plugins. According to the website, you’ll have the option to add Grafana Enterprise plugins at a later date.

Personally, Grafana is a nice addition for me to display data stored in Azure Data Explorer next to the dashboarding solution offered by Azure Data Explorer itself (currently in preview).

So, there is now an integration possible between these two worlds.

Let’s check out how Grafana dashboards can be put on top of Azure Data Explorer.

It all starts with having an Azure Data Explorer cluster.

I used that cluster created for the Flight into Azure IoT presentation (a free three-hour session about eg. devices, Stream Analytics, Azure Data Explorer, Azure Digital Twins, and Power BI).

We are going to create a Managed Grafana solution, connect to ADX, and show some telemetry.

Managed Grafana

Creating a managed Grafana solution is quite straightforward:

Basically, all you need to do is to provide a unique name and a region:

At this moment, only a limited number of regions is available:

For my solution, the West Europe region is good enough.

Notice, no extra security credentials are needed. This is all managed by Azure. As the creator of this resource, I’m automatically seen as the administrator.

You can give others administrator access too if needed:

Review and create the environment.

Compared with the traditional Grafana environment based on a VM, this new version is so much simpler in usage:

The portal basically only provides access to this public endpoint:

If you follow that link, you will arrive at the well-known Grafana landing page:

Notice you do not need to log in, at least, not with a separate (Grafana) account. Your Azure AAD account is what secures the portal. This also means workers eg. leaving the company, are automatically denied access.

This Grafana environment has no custom dashboards set up yet.

We are going to build one but we need to add a data source first!

So, let’s add your Azure Data Explorer database as a data source.

Azure Data Explorer database as a data source

If you follow that ‘add your first data source’ link on the landing page, a large number of possible types of data sources are suggested.

Filter this list on ‘data explorer’ and see ADX databases are supported:

If you select this option, you will be told you need to connect Managed Grafana to Azure Data Explorer using an AAD application:

Keep this page open while we arrange the AAD application and the connection.

Update 2023-8: An alternative to the AAD application creation steps seen below, you could also use the Grafana Managed Identity. Just use that one by searching for the Grafana resource. Easy. Simple.

There is a link available with more details on how to connect.

I went for the Azure CLI solution so I opened the Azure cloud shell, simple to access in the Azure portal:

First, I create that AAD application.

I need to copy the URL of my Grafana endpoint and add it to:

az ad sp create-for-rbac -n "http://url.to.your.grafana:3000"

This returns a number of important fields:

{
  "appId": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
  "displayName": "http://url.to.your.grafana:3000",
  "password": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX",
  "tenant": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"
}

Remember these fields/secrets. We need them below.

Note: Do not share this response test openly. I suggest storing the values in eg. a KeyVault. Then you can even automate usage.

Assign the Reader role to the Service Principal:

az role assignment create --assignee <your appId> --role Reader

This returns information about the Role assignment if succeeded. You can ignore the values in the response.

Remove the Contributor role (if applicable, check that response text):

az role assignment delete --assignee <your appId> --role Contributor

Now, switch over to the Query editor of your Azure Data explorer database:

We need to give the AAD application access to the database.

Execute this line once you have filled in the three separate parameters (database name, app id, and tenant id):

.add database <ADX database name> viewers ('aadapp=<your app id>;<your tenant id>')

Note: the documentation mentions ‘your client id’ but that is just the same as appId in this case.

When successfully executed, the app is now also added to the list of ADX users:

You can check this on the Permissions page:

When this is all done, complete the Grafana data source dialog:

Fill in the ADX Cluster URI (something like https://youradx.region.kusto.windows.net), the tenant ID, the App ID, and the App password.

When you entered that, try to ‘reload schema’ and probably you are able to select the default database already.

If so, test and save:

You need to see that Success popup.

Congratulation, You now have a data source, representing your ADX database:

Now, we can create a dashboard.

Creating a dashboard

Because we have that data source now, we can use it to build a dashboard.

Here, I created a line chart based on the EngineData table:

The integration is quite sophisticated. We get help and suggestions while writing this KQL query:

Perhaps you noticed this extra parameter:

| where deviceId == '${_device}'

This ‘_device’ is a dashboard variable I added to the dashboard upfront:

The values represented by the variable are not hard coded, the variable collects the values from the same ADX database using another KQL query:

Once saved, you can use it to filter multiple panes on the same dashboard:

So, I added a second pane showing a single value:

Again, the same filter is used.

After the panes and settings are saved, we can now enjoy this simple but effective dashboard:

I even updated the refresh rate (set to five seconds) so now I have a near-real-time look at the incoming data.

An alternative notation is using the ‘__contains’ function:

| where $__contains(deviceId, $_device)

Conclusion

This is a nice introduction to Azure Managed Grafana.

Note: Azure Managed Grafana pricing is a combination of both the number of instances and the number of active users (An active user is defined as a unique user who has accessed a Grafana instance in a calendar month.).

We have seen how Azure Data Explorer supports dashboards being created in Managed Grafana.

It actually supports many more dashboards:

See the Azure Data Explorer documentation for more details about these integration options.

This post is the fifth part of this series:

Gepubliceerd door Sander van de Velde | Microsoft MVP Azure IoT | IoT Solution Architect | Speaker | Advisor

I started as an IT consultant in 1993. I like to get my hands dirty with software innovations and I try to implement these in my daily work. Currently, I am involved in the IoT Platform part of Azure (eg. IoT Hubs, IoT Edge, StreamAnalytics, Azure Functions, etc.) and Azure in general. I've been presented with the 2017, 2018, 2019, 2020, 2021, 2022, 2023 Microsoft Most Valuable Professional (MVP) Award and I'm a member of the Microsoft Azure Advisory Board. For me, it is important to share knowledge. And I am committed to doing so by writing blogs, articles for magazines, and giving lots of presentations. When offline, I like cutting down trees using Gränsfors Bruks axes, sailing, motorcycling or geocaching with my wife and my sons.

Gepubliceerd