Forum Discussion
KQL query
Hi Team, we want failed attempt with in 5m duration but query is stopped for last line. Please correct me. let threshold=1; let authenticationWindow = 5m; SigninLogs | where UserPrincipalName =...
| summarize FailedAttempt = count() by ResultDescription, UserPrincipalName, AppDisplayName
The summarize above does not contain TimeGenerated, so the TimeGenerated field is removed from the results past that. Therefore, you cannot use it at the final line.
Try the code below.
let threshold=1;
let authenticationWindow = 5m;
let Logs = SigninLogs
| where UserPrincipalName == "email address removed for privacy reasons"
| where ResultDescription has_any ("Invalid username or password", "Invalid on-premise username or password");
Logs
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated) by bin(TimeGenerated, authenticationWindow), UserPrincipalName, AppDisplayName
| join kind=inner (
Logs
| summarize FailedAttempt = count() by ResultDescription, UserPrincipalName, AppDisplayName
| where FailedAttempt >= ["threshold"]
) on UserPrincipalName,AppDisplayName,ResultDescription
| project-away UserPrincipalName1,AppDisplayName1,ResultDescription1
Still getting error
'where' operator: Failed to resolve scalar expression named 'ResultDescription'
My bad, was missing a bit.
let threshold=1; let authenticationWindow = 5m; let Logs = SigninLogs | where UserPrincipalName == "email address removed for privacy reasons" | where ResultDescription has_any ("Invalid username or password", "Invalid on-premise username or password"); Logs | summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated) by bin(TimeGenerated, authenticationWindow), UserPrincipalName, ResultDescription, AppDisplayName | join kind=inner ( Logs | summarize FailedAttempt = count() by ResultDescription, UserPrincipalName, AppDisplayName | where FailedAttempt >= ["threshold"] ) on UserPrincipalName,AppDisplayName,ResultDescription | project-away UserPrincipalName1,AppDisplayName1,ResultDescription1Thankyou for reply. If i want to add some more field in alert like IPAddress, Location etc.. so where i ahve to edit. could you please edit so i will update again accordingly.
