KQL Search

Query Details

HomeAI ToolsDevice Query

Correlated IP Events From Important Watchlist

Query

//This query identifies security events containing IP addresses in the watch list "FocusIP"
//Useful for quick alerts against specific IPs from CISO requests or ephemeral IP-based threat intel
SecurityAlert
| project IPs = tostring(parse_json(ExtendedProperties)["IP Addresses"])
| extend IPs = split(IPs,",") | mv-expand IPs
| where isnotempty(IPs) | distinct tostring(IPs) // get only unique IPs
| union (SecurityAlert // join to Entities IP pool
| mv-expand parse_json(Entities)
| project IPs = Entities["Address"]
| where isnotempty(IPs) | distinct tostring(IPs)) // get only unique IPs
| order by IPs
// Lookup events using a Watchlist -- join data for any event that is also
| lookup kind=inner _GetWatchlist('FocusIP')
 on $left.IPs == $right.SearchKey

Explanation

This query is designed to identify security events that involve IP addresses listed in a specific watchlist called "FocusIP." Here's a simplified breakdown of what the query does:

  1. Extract IP Addresses from Security Alerts:

    • It starts by extracting IP addresses from the "ExtendedProperties" field of security alerts.
    • These IP addresses are split into individual entries and expanded into separate rows.
    • Only non-empty and unique IP addresses are retained.

  2. Combine with Entities IP Pool:

    • The query also extracts IP addresses from the "Entities" field of security alerts.
    • Similar to the previous step, it ensures only non-empty and unique IP addresses are considered.

  3. Order the IP Addresses:

    • The resulting list of unique IP addresses is sorted.

  4. Match with Watchlist:

    • Finally, it performs an inner join with the "FocusIP" watchlist to find any IP addresses from the alerts that are also present in the watchlist.

This query is useful for quickly identifying and alerting on specific IP addresses that are of interest, such as those flagged by a Chief Information Security Officer (CISO) or identified as potential threats.

Details

@TanukiSec profile picture

@TanukiSec

Released: November 10, 2024

Tables

SecurityAlert

Keywords

SecurityAlertExtendedPropertiesEntitiesAddressWatchlistFocusIPSearchKey

Operators

projecttostringparse_jsonextendsplitmv-expandwhereisnotemptydistinctunionorder bylookupon

Actions