Tips when debugging sub-queries in the New Query Language For Log Analytics

In this blog post we will show how you can effectively debug sub-queries used in assembling more complex queries in Log Analytics. For this blog post I’ll work on a reverse of the original blog post concept. In the first blog post we wrote a query to determine if it makes sense to open the windows. In this query we’ll identify if it makes sense to close the windows after they were opened. We’ll showcase a couple of tricks that I use regularly when building complex queries which have required debugging. These include:

  • How to use variables to show results
  • How to use variables to test success and failure conditions
  • How to use variables to force specific conditions to occur

We will also show the final query which completes the process of determining whether it does or does not make sense to close the windows.

Using Variables to show results

The following sub-query looks to validate if a record was written on the same day indicating that the windows were open (WindowsState_CL – which we wrote in the Microsoft Flow after indicating that we did actually open the windows).

let DayStart = startofday(now());

let WindowState = WindowState_CL | where TimeGenerated > DayStart

| count;

The query above will show the results of WindowState because we added the line directly after the ; indicating the indication of the let statement (shown in bold below).

let DayStart = startofday(now());

let WindowState = WindowState_CL | where TimeGenerated > DayStart

| count;

WindowState

When we run this sub-query we get the WindowsState which is either 1 (this record was found) or 0 (this record was not found). An example output is shown below.

When a sub-query is completed comment out the final variable name so that it will not stop by displaying the results of that sub-query (example change below in bold has // in front of WindowState).

let DayStart = startofday(now());

let WindowState = WindowState_CL | where TimeGenerated > DayStart

| count;

//WindowState

Using Variables to test success and failure conditions

The following query is looking at current weather conditions to determine if it is recommended to close the windows at a house. If we look at the following query we are looking for a weather condition where the description includes Drizzel or Rain or when the current temperature is greater than the MaxTemp (76 in this example) or if the current temperature is now less than the MinTemp (65 in this example). 1 indicates a success case (IE: close the windows) and 0 indicates a failure case (IE: do not close the windows).

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”
or Description_s contains
“Rain”) or Temp_d > MaxTemp or Temp_d < MinTemp

| count;

CurrentWeather

A sample result of this is shown below:

To assess if this is working or not we need to move back a bit and stop at the record that we are looking at. To do this we move the ; back further into the code where “top 1” occurs and we add “CurrentWeather” to see the results of the variable at that stage. A sample of this code with the changes in bold is 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;

CurrentWeather

Quick Trick: If you add this into the middle of a query you can reverse this with a few quick control-Z’s.

With a sample result of this shown below:

If we look at our rules, it should not match on “clear sky” but it should match because the current temperature (Temp_d) is greater than the MaxTemp (76). So we would expect the answer to be 1 which is what we are seeing.

Because we defined this was variables we can change the conditions to also test the failure condition. The code below changes the MaxTemp to 100 and re-runs the query producing what should be a failure (0) condition (changes to code in bold 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 = 100;

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”
or Description_s contains
“Rain”) or Temp_d > MaxTemp or Temp_d < MinTemp

| count;

CurrentWeather

With a sample result of this shown below of 0 which is what we expected.

By changing the underlying values we can run test conditions so that we test both conditions to make sure that they are working which is important to make sure that our query is fully functional.

Using variables to force specific conditions to occur

I also use variables which were previously defined to allow a quick way to bypass other variables. As an example, if I knew that the CurrentWeather value was a 1 and I wanted the WindowState value to be a 1 I can set the WindowState to the same as the CurrentWeather (bolded in the example 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 = 100;

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”
or Description_s contains
“Rain”) or Temp_d > MaxTemp or Temp_d < MinTemp

| count;

//CurrentWeather

let WindowState = CurrentWeather;

“Final” query

Below is the final query which was created to notify when it is time to close the windows. The next step on this will be to generate a Microsoft Flow which runs this on an hourly basis like we did for the opening of windows process.

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”
or Description_s contains
“Rain”) or Temp_d > MaxTemp or Temp_d < MinTemp

| count;

//CurrentWeather

let DayStart = startofday(now());

let WindowState = WindowState_CL | where TimeGenerated > DayStart

| count;

//WindowState

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”
or ForecastDescription_s contains
“Rain”) or ForecastTemp_d > MaxTemp or ForecastTemp_d < MinTemp

| count;

//ForecastWeather

let WeatherConditions = CurrentWeather | join (WindowState) on Count | join (ForecastWeather) on Count

| extend WeatherFlag = Count + Count1 + Count2

| extend CurrentTime = now()

| project WeatherFlag, CurrentTime;

WeatherConditions

| where CurrentTime > StartNotification and CurrentTime < StopNotification

| project WeatherFlag

I leave the comments (//) in place until the query is completely finished.

Summary: By building more complex queries out of simpler sub-queries we can develop extremely powerful queries. If you use the tricks of developing sub-queries with variables you can quickly show results from the various sub-queries to help with debugging. If you use variables which determine conditions you can use the values of these variables to also test your success and failure conditions. Finally, if you have an existing variable you can make a copy of the first variable to force the specific condition that you would like to occur.

Leave a Reply

x

We use cookies to ensure the best possible experience on our website. Detailed information on the use of cookies on this site is provided in our Privacy and Cookie Policy. Further instruction on how to disable our cookies can be found there.