Converting, Joining and Summarizing Data in Azure Log Analytics

This was certainly not a post I was planning on this week. It’s 4th of July here in the US and that means most places are closed for the holiday. Which also means our kids are home for the day. Normally this would be great, we would be outside having fun, but its raining.  If you’ve read my blog previously, then you know that I’m currently putting my weather data up in Azure Log Analytics. If you didn’t know that, here’s the post.

 

Anyway, everyone is asleep and I thought I would summarize Azure Log Analytics data.

Converting Strings

For some reason Log Analytics ingests my precipitation fields as strings instead of Ints or Doubles. That’s no problem though, we can use todouble() to convert the data. From here you can see I’m actually declaring a new field called Rain, that equals the precip_today_in_s field.

After that we can then summarize the average because our field is now a double instead of a string. Now you can watch in real time as the rain gauge rises and rises.

summarize data azure log analytics

But that’s not all we can do. How bout we find the hourly average rate of rainfall?

Using the same method for the to double, I’ll find my rain start time and end times with the following queries.

</pre>
let RainEventBegin = Current_Conditions_CL
| extend RainStart=todouble(precip_today_in_s)
| extend HrlyRain=todouble(precip_1hr_in_s)
| where RainStart > 0
and HrlyRain > 0
| top 1 by TimeGenerated asc
| extend Start=TimeGenerated
| project RainStart, Start, SourceSystem;

let RainEventEnd = Current_Conditions_CL
| extend RainCurrent=todouble(precip_today_in_s)
| extend HrlyRain=todouble(precip_1hr_in_s)
| where RainCurrent > 0
and HrlyRain > 0
| top 1 by TimeGenerated desc
| extend End=TimeGenerated
| project RainCurrent, End, SourceSystem;
<pre>

These two queries get the beginning of the rain event and the end or really current rain by TimeGenerated, we need those fields to find out the timespan in the next query.

RainEventBegin | join (RainEventEnd) on SourceSystem
| extend Hours = datetime_diff('hour', End, Start) 
| extend HourlyAvg = RainCurrent / Hours
| project HourlyAvg, Hours, TotalRain_Inches=RainCurrent 

While this query is short there is kind of a lot going on. First, we’re joining RainEventEnd with RainEventBegin on SourceSystem. We’re using SourceSystem because it was one of the only unique keys thats the same value between the two data sets.
Next we declare Hours equals the difference in time, using the datetime_diff() function. You could actually just subtract Start from End and get an accurate timespan, however that resultant value would not be in a format that you can do math with. Next we declare HourlyAvg is the result of dividing RainCurrent by the amount of Hours. And finally we project our results, note I rename RainCurrent to TotalRain_Inches here, which you can do with the project operator.

We get the following result

summarize data azure log analytics

 

Not bad. Not great, ideally I would like to better detect rain events, for instance if it stops raining for an hour or more I would consider that too different events. Right now the query just gets all the rain data from the last 24 hours. There are a few ways to summarize Azure Log Analytics data, beyond just the summarize operator.

If you liked this post I have another post on converting strings with substring and trim.

3 thoughts on “Converting, Joining and Summarizing Data in Azure Log Analytics”

Comments are closed.