KQL Search

Query Details

HomeAI ToolsDevice Query

ADFS Sign In Logs Password Spray Attack Against AD FS Private IP Address Anomaly

Query

let query_frequency = 1h;
let query_period = 14d;
let query_wait = 30m;
let consecutive_failures_threshold = 1;
let account_threshold = 10;
let _SuccessResultTypes = toscalar(
    _GetWatchlist("ResultType-SignInLogsErrorCodes")
    | where Notes has_any ("[Expired]", "[Success]") and isnotempty(ResultDescription)
    | summarize make_list(ResultType)
);
let _SuspiciousAddresses = toscalar(
    ADFSSignInLogs
    | where TimeGenerated between (ago(query_period + query_wait) .. ago(query_wait))
    | where ResultType in (50057, 50126, 50144, 50155, 300030) and isnotempty(parse_ipv4(IPAddress)) and ipv4_is_private(IPAddress)
    | sort by UserPrincipalName, UserId, ResultType
    | where not(ResultType == 300030 and prev(ResultType) == ResultType and prev(UserId) == UserId and prev(UserPrincipalName) == UserPrincipalName)
    | make-series Count = count() default=0 on TimeGenerated from ago(query_period + query_wait) to ago(query_wait) step 2*query_frequency by IPAddress
    | extend series_decompose_anomalies(Count, 4, toint(1d/query_frequency), 'none', consecutive_failures_threshold)
    | where array_sum(array_slice(series_decompose_anomalies_Count_ad_flag, -(consecutive_failures_threshold), -1)) == (1 * consecutive_failures_threshold)
    | summarize make_list(IPAddress)
);
ADFSSignInLogs
| where TimeGenerated between (ago((consecutive_failures_threshold * query_frequency) + query_wait) .. ago(query_wait))
| where ResultType in (50057, 50126, 50144, 50155, 300030) and IPAddress in (_SuspiciousAddresses)
| summarize
    StartTime = min(TimeGenerated),
    EndTime = max(TimeGenerated),
    FailureAccountCount = dcount(UserPrincipalName),
    FailureAccountSample = array_sort_asc(make_set(UserPrincipalName, 250)),
    take_any(TokenIssuerName)
    by IPAddress, UserAgent
| top 100 by FailureAccountCount desc
| summarize
    StartTime = min(StartTime),
    EndTime = max(EndTime),
    FailureAccountCount = sum(FailureAccountCount),
    FailureAccountSample = array_sort_asc(make_set(FailureAccountSample, 250)),
    UserAgentCount = make_list(bag_pack(iff(isnotempty(UserAgent), UserAgent, "<<<empty UserAgent>>>"), FailureAccountCount)),
    take_any(TokenIssuerName)
    by IPAddress
| where FailureAccountCount > account_threshold
| project
    StartTime,
    EndTime,
    IPAddress,
    FailureAccountCount,
    FailureAccountSample,
    UserAgentCount,
    TokenIssuerName

Explanation

This query is designed to identify suspicious IP addresses based on sign-in logs from an Active Directory Federation Services (ADFS) system.

The query runs every hour, looking back over the past 14 days, and waits for 30 minutes before executing. It identifies an IP address as suspicious if it has had more than one consecutive failure within a certain threshold.

The query first creates a list of successful result types from a watchlist, and then identifies suspicious IP addresses based on certain result types and whether the IP address is private. It sorts the data by user name, user ID, and result type, and removes any duplicates.

The query then creates a time series of the count of sign-ins by IP address, and uses a series decomposition to identify anomalies. If the sum of the anomalies equals the threshold for consecutive failures, the IP address is added to the list of suspicious addresses.

The query then looks at the sign-in logs again, this time filtering for the suspicious IP addresses and the same result types. It summarizes the data by start and end time, the count of failed accounts, a sample of failed accounts, and the token issuer name.

The query then takes the top 100 results by the count of failed accounts, and summarizes the data again by start and end time, the count of failed accounts, a sample of failed accounts, the count of user agents, and the token issuer name.

Finally, the query filters out any results where the count of failed accounts is less than a certain threshold, and projects the start and end time, IP address, count of failed accounts, sample of failed accounts, count of user agents, and token issuer name.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: August 22, 2023

Tables

ADFSSignInLogs

Keywords

ADFSSignInLogs,TimeGenerated,ResultType,IPAddress,UserPrincipalName,UserId,Count,StartTime,EndTime,FailureAccountCount,FailureAccountSample,UserAgent,TokenIssuerName

Operators

lettoscalar_GetWatchlistwherehas_anyisnotemptysummarizemake_listResultTypeADFSSignInLogsTimeGeneratedbetweenagoinparse_ipv4ipv4_is_privatesortbyUserPrincipalNameUserIdnotprevmake-seriescountdefaultonfromtostepextendseries_decompose_anomaliestointarray_sumarray_slicedcountarray_sort_ascmake_settake_anytopsummake_listbag_packiffproject.

Actions