KQL Search

Query Details

HomeAI ToolsDevice Query

Signin Logs Risky AD FS Sign In Event

Query

let _ADFSTokenIssuerNames = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "ADFSTokenIssuerName"
    | summarize make_list(Auxiliar)
);
let _ADFSResourceIdentities = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "ADFSTokenIssuerName"
    | summarize make_list(DestinationAddress)
);
let _ExpectedResultTypes = toscalar(
    _GetWatchlist("ResultType-SignInLogsErrorCodes")
    | where isnotempty(ResultDescription) and not(Notes has_any ("[Success]", "[Expired]"))
    | summarize make_list(ResultType)
);
SigninLogs
| where TokenIssuerName has_any (_ADFSTokenIssuerNames) and RiskLevelAggregated != "none" and RiskState == "atRisk"
    // and ResourceIdentity has_any (_ADFSResourceIdentities) and AppId == "NotApplicable" and ResourceDisplayName == "SSO"
| where not(ResultType in (_ExpectedResultTypes))
| project
    TimeGenerated,
    CreatedDateTime,
    UserDisplayName,
    UserPrincipalName,
    IPAddress,
    Location,
    ResultType,
    ResultDescription,
    ResourceDisplayName,
    ResourceIdentity,
    AutonomousSystemNumber,
    TokenIssuerName,
    UserAgent,
    AuthenticationDetails,
    AuthenticationProcessingDetails,
    NetworkLocationDetails,
    RiskDetail,
    RiskEventTypes,
    RiskEventTypes_V2,
    RiskLevelAggregated,
    RiskLevelDuringSignIn,
    RiskState,
    UserId,
    OriginalRequestId,
    CorrelationId
//| union (AADUserRiskEvents | where TokenIssuerType == "ADFederationServices")

Explanation

This query is designed to monitor and analyze sign-in logs for potential security risks.

Firstly, it defines three lists:

  1. _ADFSTokenIssuerNames which contains all the names of the ADFS token issuers from the watchlist "Activity-ExpectedSignificantActivity".
  2. _ADFSResourceIdentities which contains all the destination addresses associated with the ADFS token issuers from the same watchlist.
  3. _ExpectedResultTypes which contains all the result types from the watchlist "ResultType-SignInLogsErrorCodes", excluding those with empty descriptions or notes marked as "Success" or "Expired".

Then, it filters the SigninLogs for entries where the TokenIssuerName is in the _ADFSTokenIssuerNames list, the RiskLevelAggregated is not "none", and the RiskState is "atRisk". It also excludes entries where the ResultType is in the _ExpectedResultTypes list.

The query then projects a number of fields from the filtered sign-in logs, including timestamps, user details, IP address, location, result type and description, resource details, risk details, and various IDs.

The commented out lines suggest that there might be additional conditions or data sources to consider, such as checking if the ResourceIdentity is in the _ADFSResourceIdentities list, or unioning the results with AADUserRiskEvents where the TokenIssuerType is "ADFederationServices".

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: June 26, 2023

Tables

SigninLogsAADUserRiskEvents

Keywords

SigninLogs,TokenIssuerName,RiskLevelAggregated,RiskState,ResultType,TimeGenerated,CreatedDateTime,UserDisplayName,UserPrincipalName,IPAddress,Location,ResultDescription,ResourceDisplayName,ResourceIdentity,AutonomousSystemNumber,UserAgent,AuthenticationDetails,AuthenticationProcessingDetails,NetworkLocationDetails,RiskDetail,RiskEventTypes,RiskEventTypes_V2,RiskLevelDuringSignIn,UserId,OriginalRequestId,CorrelationId

Operators

toscalar()_GetWatchlist()whereisnotempty()has_any()not()summarizemake_list()SigninLogsprojectunion

Actions