In everyday security operations, it's common to sift through alerts and scrutinize logs. We frequently lean on KQL functions like now() and ago() to narrow down our log analysis to recent intervals, such as the last hour or day. However, when it comes to investigating past events, these functions seem less straightforward.
Imagine today's date is September 25, 2023. You're examining an incident involving user adm-roy.trenneman and came across a low severity alert from September 14, 2023, at 19:16:00. Your task is to analyze the login activities of adm-roy.trenneman from one hour before and after this alert.
Typically, you'd filter logs by explicitly specifying exact timestamps using the between function. However, this method can be cumbersome, especially when you want to adjust the timestamp several times since you need to do datetime arithmetic manually. Also, when there are different timestamps used in a long query, manually adjusting them becomes tedious.
A common query for this scenario would look like below:
let alert_timestamp = datetime(2023-09-14 19:16:00);
SecurityEvent
| where TimeGenerated between ((alert_timestamp - 1h) .. (alert_timestamp + 1h))
| where EventID in (4624,4625)
| where TargetUserName =~ "adm-roy.trenneman"
This query does the job but doesn't leverage the convenience of now() and ago() functions.
KQL offers a solution to this dilemma. You can use set commands to redefine the system's current date and time within the query execution context, making now() and ago() applicable even for historical events. Here’s how it works:
First, inform KQL about the relevant timestamp and the current date and time for your query:
// Define the column containing timestamp information
set query_datetimescope_column = "TimeGenerated";
// Set the most recent log timestamp
set query_datetimescope_to = datetime(2023-09-14 23:59:00);
// Specify the current date and time for the query
set query_now = datetime(2023-09-14 19:16:00);
Now, you can utilize now() function as if the current time is September 14, 2023, at 19:16:00:
// Define the column containing timestamp information
set query_datetimescope_column = "TimeGenerated";
// Set the most recent log timestamp
set query_datetimescope_to = datetime(2023-09-14 23:59:00);
// Specify the current date and time for the query
set query_now = datetime(2023-09-14 19:16:00);
SecurityEvent
| where TimeGenerated between ( now(-1h) .. now(+1h) )
| where EventID in (4624,4625)
| where TargetUserName =~ "adm-roy.trenneman"
If you wish to analyze events from a specific past hour, ensure that both query_datetimescope_to and query_now are set to the same timestamp. Note that if you want to analyze the events before a certain date only (i.e. no use of now(+1h), etc. in the query), the query_datetimescope_to and query_now has to point to the same value:
set query_datetimescope_column = "TimeGenerated";
set query_datetimescope_to = datetime(2023-09-14 19:16:00);
set query_now = datetime(2023-09-14 19:16:00);
SecurityEvent
| where TimeGenerated > ago(1h)
| where EventID in (4624, 4625)
| where TargetUserName =~ "adm-roy.trenneman"
This method can be very useful when you develop a detection query and want to test it on a historical data to see how much alert it would trigger(Microsoft Sentinel shows this information already). Also, there may be times when you investigate an incident and think "one of our detections should have detected this activity, why didn't it trigger?". In that case, you can use this method without modifying your detection query and replay it to debug the root cause.
This approach simplifies historical data analysis by allowing the continued use of now() and ago() functions. It's especially useful for scenarios where you need to analyze events as if they were occurring in real-time. This method will be utilized in upcoming lessons, to simulate a real-life experience of an analyst.
