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.