## 查询事件排序
SELECT eventname,eventtime,count(eventname) as num FROM your_athena_tablename
where eventtime between '2024-11-10' and '2024-11-11' group by eventname,eventtime order by num desc## 查询具体事件名对应的详细信息
SELECT * FROM your_athena_tablename where eventname ='GetCallerIdentity' and eventtime between '2024-11-10' and '2024-11-11' limit 50## 确定 CloudTrail 事件数量导致的成本增长情况
SELECT eventName,count(eventName) AS NumberOfChanges,eventSource
FROM your_athena_tablename
WHERE eventtime >='2024-11-10T00:00:00Z'and eventtime <'2024-11-11T00:00:00Z'
GROUP BY eventName, eventSource
ORDER BY NumberOfChanges DESC## 显示特定访问密钥的所有记录的 AWS API 活动
SELECT eventTime, eventName, userIdentity.principalId
FROM your_athena_tablename
WHERE userIdentity.accessKeyId like 'access_key_id'## 确定您的 EC2 实例的所有安全组更改
SELECT eventname, useridentity.username, sourceIPAddress, eventtime, requestparameters
FROM your_athena_tablename
WHERE (requestparameters like '%sg-5887f224%' or requestparameters like '%sg-e214609e%' or requestparameters like '%eni-6c5ca5a8%')
and eventtime >'2017-02-15T00:00:00Z'
order by eventtime asc;## 显示过去 24 小时内的所有控制台登录信息
SELECT useridentity.username, sourceipaddress, eventtime, additionaleventdata
FROM your_athena_tablename
WHERE eventname ='ConsoleLogin'
and eventtime >='2017-02-17T00:00:00Z'
and eventtime <'2017-02-18T00:00:00Z';## 显示过去 24 小时内所有失败的控制台登录尝试
SELECT useridentity.username, sourceipaddress, eventtime, additionaleventdata
FROM your_athena_tablename
WHERE eventname ='ConsoleLogin'
and useridentity.username ='HIDDEN_DUE_TO_SECURITY_REASONS'
and eventtime >='2017-02-17T00:00:00Z'
and eventtime <'2017-02-18T00:00:00Z';## 确定缺少的 IAM 权限
SELECT count (*) as TotalEvents, useridentity.arn, eventsource, eventname, errorCode, errorMessage
FROM your_athena_tablename
WHERE (errorcode like '%Denied%' or errorcode like '%Unauthorized%')
AND eventtime >='2019-10-28T00:00:00Z'
AND eventtime <'2019-10-29T00:00:00Z'
GROUP by eventsource, eventname, errorCode, errorMessage, useridentity.arn
ORDER by eventsource, eventname## 如果结果未显示失败的 API 调用，则通过类似于下面这样的方法扩大查询范围：
SELECT count (*) as TotalEvents, useridentity.arn, eventsource, eventname, errorCode, errorMessage
FROM your_athena_tablename
WHERE errorcode <>''
AND eventtime >='2019-10-28T00:00:00Z'
AND eventtime <'2019-10-29T00:00:00Z'
GROUP by eventsource, eventname, errorCode, errorMessage, useridentity.arn
ORDER by eventsource, eventname
