Azure Data Explorer

Azure Data Explorer is a high-performance time-series database, query engine, and dashboarding tool. The Home Assistant Azure Data Explorer integration集成将 Home Assistant 与您的设备、服务等连接和集成。 [Learn more] allows you to hook into the Home Assistant event bus and forward events to Azure Data Explorer for analytics and dashboarding. From here, data can be viewed in building dashboards, PowerBi, and Grafana, among others.

Prerequisites

Before you can add Azure Data Explorer to Home Assistant, you need to set up an Azure account, create a Service Principal, create a cluster, and add a database.

Creating a free Azure account

Create a free Azure account. You will be asked for credit card information, but all resources created here are free.

Creating a Service Principal (App registration)

For Home Assistant to authenticate with Azure Data Explorer, it needs a Service Principal.

1. To create a Service Principal, follow the guide on Creating Microsoft Entra application registration, steps 1-7.
2. Copy values for later use:
   • Application (client) ID: From App registration overview
   • Directory (tenant) ID: From App registration overview
   • Secret value: From when the secret was created in step 1.7

Creating a Free Azure Data Explorer cluster and database

There are two ways of creating an Azure Data Explorer Cluster: Pay as you go (PAYG) or Free. To create a paid cluster, follow the instructions from the Microsoft Quickstart Guide. However, Microsoft has released a free offer, and this guide describes how to set up a free Azure Data Explorer Cluster and database:

There are a few differences between the PAYG and Free versions:

1. Go to aka.ms/kustofree.
2. Go to My Cluster.
3. Select Create Cluster.
4. Name the cluster and database.
5. Copy the database name for later use.
6. Check the Terms and Conditions (after reading them) and select Create Cluster.
   • Within a minute, you will have an Azure Data Explorer cluster ready.
7. After the database has been created, copy the Data ingestion URI from the top of the page.

Creating an Azure data table

1. Go to aka.ms/kustofree.
2. Go to Query.
3. Write and perform the following statements one by one, replacing the placeholder content between the <> with the copied values (including the brackets).

// Give the Service Pricipal write access to the database
.add database ['<databasename>'] ingestors ('aadapp=<ApplicationID>;<DirectoryID>');

// Give the Service Pricipal read access to database (used for connectivity checks) 
.add database ['<databasename>'] viewers ('aadapp=<ApplicationID>;<DirectoryID>');

// Create a table for the data to be ingested into (replace the name and copy inserted *name* for later use)
.create table ['<name_to_be_replaced>'] (entity_id: string, state: string, attributes: dynamic, last_changed: datetime, last_updated: datetime, context: dynamic)

// Creat a mapping from the incoming JSON to the table and columns just created (replace the name with the table name from the previous step)
.create table ['<name_to_be_replaced>'] ingestion json mapping 'ha_json_mapping' '[{"column":"entity_id","path":"$.entity_id"},{"column":"state","path":"$.state"},{"column":"attributes","path":"$.attributes"},{"column":"last_changed","path":"$.last_canged"},{"column":"last_updated","path":"$.last_updated"},{"column":"context","path":"$.context"}]'

This is an example with a free cluster for reference:

.add database ['HomeAssistant'] ingestors ('aadapp=b5253d02-c8f4-1234-a0f0-818491ba2a1f;72f123bf-86f1-41af-91ab-2d7cd011db93');

.add database ['HomeAssistant'] viewers ('aadapp=b5253d02-c8f4-1234-a0f0-818491ba2a1f;72f123bf-86f1-41af-91ab-2d7cd011db93');

.create table ['raw'] (entity_id: string, state: string, attributes: dynamic, last_changed: datetime, last_updated: datetime, context: dynamic)

.create table ['raw'] ingestion json mapping 'ha_json_mapping' '[{"column":"entity_id","path":"$.entity_id"},{"column":"state","path":"$.state"},{"column":"attributes","path":"$.attributes"},{"column":"last_changed","path":"$.last_canged"},{"column":"last_updated","path":"$.last_updated"},{"column":"context","path":"$.context"}]'

配置

要将 Azure Data Explorer integration 添加到您的 Home Assistant 实例中，请使用此 My 按钮：

If using a free cluster, check the Use Queueing client in the form.

After the flow has been completed, Home Assistant sends data to Azure Data Explorer.

By default, Home Assistant buffers for 5 seconds before sending, and the Batching Policy in Azure Data Explorer will further batch up for default.

Filters

Optionally, add the following lines to your configuration.yamlconfiguration.yaml 文件是 Home Assistant 的主要配置文件。它列出了要加载的集成及其特定配置。在某些情况下，需要直接在 configuration.yaml 文件中手动编辑配置。大多数集成可以在 UI 中配置。 [Learn more] file for filtering data ingested into Azure Data Explorer:

# Example configuration.yaml entry
azure_data_explorer:
  filter:
    include_domains:
    - homeassistant
    - light
    - media_player

Configuring a filter

By default, no entity will be excluded. To limit which entities are being exposed to Azure Data Explorer, you can use the filter parameter.

# Example filter to include specified domains and exclude specified entities
azure_data_explorer:
  filter:
    include_domains:
      - alarm_control_panel
      - light
    include_entity_globs:
      - binary_sensor.*_occupancy
    exclude_entities:
      - light.kitchen_light

Filters are applied as follows:

1. No includes or excludes - pass all entities.
2. Includes, no excludes - only include specified entities.
3. Excludes, no includes - only exclude specified entities.
4. Both includes and excludes:
   • Include domain and/or glob patterns specified:
     • If the domain is included, and the entity is not excluded or matches the exclude glob pattern, pass.
     • If the entity matches include glob pattern, and the entity does not match any exclude criteria (domain, glob pattern, or listed), pass.
     • If the domain is not included, the glob pattern does not match, and the entity not included, fail.
   • Exclude domain and/or glob patterns specified and include does not list domains or glob patterns
     • If the domain is excluded and the entity is not included, fail.
     • If the entity matches an exclude glob pattern and the entity is not included, fail.
     • If the entity does not match any exclude criteria (domain, glob pattern, or listed), pass.
   • Neither include or exclude specifies domains or glob patterns
     • If the entity is included, pass (as #2 above).
     • If the entity matches both an include and an exclude pattern, the entity exclude is ignored.

Using Azure Data Explorer

Once the setup is complete, data is being sent to Azure Data Explorer, and you can start exploring your data. Here are some resources to help you learn how to use Azure Data Explorer:

• MS Learn: https://aka.ms/learn.kql, https://aka.ms/learn.adx
• YouTube: Official Microsoft Azure Data Explorer YouTube channal
• Blog: Official Microsoft Data Explorer blog