Failed service logon attempt by user account with available AuditData

id: 22f33a4c-e60f-4817-bbfe-9e2ed33cb596
name: Failed service logon attempt by user account with available AuditData
description: |
  'User account failed to logon in current period. Excludes Windows Sign in attempts and limits to only more than 10 failed logons or 3 different IPs used. Results may indicate a potential malicious use of an account that is rarely used.'
description_detailed: |
  'User account failed to logon in current period (default last 1 day). Excludes Windows Sign in attempts due to noise and limits to only more than 10 failed logons or 3 different IPs used.
  Additionally, Azure Audit Log data from the last several days(default 7 days) related to the given UserPrincipalName will be joined if available.
  This can help to understand any events for this same user related to User or Group Management.
  Results may indicate a potential malicious use of an account that is rarely used. It is possible this is an account that is new or newly enabled.
  The associated Azure Audit data should help determine any recent changes to this account and may help you understand why the logons are failing.
  Receiving no results indicates that there were no less than 10 failed logons or that the Auditlogs related to this UserPrincipalName in the default 7 days.'
requiredDataConnectors:
  - connectorId: AzureActiveDirectory
    dataTypes:
     - SigninLogs
     - AuditLogs
tactics:
  - CredentialAccess
relevantTechniques:
  - T1110
query: |

  let starttime = todatetime('{{StartTimeISO}}');
  let endtime = todatetime('{{EndTimeISO}}');
  let lookback = totimespan((endtime-starttime)*7);
  let failLimit = 10;
  let ipLimit = 3;
  let failedSignins = SigninLogs
  | where TimeGenerated between(starttime..endtime)
  | where ResultType != "0" and AppDisplayName != "Windows Sign In"
  | extend UserPrincipalName = tolower(UserPrincipalName)
  | extend CityState = strcat(tostring(LocationDetails.city),"|", tostring(LocationDetails.state))
  | extend Result = strcat(ResultType,"-",ResultDescription)
  | summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), DistinctIPAddressCount = dcount(IPAddress), IPAddresses = makeset(IPAddress),
  CityStates = makeset(CityState), DistinctResultCount = dcount(Result), Results = makeset(Result), AppDisplayNames = makeset(AppDisplayName),
  FailedLogonCount = count() by Type, OperationName, Category, UserPrincipalName = tolower(UserPrincipalName), ClientAppUsed, Location, CorrelationId
  | project Type, StartTimeUtc, EndTimeUtc, OperationName, Category, UserPrincipalName, AppDisplayNames, DistinctIPAddressCount, IPAddresses, DistinctResultCount,
  Results, FailedLogonCount, Location, CityStates
  | where FailedLogonCount >= failLimit or DistinctIPAddressCount >= ipLimit
  | extend Activity = pack("IPAddresses", IPAddresses, "AppDisplayNames", AppDisplayNames, "Results", Results, "Location", Location, "CityStates", CityStates)
  | project Type, StartTimeUtc, EndTimeUtc, OperationName, Category, UserPrincipalName, FailedLogonCount, DistinctIPAddressCount, DistinctResultCount, Activity
  | extend AccountCustomEntity = UserPrincipalName;
  let accountMods = AuditLogs | where TimeGenerated >= ago(lookback)
  | where Category == "UserManagement" or Category == "GroupManagement"
  | extend ModProps = TargetResources.[0].modifiedProperties
  | extend InitiatedBy = case(
  isnotempty(tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)), tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName),
  isnotempty(tostring(parse_json(tostring(InitiatedBy.app)).displayName)), tostring(parse_json(tostring(InitiatedBy.app)).displayName),
  "")
  | extend UserPrincipalName = tolower(tostring(TargetResources.[0].userPrincipalName))
  | mvexpand ModProps
  | extend PropertyName = tostring(ModProps.displayName), oldValue = tostring(ModProps.oldValue), newValue = tostring(ModProps.newValue)
  | extend ModifiedProps = pack("PropertyName",PropertyName,"oldValue",oldValue,"newValue",newValue)
  | summarize StartTimeUtc = min(TimeGenerated), EndTimeUtc = max(TimeGenerated), Activity = make_bag(ModifiedProps) by Type, InitiatedBy, UserPrincipalName, Category, OperationName, CorrelationId, Id
  | extend AccountCustomEntity = UserPrincipalName;
  // Gather only Audit data for UserPrincipalNames that we have Audit data for
  let accountNameOnly = failedSignins | project UserPrincipalName;
  let auditMods = accountNameOnly
  | join kind= innerunique (
  accountMods
  ) on UserPrincipalName;
  let availableAudits = auditMods | project UserPrincipalName;
  let signinsWithAudit = availableAudits
  | join kind= innerunique (
  failedSignins
  ) on UserPrincipalName;
  // Union the Current Signin failures so we do not lose them with the Auditing data we do have
  let activity = (union isfuzzy=true
  signinsWithAudit, auditMods)
  | order by StartTimeUtc, UserPrincipalName;
  activity
  | project StartTimeUtc, EndTimeUtc, DataType = Type, Category, OperationName, UserPrincipalName, InitiatedBy, Activity, FailedLogonCount, DistinctIPAddressCount, DistinctResultCount, CorrelationId, Id
  | order by UserPrincipalName, StartTimeUtc
  | extend timestamp = StartTimeUtc, AccountCustomEntity = UserPrincipalName
version: 1.0.1
metadata:
    source:
        kind: Community
    author:
        name: Shain
    support:
        tier: Community
    categories:
        domains: [ "Security - Other", "Identity" ]