While I was working on an updated Flow I ran into an interesting requirement for my underlying Log Analytics queries. I needed to output a single row of information which contained items which were not specifically related to each other. In this blog post I’ll show a quick trick that I put together which lets me output a single row of data for unrelated data types. For background, here’s what I was attempting to do and why:

For background, I’m working on a Microsoft Flow which provides “intelligent” notification on whether to open or close the windows at my house. This is both for energy efficiency and it’s darn nice to get some fresh air once in a while! I have used this as my use case in these blogs posts:

• Tips when debugging sub-queries in the New Query Language For Log Analytics
• How to send any data you want to Log Analytics without code!
• Scheduling Log Analytics queries to run in Microsoft Flow
• Creating complex queries in the new query language for Log Analytics

When I started working with Flow I started by using multiple queries to Log Analytics throughout the Flow but that proved to be extremely difficult to maintain. As a result, I changed my approach so that I run a single query at the start of the Flow which provides all relevant pieces of information back for the Flow. That way I don’t need to run multiple queries during the Flow. At some point in time I plan on presenting on my lessons learned developing what is a pretty darn complex Flow but today’s blog post will focus on a single lesson I learned in the process. For my decision to Open or Close the windows, I need to know multiple data points (each for the specific house location of course).

1. What is the current state of the windows at the house? Are they open or closed?
2. What is the current temperature outside? Is it too hot or too cold?
3. What is the forecasted temperature outside? Is it too hot or too cold?
4. What is the current weather description? Does it contain indications of rain?
5. What is the forecasted weather description? Does it contain indications of rain?
6. Optimally, we would also want to consider:
   1. Current and forecasted windspeed
   2. The current temperature inside the house

As we look at these various items, there may or may not be common attributes which exist in each of these types of queries. In this example, the status of the windows at the house currently only has one field – “WindowState_CL”. That field indicates where the windows are closed or open. An example query for this data and result is below.

WindowState_CL
| project-away TenantId, SourceSystem, MG, ManagementGroupName, Computer

I could add a second field which indicates the location but let’s assume for this example that it’s not an option to do so. How can I create an output which contains both the state of the windows and the current and forecasted weather? We can do this by creating our own key which we will then use later in the join. See the code below as an example: (this is a subset of the query that I’m still developing)

Below is what this sample code does: (note, all references to the key are in bold below)

1. Gathers the most recent record from WindowState_CL and adds a custom “MyKey” field using project.
2. Determines if the current weather information indicates that it is too cold to open the windows and adds the custom “MyKey” field using an extend.
3. Joins the two different types of data on the “MyKey” value.
4. Removes any non-required fields and reformats the data for the final query output.

let WindowsCondition = WindowState_CL
| top 1 by TimeGenerated
| project WindowState = OpenWindow_s, MyKey = "Key";
let place = "abc";
let MinTemp = 55;
let TooCold = 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 Temp_d < MinTemp
| project WeatherCondition = Description_s, CurrentTemperature = Temp_d
| count;
let FinalTooCold = TooCold
| extend MyKey = "Key";
WindowsCondition
| join FinalTooCold on MyKey
| project-away MyKey, MyKey1
| extend TooCold = Count
| project-away Count

And here’s a sample of the output:

Summary

To output unrelated data types in a Log Analytics query you can use project command or the extend command to create your own key field. This key field can then be used when joining the different types of data. Finally, the field which was used to join the data can be removed from the output of the final query resulting in a successful join of unrelated data.