CloudFront Logs Analysis with Athena and Amazon QuickSight

7 min readMar 30, 2025

Introduction

This post details an implementation that provides an overview of CloudFront logs. By default, logging is an optional feature in CloudFront, and there are four logging options: Amazon CloudWatch Logs, Amazon Kinesis Data Firehose, Amazon S3, and Amazon S3 (legacy). Unfortunately, AWS CloudFormation supports only the Amazon S3 (legacy) option. CloudFront logs are stored in text format. AWS Athena can be used to better structure and analyse these logs. Additionally, Amazon QuickSight can help visualise historical data based on these logs.

Cost Estimation

While using Athena and QuickSight provides powerful insights, costs can accumulate based on data volume and usage:

Amazon S3 Storage: Costs depend on the size of stored logs (Standard tier: ~$0.023 per GB per month).

AWS Athena: Charges per query ($5 per TB scanned).

Amazon QuickSight:
Standard Edition: $9/user/month.
Enterprise Edition: $18/user/month.

CloudFront Logging: No extra cost for enabling logging, but data transfer and storage fees apply.

Project Structure

This post builds upon my earlier article on creating a static website on S3 with CloudFront and Route 53. In this enhanced version, monitoring and log analysis for the CloudFront distribution have been added to the existing infrastructure.

Core Components

Amazon S3 Bucket: Stores logs from CloudFront and Athena query results. The logs are stored in S3 in a structured format suitable for Athena queries.
Athena Table and Athena View: Organises and queries raw log data efficiently. SQL-based queries are used to extract meaningful insights from raw logs.
Amazon QuickSight Resources: This process converts data from the Athena View into the QuickSight Dashboard for visualisation. A QuickSight dataset is created from Athena’s query results, enabling dashboards and reports.

Configuration in infrastructure/cloudfront_logs_analyses.yaml CloudFormation template:

AWSTemplateFormatVersion: '2010-09-09'
Description: CFN template for CloudFront logs analysis with Athena View and Amazon Quicksight

Parameters:
  S3LoggingBucket:
    Type: String
    Default: ''

Resources:
  CloudFrontLogsDatabase:
    Type: AWS::Glue::Database
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseInput:
        Name: cloudfront_logs_db
        Description: "Glue database for CloudFront logs analysis"

  CloudFrontLogsTable:
    Type: AWS::Glue::Table
    Properties:
      CatalogId: !Ref AWS::AccountId
      DatabaseName: !Ref CloudFrontLogsDatabase
      TableInput:
        Name: cloudfront_logs
        Description: "Athena table for CloudFront logs"
        TableType: EXTERNAL_TABLE
        Parameters:
          classification: "cloudfront"
        StorageDescriptor:
          Location: !Sub "s3://${S3LoggingBucket}/static-web-cf-logs/"
          InputFormat: "org.apache.hadoop.mapred.TextInputFormat"
          OutputFormat: "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat"
          SerdeInfo:
            SerializationLibrary: "org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"
            Parameters:
              field.delim: "\t"
          Columns:
            - Name: date
              Type: string
            - Name: time
              Type: string
            - Name: x_edge_location
              Type: string
            - Name: sc_bytes
              Type: bigint
            - Name: c_ip
              Type: string
            - Name: cs_method
              Type: string
            - Name: cs_host
              Type: string
            - Name: cs_uri_stem
              Type: string
            - Name: sc_status
              Type: int
            - Name: cs_referer
              Type: string
            - Name: cs_user_agent
              Type: string
            - Name: cs_uri_query
              Type: string
            - Name: cs_cookie
              Type: string
            - Name: x_edge_result_type
              Type: string
            - Name: x_edge_request_id
              Type: string
            - Name: x_host_header
              Type: string
            - Name: cs_protocol
              Type: string
            - Name: cs_bytes
              Type: bigint
            - Name: time_taken
              Type: double
            - Name: x_forwarded_for
              Type: string
            - Name: ssl_protocol
              Type: string
            - Name: ssl_cipher
              Type: string
            - Name: x_edge_response_result_type
              Type: string
            - Name: cs_protocol_version
              Type: string
            - Name: fle_status
              Type: string
            - Name: fle_encrypted_fields
              Type: string
            - Name: c_port
              Type: int
            - Name: time_to_first_byte
              Type: double
            - Name: x_edge_detailed_result_type
              Type: string
            - Name: sc_content_type
              Type: string
            - Name: sc_content_len
              Type: bigint
            - Name: sc_range_start
              Type: string
            - Name: sc_range_end
              Type: string

  AthenaWorkGroup:
    Type: AWS::Athena::WorkGroup
    Properties:
      Name: cloudfront_logs_workgroup
      Description: "Athena workgroup for CloudFront logs queries"
      State: ENABLED
      WorkGroupConfiguration:
        EnforceWorkGroupConfiguration: true
        ResultConfiguration:
          OutputLocation: !Sub "s3://${S3LoggingBucket}/athena-query-results/"

  QuickSightAthenaAccessRole:
    Type: AWS::IAM::Role
    Properties:
      RoleName: QuickSightAthenaRole
      AssumeRolePolicyDocument:
        Statement:
          - Effect: Allow
            Principal:
              Service: "quicksight.amazonaws.com"
            Action: "sts:AssumeRole"
      Policies:
        - PolicyName: QuickSightAthenaAccessPolicy
          PolicyDocument:
            Statement:
              - Effect: Allow
                Action:
                  - "athena:StartQueryExecution"
                  - "athena:GetQueryResults"
                  - "athena:GetQueryExecution"
                Resource: "*"
              - Effect: Allow
                Action:
                  - "s3:GetObject"
                  - "s3:ListBucket"
                Resource: !Sub "arn:${AWS::Partition}:s3:::${S3LoggingBucket}/*"

Prerequisites

Ensure the following prerequisites are in place:

- An AWS account with sufficient permissions to create and manage resources.
- An Amazon QuickSight account.
- The AWS CLI installed on your local machine.
- Infrastructure deployed with a CloudFormation template from my previous post or from my GitLab repo (if applicable).

Deployment

Fill in all necessary parameters in the CloudFormation template and create the CloudFormation stack.

aws cloudformation create-stack \
    --stack-name cloudfront-logs-analyses \
    --template-body file://infrastructure/cloudfront_logs_analyses.yaml \
    --capabilities CAPABILITY_NAMED_IAM --disable-rollback

2. Optional step: Analysing CloudFront logs using Athena. Example can be found in infrastructure/athena_results.csv file.

aws athena start-query-execution \
    --query-string "
 SELECT *
 FROM cloudfront_logs_db.cloudfront_logs
 WHERE date = '2025-03-26'
 AND time BETWEEN '08:00:00' AND '08:59:59'
 ORDER BY time DESC
 LIMIT 5;" \
    --query-execution-context Database=cloudfront_logs_db \
    --result-configuration OutputLocation=s3://static-web-cf-logs-bucket-<account-id>/athena-query-results/

aws athena get-query-execution \
    --query-execution-id <execution-id> \
    --query 'QueryExecution.ResultConfiguration.OutputLocation' \
    --output text

aws s3 cp s3://static-web-cf-logs-bucket-<account-id>/<execution-id>.csv ./athena_results.csv

3. Create Athena view for QuickSight Data source.

aws athena start-query-execution \
    --query-string "
    CREATE OR REPLACE VIEW cloudfront_aggregated_logs AS
    SELECT 
        CONCAT(date, ' ', time) AS combined_timestamp,
        COUNT(*) AS total_requests,
        COUNT(CASE WHEN x_edge_response_result_type = 'Hit' OR x_edge_response_result_type = 'Miss' THEN 1 END) AS success_requests,
        COUNT(CASE WHEN x_edge_response_result_type = 'Error' OR x_edge_detailed_result_type LIKE '%Error%' THEN 1 END) AS error_requests,
        COUNT(CASE WHEN x_edge_response_result_type = 'Redirect' THEN 1 END) AS redirect_requests
    FROM cloudfront_logs_db.cloudfront_logs
    GROUP BY CONCAT(date, ' ', time)
    ORDER BY combined_timestamp DESC;" \
    --query-execution-context "{
        \"Database\": \"cloudfront_logs_db\"
    }" \
    --result-configuration "{
        \"OutputLocation\": \"s3://static-web-cf-logs-bucket-<account-id>/\"
    }"

4. Create IAM role for QuickSight access and Amazon QuickSight Data source and Dataset.

aws iam create-role \
  --role-name QuickSightAthenaRole \
  --assume-role-policy-document file://infrastructure/quicksight_iam_role/quicksight-trust-policy.json

aws iam put-role-policy \
  --role-name QuickSightAthenaRole \
  --policy-name QuickSightAthenaAccessPolicy \
  --policy-document file://infrastructure/quicksight_iam_role/quicksight-athena-access-policy.json

aws quicksight create-data-source \
    --aws-account-id <account-id> \
    --data-source-id cloudfront-logs-ds \
    --name cloudfront_logs_ds \
    --type ATHENA \
    --data-source-parameters '{
        "AthenaParameters": {
            "WorkGroup": "primary"
        }
    }' \
    --permissions '[
        {
            "Principal": "arn:aws:quicksight:eu-central-1:<account-id>:user/default/user1",
            "Actions": [
                "quicksight:DescribeDataSource",
                "quicksight:DescribeDataSourcePermissions",
                "quicksight:PassDataSource",
                "quicksight:UpdateDataSource",
                "quicksight:DeleteDataSource",
                "quicksight:UpdateDataSourcePermissions"
            ]
        }
    ]'


aws quicksight create-data-set \
    --aws-account-id <account-id> \
    --data-set-id cloudfront-logs-requests-dataset \
    --name cloudfront_aggregated_logs \
    --import-mode DIRECT_QUERY \
    --physical-table-map '{
        "CloudFrontTable": {
            "RelationalTable": {
                "DataSourceArn": "arn:aws:quicksight:eu-central-1:<account-id>:datasource/cloudfront-logs-ds",
                "Catalog": "AwsDataCatalog",
                "Schema": "cloudfront_logs_db",
                "Name": "cloudfront_aggregated_logs",
                "InputColumns": [
                    { "Name": "combined_timestamp", "Type": "STRING" },
                    { "Name": "total_requests", "Type": "INTEGER" },
                    { "Name": "success_requests", "Type": "INTEGER" },
                    { "Name": "error_requests", "Type": "INTEGER" },
                    { "Name": "redirect_requests", "Type": "INTEGER" }
                ]
            }
        }
    }' \
    --logical-table-map '{
        "CloudFrontLogicalTable": {
            "Alias": "cloudfront_logs",
            "Source": {
                "PhysicalTableId": "CloudFrontTable"
            }
        }
    }' \
    --permissions '[
        {
            "Principal": "arn:aws:quicksight:eu-central-1:<account-id>:user/default/user1",
            "Actions": [
                "quicksight:DescribeDataSet",
                "quicksight:UpdateDataSetPermissions",
                "quicksight:PassDataSet",
                "quicksight:UpdateDataSet",
                "quicksight:DeleteDataSet",
                "quicksight:DescribeIngestion",
                "quicksight:CreateIngestion",
                "quicksight:CancelIngestion",
                "quicksight:DescribeDataSetPermissions",
                "quicksight:ListIngestions"
            ]
        }
    ]'

5. Create an Analysis in QuickSight.

Go to the QuickSight console and create an analysis. Currently, it is possible to create an analysis using AWS CLI, but only based on a template. There is no option to create an analysis that includes a sheet — this must be done manually in the QuickSight console.

For more details, refer to the AWS CLI documentation about QuickSight Analysis.

Steps to Create an Analysis in QuickSight Console:
Navigate to QuickSight Console → Analyses → New Analysis.
Select the dataset cloudfront_aggregated_logs → Use in analysis.
Click New sheet → Create.
Choose a visualization type (e.g., Line Chart).

Configure the chart:
X-axis: combined_timestamp.
Values: Select three values with average aggregation.
Example number of data points: 500.

Checking Analysis Configuration:
Find the necessary analysis ID:

aws quicksight list-analyses - aws-account-id <account-id>

Retrieve the analysis configuration. Example of analysis configuration can be found in infrastructure/analysis_config.json :

aws quicksight describe-analysis-definition - analysis-id <analysis-id> - aws-account-id <account-id>

6. Create a QuickSight Template and Dashboard. The template is not visible in the QuickSight console but is required for Dashboard creation with AWS CLI. After creation check the Dashboard view in the QuickSight console.

aws quicksight create-template \
    --aws-account-id <account-id> \
    --template-id cloudfront-logs-template \
    --name "cloudfront_logs_template" \
    --source-entity '{
        "SourceAnalysis": {
            "Arn": "arn:aws:quicksight:eu-central-1:<account-id>:analysis/8148eb46-3321-4ddb-8652-6ae218f671bd",
            "DataSetReferences": [
                {
                    "DataSetArn": "arn:aws:quicksight:eu-central-1:<account-id>:dataset/cloudfront-logs-requests-dataset",
                    "DataSetPlaceholder": "cloudfront_aggregated_logs"
                }
            ]
        }
    }' \
    --permissions '[
        {
            "Principal": "arn:aws:quicksight:eu-central-1:<account-id>:user/default/user1",
            "Actions": [
   "quicksight:UpdateTemplatePermissions",
   "quicksight:DescribeTemplatePermissions",
   "quicksight:UpdateTemplateAlias",
   "quicksight:DeleteTemplateAlias",
   "quicksight:DescribeTemplateAlias",
   "quicksight:ListTemplateAliases",
   "quicksight:ListTemplates",
   "quicksight:CreateTemplateAlias",
   "quicksight:DeleteTemplate",
   "quicksight:UpdateTemplate",
   "quicksight:ListTemplateVersions",
   "quicksight:DescribeTemplate",
   "quicksight:CreateTemplate"
            ]
        }
    ]'

aws quicksight create-dashboard \
    --aws-account-id <account-id> \
    --dashboard-id cloudfront-dashboard \
    --name "CloudFront Dashboard" \
    --source-entity '{
        "SourceTemplate": {
            "Arn": "arn:aws:quicksight:eu-central-1:<account-id>:template/cloudfront-logs-template",
            "DataSetReferences": [
                {
                    "DataSetPlaceholder": "cloudfront_aggregated_logs",
                    "DataSetArn": "arn:aws:quicksight:eu-central-1:<account-id>:dataset/cloudfront-logs-requests-dataset"
                }
            ]
        }
    }' \
    --permissions '[
        {
            "Principal": "arn:aws:quicksight:eu-central-1:<account-id>:user/default/user1",
            "Actions": [
   "quicksight:DescribeDashboard",
   "quicksight:ListDashboardVersions",
   "quicksight:UpdateDashboardPermissions",
   "quicksight:QueryDashboard",
   "quicksight:UpdateDashboard",
   "quicksight:DeleteDashboard",
   "quicksight:UpdateDashboardPublishedVersion",
   "quicksight:DescribeDashboardPermissions"
            ]
        }
    ]'

7. Cleanup Resources.
After testing, stop CloudFront logging with simple script, delete all data from the S3 bucket, delete the CloudFormation stack, and remove all resources from QuickSight. Don’t forget to terminate your Amazon QuickSight account to avoid additional costs.

./infrastructure/disable_cloudfront_distribution_logging.sh <cloudfront-id>

aws quicksight delete-dashboard --aws-account-id <account-id> --dashboard-id cloudfront-dashboard

aws quicksight delete-template --aws-account-id <account-id> --template-id cloudfront-logs-template

aws quicksight list-analyses --aws-account-id <account-id>

aws quicksight delete-analysis --analysis-id <analysis-id> --aws-account-id <account-id>

aws quicksight delete-data-set --aws-account-id <account-id> --data-set-id cloudfront-logs-requests-dataset

aws quicksight delete-data-source --aws-account-id <account-id> --data-source-id cloudfront-logs-ds

aws s3 rm s3://$BUCKET_NAME --recursive

aws cloudformation delete-stack --stack-name cloudfront-logs-analyses

Conclusion

Using Amazon QuickSight to visualise CloudFront logs is a powerful approach for log analysis. The key benefits include seamless integration with AWS services, automated resource provisioning using CloudFormation, and the ability to programmatically create QuickSight resources via AWS CLI. However, the main drawback is the potential cost, especially for smaller projects, as QuickSight pricing can add up quickly. Additionally, QuickSight currently lacks a way to fully configure analyses via AWS CLI, requiring some manual steps in the console.

If you found this post helpful and interesting, please click the clap button below to show your support. Feel free to use and share this post. 🙂