🛡️ Building a Cloud Security Dashboard with AWS Athena + QuickSight (Beginner Friendly)

⭐ Why I Built This Project

(Project 5 of 6 — Log Analysis & Dashboards with Athena + QuickSight)

Instead of studying cloud security concepts in isolation, I’m using real job descriptions as a roadmap and building hands-on projects that map directly to cloud security, cloud operations, and security engineering roles.

This 6-part series focuses on practical, resume-ready cloud security skills, including:

• Identity hardening and MFA enforcement
• IAM governance and access reviews
• Continuous monitoring of cloud resources
• Misconfiguration detection and drift analysis
• Log analysis, audit readiness, and evidence gathering
• Guard rails at scale using AWS Organizations + Service Control Policies (SCPs)
• Threat detection, anomaly monitoring, and incident triage

Each project is designed to reflect real-world responsibilities, not just theoretical learning.

---

📌 Project Sequence

👉 Part 1: AWS IAM Hardening — strengthening identity boundaries and improving authentication hygiene

👉 Part 2: Cloud Security Posture Management (CSPM) using Security Hub + AWS Config

👉 Part 3: CASB-Like Monitoring with GuardDuty + CloudTrail, focusing on anomalies, delegated admin, and safe threat simulation

👉 Part 4: Drift Detection with AWS Config, using managed rules, EventBridge routing, tags, and optional remediation

👉 Part 5: (this project) — Log Analysis & Dashboards with Athena + QuickSight

---

🔍 Why This Project Matters

Modern cloud security teams rely heavily on logs they are the single source of truth during an investigation or audit.

But raw CloudTrail logs are huge, noisy, and difficult to interpret without the right tools.

This project teaches you how to:

• Centralize your CloudTrail logs in S3
• Query them efficiently using Amazon Athena
• Build a real security dashboard using Amazon QuickSight
• Visualize user behavior, anomalies, and region-based activity
• Identify failed logins, root usage, and high-risk API calls
• Understand how analysts and cloud security engineers perform forensics and evidence gathering

You'll also learn the practical limitations of CloudTrail logs (delayed ingestion, missing fields, errorcode inconsistencies, timestamp parsing issues), and how to design visuals that still work even when the underlying logs aren’t perfect, a genuine real-world skill.

By the end of this project, you'll have a portfolio-ready CloudTrail security dashboard that demonstrates real account activity, provides security insights, and aligns directly with responsibilities listed in cloud security job descriptions.

---

Welcome to a hands-on, beginner-friendly walkthrough of building a Cloud Security Dashboard using three AWS services:

• CloudTrail → captures account activity
• Athena → queries CloudTrail logs using SQL
• QuickSight → visualizes security events

Along the way, I’ll share real troubleshooting moments, beginner tips, and cost-saving advice because learning AWS should be fun and affordable.

---

Table of Contents

1. Introduction
2. What You’ll Build
3. Prerequisites & Cost Awareness
4. Step 1 — Enable CloudTrail Logging
5. Step 2 — Verify CloudTrail Logs in S3
6. Step 3 — Set Up Athena
7. Step 4 — Create the External CloudTrail Table
8. Step 5 — Repair Partitions & Validate Data
9. Step 6 — Run Security SQL Queries in Athena
10. Step 7 — Build the QuickSight Dashboard
11. Step 8 — Publish & Share Your Dashboard
12. Step 9 — Cleanup & Cost Optimization
13. Troubleshooting
14. Final Thoughts

---

1. Introduction

Cloud security can feel overwhelming when you're new. AWS has logs everywhere, alerts everywhere, and tools everywhere. But once you learn how to connect a few core services, you unlock something powerful:

👉 You can see what’s happening inside your AWS account.

In this project, you'll turn raw CloudTrail logs into a visual dashboard showing:

• Failed console login attempts
• Root account activity
• API usage by region
• Top users making API calls

And you'll do it without needing expensive tools.

---

2. What You’ll Build

By the end, you’ll have:

• A working CloudTrail → S3 → Athena → QuickSight pipeline
• A security dashboard with four visuals
• Saved queries to help you think like a cloud security engineer
• A repeatable workflow you can show in interviews or your portfolio

And yes, this entire project stays within free or very low-cost AWS usage.

---

3. Prerequisites & Cost Awareness

You will need:

• An AWS account
• IAM permissions to use CloudTrail, S3, Athena, and QuickSight
• A region where QuickSight is supported

Cost Notes (Important)

• CloudTrail: 1 trail recording management events is free
• S3 storage: pennies for logs
• Athena: ~$5 per TB scanned (our dataset is tiny—cost is near $0)
• QuickSight: SPICE storage has a generous free tier

💡 Pro Tip: Delete Athena query results and disable CloudTrail when finished (covered in Cleanup).

---

4. Step 1 — Enable CloudTrail Logging

If you haven't already:

1. Open CloudTrail
2. Go to Trails → Create Trail
3. Choose:
   • Management events = Read/Write
   • Storage location = new S3 bucket
4. Click Create

CloudTrail will begin writing log files to S3 in about 5–10 minutes.

---

5. Step 2 — Verify CloudTrail Logs in S3

Navigate to the bucket you created:

s3://your-cloudtrail-bucket/AWSLogs/ACCOUNT-ID/CloudTrail/

Inside you should see folders named by region and date, such as:

us-east-1/2025/12/11/

Each folder contains .json.gz CloudTrail logs.

If you don’t see them, wait a few minutes or trigger activity in your account (login, create an IAM user, etc.).

---

6. Step 3 — Set Up Athena

Note: Query your data in Athena console

Go to Athena → Query Editor

1. Choose a results location (S3 bucket)
2. Create a new database:

CREATE DATABASE security_logs_db;

Select this database in the left panel.

---

7. Step 4 — Create the External CloudTrail Table

Use this DDL (adjust bucket/account ID):

CREATE EXTERNAL TABLE cloudtrail_logs(
  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
        >
      >
  >,
  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
)
PARTITIONED BY (region string, year string, month string, day string)
ROW FORMAT SERDE 'com.amazon.emr.hive.serde.CloudTrailSerde'
STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://your-cloudtrail-bucket/AWSLogs/<ACCOUNT-ID>/CloudTrail/';

Note: You'll likely need to create a table view to gather specific information.

CREATE OR REPLACE VIEW cloudtrail_flattened AS
SELECT
  -- Core event metadata
  eventtime,
  eventsource,
  eventname,
  awsregion,
  sourceipaddress,
  useragent,
  errorcode,
  errormessage,
  responseelements,
  eventid,

  -- Flattened identity fields
  useridentity.type        AS useridentity_type,
  useridentity.arn         AS useridentity_arn,
  useridentity.accountid   AS useridentity_accountid,
  useridentity.username    AS useridentity_username,
  useridentity.principalid AS useridentity_principalid,

  -- Additional context (optional but useful for investigations)
  eventtype,
  apiversion,
  readonly,
  recipientaccountid,
  vpcendpointid

FROM cloudtrail_logs;

---

8. Step 5 — Repair Partitions & Validate Data

Run:

MSCK REPAIR TABLE cloudtrail_logs;

This tells Athena to scan all folders (regions/dates) and register them.

Next, verify:

SELECT * 
FROM cloudtrail_logs 
ORDER BY eventtime DESC 
LIMIT 20;

If you get data → success!

If not, check:

• S3 bucket path
• CloudTrail folders
• IAM permissions for Athena

---

9. Step 6 — Run Security SQL Queries in Athena

Now let’s run some practical security queries.

9.1 Check event types

SELECT eventname, COUNT(*) 
FROM cloudtrail_logs
GROUP BY eventname
ORDER BY COUNT(*) DESC;

This tells you what kinds of actions occur in your account.

---

9.2 Failed Console Login Attempts

Failed logins often appear with a non-null errorMessage:

SELECT eventtime, sourceipaddress, errormessage
FROM cloudtrail_logs
WHERE eventname = 'ConsoleLogin'
  AND errormessage IS NOT NULL
ORDER BY eventtime DESC;

---

9.3 Root Account Activity

SELECT eventtime, eventname, sourceipaddress
FROM cloudtrail_logs
WHERE useridentity.type = 'Root'
ORDER BY eventtime DESC;

Any root event is worth noticing.

---

9.4 Events by Region

SELECT awsregion, COUNT(*) 
FROM cloudtrail_logs
GROUP BY awsregion
ORDER BY COUNT(*) DESC;

---

9.5 Top IAM Users by Activity

SELECT useridentity.username, COUNT(*) AS api_calls
FROM cloudtrail_logs
WHERE useridentity.username IS NOT NULL
GROUP BY useridentity.username
ORDER BY api_calls DESC
LIMIT 10;

You now have everything you need for your dashboard.

---

10. Step 7 — Build the QuickSight Dashboard

Open QuickSight → Datasets → New Dataset

Choose Athena → cloudtrail_logs → Import as SPICE.

Because CloudTrail timestamps sort correctly as strings, you do not need to convert them to date type.

---

Dashboard Visual 1 — Failed Console Logins

Visual type: Line chart

X-axis: eventtime

Value: eventid (Count)

Filter:

• eventname = ConsoleLogin
• errormessage is not null

---

Dashboard Visual 2 — Root Account Activity

Visual type: Bar chart

X-axis: eventtime

Value: eventid (Count)

Filter:

• useridentity_type = Root

---

Dashboard Visual 3 — Events by AWS Region

Visual type: Horizontal bar chart

Y-axis: awsregion

Value: eventid (Count)

---

Dashboard Visual 4 — Top Users by API Calls

Visual type: Vertical bar chart

X-axis: useridentity_username

Value: eventid (Count)

Filter:

• Top N = 10

Your dashboard is complete!

---

11. Step 8 — Publish the Dashboard

Click:

Share → Publish Dashboard → Select "All Sheets"

This ensures your entire analysis is shared, not just the first sheet.

---

12. Step 9 — Cleanup & Cost Optimization

To avoid unnecessary charges:

Athena

• Delete old query results from its S3 bucket

CloudTrail

• Disable logging if this was only for learning
• Or reduce event types to lower S3 usage

QuickSight

• Delete unused datasets to free SPICE capacity

S3

• Remove old log folders if no longer needed

---

13. Troubleshooting (Beginner-Friendly)

Even with a simple architecture like CloudTrail → S3 → Athena → QuickSight, a few common issues can cause missing data or broken visuals. Here are some troubleshooting tips you can quickly check before diving deeper.

---

- CloudTrail Data Not Appearing in Athena

Cause: CloudTrail logs haven’t reached S3 yet or the path doesn’t match the table LOCATION.

Quick Fixes:

• Wait 5–10 minutes after enabling CloudTrail.
• Trigger activity in your AWS account (login, create an IAM user, etc.).
• Confirm your S3 path looks like:

s3://your-bucket/AWSLogs/<ACCOUNT-ID>/CloudTrail/

• Re‑run:

MSCK REPAIR TABLE cloudtrail_logs;

If no rows appear afterward, the folder structure or bucket path is incorrect.

---

- MSCK REPAIR Succeeds but Queries Return No Results

Cause: Athena’s table LOCATION is missing the trailing slash or points to the wrong folder.

Quick Fix:

Make sure LOCATION ends with:

.../CloudTrail/

Then run:

MSCK REPAIR TABLE cloudtrail_logs;

Athena needs this exact folder structure to identify partitions.

---

- QuickSight SPICE Dataset Shows “No Data”

Cause: SPICE may have ingested an empty or outdated version of your dataset.

Quick Fixes:

• Go to Datasets → Refresh SPICE
• If that doesn’t fix it, delete the dataset and recreate it from Athena.
• Verify the dataset preview actually shows rows before building visuals.

---

- Visuals Still Show “No Data”

Most common causes:

• Filters are hiding all your data
• The visual is still referencing an old field
• The field type was changed and QuickSight isn’t mapping it correctly

Quick Fixes:

1. Remove all filters, then re‑add only the ones you need.
2. Re‑add fields to the visual (drag them in fresh).
3. Keep eventtime as a string—it sorts correctly and avoids parsing issues.

---

- Failed Console Login Events Not Showing

Cause: CloudTrail sometimes logs failed logins using errorMessage instead of errorCode.

Fix:

Use this condition:

errormessage is not null

This catches failures reliably.

---

- QuickSight Permissions Errors

If you see errors about S3 access or Athena metadata:

Quick Fix:

Make sure QuickSight has the following enabled:

• Access to Athena
• Access to your CloudTrail S3 bucket

You can configure this in:

QuickSight → Manage QuickSight → Security & Permissions

---

Final Advice

If something looks blank:

• Remove filters
• Refresh SPICE
• Re-add fields
• Run a simple table visual to confirm data exists

Most issues in Athena + QuickSight come down to filters, paths, or SPICE not refreshing.

You now have everything you need to keep the pipeline running smoothly!

---

14. Final Thoughts

You just built a cloud security monitoring workflow used in real organizations.

You learned:

• How CloudTrail logs work
• How to query logs with Athena
• How to analyze activity using SQL
• How to build a security dashboard in QuickSight
• How to troubleshoot common AWS data issues
• How to optimize costs

Most importantly, you now understand how to observe what's happening inside your AWS account, which is a core cloud security skill.

---

🤝 Connect

If you publish your own dashboard, have any tips or advice, tag me. I’d love to see it!

💬 Feel free to reach out or follow my journey on 👉 LinkedIn

---

Thanks for reading!