Creating complex queries in the new query language for Log Analytics

This series will introduce some tricks and tips for writing more complex queries in Log Analytics and integrating these queries into Microsoft Flow. In this blog post I will showcase an example of how to build a query composed of multiple sub-queries.

The example used for this blog post series will cover what on the top level appears to be a simple question: “Should I open the windows?” At first glance this seems to be a pretty easy item to determine, but once you peel this particular onion it’s more complicated than it looks on the surface. Here’s the first layer to the question:

Is it currently colder outside than it most likely is within the house? (I live in Texas so this example won’t even consider opening up any windows to warm up a house)
Is it raining outside? (Yes, we actually do get rain in Texas!)

But that’s just the first layer. We also need to consider the following:

Is the outside temperature still colder outside than it is within the house? (IE: is it going to stay cold long enough to justify opening the windows)
Is it forecasted to rain outside in the next few hours? (IE: is it not going to rain for long enough to justify opening the windows)
Finally, notification for this should only occur during hours that someone is actually awake and willing to take action on it. For this example we’ll assume no notifications before 7 am and no notifications after 10 pm.

In this blog post series we will unpack the above and show you how you can use Log Analytics to break down a complex query of this nature. Please note, to get the weather data required see this blog post and to get the weather forecasting data see this blog post. In this blog item we will create the query by creating a series of sub-queries for Current Weather and Weather Forecast, assemble these together and then apply an approach to cause it to only fire during specific hours.

Current Weather:

We’ll start this with a query which gathers the first two items listed above. This query identifies the current weather state for temperature (Temp_d) and weather description (Description_s).

let place = "Frisco"; // string

OpenWeather_CL

| where TimeGenerated > now(-1day) and tostring(City_s) == place

| project Description_s, Temp_d, TimeGenerated

| sort by TimeGenerated | top 1 by TimeGenerated

With sample results shown below:

Note a couple of tricks in the above query. First, we’re using the “let” statement to define variables that we will use in the query. Second, we’re limiting our results to only data generated in the last day to minimize the amount of data gathered and to maximize the speed of the query. Third, we are limiting our query to the most recent record using “top 1”.

Now we need to know what options are available for the description field so that we can identify those which indicate rain. The following query looks over time to find what weather conditions have occurred.

OpenWeather_CL | project Description_s | distinct Description_s | sort by Description_s asc

With sample results shown below:

From this set of data, it looks like we are looking to avoid any descriptions which contain “rain” or “drizzle”. And we want to exclude any temperatures outside which are greater than a certain minimum temperature and which are smaller than a certain maximum temperature. The query below shows an example:

let place = "Frisco"; // string

let MinTemp = 65;

let MaxTemp = 89;

OpenWeather_CL

| where TimeGenerated > now(-1day) and tostring(City_s) == place

| project Description_s, Temp_d, TimeGenerated

| sort by TimeGenerated | top 1 by TimeGenerated

| where Description_s !contains "Drizzle" and Description_s !contains "Rain" and Temp_d < MaxTemp and Temp_d > MinTemp

Sample results for a successful condition are shown below:

Now we need to take this condition and make a single variable that reflects the current weather in a yes/no type flag to reuse in our final query (in this case a 0 or 1). We can do this easily by adding a count to the end of the query.

let place = "Frisco"; // string

let MinTemp = 65;

let MaxTemp = 76;

let CurrentWeather = OpenWeather_CL

| where TimeGenerated > now(-1day) and tostring(City_s) == place

| project Description_s, Temp_d, TimeGenerated

| sort by TimeGenerated | top 1 by TimeGenerated

| where Description_s !contains "Drizzle" and Description_s !contains "Rain" and Temp_d < MaxTemp and Temp_d > MinTemp | count;

CurrentWeather

The above example uses a let to define CurrentWeather and note the bolded CurrentWeather above which shows us the results of the variable that we defined.

Sample results for a successful condition are shown below:

Weather Forecast:

We can take what we built for the current weather and apply the same concepts for weather forecasting by using OpenWeatherForecast_CL in place of OpenWeather_CL.

let place = "Frisco"; // string

let MinTemp = 65;

let MaxTemp = 76;

let ForecastWeather = OpenWeatherForecast_CL | sort by ForecastTimeDate_t asc

| where ForecastTimeDate_t > now() and ForecastTimeDate_t < now(+4hours) and tostring(City_s) == place

| project ForecastDescription_s, ForecastTemp_d, ForecastTimeDate_t | top 1 by ForecastTimeDate_t

| where ForecastDescription_s !contains "Drizzle" and ForecastDescription_s !contains "Rain" and ForecastTemp_d < MaxTemp and ForecastTemp_d > MinTemp | count;

ForecastWeather

An example success result looks just like our success condition for CurrentWeather.

Bringing the queries together:

To bring these queries together we remove the spaces between the queries and then we remove the duplicate copies of the variables that we defined for place, MinTemp, and MaxTemp. Next we create a join on the results where we use extend to create a WeatherFlag. If the WeatherFlag = 2 it indicates a success on both conditions (since there are two conditions for success/fail – each one having a successful value of 1 and a failure value of 0).

let place = "Frisco"; // string

let MinTemp = 65;

let MaxTemp = 76;

let CurrentWeather = OpenWeather_CL

| where TimeGenerated > now(-1day) and tostring(City_s) == place

| project Description_s, Temp_d, TimeGenerated

| sort by TimeGenerated | top 1 by TimeGenerated

| where Description_s !contains "Drizzle" and Description_s !contains "Rain" and Temp_d < MaxTemp and Temp_d > MinTemp | count;

//CurrentWeather

let ForecastWeather = OpenWeatherForecast_CL | sort by ForecastTimeDate_t asc

| where ForecastTimeDate_t > now() and ForecastTimeDate_t < now(+4hours) and tostring(City_s) == place

| project ForecastDescription_s, ForecastTemp_d, ForecastTimeDate_t | top 1 by ForecastTimeDate_t

| where ForecastDescription_s !contains "Drizzle" and ForecastDescription_s !contains "Rain" and ForecastTemp_d < MaxTemp and ForecastTemp_d > MinTemp | count;

//ForecastWeather

let WeatherConditions = CurrentWeather | join (ForecastWeather) on Count

| extend WeatherFlag = Count + Count1

| project WeatherFlag;

WeatherConditions

A sample successful output (IE: Open the Windows) is shown below:

A sample failure output (IE: Don’t open the Windows) is shown below:

Scheduling within the query:

Now we want to schedule this query to run every hour during certain hours of the week. Alerting was recently moved to Azure but we can’t use alerting in Azure to accomplish this yet due to the options which are currently available (see the screenshot below). Currently we can only specify frequency, we can’t also specify what hours not to run within that frequency.

In Microsoft Flow we can currently set a recurrence for a query in Log Analytics but here we can’t specify it to only run during certain hours of the day either.

To work around this, we’ll add the scheduling capabilities directly into the query we have created in this blog post. To add this we’ll go back to a previous blog I wrote on how to restrict on time. To do this we add variables for startDateTime, StartNotification and StopNotification. These variables are then used at the end of the query to restrict the final results to only provide data during those timeframes. The final query is shown below.

let startDatetime = startofday(now());

// StartNotification is 8:00 am (8) plus 6 hours for the UTC Offset (14)

let StartNotification = startDatetime + 14hours;

// StopNotification is 5:00 pm (17) plus 6 hours for the UTC Offset (23)

let StopNotification = startDatetime + 23hours;

let place = "Frisco"; // string

let MinTemp = 65;

let MaxTemp = 76;

let CurrentWeather = OpenWeather_CL

| where TimeGenerated > now(-1day) and tostring(City_s) == place

| project Description_s, Temp_d, TimeGenerated

| sort
by TimeGenerated | top
1
by TimeGenerated

| where Description_s !contains
"Drizzle"
and Description_s !contains
"Rain"
and Temp_d < MaxTemp and Temp_d > MinTemp | count;

//CurrentWeather

let ForecastWeather = OpenWeatherForecast_CL | sort
by ForecastTimeDate_t asc

| where ForecastTimeDate_t > now() and ForecastTimeDate_t < now(+4hours) and tostring(City_s) == place

| project ForecastDescription_s, ForecastTemp_d, ForecastTimeDate_t | top
1
by ForecastTimeDate_t

| where ForecastDescription_s !contains
"Drizzle"
and ForecastDescription_s !contains
"Rain"
and ForecastTemp_d < MaxTemp and ForecastTemp_d > MinTemp | count;

//ForecastWeather

let WeatherConditions = CurrentWeather | join (ForecastWeather) on Count

| extend WeatherFlag = Count + Count1

| extend CurrentTime = now()

| project WeatherFlag, CurrentTime;

WeatherConditions

| where CurrentTime > StartNotification and CurrentTime < StopNotification

Summary: This blog post showed some tricks to use when building more complex queries in Log Analytics. Some of my recommendations from these include:

Define your code in advance as pseudocode.
Use “let” statements to define your variables.
Limit the amount of data generated first in your queries to speed the query by restricting the timeframe.
Write your more complex query as sub-queries and then join the sub-queries together.
When you finish a sub-query create a variable which defines it and then use the name of the variable to activate it. Once it’s working comment out the run of the variable within the query (see //CurrentWeather and //ForecastWether) in the sample above). Most likely I will put together a more detailed blog post to further explain this last item.

In the next blog post we’ll use Microsoft Flow to run this query on a regular basis, how to send emails based on the results and how to use approvals to change dat