KQL Search

Query Details

HomeAI ToolsDevice Query

Analytics AWS Identity Role

Query

let _AWSAccounts = _GetWatchlist("AccountId-AuditAWSAccounts");
T
| extend
    RecipientAccountId = column_ifexists("RecipientAccountId", ""),
    UserIdentityAccountId = column_ifexists("UserIdentityAccountId", ""),
    UserIdentityType = column_ifexists("UserIdentityType", ""),
    UserIdentityPrincipalid = column_ifexists("UserIdentityPrincipalid", ""),
    UserIdentityArn = column_ifexists("UserIdentityArn", ""),
    SessionIssuerType = column_ifexists("SessionIssuerType", ""),
    EventName = column_ifexists("EventName", ""),
    RequestParameters = column_ifexists("RequestParameters", ""),
    UserIdentityInvokedBy = column_ifexists("UserIdentityInvokedBy", ""),
    UserIdentityUserName = column_ifexists("UserIdentityUserName", ""),
    UserIdentityAccessKeyId = column_ifexists("UserIdentityAccessKeyId", ""),
    SessionIssuerUserName = column_ifexists("SessionIssuerUserName", "")
| lookup (_AWSAccounts | project RecipientAccountId = AccountId, RecipientAccountName = AccountName) on RecipientAccountId
| lookup (_AWSAccounts | project UserIdentityAccountId = AccountId, UserIdentityAccountName = AccountName) on UserIdentityAccountId
| extend
    Identity = case(
        UserIdentityType == "AWSAccount", strcat(coalesce(UserIdentityAccountName, UserIdentityAccountId), extract(@"(\:[^\:]+$)", 1, UserIdentityPrincipalid)),
        UserIdentityType == "Root", strcat(coalesce(UserIdentityAccountName, UserIdentityAccountId), extract(@"(\:[^\:]+$)", 1, UserIdentityArn)),
        UserIdentityType == "IAMUser", strcat(coalesce(UserIdentityAccountName, UserIdentityAccountId), extract(@"(\:[^\:]+$)", 1, UserIdentityArn)),
        UserIdentityType == "AssumedRole" and SessionIssuerType in ("", "Role"), extract(@"\:([^\:]+$)", 1, UserIdentityPrincipalid),
        UserIdentityType == "AWSService" and EventName == "AssumeRole", tostring(todynamic(RequestParameters)["roleSessionName"]),
        UserIdentityType == "AWSService" and not(EventName == "AssumeRole"), UserIdentityInvokedBy,
        UserIdentityType == "SAMLUser" and EventName == "AssumeRoleWithSAML", UserIdentityUserName,
        UserIdentityType == "WebIdentityUser" and EventName == "AssumeRoleWithWebIdentity", UserIdentityUserName,
        UserIdentityType == "Unknown" and EventName == "UserAuthentication" and UserIdentityPrincipalid == UserIdentityAccountId, coalesce(UserIdentityAccountName, UserIdentityPrincipalid),
        UserIdentityType == "Unknown" and not(EventName == "UserAuthentication"), coalesce(UserIdentityUserName, UserIdentityPrincipalid, UserIdentityAccessKeyId),
        UserIdentityType == "", coalesce(UserIdentityInvokedBy, tostring(split(todynamic(RequestParameters)["sessionId"], "-")[0])),
        extract(@"\:([^\:]+$)", 1, UserIdentityPrincipalid)
    ),
    Role = case(
        UserIdentityType == "AssumedRole", coalesce(SessionIssuerUserName, extract(@"\:assumed-role\/([^\/]+)\/", 1, UserIdentityArn)),
        EventName matches regex "^AssumeRole", tostring(split(todynamic(RequestParameters)["roleArn"], "/")[-1]),
        UserIdentityType == "Unknown" and EventName in ("Federate", "GetRoleCredentials"), tostring(todynamic(ServiceEventDetails)["role_name"]),
        ""
    )

Explanation

This query is designed to extract and organize information about AWS accounts and their activities.

First, it retrieves a list of AWS accounts from a watchlist named "AccountId-AuditAWSAccounts".

Then, it extends the table with several new columns, each representing a different piece of information about the account or its activities. These include the account ID, user identity, event name, and more. If any of these fields do not exist in the original data, they are filled with an empty string.

Next, it uses the 'lookup' function to match the account IDs in the extended table with those in the watchlist, and adds the corresponding account names to the table.

Finally, it further extends the table with two new columns: 'Identity' and 'Role'. These columns are populated based on a series of conditions that take into account the type of user identity, the event name, and other factors. The 'Identity' column is designed to provide a clear identifier for the user or service that performed the action, while the 'Role' column indicates the role assumed by the user or service during the action.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: September 13, 2023

Tables

T_GetWatchlist

Keywords

AWSAccounts,RecipientAccountId,UserIdentityAccountId,UserIdentityType,UserIdentityPrincipalid,UserIdentityArn,SessionIssuerType,EventName,RequestParameters,UserIdentityInvokedBy,UserIdentityUserName,UserIdentityAccessKeyId,SessionIssuerUserName,Identity,Role

Operators

letextendcolumn_ifexistslookupprojectcasestrcatcoalesceextractintostringtodynamicnotmatchesregexsplitand"-"

Actions