Multiple Domain Entity VM Connection

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 = 14d;
let _TIBenignProperty =
    _GetWatchlist('ID-TIBenignProperty')
    | where Notes has_any ("[DestinationDomain]")
    | 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, AdditionalInformation, ExternalIndicatorId)
            by IndicatorId
        // Remove inactive or expired indicators
        | where not(not(Active) or ExpirationDateTime < now())
        // Pick indicators that contain the desired entity type
        | where isnotempty(DomainName)
        | extend Domain = tolower(DomainName)
        // 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.Domain == $right.BenignProperty
        // Deduplicate indicators by Domain column, equivalent to using join kind=innerunique afterwards
        | summarize hint.strategy=shuffle
            minTimeGenerated = min(minTimeGenerated),
            take_any(*)
            by Domain
        // 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(split(Domain, ".")[-1])
    //    | summarize make_set_if(AuxiliarField, isnotempty(AuxiliarField))
    //);
    //let _IndicatorsPrefilterLength = array_length(_IndicatorsPrefilter);
    let _TableEvents =
        VMConnection
        | where ingestion_time() between (table_start .. table_end)
        // Filter events that may contain indicators
        | where isnotempty(RemoteDnsQuestions) or isnotempty(RemoteDnsCanonicalNames)
        | mv-expand Domain = array_concat(todynamic(RemoteDnsQuestions), todynamic(RemoteDnsCanonicalNames)) to typeof(string)
        //| where not(_IndicatorsPrefilterLength < 10000 and not(Domain has_any (_IndicatorsPrefilter))) // valid TLD ~1500 , "has_any" limit 10000
        | summarize hint.strategy=shuffle take_any(*) by OriginalDomain = tolower(Domain)
        //| where not(_IndicatorsPrefilterLength < 10000 and not(tostring(split(OriginalDomain, ".")[-1]) in (_IndicatorsPrefilter)))
        | extend SplitLevelDomains = split(OriginalDomain, ".")
        | mv-expand Level = range(0, array_length(SplitLevelDomains) - 2) to typeof(int)
        | extend Domain = strcat_array(array_slice(SplitLevelDomains, Level, -1), ".")
        //| where not(_IndicatorsLength < 1000000 and not(Domain in (toscalar(_Indicators | summarize make_list(Domain))))) // "in" limit 1.000.000
        | project-rename VMConnection_TimeGenerated = TimeGenerated
    ;
    _Indicators
    | join kind=inner hint.strategy=shuffle _TableEvents on Domain
    // Take only a single event by key columns
    //| summarize hint.strategy=shuffle take_any(*) by Domain, Computer
    | project
        VMConnection_TimeGenerated,
        Description, ActivityGroupNames, IndicatorId, ThreatType, DomainName, Url, ExpirationDateTime, ConfidenceScore, AdditionalInformation,
        _ResourceId, Computer, SourceIp, DestinationIp, Protocol, DestinationPort, Direction, RemoteIp, ProcessName, RemoteDnsQuestions, RemoteDnsCanonicalNames, ConnectionId
};
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(VMConnection_TimeGenerated, *) by IndicatorId, Computer
| extend
    timestamp = VMConnection_TimeGenerated,
    HostCustomEntity = Computer,
    URLCustomEntity = Url

Explanation

This query is designed to identify and correlate threat indicators with network activity in a security environment, specifically using Microsoft Sentinel. Here's a simplified breakdown of what the query does:

Setup Parameters:
- It defines several parameters like query_frequency, query_period, and query_wait to control the timing and scope of the query.
- It sets a lookback period for analyzing data from the last 14 days.
Filter Benign Indicators:
- It retrieves a list of benign properties from a watchlist to exclude certain threat indicators that are deemed non-threatening.
Exclude Certain Sources:
- It excludes indicators from specific sources that are expected to generate significant activity but are not considered threats.
Process Threat Indicators:
- It fetches threat indicators from the ThreatIntelligenceIndicator table, focusing on active and non-expired indicators.
- It filters these indicators to include only those with domain names and excludes those from benign sources or with benign properties.
- It deduplicates the indicators based on the domain name.
Analyze Network Activity:
- It examines network connection events from the VMConnection table within a specified time range.
- It extracts domain names from DNS queries and canonical names in the network events.
- It processes these domain names to match them with the threat indicators.
Join and Correlate Data:
- It joins the processed threat indicators with the network activity data to find matches.
- It ensures that only unique events are considered by deduplicating based on key columns.
Output Results:
- It combines results from current and past events with new indicators.
- It summarizes the results to show the most recent event for each indicator and computer combination.
- It extends the results with additional fields for easier analysis, such as timestamp, HostCustomEntity, and URLCustomEntity.

In essence, this query is designed to identify potential security threats by correlating threat intelligence data with observed network activity, while filtering out known benign activities and sources.

Details

Jose Sebastián Canós

Released: December 13, 2023

Tables

ThreatIntelligenceIndicatorVMConnection

Keywords

ThreatIntelligenceIndicatorVMConnection

Operators

lethas_anyprojecttoscalarwheresummarizemake_listdatetimematerializeagominarg_maxnowisnotemptytolowerjoinkindleftanti$left$righttake_anyextendbetweenmv-expandarray_concattodynamictypeofsplitarray_lengtharray_slicestrcat_arrayproject-renamerangeunionby

Actions

GitHub

KQL Search