☁️
CTHFM: AWS
  • Welcome
  • Getting Started
    • Account Setup
  • AWS CLI
    • AWS CLI Overview
    • Installation
  • AWS Fundamentals
    • AWS Documentation
    • AWS Shared Responsibility Model
    • Organizational Hierarchy
    • AWS Principals
    • IAM Fundamentals
      • IAM Policy Components
      • IAM Documentation References
    • AWS Security Services Overview
    • AWS Core Services
    • AWS Frameworks
    • Regions and Availability Zones
  • SQL
    • SQL Refresher for Threat Hunting
  • Logging Reference
    • Cloudtrail
      • What is Cloudtrail?
      • Setting Up Cloudtrail
      • Cloudtrail Events Structure
      • Filtering and Searching CloudTrail Logs
      • IAM ID Prefixes
      • Additional Resources
      • API References
    • VPCFlow Logs
    • GuardDuty
      • Multi-Account Setup
      • GuardDuty Concepts
      • GuardDuty Finding References
      • S3 Protection
      • Malware Protection
        • EC2 Malware Protection
          • EC2 Protection Resources
          • Monitoring Scans
          • EC2 Malware Protection Events: CloudWatch
        • S3 Malware Protection
          • Enabling S3 Malware Protection
          • After Enabling S3 Malware Protection
          • S3 Malware Resource Plan Status
          • S3 Malware Protection Quotas
      • RDS Protection Enablement
      • Lambda Protection Enablement
      • Trusted IP Lists and Threat Lists in Amazon GuardDuty
      • Remediation Recommendations
      • GuardDuty API Reference
      • GuardDuty Quotas
    • Access Analyzer
      • Setup
      • External Access and Unused Access Analyzer Findings
      • Review Findings
      • Access Analyzer Resources
      • Access Analyzer API Reference
    • AWS Network Firewall
      • Permissions
      • Firewall Log Contents
      • Logging Destinations
      • CloudWatch Firewall Metrics
    • AWS Config
      • Resource Management in AWS Config
      • AWS Config Integrations
      • AWS Config Resources
      • Configuration Item
      • Config Rules
        • Evaluation Modes
  • CloudWatch
    • Amazon CloudWatch
      • CloudWatch Concepts
      • CloudWatch Metrics
        • Filter Pattern Syntax
      • CloudWatch Alarms
        • Alarm Recommendations
      • Subscriptions
      • CloudWatch Agent
      • CloudWatch Insights
        • Supported Logs and Discovered Fields
        • CloudWatch Insights Query Syntax
      • Anomaly Detection
        • Create Anomaly Detector
        • Alarms for Anomaly Detections
      • CloudWatch Filter Syntax
      • CloudWatch Service Quota
  • Athena For Threat Hunting
    • Introduction to Athena
    • Setting Up Athena
    • SQL For Threat Hunters
    • Automated Response
    • Query Best Practices
  • AWS Security Research and Resources
    • AWS Security Blog
    • AWS Goat
    • Cloud Goat
    • Pacu
    • Prowler
    • Scout Suite
  • Threat Hunting in AWS
    • Threat Hunting in AWS
    • Threat Hunting Introduction
    • Threat Hunting Process
      • Hypothesis Generation
      • Investigation
      • Identification
      • Resolution & Follow Up
    • Pyramid of Pain
    • MITRE Att&ck
      • MITRE Att&ck Concepts
      • MITRE Att&CK Data Sources
      • MITRE Att&CK Mitigations
    • MITRE Att&ck: AWS
      • MITRE Att&CK Matrix
      • Amazon Web Services Security Control Mappings
    • AWS Threat Hunting Ideas
      • AWS Threat Hunting Ideas: EC2
      • AWS Threat Hunting Ideas: Lambda
      • AWS Threat Hunting Ideas: SQS
      • AWS Threat Hunting Ideas: SNS
      • AWS Threat Hunting Ideas: RDS
Powered by GitBook
On this page
  1. Athena For Threat Hunting

Query Best Practices

Best Practices for Advanced Query Design

The following are general guidelines for Query design.

  1. Use WITH Clauses for Complex Queries: Break complex queries into smaller, readable parts using WITH clauses.

    WITH suspicious_logins AS (
  SELECT userName, eventTime 
  FROM cloudtrail_logs 
  WHERE eventName = 'ConsoleLogin' AND errorCode = 'AccessDenied'
)
SELECT * FROM suspicious_logins;

  2. Partition Logs by Time: Ensure logs are partitioned by day, month, or year for faster queries.

  3. Combine Data Sources: Query multiple log sources (e.g., CloudTrail + VPC Flow Logs) to identify complex attack patterns.

  4. Save Queries for Reuse: Save frequently used queries to reduce manual effort during investigations.

PreviousAutomated ResponseNextAWS Security Blog

Last updated 8 months ago