Forum Discussion

akshay250692's avatar
akshay250692
Brass Contributor
Jul 21, 2023

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 == "email address removed for privacy reasons"
| where ResultDescription has_any ("Invalid username or password", "Invalid on-premise username or password")
| summarize FailedAttempt = count() by ResultDescription, UserPrincipalName, AppDisplayName
| where FailedAttempt >= ["threshold"]
| summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated) by bin(TimeGenerated, authenticationWindow), FailedAttempt, UserPrincipalName, AppDisplayName

 

Last line getting error for TimeGenerated

KQL
  • Jonhed's avatar
    Jonhed
    Steel Contributor
    Jul 23, 2023

    akshay250692 

     

     

    | 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

     

     

     

    • akshay250692's avatar
      akshay250692
      Brass Contributor
      Jul 24, 2023

      Jonhed 

       

      Still getting error

      'where' operator: Failed to resolve scalar expression named 'ResultDescription'

      • Jonhed's avatar
        Jonhed
        Steel Contributor
        Jul 24, 2023

        akshay250692 

        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,ResultDescription1

Resources