How to align your Analytics with time windows in Azure Sentinel using KQL (Kusto Query Language)

[CliveWatson]

Overview

Thanks to Ofer Shezaf, Kieran Bhardwaj and Younes Khaldi for the ideas and proof reading!
 

Many of the query examples you see in KQL (Kusto Query Language) Detections, Rules, Hunting and Workbooks use a time filter.  In fact, the first recommendation in the best practices section is:

Source: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/best-practices

Now I will show you some hopefully simple but useful query examples (and I’ll use the demo data we provide for free so you can try these). 

Please click the links provided, all you need is a Microsoft account to grant you access to our extensive demo data.

You should also get familiar with the timespan literals: https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/scalar-data-types/timespan

Query Examples 

1. This first example looks back one day in time (looking back over the last 24hrs, from the moment you run the query); you can use 24h instead of 1d if you prefer. 
I prefer using 1d rather than 24hrs, typically I only use hours when I need a partial day i.e. 6h
This form of time span filter is one of the most common lines people add to a query.   

Please read on for an explanation on why this may not be what you intended, and ways to improve.

Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated > ago(1d)

2. You can also look between a range.  Here I look back 7days from now (today at this current time).  
Notice in the last line, I show the oldest record and the latest that are returned – I’ll do this for many of the examples just so you can see how the query is working,  I wouldn’t normally expect you to use them in a real query – only to test or when you need to show that level of detail.
I'll also have some more real life queries at the end of this article, but for now I'll keep them basic and simple.

Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated between (ago(7d) .. now() )
| summarize min(TimeGenerated), max(TimeGenerated)

Result:

min_TimeGenerated	max_TimeGenerated
2020-09-08T08:23:39.887Z	2020-09-15T08:23:29.323Z

The above is really just a longer way of writing the example in query 1 as it went to now() anyway.  It does allow you however to amend it to this, where we look between 7 and 5 days ago.

SecurityEvent 
| where TimeGenerated between (ago(7d) .. ago(5d) ) 
| summarize min(TimeGenerated), max(TimeGenerated)

Read on, in example 4 we will make these results more accurate/predictable.

3. During investigations you may have a date and time range in mind, or you wish to reduce the data volume returned. 
Tip: You can use Top or Limit to help reduce the amount of returned data.  Please look at the supplied help links for examples.

You can amend the query (#2) to provide an actual date / time.

Notice, this gets data from 1^st July through to 30^th July, but only until 9am (I added this to show you can do both date and time in the one syntax).

This is especially useful for looking maybe at your online business day or a known period that you are particularly interested in.

Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated between ( datetime(2020-07-01) .. datetime(2020-07-30, 09:00) )
| summarize min(TimeGenerated), max(TimeGenerated)

4. Here is where I see issues with many queries, just like the queries above which are using now as a point in time to pivot on. i.e. Show the data from now until 1day ago. 
This will mean that the returned data will change each time you run the query, which may give unpredictable or undesirable results. 

I have seen many issues in the past due to this, questions are often asked on the forums, asking where the data has gone, as it "was there when I last ran the query" - this is often due to this form of syntax being used, now() or ago().

We can solve this with a powerful function called startofday(), and don't worry there are other functions as well, read on for those!
Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated > startofday(ago(1d))
| summarize min(TimeGenerated), max(TimeGenerated)

Result: 

min_TimeGenerated	max_TimeGenerated
2020-09-14T00:00:00.103Z	2020-09-15T08:37:10.497Z

In this example using startofday, we are saying go from ‘the start of day’ (the first record found after mid-night) until the end time.  So in this query startofday(ago(1d)) is a fixed point in time close to midnight one day ago, until now() - so you are seeing more that one days worth of data.

Tip: This can also make your charts look better, as you get a full day of data at each end of the y-axis.

5. We can now combine startofday() and with another function called endofday().

This query guarantees you have data between two fixed points.  So you should always get the same results between query executions.
As you can see in the example it's possible to mix and match hours and days, which may be useful in certain scenarios.

Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated between ( startofday(ago(48hrs)) .. endofday(ago(1d)) )
| summarize min(TimeGenerated), max(TimeGenerated)

Result:

min_TimeGenerated	max_TimeGenerated
2020-09-13T00:00:00.043Z	2020-09-14T23:59:59.62Z

6. You can also use startofweek, startofmonth and startofyear in a similar way to the example in query #5. 

The following example uses startofmonth.  In this case no matter what entry you put in the ago(), the 1^st day of the month is used.  Again, you get a fixed point in time, the first day of the month (more of this in the extended examples at the end of the blog)

Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated > startofmonth(ago(0d))
| summarize min(TimeGenerated), max(TimeGenerated)

Startofweek and startofmonth are great functions especially for reporting, as you can show the whole week or week to date easily.  Maybe you need a graph that shows this view?

Note: The first day of the week is Sunday (day 0).
To adjust to Monday please use:

| where TimeGenerated > startofweek(ago(1d))+1d

+2 for Tuesday etc...

7. you may need the oldest or latest records, in examples #1-6 I have shown queries using min and max.  You can also use arg_min and arg_max instead, please see the help link for examples.

Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated > startofday(ago(1d))
| summarize arg_max(TimeGenerated, *)

In this query we get just the latest record for the time range selected.  This is useful if you only need the latest (arg_max) or earliest (arg_min) records, and doesn’t retrieve a lot of unwanted data, the “*” in the second parameters returns us all the Columns, you could name a specific column(s) to return if you prefer, e.g: This example just show the Task and EventID column, rather than all available data.

SecurityEvent
| where TimeGenerated > startofday(ago(1d))
| summarize  arg_max(TimeGenerated, Task, EventID)

TimeGenerated	Task	EventID
2020-09-15T16:12:58.907Z	12545	4634

8.  There is no startofhour option, but we can use the bin scalar to help us with that, as this example shows.

Go to Log Analytics and run query

let searchTime = 1h;
SecurityEvent
| where TimeGenerated > ago(searchTime)
| extend startOfHour = bin(TimeGenerated,1h)
| where startOfHour > ago(searchTime)
| summarize min(TimeGenerated), max(TimeGenerated)

Result: 

min_TimeGenerated	max_TimeGenerated
2020-09-15T16:00:00.39Z	2020-09-15T16:15:49.42Z

9. This is a common requested example, people often wish to show data between or outside a time range - maybe 'business hours'.  I have used between to allow a certain range, but you can also use !between to exclude a time range.  This example will exclude rows of data between 22pm and 6am (as we set 07 .. 22) as the allowed hours in the query.

Go to Log Analytics and run query

Heartbeat
| where TimeGenerated > ago(1d)
| extend hour = datetime_part("hour", TimeGenerated)
| where hour between (07 .. 22)
| summarize LastCall = max(TimeGenerated) by Computer, ComputerEnvironment, hour
| where LastCall < ago(10m)
| order by hour asc

10. Now lets look at certain days of the week, in this case Monday (day 1) thru Friday (day 5)

Heartbeat
| where TimeGenerated > ago(30d)
| where dayofweek(TimeGenerated) in ('1.00:00:00','2.00:00:00','3.00:00:00','4.00:00:00','5.00:00:00')
| summarize count() by Computer, bin(TimeGenerated, 1d)
| order by TimeGenerated asc

or, I think this is easier to read

 Go to Log Analytics and run query

Heartbeat
| where TimeGenerated > ago(30d)
| where dayofweek(TimeGenerated) between (1d .. 5d)
| summarize count(), make_set(dayofweek(TimeGenerated)) by Computer, bin(TimeGenerated, 1d)
| order by TimeGenerated asc

11. Lets now extend Query 10, we can also add a Column with the Name of the day, Monday, Tuesday etc… to make the report easier to read.

Heartbeat
| where TimeGenerated > ago(30d)
| where dayofweek(TimeGenerated) between (1d .. 5d)
| extend theDay = case(
                        dayofweek(TimeGenerated) == '0.00:00:00', "Sunday",
                        dayofweek(TimeGenerated) == '1.00:00:00', "Monday",
                        dayofweek(TimeGenerated) == '2.00:00:00', "Tuesday",
                        dayofweek(TimeGenerated) == '3.00:00:00', "Wednesday",
                        dayofweek(TimeGenerated) == '4.00:00:00', "Thursday",
                        dayofweek(TimeGenerated) == '5.00:00:00', "Friday",
                        dayofweek(TimeGenerated) == '6.00:00:00', "Saturday",
                        strcat("error: ", dayofweek(TimeGenerated))
                       )
| summarize by theDay

Go to Log Analytics and run query

theDay
Tuesday
Monday
Friday
Wednesday
Thursday

12. A common ask would be to remove the Weekend from the returned data, like this:

 Go to Log Analytics and run query

// 0 = Sunday
// 6 = Saturday etc...
Heartbeat
| where TimeGenerated > ago(7d)
| where Computer startswith "DC01"
| where dayofweek(TimeGenerated) between  (1d .. 5d)
| summarize count() by Computer, bin(TimeGenerated, 1d)
| order by TimeGenerated asc
| render columnchart 

Result: You can see the gap for the Weekend we excluded (in red), this assumes your Weekend is Saturday to Sunday, please amend if it isn't. 

Note: If desired you should amend the above query, removing this line (I used it to show the gap the weekend left):

| where TimeGenerated > ago(7d)

------------------------------------------------------------------------

Extended examples that you might really use.

Now we will use some of the above examples, in real queries:

1. Show unique counts of EventIds, per day over the last Week: Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated between ( startofday(ago(7d)) .. startofday(now()) )
| summarize dcount(EventID) by bin(TimeGenerated,1d)
| render timechart title = "The unique count of EventIds - past 7days"

 

 2. Show unique EventIds so far during the current month: Go to Log Analytics and run query

SecurityEvent
| where TimeGenerated between ( startofmonth(now()) .. now() )
| summarize dcount(EventID) by bin(TimeGenerated,1d)
| render timechart title = "The unique count of EventIds - Monthly View: Day 1 thru to Today"

 

3. Show unique EventIds so far this current month, excluding weekends (Saturday & Sunday). Go to Log Analytics and run query
You can adjust for a Friday to Saturday weekend,  swap 1d .. 5d to 0d .. 4d

SecurityEvent
| where TimeGenerated between ( startofmonth(now()) .. now() )
| where dayofweek(TimeGenerated) between (1d .. 5d)
| summarize dcount(EventID) by bin(TimeGenerated,1d)
| render columnchart title = "The unique count of EventIds - Monthly View: Day 1 thru to Today (excluding Weekends)"

 

4. Show unique EventIds for the current Week, Week starting on Monday. Go to Log Analytics and run query 
I used +1d to make the week start on Monday, as the default is Sunday (0d).

SecurityEvent
| where TimeGenerated > startofweek(now()) + 1d
| summarize dcount(EventID) by bin(TimeGenerated,1d)
| order by TimeGenerated asc
| render columnchart
| render columnchart title = "The unique count of EventIds - Week View: Monday thru to Today"

5.   Maintenance Window is the last weekend of the month, exclude those days.  

//  Maintenance Window is the last weekend of the month 

//

Heartbeat 

// Look at last month 

| where TimeGenerated between ( startofmonth(now(),-1).. endofmonth(now(),-1) ) 

| where Computer == "JBOX00"     // < insert your Computer name here >

// find 4th week, which is week "3" (count starts at zero) 

| extend maintSaturday_ = endofweek(startofmonth(now(),-1),3) -1d, maintSunday_ = endofweek(startofmonth(now(),-1),3) + 1d 

// exclude 4th week from data set 

| where TimeGenerated !between ( maintSaturday_ .. maintSunday_ ) 

| summarize heartbeat_per_hour=count() by bin(TimeGenerated, 1h), Computer 

Summary

The above examples should give you some ideas on how to search (and visualize) your data using Time and Dates techniques.  Did I miss any you use?  

[Luizao_f]

@CliveWatson  

Very good your article.

Really enjoyed.

Applicable examples.

I'm having a problem and maybe you can help me.

I work in GMT-3 time, however, when executing the query, only the displayed view that converts to GMT-3 time, but the query needs to be drawn in UTC time, because if inserted in my local time, it returns inconsistent values.

I usually have this problem when using the command [between], because I need to insert it in UTC time, in this case, every time I need to research thinking 3 hours ahead. It disturbs and confuses me at times.

Point X is: Can you tell me if I can modify any parameter or use a mallet to search the KQL with a time other than UTC?

[CliveWatson]

You can set Local Time in the Log Analytics settings (or swap using the UI)

or use a offset in the code, such as minus 3hours?

AzureDiagnostics   
| extend utc = now()
| extend greenland = now()-3h
| summarize max(utc), max(greenland)

[Luizao_f]

Thank you for your help, @CliveWatson .

However, my problem is not about displaying the time, but searching in my GMT-3 and not in UTC.

In the example below, the alert generated in my time at 09:36 PM on 12/20, however, I can only reach this information by searching for an additional 3 hours, thus searching, on 12/21, at 00:36.

This is bad, because I always need to think about the time to write my queries with an additional 3 hours, because if you research thinking about my schedule, in fact, the event occurred 3 hours after.

[Logan-IN]

Hi @CliveWatson ,

This is really nice and i've bookmarked this item. 

I have a requirement to get the logs based on timegenerated with filter x days. Ex: I wanted to see the logs by excluding last 2days. If today date is 24th of August, then wanted to view the logs created before 22nd of August. I tried ago, startofday command but it doesn't match my requirement, any idea how to get the same. Thank you! 

[ssosts1]

@CliveWatson Great use of various sets of queries where time & day could be tuned. 

I'm trying to come up with a query which renders mean time to respond and mean time to resolve for weekly or monthly reporting, however the challenge I've is we only operate 9-5 PM weekdays only and this is messing up the stats for obvious reasons. Let's say incident is fired midnight and no one would pick it up till 9AM and when the stats are run for mean time to respond and resolve they would naturally be higher. 

Would you be able to help here!

[Ashutoshipec]

Hi @CliveWatson , very good work, can you please help in writing below query.

Need to write a query for a use case, if user account is getting created on any day other than Monday.

[Clive_Watson]

AuditLogs
| where dayofweek(TimeGenerated) !=1d
| where OperationName =~ "Add user"
| extend userPrincipalName_ = tostring(TargetResources[0].userPrincipalName)
| summarize by dayofweek(TimeGenerated), userPrincipalName_, TimeGenerated

This is a very rough example - of when the day of the week is not equal to Monday (1d) then show the data 

[Haley Klusman]

@Clive_Watson @CliveWatson 

Hello, we are attempting to run a KQL query daily. The Query is looking back on the previous day from midnight to midnight (full 24 hours). Our issue is the UTC to local time. When we run the query with this time line: | where TimeGenerated between (datetime_local_to_utc(ago(1d), 'US/Central') .. 1d) at mightnight. its offsetting by 5 hours and showing us 7pm to 7pm instead. Is there a better way to write this line? | where TimeGenerated between (startofday(ago(1d)) .. endofday(ago(1d)))  gets us the same results. Thanks!