Azure Sentinel News
  • Home
    • Home – Layout 1
    • Home – Layout 2
    • Home – Layout 3
  • Security and Compliance
  • SOC
  • Threat Intelligence
  • Security Ochestration & Automated Response
  • SOAR
  • Security Operations
  • Artificial Intelligence
No Result
View All Result
  • Home
    • Home – Layout 1
    • Home – Layout 2
    • Home – Layout 3
  • Security and Compliance
  • SOC
  • Threat Intelligence
  • Security Ochestration & Automated Response
  • SOAR
  • Security Operations
  • Artificial Intelligence
No Result
View All Result
Azure Sentinel News
No Result
View All Result
Home KQL

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

Azure Sentinel News Editor by Azure Sentinel News Editor
November 26, 2020
in KQL
0
Microsoft improves Azure’s security to protect your business
6.9kViews
583 Shares Share on Facebook Share on Twitter

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 1st July through to 30th 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 between ( datetime(2020-07-01) .. datetime(2020-07-30, 09:00) )
| 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)
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 1st 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"

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?  

Reference: https://techcommunity.microsoft.com/t5/azure-sentinel/how-to-align-your-analytics-with-time-windows-in-azure-sentinel/ba-p/1667574

Azure Sentinel News Editor

Azure Sentinel News Editor

Related Posts

What’s new: Microsoft Teams connector in Public Preview
KQL

New Azure Sentinel Learning Modules Released

February 1, 2021
What’s new: Microsoft Teams connector in Public Preview
KQL

How to Connect the New Intune Devices Log Azure Sentinel

January 26, 2021
What’s new: Microsoft Teams connector in Public Preview
KQL

How to Create a Backup Notification in the Event an Unauthorized User Accesses Azure Sentinel

January 11, 2021
Next Post
New Recorded Future and Microsoft Azure Partnership Brings Security Intelligence to Cloud Environments

What’s New: Cross-workspace Analytics Rules

Microsoft is quietly becoming a cybersecurity powerhouse

What’s new: Azure DDoS Protection connector in Public Preview for Azure Sentinel

What’s new: Microsoft Teams connector in Public Preview

What’s new: Microsoft Teams connector in Public Preview

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Follow Us

  • 21.8M Fans
  • 81 Followers

Recommended

Open Systems Augments its Cybersecurity Capabilities With Acquisition of Leading Microsoft Azure Sentinel Expert

Open Systems Augments its Cybersecurity Capabilities With Acquisition of Leading Microsoft Azure Sentinel Expert

3 months ago
What’s new: Microsoft Teams connector in Public Preview

BT supporta Microsoft Azure Sentinel

3 months ago
Microsoft announces the public preview of Watchlist feature in Azure Sentinel

Microsoft announces the public preview of Watchlist feature in Azure Sentinel

3 months ago
With new release, CrowdStrike targets Google Cloud, Azure and container adopters

What’s new: New Fusion detections and BYOML in public preview!

2 months ago

Instagram

    Please install/update and activate JNews Instagram plugin.

Categories

  • AI & ML
  • Artificial Intelligence
  • Incident Response
  • IR
  • KQL
  • Security and Compliance
  • Security Ochestration & Automated Response
  • Security Operations
  • SIEM
  • SOAR
  • SOC
  • Threat Intelligence
  • Uncategorized

Topics

anomaly automation Azure Azure DevOps Azure Security Center Azure Sentinel Azure Sentinel API Azure Sentinel Connector BlueVoyant Call cybersecurity Detection file GitHub Hunting Huntingy IAC incident response Incident Triage infrastructure as code Investigation jupyter LAQueryLogs MDR Microsoft microsoft 365 mssp Multitenancy Notebooks Pester Playbooks PowerShell python Records Security Sentinel Sharing SIEM signin Supply Chain teams Threat hunting Watchlists Workbooks XDR
No Result
View All Result

Highlights

New Search Capability for Azure Sentinel Incidents

Follow-up: Microsoft Tech Talks Practical Sentinel : A Day in the Life of a Sentinel Analyst

Changes in How Running Hunting Queries Works in Azure Sentinel

Azure Sentinel can now Analyze All Available Azure Active Directory Log Files

Replay Now Available – Microsoft Security Insights 036: Azure Sentinel with Rod Trent

Understanding the Little Blue Permissions Locks in Azure Sentinel Data Connectors

Trending

Microsoft’s newest sustainable datacenter region coming to Arizona in 2021
IR

The Holy Grail of Azure Sentinel Data Connections: The Azure Service Diagnostic Setting

by Azure Sentinel News Editor
February 22, 2021
0

The Azure Sentinel product group continues to crank out new Data Connector after new Data Connector. There...

Microsoft’s newest sustainable datacenter region coming to Arizona in 2021

New Items of Note on the Azure Sentinel GitHub Repo

February 18, 2021
Microsoft’s newest sustainable datacenter region coming to Arizona in 2021

Tuning the MCAS Analytics Rule for Azure Sentinel: System Alerts and Feature Deprecation

February 17, 2021
What’s new: Microsoft Teams connector in Public Preview

New Search Capability for Azure Sentinel Incidents

February 16, 2021
With new release, CrowdStrike targets Google Cloud, Azure and container adopters

Follow-up: Microsoft Tech Talks Practical Sentinel : A Day in the Life of a Sentinel Analyst

February 16, 2021

We bring you the best, latest and perfect Azure Sentinel News, Magazine, Personal Blogs, etc. Visit our landing page to see all features & demos.
LEARN MORE »

Recent News

  • The Holy Grail of Azure Sentinel Data Connections: The Azure Service Diagnostic Setting February 22, 2021
  • New Items of Note on the Azure Sentinel GitHub Repo February 18, 2021
  • Tuning the MCAS Analytics Rule for Azure Sentinel: System Alerts and Feature Deprecation February 17, 2021

Categories

  • AI & ML
  • Artificial Intelligence
  • Incident Response
  • IR
  • KQL
  • Security and Compliance
  • Security Ochestration & Automated Response
  • Security Operations
  • SIEM
  • SOAR
  • SOC
  • Threat Intelligence
  • Uncategorized

[mc4wp_form]

Copyright © 2020 - Azure Sentinel News

No Result
View All Result
  • Home
  • Security and Compliance
  • SOC
  • Threat Intelligence
  • Security Ochestration & Automated Response
  • SOAR
  • Security Operations
  • Artificial Intelligence

Copyright © 2020 Azure Sentinel News