CloudTrail 証跡から Quick Sight ダッシュボード・分析の利用状況を可視化する

CREATE DATABASE IF NOT EXISTS cloudtrail_verification
COMMENT 'QuickSightダッシュボード参照ログ可視化用';

CREATE EXTERNAL TABLE cloudtrail_pp (
    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: 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,
    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,
    dt 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://cm-members-cloudtrail-<AWS アカウント ID>/AWSLogs/<AWS アカウント ID>/CloudTrail/'
TBLPROPERTIES (
    'classification'='cloudtrail',
    'projection.enabled'='true',
    'projection.region.type'='enum',
    'projection.region.values'='ap-northeast-1,us-east-1',
    'projection.dt.type'='date',
    'projection.dt.format'='yyyy/MM/dd',
    'projection.dt.range'='2025/01/01,NOW',
    'projection.dt.interval'='1',
    'projection.dt.interval.unit'='DAYS',
    'storage.location.template'='s3://cm-members-cloudtrail-<AWS アカウント ID>/AWSLogs/<AWS アカウント ID>/CloudTrail/${region}/${dt}'
);

s3://<バケット名>/AWSLogs/<AWSアカウントID>/CloudTrail/${region}/yyyy/mm/dd/

CREATE OR REPLACE VIEW qs_resource_views AS
SELECT
  region,
  dt,
  eventid AS event_id,
  date_add(
    'hour', 9,
    CAST(from_iso8601_timestamp(eventtime) AS TIMESTAMP)
  ) AS viewed_at_jst,
  CASE eventname
    WHEN 'GetDashboard' THEN 'DASHBOARD'
    ELSE 'ANALYSIS'
  END AS resource_type,
  element_at(
    split(
      COALESCE(
        json_extract_scalar(
          serviceeventdetails,
          '$.eventRequestDetails.dashboardId'
        ),
        json_extract_scalar(
          serviceeventdetails,
          '$.eventRequestDetails.analysisId'
        ),
        json_extract_scalar(
          requestparameters,
          '$.dashboardId'
        ),
        json_extract_scalar(
          requestparameters,
          '$.analysisId'
        )
      ),
      '/'
    ),
    -1
  ) AS resource_id,
  COALESCE(
    json_extract_scalar(
      json_extract(
        serviceeventdetails,
        '$.eventResponseDetails'
      ),
      '$.dashboardDetails.dashboardName'
    ),
    json_extract_scalar(
      json_extract(
        serviceeventdetails,
        '$.eventResponseDetails'
      ),
      '$.analysisDetails.analysisName'
    )
  ) AS logged_name,
  COALESCE(
    -- 1. Quick Sight 招待ユーザー・IAM ユーザーの名前
    NULLIF(useridentity.username, ''),
    -- 2. AssumedRoleのセッション名（principalidの末尾）
    NULLIF(
      element_at(
        split(useridentity.principalid, ':'),
        -1
      ),
      ''
    ),
    -- 3. ロール名（共有ロールだと全員同じになるので2より後）
    NULLIF(
      useridentity
        .sessioncontext
        .sessionissuer
        .username,
      ''
    )
  ) AS viewer,
  useridentity.type AS identity_type
FROM cloudtrail_pp
WHERE eventsource
      = 'quicksight.amazonaws.com'
  AND eventname IN (
        'GetDashboard',
        'GetAnalysis'
      );

s3://<S3 バケット名>/qs-master/resources/resources.json

import json
import os
from datetime import datetime, timezone

import boto3

ACCOUNT_ID = os.environ["ACCOUNT_ID"]
BUCKET = os.environ["MASTER_BUCKET"]
REGIONS = [
    r.strip()
    for r in os.environ.get(
        "QS_REGIONS", "ap-northeast-1,us-east-1"
    ).split(",")
    if r.strip()
]
RESOURCE_KEY = os.environ.get(
    "RESOURCE_KEY", "qs-master/resources/resources.json"
)

s3 = boto3.client("s3")

def _iso(value):
    """datetime を ISO8601(UTC) 文字列にする。None はそのまま返す。"""
    if value is None:
        return None
    return value.astimezone(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ")

def _list(client, api, result_key, **extra):
    """NextToken を手で回す。QuickSightのAPIはpaginator未提供のものがある。"""
    fn = getattr(client, api)
    items = []
    token = None
    while True:
        params = {"AwsAccountId": ACCOUNT_ID, "MaxResults": 100, **extra}
        if token:
            params["NextToken"] = token
        resp = fn(**params)
        items.extend(resp.get(result_key, []))
        token = resp.get("NextToken")
        if not token:
            return items

def _put_jsonl(key, records):
    lines = [json.dumps(r, ensure_ascii=False) for r in records]
    # 0件のときに空行だけのファイルを置くと JsonSerDe がエラーになる
    body = ("\n".join(lines) + "\n") if lines else ""
    s3.put_object(
        Bucket=BUCKET,
        Key=key,
        Body=body.encode("utf-8"),
        ContentType="application/x-ndjson",
    )

def lambda_handler(event, context):
    snapshot_date = datetime.now(timezone.utc).strftime("%Y-%m-%d")
    resources = []

    for region in REGIONS:
        qs = boto3.client("quicksight", region_name=region)

        for d in _list(qs, "list_dashboards", "DashboardSummaryList"):
            resources.append(
                {
                    "resource_type": "DASHBOARD",
                    "resource_id": d["DashboardId"],
                    "resource_name": d.get("Name"),
                    "status": None,
                    "region": region,
                    "created_time": _iso(d.get("CreatedTime")),
                    "last_updated_time": _iso(d.get("LastUpdatedTime")),
                    "last_published_time": _iso(d.get("LastPublishedTime")),
                    "snapshot_date": snapshot_date,
                }
            )

        # list-analyses はゴミ箱（30日保持）の分析も Status=DELETED で返す。
        # 除外はせずカラムで持ち、Athena側のビューで絞る。
        for a in _list(qs, "list_analyses", "AnalysisSummaryList"):
            resources.append(
                {
                    "resource_type": "ANALYSIS",
                    "resource_id": a["AnalysisId"],
                    "resource_name": a.get("Name"),
                    "status": a.get("Status"),
                    "region": region,
                    "created_time": _iso(a.get("CreatedTime")),
                    "last_updated_time": _iso(a.get("LastUpdatedTime")),
                    "last_published_time": None,
                    "snapshot_date": snapshot_date,
                }
            )

    _put_jsonl(RESOURCE_KEY, resources)

    counts = {}
    for r in resources:
        key = f"{r['region']}/{r['resource_type']}"
        counts[key] = counts.get(key, 0) + 1

    return {"counts": counts, "snapshot_date": snapshot_date}

{
  "counts": {
    "ap-northeast-1/DASHBOARD": 21,
    "ap-northeast-1/ANALYSIS": 91,
    "us-east-1/DASHBOARD": 1,
    "us-east-1/ANALYSIS": 4
  },
  "snapshot_date": "2026-07-30"
}

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ListQuickSightResources",
      "Effect": "Allow",
      "Action": [
        "quicksight:ListDashboards",
        "quicksight:ListAnalyses"
      ],
      "Resource": [
        "arn:aws:quicksight:*:<アカウントID>:dashboard/*",
        "arn:aws:quicksight:*:<アカウントID>:analysis/*"
      ]
    },
    {
      "Sid": "PutMaster",
      "Effect": "Allow",
      "Action": "s3:PutObject",
      "Resource": "arn:aws:s3:::<マスタバケット>/qs-master/*"
    }
  ]
}

CREATE EXTERNAL TABLE qs_resource_master (
  resource_type       STRING,
  resource_id         STRING,
  resource_name       STRING,
  status              STRING,
  region              STRING,
  created_time        STRING,
  last_updated_time   STRING,
  last_published_time STRING,
  snapshot_date       STRING
)
ROW FORMAT SERDE
  'org.apache.hive.hcatalog.data.JsonSerDe'
LOCATION
  's3://<マスタバケット>/qs-master/resources/'
TBLPROPERTIES ('classification'='json');

CREATE OR REPLACE VIEW qs_resource_views_90d AS
SELECT
  v.region,
  v.viewed_at_jst,
  v.resource_type,
  v.resource_id,
  COALESCE(
    m.resource_name,
    v.logged_name
  ) AS resource_name,
  v.logged_name,
  v.viewer,
  v.identity_type
FROM qs_resource_views v
LEFT JOIN qs_resource_master m
  ON  m.region = v.region
  AND m.resource_id = v.resource_id
  AND m.resource_type = v.resource_type
WHERE v.dt >= date_format(
  CAST(
    date_add(
      'hour',
      9,
      CAST(current_timestamp AS TIMESTAMP)
    ) AS DATE
  ) - INTERVAL '90' DAY,
  '%Y/%m/%d'
);

CREATE OR REPLACE VIEW qs_resource_daily_usage AS
WITH days AS (
  SELECT
    CAST(d AS DATE) AS view_date
  FROM UNNEST(
    sequence(
      CAST(
        date_add(
          'hour', 9,
          CAST(current_timestamp AS TIMESTAMP)
        ) AS DATE
      ) - INTERVAL '89' DAY,
      CAST(
        date_add(
          'hour', 9,
          CAST(current_timestamp AS TIMESTAMP)
        ) AS DATE
      ),
      INTERVAL '1' DAY
    )
  ) AS t(d)
),
views AS (
  SELECT
    CAST(viewed_at_jst AS DATE) AS view_date,
    region,
    resource_type,
    resource_id,
    COUNT(DISTINCT event_id) AS event_count,
    COUNT(DISTINCT viewer) AS unique_viewer_count
  FROM qs_resource_views
  WHERE dt >= date_format(
    CAST(
      date_add(
        'hour', 9,
        CAST(current_timestamp AS TIMESTAMP)
      ) AS DATE
    ) - INTERVAL '90' DAY,
    '%Y/%m/%d'
  )
  GROUP BY 1, 2, 3, 4
),
master AS (
  SELECT
    resource_type,
    resource_id,
    resource_name,
    region,
    created_time
  FROM qs_resource_master
  WHERE resource_type = 'DASHBOARD'
     OR status NOT IN (
          'DELETED',
          'CREATION_FAILED',
          'CREATION_IN_PROGRESS'
        )
)
SELECT
  m.resource_type,
  m.resource_id,
  m.resource_name,
  m.region,
  m.created_time,
  d.view_date,
  COALESCE(v.event_count, 0) AS event_count,
  COALESCE(v.unique_viewer_count, 0) AS unique_viewer_count
FROM master m
CROSS JOIN days d
LEFT JOIN views v
  ON  v.region = m.region
  AND v.resource_id = m.resource_id
  AND v.resource_type = m.resource_type
  AND v.view_date = d.view_date;