This SQL view of AWS CloudTrail logs creates a comprehensive presentation for Amazon QuickSight Dashboard and Analysis activities of users. It extracts and standardizes key information including event timestamps, user identity details (account ID, type, ARN), session context, source IP addresses, and user agents. The view intelligently categoriz…

qs_actions_views.sql

CREATE OR REPLACE VIEW "qs_actions_views" AS 
SELECT
    DATE_PARSE(eventtime, '%Y-%m-%dT%H:%i:%sZ') AS event_time,
    awsregion,
    eventname,
    useridentity.accountid AS user_identity_account_id,
    useridentity.type AS user_identity_type,
    useridentity.arn AS user_identity_arn,
    useridentity.sessioncontext.sessionissuer.arn AS session_issuer_arn,
    sourceipaddress,
    useragent,
    "readonly",
    
    -- Determine resource type based on event name
    CASE 
        WHEN eventname LIKE '%Dashboard' THEN 'Dashboard' 
        WHEN eventname LIKE '%Analysis' THEN 'Analysis' 
    END AS resource_type,
    
    -- Extract resource name based on event type
    CASE 
        WHEN eventname IN ('UpdateDashboard', 'CreateDashboard', 'DeleteDashboard') 
            THEN json_extract_scalar(requestparameters, '$.name')
        WHEN eventname LIKE 'UpdateAnalysis' 
            THEN NULL
        WHEN eventname LIKE 'CreateAnalysis' 
            THEN json_extract_scalar(serviceeventdetails, '$.eventRequestDetails.analysisName')
        WHEN eventname LIKE '%Dashboard' 
            THEN json_extract_scalar(serviceeventdetails, '$.eventResponseDetails.dashboardDetails.dashboardName')
        WHEN eventname LIKE '%Analysis' 
            THEN json_extract_scalar(serviceeventdetails, '$.eventResponseDetails.analysisDetails.analysisName')
    END AS resource_name,
    
    -- Extract resource ARN/ID based on event type
    CASE 
        WHEN eventname IN ('UpdateDashboard', 'CreateDashboard', 'DeleteDashboard') 
            THEN json_extract_scalar(requestparameters, '$.dashboardId')
        WHEN eventname LIKE 'UpdateAnalysis' 
            THEN regexp_extract(serviceeventdetails, '"left":"analysisId","right":"([^"]+)"')
        WHEN eventname LIKE 'CreateAnalysis' 
            THEN json_extract_scalar(serviceeventdetails, '$.eventResponseDetails.analysisId')
        WHEN eventname LIKE '%Dashboard' 
            THEN json_extract_scalar(serviceeventdetails, '$.eventResponseDetails.dashboardDetails.dashboardId')
        WHEN eventname LIKE '%Analysis' 
            THEN json_extract_scalar(serviceeventdetails, '$.eventResponseDetails.analysisDetails.analysisId')
    END AS resource_arn,
    
    COALESCE(errormessage, errorcode, '') AS "error",
    serviceeventdetails
    
FROM "${cloud_trail_table}"
WHERE eventsource = 'quicksight.amazonaws.com'
    AND (eventname LIKE '%Dashboard' OR eventname LIKE '%Analysis')