This post is related to the first Home Assistant post published earlier. I recommend you start reading the first part before this. This blog post covers, how to use Azure Data Explorer to visualize the event data provided by the Home Assistant.
Querying the Azure Data Explorer data
A query language that you need to use is called KQL (Kusto Query Language). Practically, a KQL query is a read-only request to process data and return results. KQL is widely used in various Azure products like Azure Data Explorer, Log Analytics, and Microsoft Sentinel.
I don't go through all details and nuances of KQL in this blog post. I'll recommend you to read more about KQL from the below links.
- Kusto Query Language (KQL) overview
- Learn common operators
- Use aggregation functions
- Join data from multiple tables
- The Basics of Kusto Query Language
- MustLearnKQL blog series
Query limits
This Microsoft documentation explains query limits that you should know.
Kusto is an ad-hoc query engine that hosts large data sets and attempts to satisfy queries by holding all relevant data in-memory. There's an inherent risk that queries will monopolize the service resources without bounds. Kusto provides several built-in protections in the form of default query limits.
Documentation says that KQL limits the number of records returned to the client to 500000, and the overall data size for those records to 64 MB. I'm using the Free cluster and actually, I started to receive warnings when the result set exceeded 50000 records.
Create Data Explorer Dashboard
A dashboard is a container where you can add Tiles. Tile can be a KQL query that presents data in a table or chart diagram (also maps are supported) or markdown supported text area. If you have used Dashboard in Azure Management Portal you know the drill. In this case, Azure Data Explorer Dashboards will be used to visualize Home Assistant data, especially RuuviTag sensor data (temperature, air humidity, and air pressure).
Azure Data Explorer Portal
First login to Azure Data Explorer portal (https://dataexplorer.azure.com/). Before continuing verify that the right Azure AD tenant is selected. You can check that by clicking your profile icon on the right top corner and selecting Switch Directory. For some reason, I typically need to do this tenant switching at least twice until it's properly selected.
Creating the actual Dashboard
Select Dashboards from the left navigation menu and click New Dashboard.
Give a name for the Dashboard.
Now Dashboard is ready. Let's add some content.
Dashboard Pages
Dashboards support Pages so you can have a single Dashboard that contains multiple Pages. In this sample, I'll create a separate page for each RuuviTag sensor.
Dashboard Tiles
To add the first Tile to your Dashboard, click Add tile
Next, you probably see a message that there are no data sources connected. Click Add new data source. After connecting to your Azure Data Explorer cluster, you can select the database. If you don't remember the Cluster URI, you can check it from the My Cluster view.
Queries
Now we're ready to create the first KQL queries. I'll show a few examples of how I have queried the Home Assistant event data.
Retrieve humidity and temperature data
This query fetches RuuviTag device's humidity and temperature sensor data and renders data to a line chart. You can either configure visualization in the KQL query or configure visualization via Azure Data Explorer web user interface. The render operator in KQL supports multiple diagram types from line charts to treemaps etc.
This query uses take operator to return the latest top 50000 rows.
Events
| where (entity_id has "sensor.ruuvitag_64f5_humidity" or
entity_id has "sensor.ruuvitag_64f5_temperature")
| order by last_changed desc
| take 50000
| render linechart with (xcolumn=last_updated, ycolumns=state, series=entity_id)The final result looks like this:
I also added an air pressure diagram Tile and another Tile which shows the total event count of this specific RuuviTag to the Dashboard. The final Dashboard looks like this.
Next, I'll create new Dashboard pages for each RuuviTag sensor.
What else to visualize?
Besides the temperature Dashboard, I'll create a new Dashboard that shows some general statistics from Home Assistant event data.
Show Home Assistant event count per day
Above query uses summarize operator which aggregates the total count of events per day. This is useful to follow the total event amounts per day.
Events
| summarize event_count=count() by format_datetime(last_updated, 'yyyy-MM-dd')
| order by event_count desc
| take 50000
| render linechart with (xcolumn=last_updated, ycolumns=event_count)
Group events per device type
This query uses union operator to merge two result sets which groups temperature and motion device event counts per day.
let temperature = view () { Events
| where entity_id contains "sensor.ruuvitag"
| summarize Count=count() by 'temperature', last_updated, format_datetime(last_updated, 'yyyy-MM-dd')
};
let motion = view () { Events
| where entity_id contains "motion"
| summarize Count=count() by 'motion', last_updated, format_datetime(last_updated, 'yyyy-MM-dd')
};
union withsource=tablename temperature, motion
| summarize count() by format_datetime(last_updated, 'yyyy-MM-dd'), tablename
| order by last_updated desc
| take 50000
| render linechart with (xcolumn=last_updated, ycolumns=count_, series=tablename)
What next?
Home Assistant data ingestion to Azure is super easy and effortless with Event Hub and Azure Data Explorer. I definitely recommend using this kind of service combination if you want to analyze and visualize data easily in Azure. A free cluster of Azure Data Explorer is also spot on because it allows to ingest a lot of data for free.
Later I'll write more about, how to use Azure Data Explorer SDK to ingest data and how to integrate Anomaly detection to the flow.
Thank you for reading!
Comments