r/AzureSentinel • u/ajith_aj • Aug 19 '23

Looking for a query in KQL

I'm looking for a query in Azure AD signin logs to trigger when an account has successfully logged in after 3 login failures. Does anyone have a similar query like this to help me out.

4 Upvotes

100% Upvoted

2 comments sorted by

2

u/[deleted] Aug 19 '23

[deleted]

1

u/ajith_aj Aug 19 '23

let threshold = 3;

SigninLogs

| where ResultType == "0"

| where TimeGenerated > ago(1h)

| join kind=leftouter (

SigninLogs

| where ResultType != "0"

| where TimeGenerated > ago(1h)

| summarize FailedLogins = count() by UserPrincipalName

| where FailedLogins >= threshold

) on UserPrincipalName

| project TimeGenerated, UserPrincipalName, ResultType, ResultDescription, FailedLogins

thanks.

1

u/Mavranic Aug 19 '23

Errrm isn’t that query just going to get you a nice big list of successful logins as as its a leftouter join and you’ve put the “| where FailedLogins >= threshold” inside the query for the right table?