Inactive or new account signins

id: 847c2652-547d-4d5f-9b71-d2f8d81eac62
name: Inactive or new account signins
description: |
  'Query for new sign-ins from stale/inactive accounts. UEBA filters based on ActivityInsights. Results for accounts created in the last 7 days are filtered out.'
description_detailed: |
  'Query for accounts seen signing in for the first time. These could be associated with stale/inactive accounts that ought to have been deleted 
  but were not and may have been subsequently compromised.
  UEBA is used to filter out based on ActivityInsights where we see certain First Time User events identified as true.
  Results for user accounts created in the last 7 days are filtered out.'
requiredDataConnectors:
  - connectorId: AzureActiveDirectory
    dataTypes:
      - SigninLogs
      - AuditLogs
  - connectorId: BehaviorAnalytics
    dataTypes:
      - BehaviorAnalytics
tactics:
  - InitialAccess
relevantTechniques:
  - T1078
query: |

  let starttime = todatetime('{{StartTimeISO}}');
  let endtime = todatetime('{{EndTimeISO}}');
  let lookback = starttime - 14d;
  let midtime = starttime - 7d;
  let SigninsSummary = SigninLogs
  | where TimeGenerated between(starttime..endtime)
  // successful sign-in only
  | where ResultType == 0
  | summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), SigninLogs_ItemIds = make_set(_ItemId), loginCountToday=count() by UserPrincipalName, UserId, UserType, IPAddress
  | join kind=leftanti (
     SigninLogs
     // historical successful sign-in
     | where TimeGenerated between(lookback..starttime)
     | where ResultType == 0
     | summarize by UserId
  ) on UserId;
  // need to help BehaviorAnalytics query to limit only to Signins we are interested in
  let onlyInactive = SigninsSummary | summarize make_set(UserPrincipalName);
  let SigninsWithUEBA =
  BehaviorAnalytics
  | where TimeGenerated between(starttime..endtime)
  | where ActionType in ('Sign-in','InteractiveLogon')
  | where UserPrincipalName in~ (onlyInactive)
  | extend ActivityInsights = parse_xml(ActivityInsights)
  // only looked where FirstTimeUser items are True
  | where ActivityInsights matches regex '\"FirstTimeUser([A-Za-z0-9]+)\":\"True\"'
  // only exclude when Uncommon Among Peers is false as this helps remove expected first time usage, exception is we always show FirstTimeUserConnectedFromCountry == True
  // also always keep InvestigationPriority if 1 or more
  | where (not(ActivityInsights.FirstTimeUserUsedApp == 'True' and ActivityInsights.AppUncommonlyUsedAmongPeers == 'False') or InvestigationPriority > 0)
  | where (not(ActivityInsights.FirstTimeUserConnectedViaBrowser == 'True' and ActivityInsights.BrowserUncommonlyUsedAmongPeers == 'False') or InvestigationPriority > 0)
  | where (not(ActivityInsights.FirstTimeUserAccessedResource == 'True' and ActivityInsights.ResourceUncommonlyUsedAmongPeers == 'False') or InvestigationPriority > 0)
  // for ISP, it makes more sense to exclude if Uncommon in Tenant or Uncommon among peers is false.
  | where (not(ActivityInsights.FirstTimeUserConnectedViaISP == 'True' and (ActivityInsights.ISPUncommonlyUsedInTenant == 'False' or ActivityInsights.ISPUncommonlyUsedAmongPeers == 'False')) or InvestigationPriority > 0)
  | extend UEBA_Insights = pack_dictionary("TimeGenerated", TimeGenerated, "ActivityInsights", ActivityInsights, "UsersInsights", UsersInsights, "DevicesInsights", DevicesInsights)
  | summarize UEBA_ItemIds = make_set(_ItemId), UEBA_SourceRecordIds = make_set(SourceRecordId), UEBA_Insights = make_set(UEBA_Insights) by
  UEBA_UserPrincipalName = UserPrincipalName, JoinedWithType = Type, UEBA_ActionType = ActionType, UEBA_SourceIPAddress = SourceIPAddress, UEBA_SourceIPLocation = SourceIPLocation, UEBA_InvestigationPriority = InvestigationPriority
  | extend UEBA_Info = pack_dictionary("UEBA_Insights", UEBA_Insights, "UEBA_ItemIds", UEBA_ItemIds, "UEBA_SourceRecordIds", UEBA_SourceRecordIds)
  | project-away UEBA_ItemIds, UEBA_SourceRecordIds, UEBA_Insights
  | join kind=inner ( 
    SigninsSummary
  ) on $left.UEBA_UserPrincipalName == $right.UserPrincipalName, $left.UEBA_SourceIPAddress == $right.IPAddress
  | project-reorder StartTime, EndTime, UserPrincipalName, UserId, IPAddress, UserType, loginCountToday, JoinedWithType
  ;
  SigninsWithUEBA 
  | join kind= leftanti (
     // filter out newly created user accounts from last 7 days
     AuditLogs
     | where TimeGenerated between(midtime..endtime)
     | where OperationName == "Add user"
     | summarize by NewUserId = tostring(TargetResources[0].id)
  ) on $left.UserId == $right.NewUserId
  | extend timestamp = StartTime, AccountCustomEntity = UserPrincipalName, IPCustomEntity = IPAddress

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