KQL language preview

1. Failed sign-ins over time

tabular operators, functions, and timespans

horizon-dark

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != "0"
| summarize FailedCount = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| order by FailedCount desc
| take 10

atom-one-dark

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != "0"
| summarize FailedCount = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| order by FailedCount desc
| take 10

github-dark

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != "0"
| summarize FailedCount = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| order by FailedCount desc
| take 10

dracula

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != "0"
| summarize FailedCount = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| order by FailedCount desc
| take 10

nord

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != "0"
| summarize FailedCount = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| order by FailedCount desc
| take 10

github

SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != "0"
| summarize FailedCount = count() by UserPrincipalName, bin(TimeGenerated, 1h)
| order by FailedCount desc
| take 10

2. Security event investigation

string operators, let statements, and rendering

horizon-dark

let threshold = 5;
SecurityEvent
| where EventID == 4624
| where Account !contains "SYSTEM"
| summarize LogonCount = count() by Account, Computer
| where LogonCount > threshold
| render columnchart

atom-one-dark

let threshold = 5;
SecurityEvent
| where EventID == 4624
| where Account !contains "SYSTEM"
| summarize LogonCount = count() by Account, Computer
| where LogonCount > threshold
| render columnchart

github-dark

let threshold = 5;
SecurityEvent
| where EventID == 4624
| where Account !contains "SYSTEM"
| summarize LogonCount = count() by Account, Computer
| where LogonCount > threshold
| render columnchart

dracula

let threshold = 5;
SecurityEvent
| where EventID == 4624
| where Account !contains "SYSTEM"
| summarize LogonCount = count() by Account, Computer
| where LogonCount > threshold
| render columnchart

nord

let threshold = 5;
SecurityEvent
| where EventID == 4624
| where Account !contains "SYSTEM"
| summarize LogonCount = count() by Account, Computer
| where LogonCount > threshold
| render columnchart

github

let threshold = 5;
SecurityEvent
| where EventID == 4624
| where Account !contains "SYSTEM"
| summarize LogonCount = count() by Account, Computer
| where LogonCount > threshold
| render columnchart

3. Join hints

hint.strategy and hint.shufflekey on a join

horizon-dark

SigninLogs
| join hint.strategy=broadcast (
    IdentityInfo | project AccountUPN, Department
) on $left.UserPrincipalName == $right.AccountUPN
| join hint.shufflekey=UserPrincipalName kind=leftouter (
    AuditLogs | summarize LastAudit = max(TimeGenerated) by UserPrincipalName
) on UserPrincipalName

atom-one-dark

SigninLogs
| join hint.strategy=broadcast (
    IdentityInfo | project AccountUPN, Department
) on $left.UserPrincipalName == $right.AccountUPN
| join hint.shufflekey=UserPrincipalName kind=leftouter (
    AuditLogs | summarize LastAudit = max(TimeGenerated) by UserPrincipalName
) on UserPrincipalName

github-dark

SigninLogs
| join hint.strategy=broadcast (
    IdentityInfo | project AccountUPN, Department
) on $left.UserPrincipalName == $right.AccountUPN
| join hint.shufflekey=UserPrincipalName kind=leftouter (
    AuditLogs | summarize LastAudit = max(TimeGenerated) by UserPrincipalName
) on UserPrincipalName

dracula

SigninLogs
| join hint.strategy=broadcast (
    IdentityInfo | project AccountUPN, Department
) on $left.UserPrincipalName == $right.AccountUPN
| join hint.shufflekey=UserPrincipalName kind=leftouter (
    AuditLogs | summarize LastAudit = max(TimeGenerated) by UserPrincipalName
) on UserPrincipalName

nord

SigninLogs
| join hint.strategy=broadcast (
    IdentityInfo | project AccountUPN, Department
) on $left.UserPrincipalName == $right.AccountUPN
| join hint.shufflekey=UserPrincipalName kind=leftouter (
    AuditLogs | summarize LastAudit = max(TimeGenerated) by UserPrincipalName
) on UserPrincipalName

github

SigninLogs
| join hint.strategy=broadcast (
    IdentityInfo | project AccountUPN, Department
) on $left.UserPrincipalName == $right.AccountUPN
| join hint.shufflekey=UserPrincipalName kind=leftouter (
    AuditLogs | summarize LastAudit = max(TimeGenerated) by UserPrincipalName
) on UserPrincipalName

4. Parsing and reshaping logs

mv-expand, parse, and string functions

horizon-dark

Event
| where Source has "MSSQLSERVER" // inline comment
| mv-expand Tags
| parse EventData with "user=" User " action=" Action
| extend Summary = strcat(User, " performed ", Action)
| project TimeGenerated, Summary

atom-one-dark

Event
| where Source has "MSSQLSERVER" // inline comment
| mv-expand Tags
| parse EventData with "user=" User " action=" Action
| extend Summary = strcat(User, " performed ", Action)
| project TimeGenerated, Summary

github-dark

Event
| where Source has "MSSQLSERVER" // inline comment
| mv-expand Tags
| parse EventData with "user=" User " action=" Action
| extend Summary = strcat(User, " performed ", Action)
| project TimeGenerated, Summary

dracula

Event
| where Source has "MSSQLSERVER" // inline comment
| mv-expand Tags
| parse EventData with "user=" User " action=" Action
| extend Summary = strcat(User, " performed ", Action)
| project TimeGenerated, Summary

nord

Event
| where Source has "MSSQLSERVER" // inline comment
| mv-expand Tags
| parse EventData with "user=" User " action=" Action
| extend Summary = strcat(User, " performed ", Action)
| project TimeGenerated, Summary

github

Event
| where Source has "MSSQLSERVER" // inline comment
| mv-expand Tags
| parse EventData with "user=" User " action=" Action
| extend Summary = strcat(User, " performed ", Action)
| project TimeGenerated, Summary