queries.md

Format

https://docs.aws.amazon.com/elasticloadbalancing/latest/application/load-balancer-access-logs.html

Common Queries

Count by request type (http, https, https/2)

select type, count(1) from prod_lbs_logs_2026_02_10 group by type;

Count by request type (http, https, https/2)

select type, count(1) from prod_lbs_logs_2026_02_10 group by type;

Count by Client IP

select split_part(client_port,':',1) as client_ip, count(1)
from   prod_lbs_logs_2026_02_10
where  type != 'http'
group  by split_part(client_port,':',1) 
order  by count(1)
desc   limit 100;

select split_part(request_line,',',2) as request_url, domain_name from prod_lbs_logs_2026_02_10 where type != 'http' limit 10;

select split_part(request_line,',',2) as request_url
from prod_lbs_logs_2026_02_10
where request_line like '%affiliate%';

aggregate by hour

select date_trunc('hour', time) as hour_bucket, count(1)
from   proxy_prod_lb_logs_2026_02_10 
group  by hour_bucket order by hour_bucket desc;

aggregate by hour exclude static

select date_trunc('hour', time) as hour_bucket, count(1)
from   proxy_prod_lb_logs_2026_02_10 
where  type != 'http'
and    request
group  by hour_bucket order by hour_bucket desc;

select request_line 

select hour_bucket, count() as total, sum(justice_count) as justice_count, sum(other_count) as other_count
from (
select date_trunc('hour', time) as hour_bucket, 
       case when request_line like '%affiliate=justice%' then 1 else 0 end as justice_count,
       case when request_line like '%affiliate=justice%' then 0 else 1 end as other_count,
from proxy_prod_lb_logs_2026_02_10
where type != 'http' 
and request_line not like '%infr.search.usa.gov%'
and request_line not like '%/assets/%'
and request_line not like '%/javascripts/%'
and request_line not like '%/favicon/%'
)
group by hour_bucket order by hour_bucket desc;

select request_line 
from proxy_prod_lb_logs_2026_02_10
where type != 'http' 
and request_line not like '%infr.search.usa.gov%'
and request_line not like '%/assets/%'
and request_line not like '%/javascripts/%'
and request_line not like '%/favicon/%'
and request_line like '%affiliate=justice%'
order by time desc;