Unfamiliar Signin Correlation with AzurePortal Signin Attempts and AuditLogs

id: 6962473c-bcb8-421d-a0db-826078cad280
name: Unfamiliar Signin Correlation with AzurePortal Signin Attempts and AuditLogs
description: |
  'This query looks for unfamiliar Sign-in's thats not seen recently for the given user
   with azure portal login attempts and audit logs to help detect and reduce the analysis timeline for defenders'
requiredDataConnectors:
  - connectorId: AzureActiveDirectory
    dataTypes:
      - SigninLogs
  - connectorId: AzureActiveDirectory
    dataTypes:
      - AADNonInteractiveUserSignInLogs
  - connectorId: AzureSecurityCenter
    dataTypes:
      - SecurityAlert (ASC)
  - connectorId: AzureActiveDirectory
    dataTypes:
      - AuditLogs
tactics:
  - InitialAccess
  - Impact
relevantTechniques:
  - T1190
  - T1078
query: |
  SecurityAlert 
  | where AlertName == "Unfamiliar sign-in properties"
  | extend Extprop = parsejson(Entities)
  | mv-expand Extprop
  | extend Extprop = parsejson(Extprop)
  | extend CmdLine = iff(Extprop['Type']=="process", Extprop['CommandLine'], '')
  | extend File = iff(Extprop['Type']=="file", Extprop['Name'], '')
  | extend Account = Extprop['Name']
  | extend Domain = Extprop['UPNSuffix']
  | extend Account = iif(isnotempty(Domain) and Extprop['Type']=="account", tolower(strcat(Account, "@", Domain)), iif(Extprop['Type']=="account", tolower(Account), ""))
  | extend IpAddress = iff(Extprop["Type"] == "ip",Extprop['Address'], '')
  | extend Process = iff(isnotempty(CmdLine), CmdLine, File)
  | summarize count() by AlertName,AlertSeverity,CompromisedEntity,Account
  | join kind=inner
  (
  SigninLogs
  | where AppDisplayName == "Azure Portal"
  // 50126 - Invalid username or password, or invalid on-premises username or password.
  | where ResultType == "50126"
  | extend OS = tostring(DeviceDetail.operatingSystem), Browser = tostring(DeviceDetail.browser)
  | extend StatusCode = tostring(Status.errorCode), StatusDetails = tostring(Status.additionalDetails)
  | extend State = tostring(LocationDetails.state), City = tostring(LocationDetails.city)
  | project  UserDisplayName, UserPrincipalName, AppDisplayName, ResultType, ResultDescription, StatusCode, StatusDetails, Location, State,IPAddress
  | extend AccountCustomEntity = UserPrincipalName
  ) on $left.Account == $right.AccountCustomEntity
  | project AccountCustomEntity = tolower(AccountCustomEntity),State,StatusCode,StatusDetails,IPAddress,ResultDescription,AppDisplayName,Location,AlertName
  | join kind=inner
  (
  AuditLogs
  | where Category =~ "RoleManagement"
  | where OperationName has_any ("Add member to role","Add member to role in PIM requested (permanent)")
  | extend IpAddress = case(
    isnotempty(tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)) and tostring(parse_json(tostring(InitiatedBy.user)).ipAddress) != 'null', tostring(parse_json(tostring(InitiatedBy.user)).ipAddress), 
    isnotempty(tostring(parse_json(tostring(InitiatedBy.app)).ipAddress)) and tostring(parse_json(tostring(InitiatedBy.app)).ipAddress) != 'null', tostring(parse_json(tostring(InitiatedBy.app)).ipAddress),
    'Not Available')
  | extend InitiatedBy = iff(isnotempty(tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName)), 
    tostring(parse_json(tostring(InitiatedBy.user)).userPrincipalName), tostring(parse_json(tostring(InitiatedBy.app)).displayName)), UserRoles = tostring(parse_json(tostring(InitiatedBy.user)).ipAddress)
  | extend TargetResourceName = tolower(tostring(TargetResources.[0].displayName)) 
  | project InitiatedBy = tolower(InitiatedBy), ActivityDateTime, ActivityDisplayName,IpAddress, AADOperationType, AADTenantId
  ) on $left.AccountCustomEntity == $right.InitiatedBy
  | project AccountCustomEntity,AppDisplayName,IPAddress,Location,StatusCode,StatusDetails
entityMappings:
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: IPAddress
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: AccountCustomEntity
version: 1.0.0