Security Alert A Potentially Malicious URL Click Was Detected

Query

let query_frequency = 15m;
let query_period = 1d;
let _URLRegex = toscalar(
    _GetWatchlist('RegEx-SingleRegularExpressions')
    | where UseCase == "Threat Intelligence Indicator URL"
    | project RegEx
);
SecurityAlert
| where TimeGenerated > ago(query_period)
| where AlertName has "A potentially malicious URL click was detected" and ProviderName == "OATP"
| summarize arg_min(TimeGenerated, *) by SystemAlertId
| where TimeGenerated > ago(query_frequency)
| extend ExtendedProperties = todynamic(ExtendedProperties)
| extend
    InvestigationAction = tostring(ExtendedProperties["InvestigationName"]),
    URL = tostring(split(tostring(ExtendedProperties["InvestigationName"]), " - ")[1])
| extend Domain = tostring(extract(_URLRegex, 3, URL))
| extend SLD = strcat_array(array_slice(split(Domain, "."), -2, -1), ".")
// Clean Entities
| extend Entities = replace_regex(Entities, @'(\,\"\w+\"\:\{\"\$ref\"\:\"\d+\"\}|\"\w+\"\:\{\"\$ref\"\:\"\d+\"\}\,|\,\{\"\$ref\"\:\"\d+\"\})|\"\$id\"\:\"\d+\"\,', '')
| summarize
    TimeGenerated = min(TimeGenerated),
    StartTime = min(StartTime),
    EndTime = max(EndTime),
    InvestigationActions = make_set(InvestigationAction, 250),
    URLs = make_set(URL, 250),
    Domains = make_set(Domain, 250),
    AlertLinks = make_set(AlertLink, 250),
    Entities = make_set(todynamic(Entities)),
    take_any(RemediationSteps, Tactics, Techniques)
    by AlertName, AlertSeverity, Description, SLD
| project
    TimeGenerated,
    AlertName,
    AlertSeverity,
    Description,
    RemediationSteps,
    StartTime,
    EndTime,
    SLD,
    Domains,
    URLs,
    InvestigationActions,
    AlertLinks,
    Tactics,
    Techniques,
    Entities

Explanation

This KQL query is designed to analyze security alerts related to potentially malicious URL clicks detected by the "OATP" provider. Here's a simplified breakdown of what the query does:

Set Parameters:
- query_frequency is set to 15 minutes, and query_period is set to 1 day.
- A regular expression pattern (_URLRegex) is retrieved from a watchlist to identify URLs related to threat intelligence.
Filter Alerts:
- The query looks at alerts generated within the last day (query_period).
- It specifically targets alerts with the name "A potentially malicious URL click was detected" from the "OATP" provider.
Summarize Alerts:
- For each unique alert (SystemAlertId), it keeps the earliest occurrence (arg_min).
- Further filters these alerts to those generated within the last 15 minutes (query_frequency).
Extract and Process Data:
- Converts the ExtendedProperties field to a dynamic object to extract specific information.
- Extracts the investigation action name and the URL from the alert's extended properties.
- Uses a regular expression to extract the domain from the URL and determines the second-level domain (SLD).
Clean and Organize Data:
- Cleans up the Entities field by removing unnecessary references.
- Summarizes the data by grouping alerts based on their name, severity, description, and SLD.
- Collects sets of investigation actions, URLs, domains, alert links, and entities for each group.
- Also captures any remediation steps, tactics, and techniques associated with the alerts.
Project Results:
- Selects and organizes the final output to include relevant fields such as time generated, alert details, domains, URLs, investigation actions, and other related information.

In essence, this query is designed to provide a concise summary of recent security alerts related to malicious URL clicks, extracting and organizing key information for further analysis or reporting.

Details

Jose Sebastián Canós

Released: May 20, 2024

Tables

SecurityAlert

Keywords

SecurityAlertThreatIntelligenceURLDomainInvestigationEntities

Operators

lettoscalarwhereagosummarizearg_minextendtostringsplitextractstrcat_arrayarray_slicereplace_regexminmaxmake_settodynamictake_anybyproject

Actions

GitHub

KQL Search