# こんなスクリプトを GitHub Actions で定期実行している
echo "|Account ID|Account Name|User ID|Permission Sets|" > ./assignments.md
echo "|---|---|---|---|" >> assignments.md
aws-sso-util admin assignments --no-header --separator '|' | awk -F"|" '{print "|" $8 "|" $9 "|" $4 "|" $6 "|"}' | sort >> assignments.md
...<snip>

# 出力結果の例
$ cat assignments.md
|Account ID|Account Name|User ID|Permission Sets|
|---|---|---|---|
|012345678901|product.a|[email protected]|AWSAdministratorAccess|
|012345678901|product.a|[email protected]|AWSReadOnlyAccess|
|123456789012|sandbox.wa6sn|[email protected]|AWSAdministratorAccess|
...<snip>

CREATE EXTERNAL TABLE IF NOT EXISTS `cloudtrail_logs`.`partitioned_table` (
    `eventversion` string,
    `useridentity` STRUCT <
      type: STRING,
      principalid: STRING,
      arn: STRING,
      accountid: STRING,
      invokedby: STRING,
      accesskeyid: STRING,
      userName: STRING,
      sessioncontext: STRUCT <
        attributes: STRUCT <
          mfaauthenticated: STRING,
          creationdate: STRING
        >,
        sessionissuer: STRUCT <
          type: STRING,
          principalId: STRING,
          arn: STRING,
          accountId: STRING,
          userName: STRING
        >,
        ec2RoleDelivery: string,
        webIdFederationData: 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,
    `resources` array <
      STRUCT <
        arn: STRING,
        accountid: STRING,
        type: STRING
      >
    >,
    `eventtype` string,
    `apiversion` string,
    `readonly` string,
    `recipientaccountid` string,
    `serviceeventdetails` string,
    `sharedeventid` string,
    `vpcendpointid` string,
    `tlsDetails` struct <
      tlsVersion: string,
      cipherSuite: string,
      clientProvidedHostHeader: string
    >
  )
  PARTITIONED BY (region string, date string, accountid 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://<Control Tower の Log Archive アカウントで作成された S3 バケット>/AWSLogs/<Organization ID>/'
  TBLPROPERTIES (
      'projection.enabled' = 'true',
      'projection.date.type' = 'date',
      'projection.date.range' = '2023/01/01,NOW',
      'projection.date.format' = 'yyyy/MM/dd',
      'projection.date.interval' = '1',
      'projection.date.interval.unit' = 'DAYS',
      'projection.region.type' = 'enum',
      'projection.region.values'='us-east-1,us-east-2,us-west-1,us-west-2,af-south-1,ap-east-1,ap-south-1,ap-northeast-2,ap-southeast-1,ap-southeast-2,ap-northeast-1,ca-central-1,eu-central-1,eu-west-1,eu-west-2,eu-south-1,eu-west-3,eu-north-1,me-south-1,sa-east-1',
      'projection.accountid.type' = 'injected',
      'storage.location.template' = 's3://<Control Tower の Log Archive アカウントで作成された S3 バケット>/<Organization ID>/AWSLogs/<Organization ID>/${accountid}/CloudTrail/${region}/${date}',
      'classification'='cloudtrail',
      'compressionType'='gzip',
      'typeOfData'='file',
      'classification'='cloudtrail'
  );

WITH FilteredEvents AS (
    SELECT 
        eventTime,
        useridentity.accountid as AWSAccountId,
        regexp_extract(userIdentity.arn, '([a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{{2,4}})', 1) as UserName,
        regexp_extract(userIdentity.arn, '(AWSReservedSSO_[a-zA-Z0-9_]+)', 1) as RoleName,
        userIdentity.arn AS ARN
    FROM 
        partitioned_table
    WHERE 
        accountId IN ({account_ids_str})
        AND date between '{start_date_str}' and '{end_date_str}'
        AND userIdentity.arn LIKE '%AWSReservedSSO_%'
        AND userIdentity.type = 'AssumedRole'
),
RankedEvents AS (
    SELECT 
        eventTime,
        AWSAccountId,
        UserName,
        RoleName,
        ROW_NUMBER() OVER (PARTITION BY ARN ORDER BY from_iso8601_timestamp(eventTime) DESC) AS row_num
    FROM 
        FilteredEvents
)
SELECT 
    eventTime,
    AWSAccountId,
    UserName,
    RoleName
FROM 
    RankedEvents
WHERE 
    row_num = 1
ORDER BY AWSAccountId, UserName;

# Athena によるクエリの結果の例。
$ head -3 athena_query_results.csv
eventTime,AWSAccountId,UserName,RoleName
2024-03-21T09:13:52Z,012345678901,[email protected],AWSReservedSSO_AWSAdministratorAccess_0123456789abcdef
2024-04-15T13:03:53Z,123456789012,[email protected],AWSReservedSSO_AWSAdministratorAccess_23456789abcdef01
...<snip>

target_id,target_name,principal_name,permission_set_name,last_logined
012345678901,product.a,[email protected],AWSAdministratorAccess,2024-03-21T09:13:52Z
012345678901,product.a,[email protected],AWSReadOnlyAccess,
123456789012,sandbox.wa6sn,[email protected],AWSAdministratorAccess,2024-04-15T13:03:53Z
...<snip>