Time is of the essence
By Gianni Castaldi
Welcome to the fourth blog post in the series becoming a Kusto Knight. While the previous blog post was about data types in Kusto, this blog post will be about dates and times.
When looking at data we must know when events happened, to better understand them or to correlate them to other events.
In the previous blogpost I described the date-time value in the following way:
Datetime is a value between 1-01-1T00:00 and 9999-12-31T23:59:59 and Microsoft strongly recommends this format (ISO 8601). When we subtract 2 dates the data type gets changed from datetime to timespan. Besides ISO8601 we can also use RFC 822 and RFC850.
Todatetime is the function we can use to format string data types to the datetime data types.
Format_datetime is the function we can use to convert the datetime data types back to string data types.
Now we know how it works, we can use it.
Ago is the first way to view all events before “<” or after “>” a given date.
In the example we will get the SigninLogs from the last 90 days:
Between is an operator to get results between 2 values. Do not forget if you want to specify dates manually that you must set them with the datetime data type.
Startof and Endof are prefixes of the functions we can use when we are looking for or around a specific date. We can use it to get information from a day, week month, or year, with the functions:
- startofday – endofday
- startofweek – endofweek
- startofmonth – endofmonth
- startofyear – endofyear
Bin is a function which we use to place your results in smaller buckets.
Arg_min or arg_max are functions we can for example use to get the first or last sign-in attempt.
In the last query, we will use the min and max functions to see how many distinct users with a successful MFA were active on an IP address, and how long the IP address has been used.
This query is great to find trusted IP addresses to add those to the named locations for IPC and conditional access.
For easy access, I have uploaded it to my GitHub repository.
In this blog post, we read about the datetime data type and how to use dates in queries. The next blog post will be about searching through your data.
