Cloudfront Logs

Get and merge logs

s3cmd get s3://sf-cloudfront-logs/*
gunzip *
cat * > logs

Strip out comment lines

grep -v '^#' logs > logs2

Creating postgres db table

create table req (logdate varchar(4096), logtime varchar(4096), edge varchar(40),  bytes int, cip varchar(50), method varchar(50), host varchar(4096),  uri varchar(4096), status varchar(20), creferrer varchar(4096), useragent varchar(4096), cs_uri_query varchar(4096), cookie varchar(4096), x_edge_result_type varchar(4096), x_edge_request_id varchar(4096), x_host_header varchar(4096), protocol varchar(4096), cs_bytes varchar(4096));

Load data in

copy req from '/home/vagrant/s3-cloudfront-logs/logs2';

Filter out IP addresses

delete from req where cip in ('54.212.250.36','24.137.221.230','74.86.158.106','74.86.158.107');

Report on hit percentage by URI

select h.uri, hits, miss, (hits*100/(miss+hits)) as hit_pct from 
(select uri, count(1) as hits from req where x_edge_result_type = 'Hit' group by uri) h 
inner join 
(select uri, count(1) as miss from req where x_edge_result_type = 'Miss' group by uri) m on h.uri = m.uri;

eg http://pastebin.com/raw.php?i=52EQrWsY