Multiple Email Entity Office Activity

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 ("[SourceEmailAddress]", "[DestinationEmailAddress]")
    | project IndicatorId, BenignProperty
;
let _TIExcludedSources = toscalar(
    _GetWatchlist('Activity-ExpectedSignificantActivity')
    | where Activity == "ThreatIndicatorSource"
    | summarize make_list(Auxiliar)
    );
let _EmailAddressRegex = toscalar(
    _GetWatchlist('RegEx-SingleRegularExpressions')
    | where UseCase == "EmailAddress"
    | project RegEx
    );
let _ExternalEmailAddressRegex = toscalar(
    _GetWatchlist('RegEx-SingleRegularExpressions')
    | where UseCase == "ExternalEmailAddress"
    | project RegEx
    );
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, EmailSenderAddress)
            by IndicatorId
        // Remove inactive or expired indicators
        | where not(not(Active) or ExpirationDateTime < now())
        // Pick indicators that contain the desired entity type
        | where isnotempty(EmailSenderAddress)
        | extend EmailAddress = tolower(EmailSenderAddress)
        // 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.EmailAddress == $right.BenignProperty
        // Deduplicate indicators by EmailAddress column, equivalent to using join kind=innerunique afterwards
        | summarize hint.strategy=shuffle
            minTimeGenerated = min(minTimeGenerated),
            take_any(*)
            by EmailAddress
        // 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(EmailAddress, ".")[-1])
    //    | summarize make_set_if(AuxiliarField, isnotempty(AuxiliarField))
    //);
    //let _IndicatorsPrefilterLength = array_length(_IndicatorsPrefilter);
    let _TableEvents =
        OfficeActivity
        | where ingestion_time() between (table_start .. table_end)
        // Filter events that may contain indicators
        | where not(isempty(UserId) and isempty(TargetUserOrGroupName))
        //| where not(_IndicatorsPrefilterLength < 10000 and not(UserId has_any (_IndicatorsPrefilter) or TargetUserOrGroupName has_any (_IndicatorsPrefilter))) // valid TLD ~1500 , "has_any" limit 10000
        | mv-expand EmailAddress = pack_array(UserId, TargetUserOrGroupName)
        | where isnotempty(EmailAddress)
        | extend EmailAddress = tolower(EmailAddress)
        | where EmailAddress matches regex _EmailAddressRegex
        | extend EmailAddress = case(
            EmailAddress has "#EXT#", replace_regex(EmailAddress, _ExternalEmailAddressRegex, @"\2@\3"),
            EmailAddress startswith "live.com#" or EmailAddress startswith "urn:spo:guest#", replace_regex(EmailAddress, strcat(@"(?:live\.com#|urn:spo:guest#)", _EmailAddressRegex), @"\2@\3"),
            EmailAddress
            )
        //| where not(_IndicatorsLength < 1000000 and not(EmailAddress in (toscalar(_Indicators | summarize make_list(EmailAddress))))) // "in" limit 1.000.000
        | project-rename OfficeActivity_TimeGenerated = TimeGenerated
    ;
    _Indicators
    | join kind=inner hint.strategy=shuffle _TableEvents on EmailAddress
    // Take only a single event by key columns
    //| summarize hint.strategy=shuffle take_any(*) by EmailAddress, UserId
    | project
        OfficeActivity_TimeGenerated,
        Description, ActivityGroupNames, IndicatorId, ThreatType, DomainName, Url, ExpirationDateTime, ConfidenceScore, AdditionalInformation, EmailSenderAddress,
        OfficeWorkload, RecordType, Operation, UserType, UserId, ClientIP, ResultStatus, OfficeObjectId, Parameters
};
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(OfficeActivity_TimeGenerated, *) by IndicatorId, UserId
| extend
    timestamp = OfficeActivity_TimeGenerated,
    IPCustomEntity = ClientIP,
    AccountCustomEntity = EmailSenderAddress

Explanation

This query is designed to detect and analyze suspicious email activities by matching threat intelligence indicators with email-related events from OfficeActivity logs. Here's a simplified breakdown of what the query does:

Setup and Configuration:
- It defines several parameters and helper functions to manage the query's execution frequency, period, and lookback duration.
- It retrieves lists of benign properties and excluded sources to filter out non-threatening indicators.
Threat Intelligence Indicators:
- It fetches active threat indicators related to email addresses from the ThreatIntelligenceIndicator table.
- It filters out inactive or expired indicators and those from excluded sources.
- It removes indicators with benign properties and deduplicates them based on email addresses.
Office Activity Events:
- It retrieves email-related events from the OfficeActivity table within a specified time range.
- It filters events to ensure they contain relevant email addresses and formats them appropriately.
- It matches these events against the threat indicators to find potential threats.
Matching and Results:
- It performs two types of matches:
  - Current events against all available indicators.
  - Past events against new indicators since the last query execution.
- It summarizes the results to identify the most recent suspicious activities for each indicator and user.
Output:
- The query outputs a list of suspicious activities with details like the time of the event, description, threat type, email sender, and associated user and IP address.

In summary, this query helps identify potential email threats by correlating threat intelligence data with OfficeActivity logs, filtering out non-threatening events, and highlighting suspicious activities for further investigation.

Details

Jose Sebastián Canós

Released: December 13, 2023

Tables

ThreatIntelligenceIndicatorOfficeActivity

Keywords

ThreatIntelligenceIndicatorOfficeActivityEmailAddressUserIdTargetUserOrGroupNameClientIP

Operators

lethas_anyprojecttoscalarwheresummarizemake_listproject-renameextendjoinkindleftantiarg_maxisnotemptynotisemptybetweenmv-expandmatchesregexcasestartswithreplace_regexstrcatinunionisfuzzyagoby

Actions

GitHub

KQL Search