Query Logs the AWS WAF using Amazon Athena.
Tags: aws, waf, athena, s3
Published: 2021-06-24 02:35:48

When we require to view the logs coming from the **AWS WAF – Web Application Firewall**, we count with an option to export the logs to **Amazon S3**. However, if we try to see them and would like the option to execute queries, there is **Amazon Athena**.

###Definition

**Amazon Athena**

*“Amazon Athena is an interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL. Athena is serverless, so there is no infrastructure to manage, and you pay only for the queries that you run.”*[1]

**Amazon S3**

*“Amazon Simple Storage Service (Amazon S3) is an object storage service that offers industry-leading scalability, data availability, security, and performance.”*[2]

**AWS WAF - Web Application Firewall**

*“AWS WAF is a web application firewall that helps protect your web applications or APIs against common web exploits and bots that may affect availability, compromise security, or consume excessive resources.”*[3]

**Amazon Kinesis Data Firehose**

*“Amazon Kinesis Data Firehose is the easiest way to reliably load streaming data into data lakes, data stores, and analytics services.”*[4]

Before we begin, first we must configure WAF on AWS, section **Logging and metrics -> Logging**, the idea is to obtain the logs using Kinesis Data Firehose, while they are saved on **Amazon S3** bucket.

With this link I share, you can see a simple guide available at AWS

https://aws.amazon.com/premiumsupport/knowledge-center/waf-configure-comprehensive-logging/

Then the next step, we should have activated the Logging with the option “Enabled” pointing to Amazon Kinesis Data Firehose delivery stream.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3rlh7la7e55t16tqdkhh.png)

Now, go to the **Amazon Athena** section in AWS Console and create: 

* A Database.
* A table where the data and structure the logs from AWS WAF will be.

Database creation name: **demo_waf_logs**

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j7tndfooerdyldt3fw9m.png)
 

Table creation name: **waf_logs**

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6t5rylfgwslju1xtf9fm.png)
 
**Query the creation waf_logs table.**

CREATE EXTERNAL TABLE `waf_logs`(
  `timestamp` bigint,
  `formatversion` int,
  `webaclid` string,
  `terminatingruleid` string,
  `terminatingruletype` string,
  `action` string,
  `terminatingrulematchdetails` array<
                                  struct<
                                    conditiontype:string,
                                    location:string,
                                    matcheddata:array<string>
                                        >
                                     >,
  `httpsourcename` string,
  `httpsourceid` string,
  `rulegrouplist` array<
                     struct<
                        rulegroupid:string,
                        terminatingrule:struct<
                           ruleid:string,
                           action:string,
                           rulematchdetails:string
                                               >,
                        nonterminatingmatchingrules:array<
                                                       struct<
                                                          ruleid:string,
                                                          action:string,
                                                          rulematchdetails:array<
                                                               struct<
                                                                  conditiontype:string,
                                                                  location:string,
                                                                  matcheddata:array<string>
                                                                     >
                                                                  >
                                                               >
                                                            >,
                        excludedrules:array<
                                         struct<
                                            ruleid:string,
                                            exclusiontype:string
                                               >
                                            >
                           >
                       >,
  `ratebasedrulelist` array<
                        struct<
                          ratebasedruleid:string,
                          limitkey:string,
                          maxrateallowed:int
                              >
                           >,
  `nonterminatingmatchingrules` array<
                                  struct<
                                    ruleid:string,
                                    action:string
                                        >
                                     >,
  `requestheadersinserted` string,
  `responsecodesent` string,
  `httprequest` struct<
                      clientip:string,
                      country:string,
                      headers:array<
                                struct<
                                  name:string,
                                  value:string
                                      >
                                   >,
                      uri:string,
                      args:string,
                      httpversion:string,
                      httpmethod:string,
                      requestid:string
                      >,
  `labels` array<
             struct<
               name:string
                   >
                  >
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
 'paths'='action,formatVersion,httpRequest,httpSourceId,httpSourceName,labels,nonTerminatingMatchingRules,rateBasedRuleList,requestHeadersInserted,responseCodeSent,ruleGroupList,terminatingRuleId,terminatingRuleMatchDetails,terminatingRuleType,timestamp,webaclId')
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://waf-sandbox/2021/05/'

**Important:** the option **LOCATION** is the place where the logs **AWS WAF** are, we can obtain the information searching on **Amazon S3** Bucket that we are using to store the logs as it is presented on the picture. 

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w8ijriny7z5o084aguq6.png)
 
We proceed to view the result before executing the query

**SELECT * FROM "demo_waf_logs"."waf_logs" limit 10;**

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ya40x2vk4f96ihzpkrb3.png)
 
If we execute a query with a filter 

**SELECT * FROM "demo_waf_logs"."waf_logs" where action='BLOCK' limit 10;**

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tdsqnq7ks8owh1odk83w.png)
 
If we execute a query with a filter IP address 

**SELECT * FROM "demo_waf_logs"."waf_logs" where httprequest.clientip='45.146.164.125' limit 10;**

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/35x1zrsqczdvsqw3mipc.png)
 
**Reference**
[1]: https://aws.amazon.com/athena/
[2]: https://aws.amazon.com/s3/
[3]: https://aws.amazon.com/waf/
[4]: https://aws.amazon.com/es/kinesis/data-firehose/
https://docs.aws.amazon.com/athena/latest/ug/waf-logs.html