Azure Data Explorer connector to Power Automate (Preview)

The Azure Data Explorer Power Automate (previously Microsoft Flow) connector allows Azure Data Explorer to use the flow capabilities of Microsoft Power Automate. You can run Kusto queries and commands automatically, as part of a scheduled or triggered task.

You can:

  • Send daily reports containing tables and charts.
  • Set notifications based on query results.
  • Schedule control commands on clusters.
  • Export and import data between Azure Data Explorer and other databases.

For more information, see Azure Data Explorer Power Automate connector usage examples.

Sign in

  1. When you connect for the first time, you're prompted to sign in.

  2. Select Sign in, and enter your credentials.

Screenshot of Azure Data Explorer Sign in prompt

Authentication

You can authenticate with user credentials, or with an Azure Active Directory (Azure AD) application.

Note

Make sure your application is an Azure AD application, and is authorized to run queries on your cluster.

  1. In Run control command and visualize results, select the three dots at the top right of the flow connector.

    Screenshot of Run control command and visualize results

  2. Select Add new connection > Connect with Service Principal.

    Screenshot of Azure Data Explorer Sign in prompt, with Connect with Service Principal option

  3. Enter the required information:

    • Connection Name: A descriptive and meaningful name for the new connection.
    • Client ID: Your application ID.
    • Client Secret: Your application key.
    • Tenant: The ID of the Azure AD directory in which you created the application.

    Screenshot of Azure Data Explorer application authentication dialog box

When authentication is complete, you'll see that your flow uses the newly added connection.

Screenshot of completed application authentication

From now on, this flow will run by using these application credentials.

Find the Azure Kusto connector

To use the Power Automate connector, you need to first add a trigger. You can define a trigger based on a recurring time period, or as a response to a previous flow action.

  1. Create a new flow, or, from the Microsoft Power Automate home page, select the My flows > + New.

    Screenshot of the Microsoft Power Automate home page, with My flows and New highlighted

  2. Select Scheduled--from blank.

    Screenshot of New dialog box, with Scheduled from blank highlighted

  3. In Build a scheduled flow, enter the required information.

    Screenshot of Build a scheduled flow page, with Flow name options highlighted

  4. Select Create > + New step.

  5. In the search box, enter Kusto, and select Azure Data Explorer.

    Screenshot of Choose an action options, with search box and Azure Data Explorer highlighted

Flow actions

When you open the Azure Data Explorer connector, there are three possible actions you can add to your flow. This section describes the capabilities and parameters for each action.

Screenshot of Azure Data Explorer connector actions

Run control command and visualize results

Use this action to run a control command.

  1. Specify the cluster URL. For example, https://clusterName.eastus.kusto.windows.net.
  2. Enter the name of the database.
  3. 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.
  4. 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.

Screenshot of Run control command and visualize results in recurrence pane

Important

In the Cluster Name field, enter the cluster URL.

Run query and list results

Note

If your query starts with a dot (meaning that it's a control command), use Run control command and visualize results.

This action sends a query to the Kusto 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 lines in the database, and then sends an email only if the number of lines is greater than 0.

Screenshot of Run query and list results

Note

If the column has several lines, the connector will run for each line in the column.

Run query and visualize results

Note

If your query starts with a dot (meaning that it's a control command), use Run control command and visualize results.

Use this action to visualize a Kusto 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 an HTML table.

Screenshot of Run query and visualize results

Important

In the Cluster Name field, enter the cluster URL.

Email Kusto query results

You can include a step in any flow to send reports by email, to any email address.

  1. Select + New Step to add a new step to your flow.
  2. In the search box, enter Office 365 and select Office 365 Outlook.
  3. Select Send an email (V2).
  4. Enter the email address to which you want the email report sent.
  5. Enter the subject of the email.
  6. Select Code view.
  7. Place your cursor in the Body field, and select Add dynamic content.
  8. Select BodyHtml. Screenshot of Send an email dialog box, with Body field and BodyHtml highlighted
  9. Select Show advanced options.
  10. Under Attachments Name -1, select Attachment Name.
  11. Under Attachments Content, select Attachment Content.
  12. If necessary, add more attachments.
  13. If necessary, set the importance level.
  14. Select Save.

Screenshot of Send an email dialog box, with Attachments Name, Attachments Content, and Save highlighted

Check if your flow succeeded

To check if your flow succeeded, see the flow's run history:

  1. Go to the Microsoft Power Automate home page.

  2. From the main menu, select My flows.

    Screenshot of Microsoft Power Automate main menu, with My flows highlighted

  3. On the row of the flow you want to investigate, select the more commands icon, and then select Run history.

    Screenshot of My flows tab, with Run history highlighted

    All flow runs are listed, with information about start time, duration, and status. Screenshot of Run history results page

    For full details about the flow, on My flows, select the flow you want to investigate.

    Screenshot of Run history full results page

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.

Screenshot of flow error page

Timeout exceptions

Your flow can fail and return a "RequestTimeout" exception if it runs for more than seven minutes.

Screenshot of the flow request timeout exception error

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 seven minutes to run.
  • The connector doesn't support the fork and facet operators.
  • Flow works 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.