Appendix D: Amazon Athena Queries - AWS WAF Security Automations

Appendix D: Amazon Athena Queries

If you selected Yes - Amazon Athena log parser for the Activate HTTP Flood Protection and/or the Activate Scanner & Probe Protection template parameters, this solution creates and executes Athena queries for CloudFront/ALB (ScannersProbesLogParser) or WAF logs (HTTPFloodLogParser), parses the output, and updates AWS WAF accordingly.

In order to improve performance and keep costs low, logs are partitioned based on timestamps in the file names. Athena queries are dynamically generated to use partition keys (year, month, day, and hour). By default, queries run every five minutes. You can configure their run schedules by changing QueryScheduledRunTime in aws-waf-security-automations.template. Each query run scans the last four to five hours of data by default. You can configure the amount of data that a query scans by changing the value for the WAF Block Period template parameter. Queries are also placed in separate workgroups to manage query access and costs.

Note

Verify that Amazon Athena is configured to access the AWS Glue Data Catalog. This solution creates the access logs data catalog in AWS Glue and configures an Athena query to process the data. If Athena is not configured correctly, the query will fail to execute. For more information, see Upgrading to the latest AWS Glue Data Catalog Step-by-Step.

Use the following procedure to view these queries:

View WAF log queries:

  1. Navigate to the Amazon Athena console, select the Workgroup tab.

  2. Select WAFLogAthenaQueryWorkGroup from the list, then click Switch workgroup. This workgroup exists only if you selected Yes - Amazon Athena log parser for the Activate HTTP Flood Protection template parameter.

    
          Amazon Athena workgroups

    Figure 11: Amazon Athena WAF workgroups

  3. Select the History tab.

  4. Select and open SELECT queries from the list.

View application access log queries:

  1. Navigate to the Amazon Athena console, select the Workgroup tab.

  2. Select WAFAppAccessLogAthenaQueryWorkGroup from the list, then click Switch workgroup. This workgroup exists only if you selected Yes - Amazon Athena log parser for the Activate Scanner & Probe Protection template parameter.

  3. Select the History tab.

  4. Select and open SELECT queries from the list.

View adding Athena partition queries:

  1. Navigate to the Amazon Athena console, select the Workgroup tab.

  2. Select WAFAddPartitionAthenaQueryWorkGroup from the list, then click Switch workgroup. This workgroup exists only if you selected Yes - Amazon Athena log parser for the Activate HTTP Flood Protection and/or the Activate Scanner & Probe Protection template parameters.

  3. Select the History tab.

  4. Select and open ALTER TABLE queries from the list. These queries run every hour to add a new hourly partition to the Glue/Athena table