Azure KQL – Time After Time

Introduction

Ok, the title is a bit cheesy, but the song just came on and it really does fit. Anyone who has used KQL for any length of time knows about the “datetime”, “now”, and “ago” command as in

|where TimeGenerated > ago(5d)

To see information in the last 5 days. However, KQL provides a lot more capabilities regarding dates and times and we will discuss some of those in this blog post.

dayofX

KQL provides a few commands the actual day of an object. Depending on what you need, there is

• dayofmonth(date) – returns the day number of a given month
• dayofweek(date) – a timespan value representing the number of days since Sunday.
• dayofyear(date) – returns the integer number representing the number of days into the year

If we were to run the KQL command below where the “print” command just prints out the results

let today=now();
print today, dayofmonth(today), dayofweek(today), dayofyear(today)

we will see the following results. Of course, the values will change depending on what date you run this.

First, we see (under the column header “print_0”) the actual date we are using in the rest of the command. Next is the day of the month, 14 in this case.

After that is the day of the week. I have no idea why it was decided to use a timespan value since it is just a simple number, but to convert to an integer divide by “1D” as in

| extend dayofWeek = dayofweek(TimeGenerated)/1d

The date being used represents a Saturday which is 6 days since last Sunday, so the result will be a 6. A Sunday would be a 0, a Wednesday would be a 3 and you can figure out rest.

Finally, the number of days in the year, so far, is shown. In this case it has been 134 days since January 1st 2022.

datetime_part

This command allows you to get any bit of information from a datetime type that you want. The format for this command is:

datetime_part(whatToSee,date)

The values you can use in the “whatToSee” argument are:

• Year
• Quarter
• Month
• week_of_year
• Day
• DayOfYear
• Hour
• Minute
• Second
• Millisecond
• Microsecond
• Nanosecond

As you can see, you can use command instead of “dayofmonth” and “dayofyear” if you want to although my preference is to use the “dayofX” command as it is easier to tell what you are looking for.

If we run the commands

let today=now();
print today, datetime_part("Year",today), datetime_part("Quarter",today), datetime_part("Month",today), datetime_part("Hour",today)

we get the results

Here you can see the actual date the command was run, the year, the quarter (more on that later), the month, and the hour.

If you don’t now, the quarter represents which block of 3 months the date is in. In this case, the date falls in the April, May, June block so the number is 2. The first block is January, February, and March and if a date falls into that block the value returned would be 1.

Now here is something interesting. If you look at the date when the command was run, you can see this was run at 8AM and yet the hour being returned is 12. This is because the “now()” command returns the UTC time but my Logs area in MS Sentinel is set to show the local time, as it states in the header.

This is something to keep in mind when working with times, and possibly dates, is the difference between UTC and your local time. I can add an offset to the “now()” command and change my Logs are to show UTC like shown below

let today=now(-4h);
print today, datetime_part("Year",today), datetime_part("Quarter",today), datetime_part("Month",today), datetime_part("Hour",today)

and now the results look like

So now the hour portion of the date matches up. It is definitely something to keep in mind when you start manipulating dates and times. Also, don’t forget daylight saving time! Also, keep in mind that this is the number of hours so 1PM would return 13 and 11PM would return 23.

startofX / endofX

What is you need to know the beginning of a day, month, week, or year? It is easy enough to do the math, but you don’t need to. KQL provides commands to do it for you.

• startofday(date, [offset]) – returns the start of the day, taking into account an offset if provided.
• startofmonth(date, [offset]) – returns the start of the month, taking into account an offset if provided.
• startofweek(date, [offset]) – returns the start of the week, taking into account an offset if provided.
• startofyear(date, [offset]) – returns the start of the year, taking into account an offset if provided.

Each of these commands return a datetime value that represents the beginning of what you are looking for. Note that there is also a corresponding endofX that returns the end of whatever you are looking for.

To look at the startofX commands, we run the following KQL:

let today=now(-4h);
print today, startofday(today), startofmonth(today), startofweek(today), startofyear(today)

and get the following results

It should be fairly self-explanatory what values are being returned. “startofday” returns the very first nanosecond of the day, which isn’t actually shown but is all zeros . “startofmonth” does the same as if we were passing in the first of the month. “startofweek” is like we passed in the preceding Sunday’s date and “startofyear” is like we passed in the date for January 1st of the year.

If we change all the commands to their corresponding “endof” commands as shown below

let today=now(-4h);
print today, endofday(today), endofmonth(today), endofweek(today), endofyear(today)

we get

In this case, the very last nanosecond is used but once again is not shown. Just know that it is the last possible nanosecond before switching to the next day, month, week, or year. If you could see the number, by using something like

let today=now(-4h);
print today, datetime_part("nanosecond",endofday(today))

You would see that the number being returned is 999,999,900. Not sure why it ends at 900 and not 999. It may be a bug but seriously, if that upsets your calculations, I would love to know what you are doing!

datetime_diff

The last command we will look at is “datetime_diff(period, firstDate, secondDate)”. This will return the result of “firstDate” minus “seconDate” and the results will be based on what period you passed in. You can use the following entries for the period argument

• Year
• Quarter
• Month
• Week
• Day
• Hour
• Minute
• Second
• Millisecond
• Microsecond
• Nanosecond

The following KQL command shows some examples of using this command

let firstdate=now();
let seconddate = now(-28h);
print firstdate, seconddate, datetime_diff("Minute",firstdate,seconddate), datetime_diff("Hour",firstdate,seconddate), datetime_diff("Day",firstdate,seconddate)

Running this command yields:

You can see that there are 1,680 minutes or 28 hours or1 day difference between the dates.

Example

Let’s take a look at using some of the commands in action. Suppose you need to create a report that shows all the alerts that were created during business hours. For the sake of this example, business hours are from 7:30AM until 6:00PM Monday through Friday.

We are not going to worry about filtering the time to look at. You can do this in the workbook or in the Logs area.

The first thing I will do is convert the “TimeGenerated” field to my local time. I am just going to convert it using -4 hours since that works right now for the Eastern US Time Zone. You could easily use a formula based on the start and ending dates of daylight saving time to always use the correct value.

SecurityAlert
| extend localTime = datetime_add("Hour",-4,TimeGenerated)

Now that I have a time, the first thing I will do is filter out all those alerts that fall on Saturday or Sunday, since we know those days are outside our business hours. I will get the day of the week, convert it to an integer, and then filter out the rows where the day of the week is either 6 (Saturday) or 0 (Sunday)

| extend dayofWeek = dayofweek(localTime)/1d
//ignore Saturday and Sunday
| where (dayofWeek !=6 and dayofWeek !=0 )

Next, we want to get rid of any alerts that happen after 6PM. This is done by making sure the hour value of our “localTime” variable is less than 18 (6PM).

//get the hour of the day that the alert was generated
| extend timeGeneratedHour = datetime_part("Hour",localTime)
| where (timeGeneratedHour <18)

To filter out those alerts that happened before the start of our business hours, 7:30AM, will take a bit more thought. We can’t just filter out only those alerts before 7AM like we did with those that happened after 6PM since there is another half hour we need to think about. Unfortunately, no command has an argument like “MinutesInDay”.

What we can do is to combine a few commands we learned to get the number of minutes in the day that the alert was created and compare that to how many minutes have passed until 7:30AM (7 * 60 + 30 = 450).

We do this by getting the start of the day, subtracting that from the alert time, and return the time as minutes as shown below

//get how many minutes have passed since the beginning of the day
| extend timediff = datetime_diff("Minute",TimeGenerated,startofday(localTime))
| where timediff >450   //450 minutes ==7:30AM

When all the code snippets are combined you get

SecurityAlert
| extend localTime = datetime_add("Hour",-4,TimeGenerated)
| extend dayofWeek = dayofweek(localTime)/1d
//ignore Saturday and Sunday
| where (dayofWeek !=6 and dayofWeek !=0 )
//get the hour of the day that the alert was generated
| extend timeGeneratedHour = datetime_part("Hour",localTime)
| where (timeGeneratedHour <18)
//get how many minutes have passed since the beginning of the day
| extend timediff = datetime_diff("Minute",TimeGenerated,startofday(localTime))
| where timediff >450   //450 minutes ==7:30AM

Which will only return those alerts that were generated between 7:30AM and 6PM Monday through Friday (assuming I have not made any coding errors).

Summary

KQL provides many different commands to manipulate dates and times. You can use these in your code to get very precise, down to the nanosecond even, what you want to see.