Analyzing Cloudflare Logs with AWS Athena

Nicholas Duffy — Fri, 09 Aug 2019 13:57:00 +0000

As with many features with Cloudflare, you can enable their Logpush service with the click of a button. Logpush sends your HTTP request logs to your cloud storage provider every 5 minutes.

If you are using AWS S3 for your storage, you can then utilize Athena to analyze your logs.

Athena is an interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL.

So with a little setup and some simple SQL, you can analyze your Cloudflare logs.

Table DDL

In order to being querying, however, you need to create an external table in Athena that matches the format of your Cloudflare logs, which are JSON with a newline delineating each record.

Luckily, this is pretty easy to setup in Athena. Here is the DDL for all of the fields currently included in Cloudflare Logpush.

Note: You can customize the fields that Logpush includes, so if you have, your list of fields may not match the below exactly.

CREATE EXTERNAL TABLE cloudflare_logs (
    CacheCacheStatus string,
    CacheResponseBytes int,
    CacheResponseStatus int,
    CacheTieredFill boolean,
    ClientASN int,
    ClientCountry string,
    ClientDeviceType string,
    ClientIP string,
    ClientIPClass string,
    ClientRequestBytes int,
    ClientRequestHost string,
    ClientRequestMethod string,
    ClientRequestPath string,
    ClientRequestProtocol string,
    ClientRequestReferer string,
    ClientRequestURI string,
    ClientRequestUserAgent string,
    ClientSSLCipher string,
    ClientSSLProtocol string,
    ClientSrcPort int,
    EdgeColoID int,
    EdgeEndTimestamp string,
    EdgePathingOp string,
    EdgePathingSrc string,
    EdgePathingStatus string,
    EdgeRateLimitAction string,
    EdgeRateLimitID int,
    EdgeRequestHost string,
    EdgeResponseBytes int,
    EdgeResponseCompressionRatio double,
    EdgeResponseContentType string,
    EdgeResponseStatus int,
    EdgeServerIP string,
    EdgeStartTimestamp string,
    FirewallMatchesActions ARRAY < string >,
    FirewallMatchesSources ARRAY < string >,
    FirewallMatchesRuleIDs ARRAY < string >,
    OriginIP string,
    OriginResponseBytes int,
    OriginResponseHTTPExpires string,
    OriginResponseHTTPLastModified string,
    OriginResponseStatus int,
    OriginResponseTime bigint,
    OriginSSLProtocol string,
    ParentRayID string,
    RayID string,
    SecurityLevel string,
    WAFAction string,
    WAFFlags string,
    WAFMatchedVar string,
    WAFProfile string,
    WAFRuleID string,
    WAFRuleMessage string,
    WorkerCPUTime int,
    WorkerStatus string,
    WorkerSubrequest boolean,
    WorkerSubrequestCount int,
    ZoneID bigint)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' LOCATION 's3://my-cloudflare-logs/'

Of course, change s3://my-cloudflare-logs/ to the name of your bucket that you used when setting up Logpush.

Querying

An now that we have a table created in Athena, we can analyze our logs in a myriad of ways.

How about checking to see how many requests you've received by the request protocol?

SELECT count(*) as requests,
         c.clientrequestprotocol
FROM "cloudflare_logs"."cloudflare_logs" c
GROUP BY  c.clientrequestprotocol
ORDER BY  count(*) DESC limit 10;

    requests    clientrequestprotocol
1   15063737    HTTP/2
2   6842951     HTTP/1.1
3   4342        HTTP/1.0

Or maybe for reasons unknown, you want to see the average client request size in bytes for today, grouped by the Cloudflare edge colo ID.

SELECT avg(clientrequestbytes),
         edgecoloid
FROM "cloudflare_logs"."cloudflare_logs"
-- Assuming you are using the default date format with Logpush
WHERE date_trunc('day', from_iso8601_timestamp(edgestarttimestamp)) = current_date
GROUP BY  edgecoloid
ORDER BY  avg(clientrequestbytes) ASC;

As you can imagine, the ways that you can slice and dice your Cloudflare HTTP logs is nearly limitless. Enjoy diving deep on your Cloudflare logs!

You Are Great

Nicholas Duffy — Fri, 09 Aug 2019 12:09:04 +0000

Though it may not always feel like it, it's important to remember that you are special, you are wanted, you are great.

I made a thing to try to help make a sad friend feel better. Maybe it will bring a smile to your face, too.

https://youaregreat.fyi

curl https://youaregreat.fyi/api/
{"message":"You are great. 🌟"}

curl https://youaregreat.fyi/api/?name=Julie
{"message":"Hello Julie. You are appreciated."}

Creating a Date Dimension Table in PostgreSQL

Nicholas Duffy — Sat, 23 Apr 2016 00:57:53 +0000

If you’ve ever read any of Ralph Kimball’s data warehouse books or have any experience with modeling data in the Kimball style, you know that a date dimension is a key part of any star schema. You probably found resources for creating Oracle, SQL Server or MySQL but may have had difficulty finding SQL to create a date dimension table for PostgreSQL. Here’s a bit of code adapted from the PostgreSQL wiki that I like for creating the ever necessary date dimension in PostgreSQL.

Forem: Nicholas Duffy

Analyzing Cloudflare Logs with AWS Athena

Table DDL

Querying

You Are Great

Creating a Date Dimension Table in PostgreSQL