KQL Search

Query Details

HomeAI ToolsDevice Query

Noise Commonsecuritylog

Query

// CommonSecurityLog - Device Vendor/Product Noise Analysis
// CEF logs via CommonSecurityLog are often the most expensive connector.
// This identifies which firewall/IDS/proxy products drive the most volume
// and which DeviceAction + DeviceEventClassID combos can be safely filtered.
// =====================================================================

let _Window = 7d;
let _TotalCSL = toscalar(CommonSecurityLog | where TimeGenerated > ago(_Window) | count);
// --- Part 1: By Device Vendor + Product ---
let _ByProduct =
    CommonSecurityLog
    | where TimeGenerated > ago(_Window)
    | summarize 
        EventCount = count(),
        DistinctSources = dcount(DeviceAddress),
        DistinctDestinations = dcount(DestinationIP)
        by DeviceVendor, DeviceProduct
    | extend PctOfTotal = round(toreal(EventCount) * 100.0 / _TotalCSL, 1)
    | extend 
        EstDailyGB = round(toreal(EventCount) / 7.0 * 0.001 / 1024.0, 2),  // ~1KB/event avg
        FilterAction = case(
            PctOfTotal > 40, "CRITICAL: >40% - Investigate activity filter or DCR transform",
            PctOfTotal > 20, "HIGH: >20% - Review DeviceAction distribution below",
            PctOfTotal > 10, "MODERATE: >10% - Evaluate what actions are ingested",
            "OK"
        )
    | project Section = "BY_PRODUCT", Name = strcat(DeviceVendor, " / ", DeviceProduct), EventCount, PctOfTotal, EstDailyGB, FilterAction;
// --- Part 2: By DeviceAction (Allow/Deny/Drop) ---
let _ByAction =
    CommonSecurityLog
    | where TimeGenerated > ago(_Window)
    | summarize EventCount = count() by DeviceAction, DeviceVendor
    | extend PctOfTotal = round(toreal(EventCount) * 100.0 / _TotalCSL, 1)
    | extend FilterAction = case(
        DeviceAction has_any ("allow", "permit", "pass", "accept") and PctOfTotal > 30, 
            "FILTER: Allowed traffic >30% - Filter routine allows, keep denies",
        DeviceAction has_any ("allow", "permit", "pass") and PctOfTotal > 15, 
            "REVIEW: Allowed traffic >15% - Consider ingesting denies only",
        DeviceAction has_any ("drop", "deny", "block", "reject"), 
            "KEEP: Blocked traffic - Security relevant",
        PctOfTotal > 10, "REVIEW: High volume action",
        "OK"
    )
    | project Section = "BY_ACTION", Name = strcat(DeviceVendor, ": ", DeviceAction), EventCount, PctOfTotal, EstDailyGB = 0.0, FilterAction;
// --- Part 3: Top Event Class IDs (noisiest rule triggers) ---
let _ByEventClass =
    CommonSecurityLog
    | where TimeGenerated > ago(_Window)
    | summarize EventCount = count() by DeviceEventClassID, DeviceVendor, Activity
    | extend PctOfTotal = round(toreal(EventCount) * 100.0 / _TotalCSL, 1)
    | where PctOfTotal > 2
    | extend FilterAction = case(
        Activity has_any ("health", "heartbeat", "keepalive", "status"), "FILTER: Health/heartbeat traffic - Zero detection value",
        Activity has_any ("session", "traffic", "flow") and PctOfTotal > 10, "REVIEW: Session/flow logs >10% - May overlap with firewall allow",
        PctOfTotal > 15, "REVIEW: >15% from single rule - Investigate",
        "KEEP"
    )
    | project Section = "BY_EVENTCLASS", Name = strcat(DeviceVendor, ": ", DeviceEventClassID, " - ", Activity), EventCount, PctOfTotal, EstDailyGB = 0.0, FilterAction
    | top 20 by EventCount desc;
_ByProduct | union _ByAction | union _ByEventClass
| order by Section asc, EventCount desc

Explanation

This query is designed to analyze and identify the most significant sources of log data from security devices like firewalls, IDS, and proxies, which are collected through the CommonSecurityLog. It aims to help manage and potentially reduce the volume of logs ingested, which can be costly. Here's a breakdown of what the query does:

  1. Time Frame: The analysis is conducted over the past 7 days.

  2. Total Log Count: It calculates the total number of logs collected in this period.

  3. Analysis by Device Vendor and Product:

    • It counts the number of events, distinct source addresses, and distinct destination IPs for each combination of device vendor and product.
    • It calculates the percentage of total logs each product contributes and estimates the daily data volume in gigabytes.
    • It categorizes the products based on their log volume contribution, suggesting actions like investigating, reviewing, or evaluating the logs for potential filtering.

  4. Analysis by Device Action:

    • It summarizes the log count by device action (e.g., allow, deny) and vendor.
    • It calculates the percentage of total logs for each action and suggests filtering or reviewing actions based on their contribution to the total log volume.

  5. Analysis by Event Class ID:

    • It identifies the top event class IDs (rules or triggers) that generate the most logs.
    • It calculates the percentage of total logs for each event class and suggests actions like filtering or reviewing based on their significance and nature (e.g., health checks, session logs).

  6. Output:

    • The results from the three analyses are combined and sorted by section and event count, providing a comprehensive view of the log data sources and potential areas for optimization.

Overall, this query helps in identifying and managing high-volume log sources to optimize data ingestion and reduce costs.

Details

David Alonso profile picture

David Alonso

Released: April 8, 2026

Tables

CommonSecurityLog

Keywords

CommonSecurityLogDeviceVendorDeviceProductDeviceAddressDestinationIPDeviceActionDeviceEventClassIDActivityTimeGeneratedEventCountDistinctSourcesDistinctDestinationsPctOfTotalEstDailyGBFilterActionSectionName

Operators

lettoscalaragocountwheresummarizedcountbyextendroundtorealcaseprojectstrcathas_anytopunionorder by

Actions