Anomalous sign-in location by user account and authenticating application - with sign-in details

id: 7f6e8f14-62fa-4ce6-a490-c07f1d9888ba
name: Anomalous sign-in location by user account and authenticating application - with sign-in details
description: |
  'This query examines Microsoft Entra ID sign-ins and identifies anomalous changes in a user's location profile. A variation joins results back onto the original sign-in data to review the location set with each identified user in tabular form.'
description_detailed: |
  'This query over Microsoft Entra ID sign-in considers all user sign-ins for each Azure Active 
  Directory application and picks out the most anomalous change in location profile for a user within an 
  individual application. The intent is to hunt for user account compromise, possibly via a specific application
  vector.
  This variation of the query joins the results back onto the original sign-in data to allow review of the 
  location set with each identified user in tabular form.'
requiredDataConnectors:
  - connectorId: AzureActiveDirectory
    dataTypes:
      - SigninLogs
tactics:
  - InitialAccess
relevantTechniques:
  - T1078
query: |

  SigninLogs 
  // Forces Log Analytics to recognize that the query should be run over full time range
  | extend  locationString= strcat(tostring(LocationDetails["countryOrRegion"]), "/", 
  tostring(LocationDetails["state"]), "/", tostring(LocationDetails["city"]), ";") 
  | project TimeGenerated, AppDisplayName , UserPrincipalName, locationString 
  // Create time series 
  | make-series dLocationCount = dcount(locationString) on TimeGenerated step 1d 
  by UserPrincipalName, AppDisplayName 
  // Compute best fit line for each entry 
  | extend (RSquare,Slope,Variance,RVariance,Interception,LineFit)=series_fit_line(dLocationCount) 
  // Chart the 3 most interesting lines  
  // A 0-value slope corresponds to an account being completely stable over time for a given Azure Active Directory application
  | top 3 by Slope desc  
  // Extract the set of locations for each top user:
  | join kind=inner (SigninLogs
  | extend  locationString= strcat(tostring(LocationDetails["countryOrRegion"]), "/", 
  tostring(LocationDetails["state"]), "/", tostring(LocationDetails["city"]), ";")
  | summarize locationList = makeset(locationString), threeDayWindowLocationCount=dcount(locationString) by AppDisplayName, UserPrincipalName, 
  timerange=bin(TimeGenerated, 3d)) on AppDisplayName, UserPrincipalName
  | order by UserPrincipalName, timerange asc
  | project timerange, AppDisplayName , UserPrincipalName, threeDayWindowLocationCount, locationList 
  | order by AppDisplayName, UserPrincipalName, timerange asc
  | extend timestamp = timerange, AccountCustomEntity = UserPrincipalName

entityMappings:
  - entityType: Account
    fieldMappings:
      - identifier: FullName
        columnName: AccountCustomEntity
version: 1.0.1
metadata:
    source:
        kind: Community
    author:
        name: Shain
    support:
        tier: Community
    categories:
        domains: [ "Security - Other", "Identity" ]