Using local time in Microsoft Sentinel queries

Introduction

When you perform a query and look at a table, for instance Heartbeat, in Microsoft Sentinel, you will see that the datetime fields are stored as UTC time zone values. While there are exceptions to this rule, the TimeGenerated will always be stored using the UTC time zone.

This can be both a blessing and a curse. It is a blessing in that all the times stored show up in order. So, an entry that was added at 08/21/2022 04:00:00AM from a server running in Seattle will be added before an entry that was recorded at 08/21/2022 08:00:01AM from a server running New York. Even though, Seattle is 4 hours behind New York, the time that the entry was added was normalized to the UTC time zone so they will be added in the correct order.

Now the curse part comes in when you try to find an entry when you only have the local time it happened. You can always do the math yourself to figure out the actual time to use, but there is a different (and perhaps easier) way.

The post will talk about how to do just that.

More on the problem

The image below shows a simple query of the Heartbeat table.

I ran this on 08/21/2022 09:12AM but you can see that the TimeGenerated field there are times that are later than 1PM. This is because my time zone (EST) is 4 hours behind UTC’s time zone. Now, those of you who are used to logs in Microsoft Sentinel, know you can use the drop down at the bottom of the page to change this to show the times in your Local time as shown below.

Great! We see the times when the entries were added based on our local time zone. Let’s do a query for one of the entries. We will look for the first entry listed that has the TimeGenerated of “08/21/2022, 09:15:14.777 AM”

Since the TimeGenerated field is the datetime type, we will need to convert our time string into a datetime type using the datetime operator.

Heartbeat
| where TimeGenerated == datetime(08/21/2022, 09:15:14.777 AM)

But when we run this, we don’t get any results returned (or at least I didn’t). Why?

The problem is because the datetime operator ALWAYS returns the date using the UTC time zone. If you run

print datetime(08/21/2022, 09:15:14.777 AM)

You will see that the value returned is the time based on the UTC time zone. For me, if I have the display set to show my local time, I get “08/21/2022, 05:15:14.777 AM” since I am 4 hours behind. If I have my display set to show UTC time, it will show “08/21/2022, 09:15:14.777 AM” of course.

How to fix

Luckily, the creators of KQL thought about this and provided us with a solution. The datetime_local_to_utc() function will convert local datetime to the UTC datetime based on a time zone passed in. See its documentation here: datetime_local_to_utc() – Azure Data Explorer | Microsoft Docs

The command is pretty easy to use. You just pass in your local time and the time zone you are in. It has a link to the full listing of time zones in the documentation or you can see them here: Timezone – Azure Data Explorer | Microsoft Docs. I don’t know about you, but I never realized there were so many time zones that are supported! Almost 600 of them! Most major cities are listed as well as the typical ones liked “UTC”, “EST”, “MST”, and more.

The code below will show you the local time in my time zone. Feel free to change the time zone to match yours to make it more useful. With the almost 600 of them, I am sure you can find one that works!

let timeZone='US/Eastern';
let currentDateTime = datetime(08/21/2022, 09:15:14.777 AM);
let localToUTC = datetime_local_to_utc(currentDateTime,timeZone);
print localToUTC

When I run this, and have the display set to show UTC time, I get “8/21/2022, 1:15:14.777 PM” which is exactly what I needed to match that first entry! I then use that in my code to search the Heartbeat table as shown below

let timeZone='US/Eastern';
let currentDateTime = datetime(08/21/2022, 09:15:14.777 AM);
let localToUTC = datetime_local_to_utc(currentDateTime,timeZone);
Heartbeat
| where TimeGenerated == localToUTC

and I get

Which is exactly what we want!!!

WARNING!!!

I did notice that if I tried to use the “EST” time zone, it generated a time that was an hour off. It appears that not all the time zones recognize Daylight Saving time so you will want to do some testing before deciding which time zone to use.

Summary

This blog post showed how you can use your local time to perform searches in tables where the dates are stored in the UTC time without having to do the conversion yourself. Hope it helps!