Forum Discussion
Solved
KQL Query help
Hi, I'm new to Sentinel and KQL and wish to use the Security Event logs that are being sent to sentinel to get information about AD logons.
I have manged to get the logs but I am not able to sort the data the way I want.
What I want is to query the SecurityEvent for logons and logoff for a user for x number of days and get the first logon of each day and last last off of each day.
KQL Query:
SecurityEvent
| where TimeGenerated > startofday(ago(2d))
| where TimeGenerated < endofday(ago(1h))
| where EventID in (4624, 4625, 4634)
| where TargetUserName contains "<Username>"
results:
TenantId TimeGenerated [UTC] SourceSystem Account AccountType Computer EventSourceName Channel
Removed for security 6/30/2023, 5:09:53.984 AM OpsManager Removed for security User Removed for security Microsoft-Windows-Security-Auditing Security
Removed for security 6/30/2023, 5:14:38.557 AM OpsManager Removed for security User Removed for security Microsoft-Windows-Security-Auditing Security
Removed for security 6/30/2023, 5:14:38.595 AM OpsManager Removed for security User Removed for security Microsoft-Windows-Security-Auditing Security
Removed for security 6/30/2023, 5:14:38.605 AM OpsManager Removed for security User Removed for security Microsoft-Windows-Security-Auditing Security
Removed for security 6/30/2023, 5:14:38.639 AM OpsManager Removed for security User Removed for security Microsoft-Windows-Security-Auditing Security
Removed for security 6/30/2023, 5:14:53.450 AM OpsManager Removed for security User Removed for security Microsoft-Windows-Security-Auditing Security
Removed for security 6/30/2023, 5:14:36.897 AM OpsManager Removed for security User Removed for security Microsoft-Windows-Security-Auditing Security
Removed for security 6/30/2023, 5:14:37.290 AM OpsManager Removed for security User Removed for security Microsoft-Windows-Security-Auditing Security
Removed for security 6/30/2023, 5:14:37.303 AM OpsManager Removed for security User Removed for security Microsoft-Windows-Security-Auditing Security
Removed for security 6/30/2023, 5:14:37.998 AM OpsManager Removed for security User Removed for security Microsoft-Windows-Security-Auditing Security
- This should do the trick, just be aware that the FirstLogonOfTheDay might as well be a failed attempt if you keep 4625. Also, summarizing like this will not give you a full picture of user's activity i.e. a single, all-day session vs several shorter ones will return exactly the same time values. It all depends on what your use case is, but you might need to modify the query a bit further.
let FirstLogonOfTheDay=SecurityEvent
| where TimeGenerated between (startofday(ago(2d)) .. endofday(ago(1h)))
| where AccountType == 'User' and EventID in (4624, 4625)
| extend Date=format_datetime(TimeGenerated, 'dd-MM-yyyy')
| summarize arg_min(TimeGenerated, *) by TargetUserName, Date
| extend FirstLogonOfTheDay=TimeGenerated;
SecurityEvent
| where TimeGenerated between (startofday(ago(2d)) .. endofday(ago(1h)))
| where AccountType == 'User' and EventID in (4634)
| extend Date=format_datetime(TimeGenerated, 'dd-MM-yyyy')
| summarize arg_max(TimeGenerated, *) by TargetUserName, Date
| extend LastLogoffOfTheDay=TimeGenerated
| join kind=inner FirstLogonOfTheDay on Date, TargetUserName
//| where TargetUserName =~ 'jsmith'
| project Date, TargetUserName, FirstLogonOfTheDay, LastLogoffOfTheDay, SourceSystem, Account, AccountType, Computer, EventSourceName, Channel
| sort by TargetUserName asc, Date desc
- This should do the trick, just be aware that the FirstLogonOfTheDay might as well be a failed attempt if you keep 4625. Also, summarizing like this will not give you a full picture of user's activity i.e. a single, all-day session vs several shorter ones will return exactly the same time values. It all depends on what your use case is, but you might need to modify the query a bit further.
let FirstLogonOfTheDay=SecurityEvent
| where TimeGenerated between (startofday(ago(2d)) .. endofday(ago(1h)))
| where AccountType == 'User' and EventID in (4624, 4625)
| extend Date=format_datetime(TimeGenerated, 'dd-MM-yyyy')
| summarize arg_min(TimeGenerated, *) by TargetUserName, Date
| extend FirstLogonOfTheDay=TimeGenerated;
SecurityEvent
| where TimeGenerated between (startofday(ago(2d)) .. endofday(ago(1h)))
| where AccountType == 'User' and EventID in (4634)
| extend Date=format_datetime(TimeGenerated, 'dd-MM-yyyy')
| summarize arg_max(TimeGenerated, *) by TargetUserName, Date
| extend LastLogoffOfTheDay=TimeGenerated
| join kind=inner FirstLogonOfTheDay on Date, TargetUserName
//| where TargetUserName =~ 'jsmith'
| project Date, TargetUserName, FirstLogonOfTheDay, LastLogoffOfTheDay, SourceSystem, Account, AccountType, Computer, EventSourceName, Channel
| sort by TargetUserName asc, Date desc
