Create Azure Data Explorer Dashboards for IoT Data and Alarm Visualisation
We show how to configure simple but effective Azure Data Explorer (ADX) dashboards on streaming data ingested from Azure IoT Hub with the aim of creating visual indication of alarm conditions (e.g. temperature exceeding a threshold). ADX is a natural destination for IoT data as it provides managed ingestion from IoT Hub and advanced analytics/ad-hoc queries on the ingested data. Recently, the ADX team has added a powerful dashboarding feature that allows the mapping of ADX Kusto Query Language (KQL) queries into dashboards within the ADX Web UI. The native dashboards allow the user to seamlessly export queries from the Web UI, optimise dashboard rendering performance and provides auto refresh capability for near real-time visualisation experience.
The starting point is having an IoT Hub with sensors transmitting data to its device to cloud endpoint. Once data starts flowing into the IoT Hub, start configuring the ingestion into an ADX table and building the dashboards using the typical end to end scenario in the following steps:
- Create an ADX cluster by following the steps here.
- Enable streaming ingestion on the ADX cluster as explained here. Streaming ingestion is a powerful feature in situations where low latency between ingestion and query is needed as is typical for IoT scenarios.
- Create an ADX database by following the steps here.
For the remaining steps use the ADX web interface to run the necessary queries and view the dashboards. Add the ADX cluster to the web interface as explained in the previous link. One of the great features of the ADX Web UI is that it can be hosted by other web portals as an HTML iframe.
- Create a table in ADX to put the ingested data in. In this example a simple message json structure is used with many sensors identified by “sensorName”, where, for example, “sensor1” carries Temperature values:
{
"sensorName": "sensor1",
"SensorReading": 21.171
}
The following ADX query creates a table ‘iot_parsed’ with three columns. Note that since the message does not carry a timestamp, the 'iothub-enqueuedtime' property, which is generated by the IoT Hub, is used for that purpose:
.create table iot_parsed (Timestamp: datetime, Sensor: string, Value: real)
Add a json ingestion mapping to instruct the ADX cluster to place the message components in the correct table columns:
.create table ['iot_parsed'] ingestion json mapping
'iot_parsed_mapping' '[{"column":"Timestamp","path":"$.iothub-enqueuedtime","datatype":"datetime"},{"column":"Sensor","path":"$.sensorName","datatype":"string"},{"column":"Value","path":"$.SensorReading","datatype":"real"},]'
This link provides more information on ingestion mapping in ADX. The table is now ready to receive data from the IoT Hub.
- Next use the instructions here to connect the IoT Hub to the ADX cluster and start ingesting the data into the staging table.
- Once the connection is verified, data will start flowing to the table. Use the following query in the ADX Web UI to examine a data sample of 10 rows:
iot _parsed
| take 10
It is important to note at this point that since a very simple telemetry message structure is used in this example, it is straightforward to create a table with a specific schema to ingest the data directly from the IoT Hub. In scenarios where the message structure is more complex, and probably variable over time, it is good practice to first ingest the data into a staging table with one ‘dynamic’ column. The staging table can then be processed into other tables each of which with a specific schema to serve different analytics use-cases. This processing can be carried out as new data arrives in the staging table using update policies.
- Now that the data is streamed regularly into the table, start building dashboards to display the data and to indicate alarm conditions in near real-time. The dashboard experience is available in the web UI and can be accessed in the left menu. Select “Dashboards (Preview)” and then select the option to build a new dashboard. Specify the ADX cluster URI and the database to use as a data source for the dashboards.
- In order to add flexibility and filtering capabilities to the dashboards, parameters can be configured for use in the visuals. After selecting “Parameters” add two single selection parameters one representing the limit to trigger a high temperature alarm and the other as a limit for triggering a low temperature alarm. Note that by default there are two parameters defined for the start and end of the time period that is used in the time charts. Each parameter in this case can have a number of values from which one value can be selected to use in the dashboards at any time (e.g. to change the alarm threshold at any time without having to re-build the dashboards).
- Add a time chart of the temperature values (sensor1 in this case) between time values represented by the built-in parameters _startTime and _endTime. Use the query:
iot_parsed
| where Timestamp between (['_startTime'] .. ['_endTime'])
| where Sensor =="sensor1"
On the “Visual formatting” section use the “Time chart” visual type. For the other fields leave the “Infer” option so that ADX can decide the values needed as shown below. Next click “Run” to see the time plot for sensor1. Apply the changes to use the visual in the dashboard.
(Note: inferring the axes parameters for the plot is straightforward in this case as the table has a simple schema. In cases where there is a more complex table structure it is advisable to specify the values by the developer).
- Extend the time chart of the first visual by adding values from the parameters that represent the alarm thresholds. In addition, add two logic variables (true or false) that would be set by the query if the actual temperature reading is larger than the high threshold or lower than then low threshold. The latter is achieved using the “iff” operator.
iot_parsed
| where Timestamp between (['_startTime'] .. ['_endTime'])
| where Sensor =="sensor1"
| project Timestamp, Value, _temp_High_Alarm, _temp_Low_Alarm,
HighAlarm = iff(Value>_temp_High_Alarm,true,false),
LowAlarm = iff(Value<_temp_Low_Alarm,true,false)
| render timechart with (ysplit = panels)
Examining the resulting charts observe that the “HighAlarm” variable becomes “True” as soon as the temperature goes above the “_temp_High_Alarm” parameter value. By changing the parameter value, alarm thresholds can change immediately and applied in the visual.
- Finally use the “Conditional Formatting” feature for the “Multi Stat” visual to build a coloured alarm indicator where a panel changes colour according the whether the temperature is above the high threshold, below the low threshold or in the normal region between the two thresholds. The query to use here is:
iot_parsed
| where Timestamp between (['_startTime'] .. ['_endTime'])
| where Sensor =="sensor1"
| summarize arg_max(Timestamp,*)
| project Timestamp, TemperatureAlarm = iff(Value>_temp_High_Alarm,"High Temperature",iff(Value<_temp_Low_Alarm,"Low Temperature","Normal Temperature")), Value
The above query uses the “summarize arg_max(Timestamp,*)” operation to get the latest temperature value, then a nested “iff” statement is used to set a variable called “TemperatureAlarm” to one of three values ("High Temperature", "Low Temperature" or "Normal Temperature"). Use the value of “TemperatureAlarm” in the Conditional Formatting rule panel to set the visual colour to red, blue or green as shown below.
- Below is the final dashboard with all the three visuals together. Set this dashboard to update at regular time interval using the auto refresh feature to achieve a fast and near real time data and alarm display.
- Clean up resources: If the Azure resources used in this example are no further needed then remove the ADX cluster as explained here.
ADX is an excellent destination for IoT data and we have demonstrated one reason for that in building these versatile dashboards. But this is not the end of the story and we highly recommend considering how to combine these dashboards with more advanced features of ADX such as Time Series Analysis and Anomaly Detection. Additionally, we can combine the Azure Industrial IoT platform with ADX to build a truly powerful solution for ingestion, analysing and displaying OPC-UA data from the factory floor. ADX also has powerful integration features that allow us, for example, to extend the alarm detection logic by sending emails or triggering other business processes using other Azure capabilities such as Logic Apps.