✄𐘗
text code block:
✄𐘗from logs-azure.signinlogs-*, logs-azure.graphactivitylogs-* metadata _id, _version, _index
| where
    (data_stream.dataset == "azure.signinlogs"
     and source.`as`.organization.name != "MICROSOFT-CORP-MSN-AS-BLOCK"
     and azure.signinlogs.properties.session_id is not null)
    or
    (data_stream.dataset == "azure.graphactivitylogs"
     and source.`as`.organization.name != "MICROSOFT-CORP-MSN-AS-BLOCK"
     and azure.graphactivitylogs.properties.c_sid is not null)

| eval
    Esql.azure_signinlogs_properties_session_id_coalesce = coalesce(azure.signinlogs.properties.session_id, azure.graphactivitylogs.properties.c_sid),
    Esql.azure_signinlogs_properties_user_id_coalesce = coalesce(azure.signinlogs.properties.user_id, azure.graphactivitylogs.properties.user_principal_object_id),
    Esql.azure_signinlogs_properties_app_id_coalesce = coalesce(azure.signinlogs.properties.app_id, azure.graphactivitylogs.properties.app_id),
    Esql.source_ip = source.ip,
    Esql.@timestamp = @timestamp,
    Esql.event_type_case = case(
        data_stream.dataset == "azure.signinlogs", "signin",
        data_stream.dataset == "azure.graphactivitylogs", "graph",
        "other"
    ),
    Esql.signin_source_asn = case(data_stream.dataset == "azure.signinlogs", source.`as`.organization.name, null),
    Esql.graph_source_asn = case(data_stream.dataset == "azure.graphactivitylogs", source.`as`.organization.name, null)

| where Esql.azure_signinlogs_properties_app_id_coalesce not in (
    "4354e225-50c9-4423-9ece-2d5afd904870",  // Augmentation Loop
    "cc15fd57-2c6c-4117-a88c-83b1d56b4bbe",  // Microsoft Teams Services
    "ecd6b820-32c2-49b6-98a6-444530e5a77a",  // Microsoft Edge [Community Contributed]
    "e8be65d6-d430-4289-a665-51bf2a194bda",  // Microsoft 365 App Catalog Services
    "ab9b8c07-8f02-4f72-87fa-80105867a763",  // OneDrive SyncEngine
    "394866fc-eedb-4f01-8536-3ff84b16be2a",  // Microsoft People Cards Service
    "66a88757-258c-4c72-893c-3e8bed4d6899",  // Office 365 Search Service
    "9ea1ad79-fdb6-4f9a-8bc3-2b70f96e34c7",  // Bing
    "d7b530a4-7680-4c23-a8bf-c52c121d2e87",  // Microsoft Edge Enterprise New Tab Page [Community Contributed]
    "6f7e0f60-9401-4f5b-98e2-cf15bd5fd5e3",  // Microsoft Application Command Service [Community Contributed]
    "52c2e0b5-c7b6-4d11-a89c-21e42bcec444",  // Graph Files Manager
    "27922004-5251-4030-b22d-91ecd9a37ea4",  // Outlook Mobile
    "bb893c22-978d-4cd4-a6f7-bb6cc0d6e6ce",  // Olympus [Community Contributed]
    "26a7ee05-5602-4d76-a7ba-eae8b7b67941",  // Windows Search
    "00000007-0000-0000-c000-000000000000",  // Dataverse
    "6bc3b958-689b-49f5-9006-36d165f30e00",  // Teams CMD Services Artifacts
    "0ec893e0-5785-4de6-99da-4ed124e5296c",  // Office UWP PWA [Community Contributed]
    "fc108d3f-543d-4374-bbff-c7c51f651fe5",  // Zoom
    "01fc33a7-78ba-4d2f-a4b7-768e336e890e",  // MS PIM
    "7ab7862c-4c57-491e-8a45-d52a7e023983"   // Power Automate / Logic Apps Graph Connector
    ) and Esql.signin_source_asn IS NOT NULL and Esql.graph_source_asn IS NOT NULL

| keep
    Esql.azure_signinlogs_properties_session_id_coalesce,
    Esql.source_ip,
    Esql.@timestamp,
    Esql.event_type_case,
    Esql.azure_signinlogs_properties_user_id_coalesce,
    Esql.azure_signinlogs_properties_app_id_coalesce,
    Esql.signin_source_asn,
    Esql.graph_source_asn,
    source.`as`.organization.name,
    user_agent.original,
    url.original,
    azure.graphactivitylogs.properties.scopes,
    azure.signinlogs.properties.user_principal_name

| stats
    Esql.azure_signinlogs_properties_user_id_coalesce_values = values(Esql.azure_signinlogs_properties_user_id_coalesce),
    Esql.azure_signinlogs_properties_session_id_coalesce_values = values(Esql.azure_signinlogs_properties_session_id_coalesce),
    Esql_priv.azure_signinlogs_properties_user_principal_name_values = values(azure.signinlogs.properties.user_principal_name),
    Esql.source_ip_values = values(Esql.source_ip),
    Esql.source_ip_count_distinct = count_distinct(Esql.source_ip),
    Esql.source_as_organization_name_values = values(source.`as`.organization.name),
    Esql.source_as_organization_name_count_distinct = count_distinct(source.`as`.organization.name),
    Esql.signin_source_asn_values = values(Esql.signin_source_asn),
    Esql.signin_source_asn_count_distinct = count_distinct(Esql.signin_source_asn),
    Esql.graph_source_asn_values = values(Esql.graph_source_asn),
    Esql.graph_source_asn_count_distinct = count_distinct(Esql.graph_source_asn),
    Esql.user_agent_original_values = values(user_agent.original),
    Esql.azure_signinlogs_properties_app_id_coalesce_values = values(Esql.azure_signinlogs_properties_app_id_coalesce),
    Esql.azure_signinlogs_properties_app_id_coalesce_count_distinct = count_distinct(Esql.azure_signinlogs_properties_app_id_coalesce),
    Esql.event_type_case_values = values(Esql.event_type_case),
    Esql.event_type_case_count_distinct = count_distinct(Esql.event_type_case),
    Esql.signin_time_min = min(case(Esql.event_type_case == "signin", Esql.@timestamp, null)),
    Esql.graph_time_min = min(case(Esql.event_type_case == "graph", Esql.@timestamp, null)),
    Esql.url_original_values = values(url.original),
    Esql.azure_graphactivitylogs_properties_scopes_values = values(azure.graphactivitylogs.properties.scopes),
    Esql.event_count = count()
  by
    Esql.azure_signinlogs_properties_session_id_coalesce,
    Esql.azure_signinlogs_properties_app_id_coalesce,
    Esql.azure_signinlogs_properties_user_id_coalesce

| eval
    Esql.event_signin_to_graph_delay_minutes_date_diff = date_diff("minutes", Esql.signin_time_min, Esql.graph_time_min),
    Esql.event_signin_to_graph_delay_days_date_diff = date_diff("days", Esql.signin_time_min, Esql.graph_time_min)

| where
    Esql.event_type_case_count_distinct > 1 and
    Esql.source_ip_count_distinct > 1 and
    Esql.source_as_organization_name_count_distinct > 1 and
    Esql.azure_signinlogs_properties_app_id_coalesce_count_distinct == 1 and
    Esql.signin_time_min is not null and
    Esql.graph_time_min is not null and
    Esql.event_signin_to_graph_delay_minutes_date_diff > 0 and
    Esql.event_signin_to_graph_delay_days_date_diff == 0 and
    (Esql.signin_source_asn_count_distinct + Esql.graph_source_asn_count_distinct) == Esql.source_as_organization_name_count_distinct