Kusto KQL - Part 2 - Dealing with Time

Working with Time in KQL

Handling time effectively in Kusto Query Language (KQL) is essential for accurate analysis, efficient data management, and optimized query performance, especially when working in environments like Microsoft Sentinel or Microsoft Defender.

Time Filtering Options

Most logging platforms, including Microsoft Sentinel, provide a GUI-based datetime picker to filter logs within a specific timeframe. However, you can override the GUI time filter by specifying a time condition directly in your query, this is often preferred for consistent, portable queries.

Key Time Functions in KQL

KQL provides several built-in functions for working with time:

Returns the current timestamp.

• now()

Returns a timestamp that’s a specified timespan before now.

• ago(timespan)

Explicitly sets a fixed date and optional time.

• datetime("YYYY-MM-DD [HH:MM]")

KQL time filter examples

• Search the past 1 hour:

| where TimeGenerated > ago(1h)
// or
| where TimeGenerated > now(-1h)

• Search the past 15 days:

| where TimeGenerated > ago(15d)

• Search between specific relative times:

| where TimeGenerated between (ago(4h) .. ago(1h))

• Search between specific absolute times:

| where TimeGenerated between (datetime("2025-06-15") .. datetime("2025-07-31"))

• Include time of day:

| where TimeGenerated between (datetime("2025-06-15 09:00") .. datetime("2025-07-31 12:00"))

Time Field Naming in Sentinel vs. Defender

When working across different Microsoft security tools, be aware of differing field names:

• Sentinel uses: TimeGenerated

• Defender uses: Timestamp

If you copy a KQL query from Sentinel to Defender and it doesn't return results, you may need to map or adjust time fields to match the schema of the destination system.

Common DateTime Fields in Security Logs

Here are some commonly encountered datetime fields in KQL tables:

• TimeGenerated

• Timestamp

• InitiatingProcessCreationTime

• InitiatingProcessParentCreationTime

• ProcessCreationTime

How to List All DateTime Columns in a Table

To view all columns in a table that use the System.DateTime data type:

TableName
| getschema
| where DataType == "System.DateTime"
| project ColumnName

This is particularly useful when exploring new data sources or troubleshooting time-based filters that don't seem to work.

Tips for Effective Time-Based Queries

• Always limit your time window to reduce data scanned and improve performance.

• Use ago() when writing reusable or automated queries.

• Use datetime() when running point-in-time analysis or replaying historical events.

• Ensure you’re referencing the correct timestamp field based on the data source (Sentinel vs. Defender vs. raw log source).