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 =...
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.
- I tried this one n working
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, IPAddress, Location | join kind=inner ( Logs | summarize FailedAttempt = count() by ResultDescription, UserPrincipalName, AppDisplayName, IPAddress, Location | where FailedAttempt >= ["threshold"] ) on UserPrincipalName, AppDisplayName, ResultDescription | project-away UserPrincipalName1, AppDisplayName1, ResultDescription1, IPAddress1, Location1 - It all depends on what you want the end result to look like and how the filtering is supposed to be done.
Chatgpt can also generate KQL queries.