AWS Cloud Trail Aws Credential Access Failed Login

Query

let query_frequency = 1h;
let query_period = 14d;
let _SuccessResultTypes = toscalar(
    _GetWatchlist("ResultType-SignInLogsErrorCodes")
    | where Notes has_all ("[Success]", "[Complete]") and isnotempty(ResultDescription)
    | summarize make_list(ResultType)
);
AWSCloudTrail
| where TimeGenerated > ago(query_frequency)
| where EventName == "ConsoleLogin" and isnotempty(ErrorMessage)
| extend MFAUsed = tostring(coalesce(todynamic(AdditionalEventData)["MFAUsed"], todynamic(AdditionalEventData)["AFAUsed"]))
// | where MFAUsed == "Yes"
| summarize arg_min(TimeGenerated, *) by UserIdentityType, UserIdentityAccountId, SourceIpAddress, ErrorMessage, MFAUsed
| extend Location = geo_info_from_ip_address(SourceIpAddress)
| join kind=leftouter (
    SignInLogsTables
    | where TimeGenerated > ago(query_period)
    | where ResultType in (_SuccessResultTypes)
    | summarize IPAddressUserPrincipalNames = make_set(UserPrincipalName, 50) by IPAddress
    ) on $left.SourceIpAddress == $right.IPAddress
| invoke AWSIdentityRole()
| project
    TimeGenerated,
    UserIdentityType,
    Identity,
    ActorRole,
    UserIdentityAccountId,
    UserIdentityAccountName,
    RecipientAccountId,
    RecipientAccountName,
    AWSRegion,
    SessionCreationDate,
    UserIdentityPrincipalid,
    UserIdentityArn,
    SourceIpAddress,
    Location,
    EventSource,
    EventTypeName,
    EventName,
    ManagementEvent,
    ReadOnly,
    ErrorCode,
    ErrorMessage,
    MFAUsed,
    IPAddressUserPrincipalNames,
    RequestParameters,
    ResponseElements,
    Resources,
    SessionMfaAuthenticated,
    UserAgent,
    AwsEventId

Explanation

This KQL query is designed to analyze AWS CloudTrail logs, specifically focusing on failed console login attempts. Here's a simplified breakdown of what the query does:

Set Parameters:
- query_frequency is set to 1 hour, meaning the query looks at data from the past hour.
- query_period is set to 14 days, meaning it considers sign-in logs from the past 14 days.
Define Success Result Types:
- It retrieves a list of successful result types from a watchlist named "ResultType-SignInLogsErrorCodes" where the notes contain "[Success]" and "[Complete]".
Filter AWS CloudTrail Logs:
- It filters the AWSCloudTrail logs to include only those generated in the last hour.
- It further narrows down to events where the EventName is "ConsoleLogin" and there is an ErrorMessage.
Extract MFA Information:
- It checks if Multi-Factor Authentication (MFA) was used during the login attempt by examining additional event data.
Summarize and Extend Data:
- It summarizes the earliest occurrence of each unique combination of user identity type, account ID, source IP address, error message, and MFA usage.
- It extends the data with geographical information derived from the source IP address.
Join with Sign-In Logs:
- It performs a left outer join with SignInLogsTables to match source IP addresses with user principal names from successful sign-ins within the last 14 days.
Invoke AWS Identity Role:
- It calls a function AWSIdentityRole() to enrich the data with identity role information.
Select and Project Data:
- Finally, it selects and projects a wide range of fields for further analysis, including user identity details, event information, error details, and more.

In summary, this query identifies failed AWS console login attempts within the last hour, checks if MFA was used, and correlates these attempts with successful sign-ins from the past 14 days to provide a comprehensive view of login activities and potential security issues.

Details

Jose Sebastián Canós

Released: April 10, 2024

Tables

AWSCloudTrailSignInLogsTables

Keywords

AWSCloudTrailSignInLogsTablesUserIdentityTypeUserIdentityAccountIdSourceIpAddressErrorMessageMFAUsedLocationIPAddressUserPrincipalNamesAWSIdentityRoleUserIdentityAccountNameRecipientAccountIdRecipientAccountNameAWSRegionSessionCreationDateUserIdentityPrincipalidUserIdentityArnEventSourceEventTypeNameEventNameManagementEventReadOnlyErrorCodeRequestParametersResponseElementsResourcesSessionMfaAuthenticatedUserAgentAwsEventId

Operators

lettoscalar_GetWatchlisthas_allisnotemptysummarizemake_listwhere==extendtostringcoalescetodynamicarg_minbygeo_info_from_ip_addressjoinkind=leftouterinmake_setoninvokeproject

Actions

GitHub

KQL Search