WAF insights with QuickSight

Following on from my last post using Athena to query WAF logs, I decided to take this a big further and look at what I can do with QuickSight to get some insights.

Directly quoted from the Amazon QuickSight home page:

Amazon QuickSight is a scalable, serverless, embeddable, machine learning-powered business intelligence (BI) service built for the cloud. QuickSight lets you easily create and publish interactive BI dashboards that include Machine Learning-powered insights. QuickSight dashboards can be accessed from any device, and seamlessly embedded into your applications, portals, and websites.

This little write up assumes you already have WAF logs setup in Athena and have a QuickSight instance.

You are going to need to setup a new Athena based Dataset. Give it a name and pick the workgroup that you setup your Athena queries for WAF in.

On the data page, don’t pick the database and table you created in Athena as QuickSight is unable to deal with JSON or Array data types. Instead use custom SQL and paste in the following:

SELECT to_iso8601(from_unixtime(timestamp / 1000)) AS time_ISO_8601,
         rules.rulegroupid,
         rules.terminatingrule.ruleid AS ruleid,
         httprequest.uri,
         httprequest.country,
         httprequest.httpversion,
         httprequest.clientip,
         httprequest.httpmethod,
         httprequest.args
FROM sampledb.prod_waf_logs, UNNEST(rulegrouplist) t(rules)
WHERE cardinality(nonterminatingmatchingrules) !=0

I suggest importing this into SPICE if you have the capacity and setup a refresh schedule.

Now that you have the data in QuickSight you can create some awesome looking dashboards and hopefully garner some insights.