Correlation: Firewall Allowed Traffic + Azure AD Sign-In from Same IP

13 CSL Firewall AAD Sign In Correlation

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

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:

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.
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.
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.
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.
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.
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

Released: March 2, 2026

Tables

CommonSecurityLogSigninLogs

Keywords

FirewallAzureADIPSignInDeviceVendorDeviceActionDestinationIPSourceIPSentBytesUserPrincipalNameAppDisplayNameLocationRiskLevelDuringSignIn

Operators

letwherein!in==!=ipv4_is_privatesummarizecountmake_setsumbyagoisnotemptyjoinkindonprojectorder bydesc

Severity

High

Tactics

InitialAccessDefenseEvasion

MITRE Techniques

T1078

Frequency: 1h

Period: 1d

Actions

GitHub

KQL Search