Scheduling Log Analytics queries to run in Microsoft Flow

In the previous blog post we created a query in Log Analytics which determined whether or not it makes sense to open the windows in a house. In this blog post we will use Microsoft Flow to run the query which we scheduled on an hourly basis. And we can perform tasks based upon the results of the query (including writing back different data into Log Analytics).

To build this we will work through the following steps:

Creating a new Flow
Scheduling the Log Analytics query to run in Microsoft Flow
Running the query
Setting up a condition based on the query
Send an email (diagnostic)
Acknowledging if the task was done
Setting up a condition for if the approval was accepted
Writing status back to Log Analytics
The end result in Flow
A note on debugging an issue writing to Log Analytics

Creating a new Flow:

In Flow it’s easy to create your own Flow’s from an existing template or to import an existing flow. For this example we are going to use the “Create from blank” option.

We skip past the common items and instead use the option to “Search hundreds of connectors and triggers”

Next we search for “Schedule” as this is how we’ll get it to schedule our Flow to run.

Scheduling the Log Analytics query to run in Microsoft Flow:

For this example we schedule the recurrence to run hourly (remember, in the previous blog post we designed the query so it can exclude specific hours of the day). We also set the time zone.

Running the query:

We can add an action below the recurrence to query log analytics. For this one we search on Log Analytics.

We are going to use “Run query and list results”. Specify your subscription, resource group and workspace name and transfer in your Log Analytics query.

Setting up a condition based on the query:

Now we add a condition based on the results of the query. If our WeatherFlag = 2 this indicates that we should open the windows. Otherwise we should not.

Send an email (diagnostic):

While I was developing this I put an action under both the success and failure conditions to send an email so that I knew that the job had run and had made it this far.

Each of these were basically identical except for indicating the subject and body contents. The yes option (IE open the windows) is below.

The no option (IE don’t open the windows) is below:

NOTE: Once debugging of the Flow has been completed, the no option should be removed to avoid spamming the email address on an hourly basis when it is not time to open windows. Additionally the yes email option will also probably be removed as the approval process would also be sending an email.

Acknowledging if the task was done:

Next we use an approval step to indicate whether the option was or was not chosen to open the windows (again, this will hopefully be used when I’m writing the “Close the windows” part of this blog series.

Was the approval accepted?

We add a new condition after the start of the approval which checks for the results of the approval.

So that if the response is positive it will continue on to the final step in this version of the Flow.

Writing status back to Log Analytics:

Finally (at least for this blog post), we write back to Log Analytics indicating that the windows were opened. We use the “Send Data” capability for this.

We need to create a simple JSON file which just indicates that the action was taken to open the window. This is the simplest JSON I could imagine J

{

“OpenWindow”: 1

}

And we assigned it a custom log name (WindowsState) which should appear in Log Analytics as WindowsState_CL. Here’s a sample of the resulting data in Log Analytics.

What is the end result in Flow?

The end result in Flow gives us a scheduled task which runs a query. That branches into two conditions. The success condition sends an email and then an approval. If the approval is positive it then writes data back into Log Analytics indicating that state. The graphic below shows the high level for how the flow works.

A note on debugging an issue writing to Log Analytics:

I did run into one error when trying to write to Log Analytics where it was consistently returning a status code of 500 (shown below).

The resolution was to define the workspace key on the connection for Azure Log Analytics Data Collector. (Thank you to Donnie!)

Summary: It really simple to develop some pretty complex processes using Flow! Using Microsoft Flow we can not only schedule queries against Log Analytics, we can also take actions based on the results of the query, send notifications and approvals, and even write back results into Log Analytics! In the next blog post of this series we’ll go into how to debug complex queries in Log Analytics.