# 使用CloudTrail  Lake进行日志筛选

\[Github] [aws-samples](https://github.com/aws-samples)/[cloud-trail-lake-query-samples](https://github.com/aws-samples/cloud-trail-lake-query-samples)

通过复制粘贴提供的检索样例语句，可以快速查找所需要的信息，对日志进行分析。

<table><thead><tr><th>name</th><th>作用</th></tr></thead><tbody><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/SelfAssumeRole.sql">SelfAssumeRole.sql</a></td><td>Roles assuming themselves are typically the result of unnecessary operations in code Self assume role events count towards the STS quota.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/activity-performed-by-IP-address.sql">activity-performed-by-IP-address.sql</a></td><td>This query confirms if there were any activity performed from IP address in other AWS accounts in your organization except one during specific window time grouped by AWS account. Replace with your Event Data Store Id number, the &#x3C;192.0.2.76> with the IP address you are looking for and the &#x3C;555555555555> with the AWS account you want to exclude.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/aurora-databases-with-performance-insights.sql">aurora-databases-with-performance-insights.sql</a></td><td>This query returns Aurora Postgresql DB instances that have performance insights enabled Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/aurora-mysql-databases-with-instance-class-information.sql">aurora-mysql-databases-with-instance-class-information.sql</a></td><td>This query returns Aurora MySQL databases with Instance class information created from beginning of 2022. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/aurora-postgresql-databases-with%20availability-zone-information.sql">aurora-postgresql-databases-with availability-zone-information.sql</a></td><td>This query returns Aurora PostgreSQL databases with Availability zone information. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/aws-support-access.sql">aws-support-access.sql</a></td><td>This query will show if AWS Support has taken over the AWSServiceRoleForSupport Role, for Data Sovereignty requirements. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/cloudtrail-configuration-changes.sql">cloudtrail-configuration-changes.sql</a></td><td>This query modifications to CloudTrail trails. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/console-login-with-no-mfa.sql">console-login-with-no-mfa.sql</a></td><td>his query returns console logins with no MFA. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/cross-account-access.sql">cross-account-access.sql</a></td><td>This query returns results where cross-account access was granted. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/data-by-week-day.sql">data-by-week-day.sql</a></td><td>This query shows counts of all Data events by Day of the Week. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/database-failover-information.sql">database-failover-information.sql</a></td><td>This query returns database failover information: Returns Region, DB, user, and time of a failover event for a database Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/database-reboot-information.sql">database-reboot-information.sql</a></td><td>This query results are a list in chronological order of DB reboots that have occured Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/db-cluster-point-in-time-restore.sql">db-cluster-point-in-time-restore.sql</a></td><td>This query returns source and target of an RDS point in time restore Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/dynamodb-scan-operations.sql">dynamodb-scan-operations.sql</a></td><td>This query returns raw records for all "scan" DunamoDB management events. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/ec2-api-run-instances.sql">ec2-api-run-instances.sql</a></td><td>This query returns EC2 instances information created across the organization during specific window time. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/ec2-events.sql">ec2-events.sql</a></td><td>This query lists raw records for all EC2 management events. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/event-response-element-by-event-id.sql">event-response-element-by-event-id.sql</a></td><td>This query obtain response element for a given CloudTrail event Id. Replace with your Event Data Store Id number and the CloudTrail event Id &#x3C;3270e016-59a1-4448-8dd1-d27a4796502d></td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/find-snapshots-not-encrypted.sql">find-snapshots-not-encrypted.sql</a></td><td>This query returns snapshots that are created which are not encrypted.<br>Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/iam-access-key-activity.sql">iam-access-key-activity.sql</a></td><td>This query returns AWS API activity performed by an IAM user access key and from which IP address during specific time window ordered by AWS service. Replace with your Event Data Store Id number and with the IAM user access keys.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/iam-access-key-success-activity.sql">iam-access-key-success-activity.sql</a></td><td>This query obtain successful activity performed by IAM user access key during specific window time grouped by AWS services and API. Replace with your Event Data Store Id number and the with the IAM user access keys.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/iam-idc-authenticate-api.sql">iam-idc-authenticate-api.sql</a></td><td>This query returns IAM Identity Center users who has authenticated into IAM Identity Center portal during specific window time. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/iam-idc-federate-api-by-idc-user.sql">iam-idc-federate-api-by-idc-user.sql</a></td><td>This query helps to confirm in which AWS accounts the IAM Identify Center user has federated using which IAM roles during specific window time. Replace with your Event Data Store Id number and the IAM Identity Center user <a href="mailto:alice@example.com">alice@example.com</a>.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/iam-idc-user-count-and-group-activity.sql">iam-idc-user-count-and-group-activity.sql</a></td><td>This query count and group activity based on APIs and the AWS services performed by the IAM Identity Center user during specific window time. Replace with your Event Data Store Id number and the IAM Identity Center user <a href="mailto:alice@example.com">alice@example.com</a>.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/iam-idc-user-create-iam-role.sql">iam-idc-user-create-iam-role.sql</a></td><td>This query returns API IAM CreateRole called by the IAM Identity Center user during specific window time. Replace with your Event Data Store Id number and the IAM Identity Center user <a href="mailto:alice@example.com">alice@example.com</a>.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/iam-idc-user-create-iam-user-acces-keys.sql">iam-idc-user-create-iam-user-acces-keys.sql</a></td><td>This query returns information about API IAM CreateUserAccessKeys performed by the IAM Identity Center user during specific window time. Replace with your Event Data Store Id number and the IAM Identity Center user <a href="mailto:alice@example.com">alice@example.com</a>.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/iam-idc-user-create-iam-user.sql">iam-idc-user-create-iam-user.sql</a></td><td>This query returns information about API IAM CreateUser performed by the IAM Identity Center user during specific window time. Replace with your Event Data Store Id number and the IAM Identity Center user <a href="mailto:alice@example.com">alice@example.com</a>.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/iam-idc-user-denied-activity.sql">iam-idc-user-denied-activity.sql</a></td><td>This query returns denied activity based errorCode response and the AWS services performed by the IAM Identity Center user during specific window time. Replace with your Event Data Store Id number and the IAM Identity Center user <a href="mailto:alice@example.com">alice@example.com</a>.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/iam-idc-user-modify-iam-policy.sql">iam-idc-user-modify-iam-policy.sql</a></td><td>This query returns information about API IAM PutRolePolicy called by the IAM Identity Center user during specific window time. Replace with your Event Data Store Id number and the IAM Identity Center user <a href="mailto:alice@example.com">alice@example.com</a>.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/iam-idc-user-mutable-actions.sql">iam-idc-user-mutable-actions.sql</a></td><td>This query returns activity based on mutable APIs and the AWS services performed by the IAM Identity Center user during specific window time. Replace with your Event Data Store Id number and the IAM Identity Center user <a href="mailto:alice@example.com">alice@example.com</a>.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/iam-principal-by-launched-ec2-instance-id.sql">iam-principal-by-launched-ec2-instance-id.sql</a></td><td>This query confirms who (principal Id) has launched an EC2 instance. Replace with your Event Data Store Id number and the with the EC2 instance that you are looking for.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/iam-role-activity.sql">iam-role-activity.sql</a></td><td>This query helps to confirm successful activity performed by IAM role during specific window time. Replace with your Event Data Store Id number and the <a href="arn:aws:iam::555555555555:role/alice">arn:aws:iam::555555555555:role/alice</a> with the IAM role ARN.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/iam-role-assumed-by-iam-access-key.sql">iam-role-assumed-by-iam-access-key.sql</a></td><td>This query helps to confirm which IAM role was assumed by an IAM user access keys during specific window time. Replace with your Event Data Store Id number and with the IAM user access keys.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/list-all-S3-PutObjects-Encryption-status.sql">list-all-S3-PutObjects-Encryption-status.sql</a></td><td>This query lists the encryption status of Objects uploaded to S3 buckets in the descending order of event time. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/list-all-errors-for-s3-events.sql">list-all-errors-for-s3-events.sql</a></td><td>This query can be used for troubleshooting purposes as it lists all the error messages for S3 source. You can use the query for all resources, just modifying the eventSource. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/list-all-events-not-in-authorized-region.sql">list-all-events-not-in-authorized-region.sql</a></td><td>This query can be used if there are requirements to use only a subsets of AWS regions.<br>It lists any events which involve non authorized regions which may help to identify non-compliance scenarios. Replace with your Event Data Store Id number and replace , with the desired region and your account id.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/list-all-manually-created-resources.sql">list-all-manually-created-resources.sql</a></td><td>This query gets a list of all resources that have been created manually (i.e outside of CloudFormation or via set list of CI/CD users), along with details on the action taken. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/list-attempted-s3-requests..sql">list-attempted-s3-requests..sql</a></td><td>This query Shows wich identity is making the most GetObject requests from S3 and what it is downloading, including error detail and attempted unauthorized accesses. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/list-events-with-errors.sql">list-events-with-errors.sql</a></td><td>This query analyzes CloudTrail Events and identifies any calls that result in errors. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/list-publicly-accessible-rds-instances.sql">list-publicly-accessible-rds-instances.sql</a></td><td>This query lists Publicly Accessible RDS Instances. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/list-write-events-from-management-console.sql">list-write-events-from-management-console.sql</a></td><td>This query analyzes CloudTrail Events and identifies any calls that are made to AWS service APIs via the AWS Management Console. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/logs-by-region.sql">logs-by-region.sql</a></td><td>This query returns summary of regions in use and well as what services are used in these regions. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/rds-db-deleted-with-no-snapshot.sql">rds-db-deleted-with-no-snapshot.sql</a></td><td>This query returns details when a RDS DB was deleted without taking final snapshot. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/s3-actions-by-iam-access-key.sql">s3-actions-by-iam-access-key.sql</a></td><td>This query obtain S3 bucket and object names affected by an IAM user access kesy during a specifc window time. Replace with your Event Data Store Id number and the with the IAM user access keys.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/s3-buckets-using-acls.sql">s3-buckets-using-acls.sql</a></td><td><p>This query identifies buckets across an Organization with requests that rely on ACLs. This can help when migrating away from legacy ACLs to IAM Policies.</p><p>Replace with your Event Data Store ID number.</p></td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/tag-history-by-resource.sql">tag-history-by-resource.sql</a></td><td>This query returns tag history for resources. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/tls-version.sql">tls-version.sql</a></td><td>This query shows all API requests where the specified TLS version was not used. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/top-actions-by-iam-role.sql">top-actions-by-iam-role.sql</a></td><td>This query count activity performed by an IAM role during specific time period grouped by AWS services and APIs. Replace with your Event Data Store Id number and the <a href="arn:aws:iam::555555555555:role/alice">arn:aws:iam::555555555555:role/alice</a> with the IAM role ARN.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/top-errors.sql">top-errors.sql</a></td><td>This query lists the top Error messages for the specified time range Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/top-iam-actions-by-principal.sql">top-iam-actions-by-principal.sql</a></td><td><p></p><p>This query identifies the top callers of the AWS IAM service based on their number of API calls. It can help you identity which principals are calling IAM the most and if these principals may be close to service limits.</p><pre><code>To use this query, Replace &#x3C;EDS ID> with your Event Data Store Id.
</code></pre></td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/top-s3-actions.sql">top-s3-actions.sql</a></td><td>This query lists the count of data events by API actions for a specified S3 bucket Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/top-s3-objects.sql">top-s3-objects.sql</a></td><td>This query returns the most retrieved S3 Objects. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/user-added-to-admin-group.sql">user-added-to-admin-group.sql</a></td><td>This query returns when some user was made admin and who did it (added to any groups with name containing word ‘admin’). Helps identifying privilege escalation related issues. Replace with your Event Data Store Id number.</td></tr><tr><td><a href="https://github.com/aws-samples/cloud-trail-lake-query-samples/blob/main/users-by-account.sql">users-by-account.sql</a></td><td>This query returns all requests by user by account for the specified time period. Ordered by request count. Replace with your Event Data Store Id number. </td></tr></tbody></table>
