Multiple IP Entity Dns Events

Query

// This query assumes a feed of threat indicators is ingested/synchronized periodically, and each synchronization ingests new indicators and only old indicators that have been modified.
// Active threat indicators in Sentinel are renovated as ThreatIntelligenceIndicator events every ~12 days.
let query_frequency = 1h;
let query_period = 14d;
let query_wait = 0h;
let table_query_lookback = 1d;
let _TIBenignProperty =
    _GetWatchlist('ID-TIBenignProperty')
    | where Notes has_any ("[DestinationIPAddress]")
    | project IndicatorId, BenignProperty
;
let _TIExcludedSources = toscalar(
    _GetWatchlist('Activity-ExpectedSignificantActivity')
    | where Activity == "ThreatIndicatorSource"
    | summarize make_list(Auxiliar)
    );
let _TITableMatch = (table_start: datetime, table_end: datetime, only_new_ti: boolean, ti_start: datetime = datetime(null)) {
    // Scheduled Analytics rules have a query period limit of 14d
    let _Indicators =// materialize(
        ThreatIntelligenceIndicator
        | where TimeGenerated > ago(query_period)
        // Take the earliest TimeGenerated and the latest column info
        | summarize hint.strategy=shuffle
            minTimeGenerated = min(TimeGenerated),
            arg_max(TimeGenerated, Active, Description, ActivityGroupNames, IndicatorId, ThreatType, DomainName, Url, ExpirationDateTime, ConfidenceScore, SourceSystem, Tags, AdditionalInformation, ExternalIndicatorId, NetworkIP, NetworkSourceIP, NetworkDestinationIP, EmailSourceIpAddress)
            by IndicatorId
        // Remove inactive or expired indicators
        | where not(not(Active) or ExpirationDateTime < now())
        // Pick indicators that contain the desired entity type
        | mv-expand IPAddress = pack_array(NetworkIP, NetworkSourceIP, NetworkDestinationIP, EmailSourceIpAddress) to typeof(string)
        | where isnotempty(IPAddress)
        | extend TI_IPAddress = IPAddress
        // Remove indicators from specific sources
        | where not(AdditionalInformation has_any (_TIExcludedSources) or Description has_any (_TIExcludedSources))
        // Remove excluded indicators with benign properties
        | join kind=leftanti _TIBenignProperty on IndicatorId, $left.IPAddress == $right.BenignProperty
        // Deduplicate indicators by IPAddress column, equivalent to using join kind=innerunique afterwards
        | summarize hint.strategy=shuffle
            minTimeGenerated = min(minTimeGenerated),
            take_any(*)
            by IPAddress
        // If we want only new indicators, remove indicators received previously
        | where not(only_new_ti and minTimeGenerated < ti_start)
    //)
    ;
    //let _IndicatorsLength = toscalar(_Indicators | summarize count());
    //let _IndicatorsPrefilter = toscalar(
    //    _Indicators
    //    | extend AuxiliarField = tostring(extract(@"([0-9A-Za-f]+)[\.\:]", 1, IPAddress))
    //    | summarize make_set_if(AuxiliarField, isnotempty(AuxiliarField), 10000)
    //);
    //let _IndicatorsPrefilterLength = array_length(_IndicatorsPrefilter);
    let _TableEvents =
        DnsEvents
        | where TimeGenerated between (table_start .. table_end)
        // Filter events that may contain indicators
        | where isnotempty(IPAddresses) and SubType == "LookupQuery"
        //| where not(_IndicatorsPrefilterLength < 10000 and not(IPAddresses has_any (_IndicatorsPrefilter))) // "has_any" limit 10000
        | mv-expand IPAddress = split(IPAddresses, ", ") to typeof(string)
        | where not(isnotempty(parse_ipv4(IPAddress)) and (ipv4_is_private(IPAddress) or ipv4_is_in_any_range(IPAddress, "0.0.0.0/8", "127.0.0.0/8")))
        //| where not(_IndicatorsLength < 1000000 and not(IPAddress in (toscalar(_Indicators | summarize make_list(TI_IPAddress))))) // "in" limit 1.000.000
        | project-rename DnsEvents_TimeGenerated = TimeGenerated
    ;
    _Indicators
    | join kind=inner hint.strategy=shuffle _TableEvents on IPAddress
    // Take only a single event by key columns
    //| summarize hint.strategy=shuffle take_any(*) by IPAddress, ClientIP
    | project
        DnsEvents_TimeGenerated,
        Description,
        ActivityGroupNames,
        IndicatorId,
        ThreatType,
        DomainName,
        Url,
        ExpirationDateTime,
        ConfidenceScore,
        SourceSystem,
        Tags,
        AdditionalInformation,
        TI_IPAddress,
        NetworkIP,
        NetworkSourceIP,
        NetworkDestinationIP,
        EmailSourceIpAddress,
        Computer,
        SubType,
        ClientIP,
        QueryType,
        Name,
        IPAddresses,
        MaliciousIP,
        IndicatorThreatType
};
union// isfuzzy=true
    // Match      current table events                                all indicators available
    _TITableMatch(ago(query_frequency + query_wait), ago(query_wait), false),
    // Match      past table events                                                          new indicators since last query execution
    _TITableMatch(ago(table_query_lookback + query_wait), ago(query_frequency + query_wait), true, ago(query_frequency))
| summarize arg_max(DnsEvents_TimeGenerated, *) by IndicatorId, ClientIP
| extend
    timestamp = DnsEvents_TimeGenerated,
    IPCustomEntity = TI_IPAddress

Explanation

This query is designed to identify and correlate threat indicators with DNS events in a security monitoring system, such as Microsoft Sentinel. Here's a simplified breakdown of what the query does:

Setup and Definitions:
- The query defines several parameters and helper functions to manage time periods and filter criteria.
- It uses watchlists to exclude benign properties and certain sources from the analysis.
Threat Indicators Processing:
- It retrieves active threat indicators from the ThreatIntelligenceIndicator table, filtering out inactive or expired ones.
- It expands the indicators to include various IP-related fields and excludes those from specific sources or with benign properties.
- It deduplicates the indicators based on IP addresses.
DNS Events Processing:
- It retrieves DNS events from the DnsEvents table within a specified time range.
- It filters these events to include only those that are relevant, such as non-private IP addresses involved in DNS lookup queries.
Correlation:
- The query joins the processed threat indicators with the DNS events based on matching IP addresses.
- It selects relevant fields from the correlated data for further analysis.
Union and Summarization:
- It combines results from current and past DNS events with both all available indicators and new indicators since the last query execution.
- It summarizes the results to get the most recent DNS event for each unique indicator and client IP combination.
Output:
- The final output includes details about the threat indicators and the corresponding DNS events, with additional fields for further investigation.

In essence, this query helps security analysts identify potential threats by correlating known threat indicators with observed DNS activity, while excluding benign or irrelevant data.

Details

Jose Sebastián Canós

Released: December 13, 2023

Tables

ThreatIntelligenceIndicatorDnsEvents

Keywords

ThreatIntelligenceIndicatorDnsEvents

Operators

lethas_anyprojecttoscalarwheresummarizemake_listdatetimematerializeagominarg_maxmv-expandpack_arraytypeofisnotemptyextendjoinkindleftantihint.strategytake_anybetweensplitproject-renameparse_ipv4ipv4_is_privateipv4_is_in_any_rangeinunionisfuzzyby

Actions

GitHub

KQL Search