Failed AzureAD logons but success logon to host

//Adjust this threshold to fit the environment
let signin_threshold = 5;
//Make a list of all IPs with failed signins to AAD above our threshold
let aadFunc = (tableName:string){
let suspicious_signins =
table(tableName)
| where ResultType !in ("0", "50125", "50140")
| where IPAddress !in ('127.0.0.1', '::1', '')
| summarize count() by IPAddress
| where count_ > signin_threshold
| summarize make_set(IPAddress);
//See if any of these IPs have sucessfully logged into *nix hosts
let linux_logons =
Syslog
| where Facility contains "auth" and ProcessName != "sudo"
| where SyslogMessage has "Accepted"
| extend SourceIP = extract("(([0-9]{1,3})\\.([0-9]{1,3})\\.([0-9]{1,3})\\.(([0-9]{1,3})))",1,SyslogMessage)
| where SourceIP in (suspicious_signins)
| extend Reason = "Multiple failed AAD logins from IP address"
| project TimeGenerated, Computer, HostIP, IpAddress = SourceIP, SyslogMessage, Facility, ProcessName, Reason;
//See if any of these IPs have sucessfully logged into Windows hosts
let win_logons = (union isfuzzy=true
(SecurityEvent
| where EventID == 4624
| where LogonType in (10, 7, 3)
| where IpAddress != "-"
| where IpAddress in (suspicious_signins)
| extend Reason = "Multiple failed AAD logins from IP address"
| project TimeGenerated, Account, AccountType, Computer, Activity, EventID, LogonProcessName, IpAddress, LogonTypeName, TargetUserSid, TargetUserName, TargetDomainName, _ResourceId, Reason
),
(WindowsEvent
| where EventID == 4624 and has_any_ipv4(EventData, toscalar(suspicious_signins))
| extend LogonType = tostring(EventData.LogonType)
| where LogonType in (10, 7, 3)
| extend  IpAddress = tostring(EventData.IpAddress)
| where IpAddress != "-"
| where IpAddress in (suspicious_signins)
| extend Reason = "Multiple failed AAD logins from IP address"
| extend Activity = "4624 - An account was successfully logged on."
| extend TargetUserName = tostring(EventData.TargetUserName), TargetDomainName = tostring(EventData.TargetDomainName)
| extend Account =  strcat(TargetDomainName,"\\", TargetUserName)
| extend TargetUserSid = tostring(EventData.TargetUserSid)
| extend TargetAccount = strcat(EventData.TargetDomainName,"\\", EventData.TargetUserName)
| extend AccountType =case(Account endswith "$" or TargetUserSid in ("S-1-5-18", "S-1-5-19", "S-1-5-20"), "Machine", isempty(TargetUserSid), "", "User")
| extend LogonProcessName = tostring(EventData.LogonProcessName)
| project TimeGenerated, Account, AccountType, Computer, Activity, EventID, LogonProcessName, IpAddress, TargetUserSid, TargetUserName, TargetDomainName, _ResourceId, Reason
)
);
union isfuzzy=true linux_logons,win_logons
| extend timestamp = TimeGenerated
| extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
| extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex+1), Computer)
};
let aadSignin = aadFunc("SigninLogs");
let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
union isfuzzy=true aadSignin, aadNonInt

OriginalUri: https://github.com/Azure/Azure-Sentinel/blob/master/Detections/MultipleDataSources/AADHostLoginCorrelation.yaml
severity: Medium
queryFrequency: 1d
version: 1.3.2
metadata:
  categories:
    domains:
    - Security - Others
    - Identity
  source:
    kind: Community
  author:
    name: Microsoft Security Research
  support:
    tier: Community
entityMappings:
- fieldMappings:
  - identifier: FullName
    columnName: Account
  - identifier: Name
    columnName: TargetUserName
  - identifier: NTDomain
    columnName: TargetDomainName
  entityType: Account
- fieldMappings:
  - identifier: FullName
    columnName: Computer
  - identifier: HostName
    columnName: HostName
  - identifier: NTDomain
    columnName: HostNameDomain
  entityType: Host
- fieldMappings:
  - identifier: AzureID
    columnName: _ResourceId
  entityType: Host
- fieldMappings:
  - identifier: Address
    columnName: IpAddress
  entityType: IP
triggerOperator: gt
query: |
  //Adjust this threshold to fit the environment
  let signin_threshold = 5;
  //Make a list of all IPs with failed signins to AAD above our threshold
  let aadFunc = (tableName:string){
  let suspicious_signins =
  table(tableName)
  | where ResultType !in ("0", "50125", "50140")
  | where IPAddress !in ('127.0.0.1', '::1', '')
  | summarize count() by IPAddress
  | where count_ > signin_threshold
  | summarize make_set(IPAddress);
  //See if any of these IPs have sucessfully logged into *nix hosts
  let linux_logons =
  Syslog
  | where Facility contains "auth" and ProcessName != "sudo"
  | where SyslogMessage has "Accepted"
  | extend SourceIP = extract("(([0-9]{1,3})\\.([0-9]{1,3})\\.([0-9]{1,3})\\.(([0-9]{1,3})))",1,SyslogMessage)
  | where SourceIP in (suspicious_signins)
  | extend Reason = "Multiple failed AAD logins from IP address"
  | project TimeGenerated, Computer, HostIP, IpAddress = SourceIP, SyslogMessage, Facility, ProcessName, Reason;
  //See if any of these IPs have sucessfully logged into Windows hosts
  let win_logons = (union isfuzzy=true
  (SecurityEvent
  | where EventID == 4624
  | where LogonType in (10, 7, 3)
  | where IpAddress != "-"
  | where IpAddress in (suspicious_signins)
  | extend Reason = "Multiple failed AAD logins from IP address"
  | project TimeGenerated, Account, AccountType, Computer, Activity, EventID, LogonProcessName, IpAddress, LogonTypeName, TargetUserSid, TargetUserName, TargetDomainName, _ResourceId, Reason
  ),
  (WindowsEvent
  | where EventID == 4624 and has_any_ipv4(EventData, toscalar(suspicious_signins))
  | extend LogonType = tostring(EventData.LogonType)
  | where LogonType in (10, 7, 3)
  | extend  IpAddress = tostring(EventData.IpAddress)
  | where IpAddress != "-"
  | where IpAddress in (suspicious_signins)
  | extend Reason = "Multiple failed AAD logins from IP address"
  | extend Activity = "4624 - An account was successfully logged on."
  | extend TargetUserName = tostring(EventData.TargetUserName), TargetDomainName = tostring(EventData.TargetDomainName)
  | extend Account =  strcat(TargetDomainName,"\\", TargetUserName)
  | extend TargetUserSid = tostring(EventData.TargetUserSid)
  | extend TargetAccount = strcat(EventData.TargetDomainName,"\\", EventData.TargetUserName)
  | extend AccountType =case(Account endswith "$" or TargetUserSid in ("S-1-5-18", "S-1-5-19", "S-1-5-20"), "Machine", isempty(TargetUserSid), "", "User")
  | extend LogonProcessName = tostring(EventData.LogonProcessName)
  | project TimeGenerated, Account, AccountType, Computer, Activity, EventID, LogonProcessName, IpAddress, TargetUserSid, TargetUserName, TargetDomainName, _ResourceId, Reason
  )
  );
  union isfuzzy=true linux_logons,win_logons
  | extend timestamp = TimeGenerated
  | extend HostName = tostring(split(Computer, ".")[0]), DomainIndex = toint(indexof(Computer, '.'))
  | extend HostNameDomain = iff(DomainIndex != -1, substring(Computer, DomainIndex+1), Computer)
  };
  let aadSignin = aadFunc("SigninLogs");
  let aadNonInt = aadFunc("AADNonInteractiveUserSignInLogs");
  union isfuzzy=true aadSignin, aadNonInt  
queryPeriod: 1d
kind: Scheduled
id: 8ee967a2-a645-4832-85f4-72b635bcb3a6
relevantTechniques:
- T1078
- T1110
description: |
  'Identifies a list of IP addresses with a minimum number (default of 5) of failed logon attempts to Microsoft Entra ID.
  Uses that list to identify any successful remote logons to hosts from these IPs within the same timeframe.'  
requiredDataConnectors:
- dataTypes:
  - SigninLogs
  connectorId: AzureActiveDirectory
- dataTypes:
  - AADNonInteractiveUserSignInLogs
  connectorId: AzureActiveDirectory
- dataTypes:
  - SecurityEvent
  connectorId: SecurityEvents
- dataTypes:
  - Syslog
  connectorId: Syslog
- dataTypes:
  - SecurityEvents
  connectorId: WindowsSecurityEvents
- dataTypes:
  - WindowsEvent
  connectorId: WindowsForwardedEvents
triggerThreshold: 0
tactics:
- InitialAccess
- CredentialAccess
name: Failed AzureAD logons but success logon to host