KQL Search

Query Details

13 CSL Firewall AAD Sign In Correlation

Query

id: a3b4c5d6-e7f8-4a9b-0c1d-2e3f4a5b6c7d
name: "Correlation: Firewall Allowed Traffic + Azure AD Sign-In from Same IP"
version: 1.0.0
kind: Scheduled
description: |
  Correlates firewall-allowed outbound traffic with Azure AD sign-in events sharing the same
  external IP. An IP that appears in both AAD sign-in logs and firewall sessions simultaneously
  may indicate a threat actor using the same infrastructure for credential-based cloud access
  and network-level data collection. This correlation amplifies low-confidence signals from
  each source into a high-confidence cross-vector indicator.
  MITRE ATT&CK: T1078 (Valid Accounts)
severity: High
requiredDataConnectors:
  - connectorId: CommonSecurityEvents
    dataTypes:
      - CommonSecurityLog
  - connectorId: AzureActiveDirectory
    dataTypes:
      - SigninLogs
queryFrequency: 1h
queryPeriod: 1d
triggerOperator: gt
triggerThreshold: 0
tactics:
  - InitialAccess
  - DefenseEvasion
relevantTechniques:
  - T1078
query: |
  let FirewallExternalIPs =
      CommonSecurityLog
      | where TimeGenerated > ago(1d)
      | where DeviceVendor in ("Fortinet", "Palo Alto Networks", "Zscaler")
      | where DeviceAction !in ("deny", "block", "drop", "BLOCK", "DROP")
      | where ipv4_is_private(DestinationIP) == false
      | summarize
          FW_Connections = count(),
          FW_Sources     = make_set(SourceIP, 10),
          FW_BytesSent   = sum(SentBytes),
          FW_Vendors     = make_set(DeviceVendor)
        by ExternalIP = DestinationIP;
  SigninLogs
  | where TimeGenerated > ago(1d)
  | where ResultType == 0
  | where isnotempty(IPAddress)
  | summarize
      AAD_SignInCount  = count(),
      AAD_Users        = make_set(UserPrincipalName, 20),
      AAD_AppNames     = make_set(AppDisplayName, 10),
      AAD_Countries    = make_set(Location, 5),
      AAD_RiskLevels   = make_set(RiskLevelDuringSignIn, 5)
    by IPAddress
  | join kind=inner FirewallExternalIPs on $left.IPAddress == $right.ExternalIP
  | project
      IPAddress,
      AAD_SignInCount,
      AAD_Users,
      AAD_Countries,
      AAD_RiskLevels,
      FW_Connections,
      FW_Sources,
      FW_BytesSent,
      FW_Vendors
  | order by FW_Connections desc
entityMappings:
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: IPAddress
customDetails:
  AAD_SignInCount: AAD_SignInCount
  FW_Connections: FW_Connections
alertDetailsOverride:
  alertDisplayNameFormat: "Cross-Signal IP Match - {{IPAddress}} in Firewall + AAD Sign-In"
  alertDescriptionFormat: "IP {{IPAddress}} generated {{AAD_SignInCount}} AAD sign-ins and {{FW_Connections}} firewall sessions. Multi-vector activity from a single source is a strong compromise indicator."
incidentConfiguration:
  createIncident: true
  groupingConfiguration:
    enabled: true
    reopenClosedIncident: false
    lookbackDuration: PT12H
    matchingMethod: Selected
    groupByEntities:
      - IP
    groupByAlertDetails: []
    groupByCustomDetails: []

Explanation

This query is designed to detect potential security threats by correlating data from two sources: firewall logs and Azure Active Directory (AAD) sign-in logs. Here's a simplified breakdown of what the query does:

  1. Purpose: The query identifies instances where the same external IP address is involved in both successful Azure AD sign-ins and allowed outbound traffic through firewalls. This could indicate a threat actor using the same IP for unauthorized access and data collection.

  2. Data Sources:

    • Firewall Logs: It looks at logs from firewalls by vendors like Fortinet, Palo Alto Networks, and Zscaler, focusing on allowed traffic (not blocked or denied) to external IPs.
    • Azure AD Sign-In Logs: It examines successful sign-in attempts (ResultType == 0) from external IPs.

  3. Process:

    • Firewall Data: It collects data on the number of connections, source IPs, bytes sent, and vendors for each external IP.
    • AAD Data: It gathers data on the number of sign-ins, users, applications, countries, and risk levels associated with each IP address.
    • Correlation: The query joins these two datasets on the external IP address to find matches.

  4. Output: The result is a list of IP addresses that appear in both datasets, along with details like the number of sign-ins, users involved, countries, risk levels, firewall connections, source IPs, bytes sent, and vendors.

  5. Alerting: If any matches are found, an alert is generated with a high severity level, indicating potential compromise. The alert includes details about the IP address, the number of AAD sign-ins, and firewall sessions.

  6. Incident Management: The query is configured to create incidents for these alerts, grouping them by IP address to manage related alerts together.

Overall, this query helps security teams identify and respond to potential threats by highlighting suspicious multi-vector activity from the same IP address.

Details

David Alonso profile picture

David Alonso

Released: March 2, 2026

Tables

CommonSecurityLogSigninLogs

Keywords

FirewallAzureADIPSignInDeviceVendorDeviceActionDestinationIPSourceIPSentBytesUserPrincipalNameAppDisplayNameLocationRiskLevelDuringSignIn

Operators

letwherein!in==!=ipv4_is_privatesummarizecountmake_setsumbyagoisnotemptyjoinkindonprojectorder bydesc

Actions