How do I use Athena to query my CloudTrail logs for AWS Support API calls?

Short description

To help you resolve your issue, AWS Support might gather secure read-only metadata about your AWS resources. AWS Support uses an AWS Identity and Access Management (IAM) service-linked role that's called AWSServiceRoleForSupport to gather the metadata.

To find information about the API calls that AWS Support makes to your account, use Athena to query your CloudTrail logs.

Resolution

Use partition projection to manually create a CloudTrail table

Even when you partition the CloudTrail table to reduce the query runtime, CloudTrail logs can grow in size over time. Queries that you run against a highly partitioned table have a high plan time and don't complete quickly.

To reduce query runtimes, use partition projection to manually create a CloudTrail table.

For example, the following CREATE TABLE statement automatically uses partition projection on CloudTrail logs within a specified period of time in one AWS Region:

CREATE EXTERNAL TABLE cloudtrail_logs_pp(  
    eventversion STRING,  
    useridentity STRUCT<  
        type: STRING,  
        principalid: STRING,  
        arn: STRING,  
        accountid: STRING,  
        invokedby: STRING,  
        accesskeyid: STRING,  
        username: STRING,  
        onbehalfof: STRUCT<  
             userid: STRING,  
             identitystorearn: STRING>,  
        sessioncontext: STRUCT<  
            attributes: STRUCT<  
                mfaauthenticated: STRING,  
                creationdate: STRING>,  
            sessionissuer: STRUCT<  
                type: STRING,  
                principalid: STRING,  
                arn: STRING,  
                accountid: STRING,  
                username: STRING>,  
            ec2roledelivery:string,  
            webidfederationdata: STRUCT<  
                federatedprovider: STRING,  
                attributes: map<string,string>>  
        >  
    >,  
    eventtime STRING,  
    eventsource STRING,  
    eventname STRING,  
    awsregion STRING,  
    sourceipaddress STRING,  
    useragent STRING,  
    errorcode STRING,  
    errormessage STRING,  
    requestparameters STRING,  
    responseelements STRING,  
    additionaleventdata STRING,  
    requestid STRING,  
    eventid STRING,  
    readonly STRING,  
    resources ARRAY<STRUCT<  
        arn: STRING,  
        accountid: STRING,  
        type: STRING>>,  
    eventtype STRING,  
    apiversion STRING,  
    recipientaccountid STRING,  
    serviceeventdetails STRING,  
    sharedeventid STRING,  
    vpcendpointid STRING,  
    vpcendpointaccountid STRING,  
    eventcategory STRING,  
    addendum STRUCT<  
      reason:STRING,  
      updatedfields:STRING,  
      originalrequestid:STRING,  
      originaleventid:STRING>,  
    sessioncredentialfromconsole STRING,  
    edgedevicedetails STRING,  
    tlsdetails STRUCT<  
      tlsversion:STRING,  
      ciphersuite:STRING,  
      clientprovidedhostheader:STRING>  
  )  
PARTITIONED BY (  
   `timestamp` string)  
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'  
STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'  
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'  
LOCATION  
  's3://example-bucket/AWSLogs/account-id/CloudTrail/aws-region'  
TBLPROPERTIES (  
  'projection.enabled'='true',   
  'projection.timestamp.format'='yyyy/MM/dd',   
  'projection.timestamp.interval'='1',   
  'projection.timestamp.interval.unit'='DAYS',   
  'projection.timestamp.range'='2020/01/01,NOW',   
  'projection.timestamp.type'='date',   
  'storage.location.template'='s3://example-bucket/AWSLogs/account-id/CloudTrail/aws-region/${timestamp}')

Note: In the LOCATION and TBLPROPERTIES clauses, replace example-bucket with your bucket name, account-id with your account ID, and aws-region with your Region. For the projection.timestamp.range, replace 2020/01/01 with your start date.

To run the preceding query and create a partitioned CloudTrail logs table, complete the following steps:

1. Open the Athena console.
2. Choose New query, and then choose the dialog box to clear the sample query.
3. Enter the preceding query, and then choose Run Query.

For more information, see Create the table for CloudTrail logs in Athena using partition projection.

Search the CloudTrail logs table for AWS Support API calls

The following are example queries that you can use to search for AWS Support calls in the CloudTrail logs table during a specified time range. The examples use 2025/01/01 to 2025/01/07 as the specified time range.

Note: In the following example queries, replace default with the name of the database where you created the cloudtrail_logs_pp table.

Count the number of AWS Support API calls

The following query counts the total number of API calls that AWS Support made to your account:

SELECT COUNT(*)  
  FROM "default"."cloudtrail_logs_pp"  
 WHERE timestamp <= '2025/01/07'  
       AND timestamp > '2025/01/01'  
       AND sourceipaddress = 'support.amazonaws.com'

Get all of the AWS Support API calls

The following query shows all API calls that AWS Support made to your account:

SELECT *  
  FROM "default"."cloudtrail_logs_pp"  
 WHERE timestamp <= '2025/01/07'  
       AND timestamp > '2025/01/01'  
       AND sourceipaddress = 'support.amazonaws.com'  
 LIMIT 10

Count the number of AWS Support API calls by username and event source

The following query shows the total number of API calls for each username and event source that AWS Support made to your account:

SELECT useridentity.sessioncontext.sessionissuer.username,  
       eventsource,  
       COUNT(*) AS number_of_event  
  FROM "default"."cloudtrail_logs_pp"  
 WHERE timestamp <= '2025/01/07'  
       AND timestamp > '2025/01/01'  
       AND sourceipaddress = 'support.amazonaws.com'  
 GROUP BY useridentity.sessioncontext.sessionissuer.username,  
       eventsource  
 ORDER BY number_of_event DESC

Get the ARNs for API calls that AWS Support made to your account

The following query shows the ARN for all API calls that AWS Support made to your account:

SELECT useridentity.arn  
  FROM "default"."cloudtrail_logs_pp"  
 WHERE timestamp <= '2025/01/07'  
       AND timestamp > '2025/01/01'  
       AND regexp_like(useridentity.arn, 'AWSServiceRoleForSupport')  
 LIMIT 10

Get the principal IDs for API calls that AWS Support made to your account

The following query shows the principal IDs for all API calls that AWS Support made to your account:

SELECT useridentity.principalid  
  FROM "default"."cloudtrail_logs_pp"  
WHERE timestamp <= '2025/01/07'  
      AND timestamp > '2025/01/01'  
      AND regexp_like(useridentity.arn, 'AWSServiceRoleForSupport')  
LIMIT 10

Related information

Adding and removing IAM identity permissions

Identity and access management in Athena

How can I use partition projection to create CloudTrail tables for Athena queries?