KQL Search

// 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 ("[SourceIPAddress]")
    | 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 =
        AzureDiagnostics
        | where TimeGenerated between (table_start .. table_end)
        | where ResourceProvider == "MICROSOFT.SQL"
        | extend IPAddress = column_ifexists("client_ip_s", "")
        // Filter events that may contain indicators
        | where isnotempty(IPAddress) and (isnotempty(parse_ipv4(IPAddress)) or isnotempty(parse_ipv6(IPAddress)))
        //| where not(_IndicatorsPrefilterLength < 10000 and not(IPAddress has_any (_IndicatorsPrefilter))) // "has_any" limit 10000
        | 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
        | extend
            HostName = column_ifexists("host_name_s", ""),
            ApplicationName = column_ifexists("application_name_s", ""),
            ActionName = column_ifexists("action_name_s", ""),
            ClassTypeDescription = column_ifexists("class_type_description_s", ""),
            ServerPrincipalName = column_ifexists("server_principal_name_s", ""),
            ServerInstanceName = column_ifexists("server_instance_name_s", ""),
            LogicalServerName = column_ifexists("LogicalServerName_s", ""),
            DatabasePrincipalName = column_ifexists("database_principal_name_s", ""),
            DatabaseName = column_ifexists("database_name_s", ""),
            AzureDiagnostics_AdditionalInformation = column_ifexists("additional_information_s", "")
        | project-rename AzureDiagnostics_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, ResourceId
    | project
        AzureDiagnostics_TimeGenerated,
        Description,
        ActivityGroupNames,
        IndicatorId,
        ThreatType,
        DomainName,
        Url,
        ExpirationDateTime,
        ConfidenceScore,
        SourceSystem,
        Tags,
        AdditionalInformation,
        TI_IPAddress,
        NetworkIP,
        NetworkSourceIP,
        NetworkDestinationIP,
        EmailSourceIpAddress,
        Category,
        ResourceId,
        ResourceType,
        OperationName,
        ActionName,
        HostName,
        IPAddress,
        ApplicationName,
        ClassTypeDescription,
        ServerPrincipalName,
        ServerInstanceName,
        LogicalServerName,
        DatabasePrincipalName,
        DatabaseName,
        AzureDiagnostics_AdditionalInformation
};
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(AzureDiagnostics_TimeGenerated, *) by IndicatorId, ResourceId
| extend
    timestamp = AzureDiagnostics_TimeGenerated,
    HostCustomEntity = HostName,
    IPCustomEntity = TI_IPAddress