Inside Log Analytics and therefore many other products that use Kusto, we have many options to play with time. In this post I’ll show real world examples when working with time in Log Analytics. In particular how to use Timespans, Datetime and Time Difference in Kusto. For the purposes of this post all examples will be in Log Analytics.
UTC Time
The first thing we need to know about working with times in Log Analytics, in particular, is that all times are Coordinated Universal Time (UTC) and in ISO 8601 format. If Log Analytics detects our data as a date it will convert it to the ISO 8601 format. The confusing bit here is that when Log Analytics detects a properly formatted time it notes it as UTC, even if you sent it in 8601 format and -5 or whatever for your time zone. So you could have a time noted field with UTC next to it thats five hours off of the TimeGenerated field which is also UTC. My recommendation is to send all times in UTC and figure out time zones in your queries and alerts.
An example. To get our current time we need to use the following query with your UTC offset, in my case its -5 because its currently Daylight Saving Time.
print now(-5h), now()
this gets really confusing since -5 at certain times of the day is actually a different date.
Timespans
Working with any two valid date fields we can instantly create a time span by doing simple addition or subtraction. Using the Sunrise and Sunset times from my LogicApp, we can use the below query to create a new time span field.
DayLight_CL | extend hours = Sunset_t - Sunrise_t
There is also a totimespan() scalar function. No offense to whomever wrote that particular example, but its kind of misleading with they query it provides with the explanation of the return. There are many great examples in the Log Analytics reference, and then there are others that are bare bones. Personally I prefer to see the actual output the query or operator is going to produce. The particular example there is this:
totimespan("0.00:01:00") == time(1min)
this is the explanation of the return “If conversion is successful, result will be a timespan value. If conversion is not successful, result will be null.”
running the query we actually get back true, which is accurate.
But it would have made more sense, at least to me, to just provide the output of print totimespan(“0.00:01:00”), which is simply print_0: 00:01:00. Or provide some further examples of using totimespan.
Datetime_Diff
Another somewhat useful scalar function is datetime_diff(), which I used to determine session times in WVD. The function will get the difference between two times you provide, from nanoseconds to years.
Using the same Daylight log from before, we’ll get the difference between sunrise and sunset in seconds.
DayLight_CL
| extend seconds = datetime_diff('second', Sunset_t, Sunrise_t)
| extend hours = seconds / 3600
| extend doublehour = todouble(seconds) / 3600
| extend minute = seconds / 60 / 60
| extend doubleminute = todouble(seconds) / 60 /60
Note: this is the exact method i used to do session time in WVD, using log on and log off time.
If you want to get exact times, you’ll need to convert the result to a double before converting to the time format you want. The output is an integer and rounds up or down. You can see in the results the fields I did not convert are rounded down.
Between
Between operates kind of like iff but for time and numbers. Meaning it will return Boolean value for the expression you give it. Using the same log as before we’ll check to see if sunrise is between now and 25 minutes ago.
DayLight_CL | extend Sunrise = Sunrise_t between(ago(25m) .. now()) | extend Sunset = Sunset_t between(ago(25m) .. now())
This is useful if you want to alert between a certain amount of time. For that we simply add a where statement at the end.
DayLight_CL
| extend Sunrise = Sunrise_t between(ago(25m) .. now())
| extend Sunset = Sunset_t between(ago(25m) .. now())
| project Sunrise, Sunset
| where (Sunrise == True)
or (Sunset == True)
using where and or we actually have 1 query that will trigger two alerts throughout the day.
Format Datetime
Yes, I already did a whole post on Format Datetime, but not mentioned there is that when you format a datetime in Log Analytics, what you get out is a string. So just be aware that you cannot use format_datetime and then compare that value to a UTC value without then converting it back to a datetime.
Summary
Remember that all times in Log Analytics are in UTC and marked as such. So be aware when you start converting times to local time or when sending times into Log Analytics. Using Timespans, Between, Datetime_diff and other time related functions we can do a lot in Kusto queries.
3 thoughts on “Working with Time in Log Analytics”
Comments are closed.