LoFP / microsoft teams, microsoft office, onedrive syncengine, microsoft authentication broker, outlook mobile, bing, azure portal, adibizaux, and office 365 management are omitted. the rest of the secureworks known-foci-clients.csv family (edge, onedrive, intune company portal, windows search, authenticator, sharepoint, planner, power bi, and similar) is omitted for the same reason: routine workstation or mobile graph whose microsoft 365 egress often differs from the windows sign-in ip without cookie theft. hunt those app_ids separately if harvest is already confirmed.

from logs-azure.signinlogs-*
// find successful sign-in events where a managed device exists
| where event.dataset == "azure.signinlogs"
    and azure.signinlogs.properties.status.error_code == 0
    and azure.signinlogs.properties.device_detail.device_id is not null

// filter for device sign-in events from Windows Sign-In or WAM (login session)
| eval Esql.is_device_session = azure.signinlogs.properties.app_display_name == "Windows Sign In"
    or user_agent.original == "Windows-AzureAD-Authentication-Provider/1.0"

// filter for tooling FOCI clients that can redeem a PRT (replay)
| eval Esql.is_prt_replay = azure.signinlogs.properties.app_id in (
        "04b07795-8ddb-461a-bbee-02f9e1bf7b46",  // Microsoft Azure CLI
        "1950a258-227b-4e31-a9cf-717495945fc2",  // Microsoft Azure PowerShell
        "aebc6443-996d-45c2-90f0-388ff96faa56",  // Visual Studio Code
        "14d82eec-204b-4c2f-b7e8-296a70dab67e",  // Microsoft Graph Command Line Tools
        "1b730954-1685-4b74-9bfd-dac224a7b894",  // Azure Active Directory PowerShell
        "872cd9fa-d31f-45e0-9eab-6e460a02d1f1"   // Visual Studio
    )

    // target resource are common adversary targets for access
    and azure.signinlogs.properties.resource_id in (
        "00000003-0000-0000-c000-000000000000",  // Microsoft Graph
        "00000003-0000-0ff1-ce00-000000000000",  // Office 365 SharePoint Online
        "6a9b9266-8161-4a7b-913a-a9eda19da220",  // OneDrive for Business
        "00000002-0000-0ff1-ce00-000000000000"   // Office 365 Exchange Online
    )
    and azure.signinlogs.properties.incoming_token_type == "primaryRefreshToken"
    and (
        azure.signinlogs.properties.device_detail.is_compliant == true
        or azure.signinlogs.properties.device_detail.is_managed == true
    )

// device session or PRT replay event have to exist
| where Esql.is_device_session or Esql.is_prt_replay

// aggregate entities for both device session and PRT replay events
// aggregate by user and device
| stats
    Esql.source_ip_device_values = values(source.ip) where Esql.is_device_session,
    Esql.source_ip_replay_values = values(source.ip) where Esql.is_prt_replay,
    Esql.event_count_replay = count(*) where Esql.is_prt_replay,
    Esql.event_count_device_session = count(*) where Esql.is_device_session,
    Esql.user_principal_name_values = values(azure.signinlogs.properties.user_principal_name),
    Esql.app_display_name_values = values(azure.signinlogs.properties.app_display_name) where Esql.is_prt_replay,
    Esql.resource_id_values = values(azure.signinlogs.properties.resource_id) where Esql.is_prt_replay,
    Esql.resource_display_name_values = values(azure.signinlogs.properties.resource_display_name) where Esql.is_prt_replay,
    Esql.device_display_name_values = values(azure.signinlogs.properties.device_detail.display_name),
    Esql.user_agent_original_values = values(user_agent.original) where Esql.is_prt_replay,
    Esql.device_is_compliant_values = values(azure.signinlogs.properties.device_detail.is_compliant) where Esql.is_prt_replay,
    Esql.device_is_managed_values = values(azure.signinlogs.properties.device_detail.is_managed) where Esql.is_prt_replay,
    Esql.authentication_requirement_values = values(azure.signinlogs.properties.authentication_requirement) where Esql.is_prt_replay,
    Esql.conditional_access_status_values = values(azure.signinlogs.properties.conditional_access_status) where Esql.is_prt_replay,
    Esql.earliest_timestamp = min(@timestamp),
    Esql.latest_timestamp = max(@timestamp)
  by azure.signinlogs.properties.user_id, azure.signinlogs.properties.device_detail.device_id

// filter for PRT replay events that have at least one replay IP
| where Esql.event_count_replay > 0
    and Esql.event_count_device_session > 0
    and Esql.source_ip_replay_values is not null
    and Esql.source_ip_device_values is not null

// expand the replay IP list and keep only IPs that are not in the device-session set
| mv_expand Esql.source_ip_replay_values
| where not mv_contains(Esql.source_ip_device_values, Esql.source_ip_replay_values)
| eval Esql.source_ip_replay = Esql.source_ip_replay_values,
       user.id = azure.signinlogs.properties.user_id,
       source.ip = Esql.source_ip_replay_values
| keep
    user.id,
    source.ip,
    azure.signinlogs.properties.user_id,
    azure.signinlogs.properties.device_detail.device_id,
    Esql.*