KQL Search

Query Details

14 CSL Firewall Security Alert IOC Correlation

Query

id: b4c5d6e7-f8a9-4b0c-1d2e-3f4a5b6c7d8e
name: "Correlation: Firewall Traffic + Active Security Alert - Shared IOC IP"
version: 1.0.0
kind: Scheduled
description: |
  Identifies IP addresses that appear in both active High/Medium Sentinel security alerts
  (within the last 7 days) and allowed firewall traffic (last 24 hours). Cross-signal
  correlation between alert IOCs and live firewall activity confirms that a flagged IP is
  still actively communicating through the network, elevating response priority.
  MITRE ATT&CK: T1078 (Valid Accounts), T1071 (Application Layer Protocol)
severity: High
requiredDataConnectors:
  - connectorId: CommonSecurityEvents
    dataTypes:
      - CommonSecurityLog
queryFrequency: 1h
queryPeriod: 7d
triggerOperator: gt
triggerThreshold: 0
tactics:
  - InitialAccess
  - CommandAndControl
relevantTechniques:
  - T1078
  - T1071
query: |
  let AlertIPs =
      SecurityAlert
      | where TimeGenerated > ago(7d)
      | where AlertSeverity in ("High", "Medium")
      | mv-expand todynamic(Entities)
      | where Entities.Type == "ip"
      | extend AlertIP = tostring(Entities.Address)
      | where isnotempty(AlertIP)
      | summarize
          AlertCount    = count(),
          AlertNames    = make_set(AlertName, 10),
          Severities    = make_set(AlertSeverity, 3),
          Products      = make_set(ProductName, 5)
        by AlertIP;
  CommonSecurityLog
  | where TimeGenerated > ago(1d)
  | where DeviceVendor in ("Fortinet", "Palo Alto Networks", "Zscaler")
  | where isnotempty(SourceIP) or isnotempty(DestinationIP)
  | extend CheckIP = iff(ipv4_is_private(DestinationIP) == false, DestinationIP, SourceIP)
  | summarize
      FW_HitCount     = count(),
      FW_Actions      = make_set(DeviceAction, 5),
      FW_InternalIPs  = make_set(SourceIP, 10),
      FW_Vendors      = make_set(DeviceVendor),
      FW_FirstSeen    = min(TimeGenerated),
      FW_LastSeen     = max(TimeGenerated)
    by CheckIP
  | join kind=inner AlertIPs on $left.CheckIP == $right.AlertIP
  | project
      IOC_IP        = CheckIP,
      AlertCount,
      AlertNames,
      Severities,
      Products,
      FW_HitCount,
      FW_Actions,
      FW_InternalIPs,
      FW_Vendors,
      FW_FirstSeen,
      FW_LastSeen
  | order by AlertCount desc
entityMappings:
  - entityType: IP
    fieldMappings:
      - identifier: Address
        columnName: IOC_IP
customDetails:
  AlertCount: AlertCount
  FW_HitCount: FW_HitCount
alertDetailsOverride:
  alertDisplayNameFormat: "Active IOC in Firewall Traffic - {{IOC_IP}} ({{AlertCount}} alerts)"
  alertDescriptionFormat: "IP {{IOC_IP}} is referenced in {{AlertCount}} Sentinel alerts and generated {{FW_HitCount}} live firewall events. Confirmed active IOC still communicating through the perimeter."
incidentConfiguration:
  createIncident: true
  groupingConfiguration:
    enabled: true
    reopenClosedIncident: false
    lookbackDuration: PT12H
    matchingMethod: Selected
    groupByEntities:
      - IP
    groupByAlertDetails: []
    groupByCustomDetails: []

Explanation

This query is designed to identify IP addresses that are both part of active security alerts and have been allowed through the firewall recently. Here's a simple breakdown:

  1. Purpose: The query looks for IP addresses that are flagged in high or medium severity security alerts within the last 7 days and have also been seen in allowed firewall traffic in the last 24 hours. This helps in identifying potentially malicious IPs that are still actively communicating with the network, which could require immediate attention.

  2. Data Sources: It uses data from security alerts and firewall logs from vendors like Fortinet, Palo Alto Networks, and Zscaler.

  3. Process:

    • It first gathers IPs from security alerts that are considered high or medium severity.
    • Then, it checks firewall logs to see if these IPs have been involved in any traffic in the last day.
    • It correlates these two datasets to find IPs that appear in both, indicating ongoing activity.

  4. Output: The query outputs a list of these IPs along with details such as the number of alerts they are associated with, the actions recorded in the firewall logs, and the vendors involved.

  5. Severity and Response: The severity of this query is marked as high, suggesting that the identified IPs should be prioritized for investigation. It also creates incidents for these findings to ensure they are addressed promptly.

  6. MITRE ATT&CK Techniques: The query is associated with techniques T1078 (Valid Accounts) and T1071 (Application Layer Protocol), indicating the types of tactics these IPs might be using.

Overall, this query helps security teams quickly identify and respond to IPs that are actively involved in suspicious activities, enhancing the organization's security posture.

Details

David Alonso profile picture

David Alonso

Released: March 2, 2026

Tables

SecurityAlertCommonSecurityLog

Keywords

SecurityAlertCommonSecurityLogDeviceVendorSourceIPDestinationIPAlertIPCheckIPAlertCountAlertNamesSeveritiesProductsFW_HitCountFW_ActionsFW_InternalIPsFW_VendorsFW_FirstSeenFW_LastSeenIOC_IPAddress

Operators

let|whereinmv-expandtodynamicextendtostringisnotemptysummarizemake_setbyagoiffipv4_is_privatejoinkindonprojectorder bydesc==>=orandminmax

Actions