Azure Data Explorer connector for Microsoft Power Automate
The Azure Data Explorer connector for Power Automate (previously Microsoft Flow) enables you to orchestrate and schedule flows, send notifications, and alerts, as part of a scheduled or triggered task.
You can:
- Send notifications and alerts based on query results, such as when thresholds exceed certain limits.
- Send regular, such as daily or weekly, reports containing tables and charts.
- Schedule regular jobs using control commands on clusters. For example, copy data from one table to another using the
.set-or-appendcommand. - Export and import data between Azure Data Explorer and other databases.
For more information, see Azure Data Explorer Power Automate connector usage examples.
Create a new flow using the Azure Data Explorer connector
To use the connector, you must first add a trigger. You can define a trigger based on a recurring time period, or as a response to a previous flow action.
Sign in to Power Automate.
Create a new flow, or, from the Power Automate home page, select the My flows > + New flow.
Select Scheduled cloud flow.
In Build a scheduled cloud flow, enter the required information.
Select Create > + New step.
In the search box, enter Kusto or Azure Data Explorer, and select Azure Data Explorer.
Select an action from the list. For an explanation of each action and how to configure them, see Flow actions.
Important
You must have a valid Azure Data Explorer connection for your flow to run. For information about creating a connection, see Create an Azure Data Explorer connection in Power Automate.
Flow actions
When you select the Azure Data Explorer connector, you can choose one of the following actions to add to your flow:
- Run KQL query
- Run KQL query and render a chart
- Run async control command
- Run control command and render a chart
- Run show control command
This section describes the capabilities and parameters for each action and provides an example showing how to add an email action to any flow.
Run KQL query
Note
If your query starts with a dot (meaning that it's a control command), use Run control command and render a chart.
This action sends a query to the specified cluster. The actions that are added afterwards iterate over each line of the results of the query.
The following example triggers a query every minute, and sends an email based on the query results. The query checks the number of records in the table, and then sends an email only if the number of records is greater than 0.
Note
If the column has several records, the connector will run for each record in the column.
Run KQL query and render a chart
Note
If your query starts with a dot (meaning that it's a control command), use Run control command and render a chart.
Use this action to visualize a KQL query result as a table or chart. For example, use this flow to receive daily reports by email.
In this example, the results of the query are returned as a timechart.
Important
In the Cluster Name field, enter the cluster URL.
Run async control command
This action runs control command in async mode and returns its ID, state and status on completion. 'async' keyword is mandatory. It's always recommended to execute control commands in async mode so they keep running in the background. KQL commands can run for maximum of 1 hour. Also, you get an operation ID of the async command after execution that can be used with .show operations OPERATION_ID_RETURNED_BY_CMD command to get the status and details of that async command.
The following example triggers an async command to copy the sample 10 records from 'TransformedSysLogs' table to 'TargetTable'.
Run control command and render a chart
Use this action to run a control command and get the result as a chart of your choice.
- Specify the cluster URL. For example,
https://clusterName.eastus.kusto.windows.net. - Enter the name of the database.
- Specify the control command:
- Select dynamic content from the apps and connectors used in the flow.
- Add an expression to access, convert, and compare values.
- To send the results of this action by email as a table or a chart, specify the chart type. This can be:
- An HTML table.
- A pie chart.
- A time chart.
- A bar chart.
Important
In the Cluster Name field, enter the cluster URL
Run show control command
This action runs the show control command and returns the result that can be used in the following connectors.
Note
This action is specifically for running any of the .show commands, there are different actions given above to run other type of commands in sync or async mode.
The following example executes the .show operation command to find the status of an async command using an operation ID returned by an async command execution.
Email Kusto query results
You can include a step in any flow to send reports by email, to any email address.
- Select + New Step to add a new step to your flow.
- In the search box, enter Office 365 and select Office 365 Outlook.
- Select Send an email (V2).
- Enter the email address to which you want the email report sent.
- Enter the subject of the email.
- Select Code view.
- Place your cursor in the Body field, and select Add dynamic content.
- Select BodyHtml.
- Select Show advanced options.
- Under Attachments Name -1, select Attachment Name.
- Under Attachments Content, select Attachment Content.
- If necessary, add more attachments.
- If necessary, set the importance level.
- Select Save.
Create an Azure Data Explorer connection
To run a flow that contains an Azure Data Explorer connector, you must use a valid Azure Data Explorer connection. You can create and authenticate a new connection from the Power Automate left pane, select Data > Connections or from within the flow, by selecting the Azure Data Explorer connector's menu > Add new connection.
The following steps show how to create a connection from within a flow.
In Run KQL query, select the three dots at the top right of the power automate connector.
Select Add new connection. When you connect for the first time, you're prompted to sign in to authenticate the connection.
Authentication
You can authenticate with user credentials or with an Azure Active Directory (Azure AD) application. To authenticate with credentials, select Sign in, and enter your credentials.
To authenticate with a Service Principal:
Select Connect with Service Principal.
Fill out the form with the following information:
- Connection Name: A descriptive and meaningful name for the new connection. In this example, we've used "MyApplication".
- Client ID: Your application ID.
- Client Secret: Your application key.
- Tenant: The ID of the Azure AD directory in which you created the application.
Note
Make sure your application is an Azure AD application and is authorized to run queries on your cluster.
When authentication is complete, verify that your flow uses the new connection.
Once the connection is set, the flow runs using the application credentials.
Test the flow
To check if your flow works, check the flow's run history:
Go to the Power Automate home page.
From the main menu, select My flows.
On the row of the flow you want to investigate, select the more commands icon, and then select Run history.
All flow runs are listed, with information about start time, duration, and status.
For full details about the flow, on My flows, select the flow you want to investigate.
To see why a run failed, select the run start time. The flow appears, and the step of the flow that failed is indicated by a red exclamation point. Expand the failed step to view its details. The Details pane on the right contains information about the failure so that you can troubleshoot it.
Timeout exceptions
Your flow can fail and return a "RequestTimeout" exception if it runs for more than 90 seconds.
To fix a timeout issue, make your query more efficient so that it runs faster, or separate it into chunks. Each chunk can run on a different part of the query. For more information, see Query best practices.
The same query might run successfully in Azure Data Explorer, where the time isn't limited and can be changed.
Limitations
- Results returned to the client are limited to 500,000 records. The overall memory for those records can't exceed 64 MB and a time of 90 seconds to run.
- The connector doesn't support operators that aren't supported by the
getschemaoperator. For example, the fork, facet, and evaluate operators aren't supported. - Flows work best on Microsoft Edge and Google Chrome.
Next steps
Learn about the Azure Kusto Logic App connector, which is another way to run Kusto queries and commands automatically, as part of a scheduled or triggered task.
Feedback
Submit and view feedback for