AppConfig で料金が高騰した場合に、呼び出し元を特定する Athena クエリ for CloudTrail

• AppConfig で料金が高騰した場合に、呼び出し元を特定するクエリ

SELECT useridentity.arn AS useridentity_arn,
       count(*) as configurationReceived
FROM cloudtrail_table
WHERE eventsource = 'appconfig.amazonaws.com'
  AND eventname = 'GetConfiguration'
  AND date = '2021/06/20' -- 確認したい日付に変更すること
  AND CAST(json_extract_scalar(requestparameters, '$.clientConfigurationVersion') AS VARCHAR) IS NULL
GROUP BY useridentity.arn
ORDER BY configurationReceived DESC;

• IAM ロール対応版

SELECT useridentity.sessioncontext.sessionissuer.arn,
       count(*) as configurationReceived
FROM  cloudtrail_table
WHERE eventsource = 'appconfig.amazonaws.com'
  AND eventname = 'GetConfiguration'
  AND date = '2021/07/02' -- 確認したい日付に変更すること
  AND CAST(json_extract_scalar(requestparameters, '$.clientConfigurationVersion') AS VARCHAR) IS NULL
GROUP BY useridentity.sessioncontext.sessionissuer.arn
ORDER BY configurationReceived DESC

Yuki Fujimura(c)