Query Details
Multiple IP Entity AAD Non Interactive User Sign In Logs
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 ("[SourceIPAddress]")
| project IndicatorId, BenignProperty
;
let _TIExcludedSources = toscalar(
_GetWatchlist('Activity-ExpectedSignificantActivity')
| where Activity == "ThreatIndicatorSource"
| summarize make_list(Auxiliar)
);
let _UncompromisedFailureResultTypes = toscalar(
_GetWatchlist('ResultType-SignInLogsErrorCodes')
| where isnotempty(ResultDescription) and not(Notes has_any ("[Success]", "[Expired]"))
| summarize make_list(ResultType)
);
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, 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))
// 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 =
AADNonInteractiveUserSignInLogs
| where TimeGenerated between (table_start .. table_end)
// Filter events that may contain indicators
| where not(ResultType in (_UncompromisedFailureResultTypes))
//| where not(_IndicatorsPrefilterLength < 10000 and not(IPAddress has_any (_IndicatorsPrefilter))) // "has_any" limit 10000
//| where not(_IndicatorsLength < 1000000 and not(IPAddress in (toscalar(_Indicators | summarize make_list(TI_IPAddress))))) // "in" limit 1.000.000
| project-rename AADNonInteractiveUserSignInLogs_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, UserId
| project
AADNonInteractiveUserSignInLogs_TimeGenerated,
Description, ActivityGroupNames, IndicatorId, ThreatType, DomainName, Url, ExpirationDateTime, ConfidenceScore, AdditionalInformation, TI_IPAddress, NetworkIP, NetworkSourceIP, NetworkDestinationIP, EmailSourceIpAddress,
Category, UserPrincipalName, UserDisplayName, IPAddress, Location, ResultType, ResultDescription, ClientAppUsed, AppDisplayName, ResourceDisplayName, DeviceDetail, UserAgent, AuthenticationDetails, ConditionalAccessPolicies, RiskState, RiskEventTypes, RiskLevelDuringSignIn, RiskLevelAggregated, UserId, OriginalRequestId, CorrelationId
};
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(AADNonInteractiveUserSignInLogs_TimeGenerated, *) by IndicatorId, UserId
| extend
timestamp = AADNonInteractiveUserSignInLogs_TimeGenerated,
AccountCustomEntity = UserPrincipalName,
IPCustomEntity = TI_IPAddress
Explanation
This query is used to match threat indicators with table events in Azure Sentinel. The query assumes that a feed of threat indicators is ingested periodically, and each synchronization only ingests new indicators and modified old indicators. The query includes several variables and functions to define the frequency and period of the query.
The query first retrieves a list of benign properties and excluded sources from watchlists. It then defines a function called _TITableMatch that takes the start and end time of the table events, a flag to indicate whether only new indicators should be considered, and the start time of the threat indicators.
Within the _TITableMatch function, the query retrieves the threat intelligence indicators from the ThreatIntelligenceIndicator table. It filters out inactive or expired indicators, selects indicators that contain the desired entity type (IP address), removes indicators from specific sources, and removes excluded indicators with benign properties. The indicators are then deduplicated by the IP address column and filtered based on whether only new indicators are requested.
The query also retrieves the table events from the AADNonInteractiveUserSignInLogs table and filters out events that may contain indicators based on certain result types. The threat indicators are then joined with the table events based on the IP address column.
Finally, the query performs a union operation to match the current table events with all available indicators and past table events with new indicators since the last query execution. It summarizes the results by selecting the latest table event for each indicator and user. The query also adds additional columns and renames some columns for the final result.
Details
Jose Sebastián Canós
Released: December 5, 2022
Tables
Keywords
Operators