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 th...
- 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
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
