Skip to content

Instantly share code, notes, and snippets.

@mortalius
Last active September 4, 2020 12:21
Show Gist options
  • Save mortalius/00bdbec17e378428d4a93a69adc69dc6 to your computer and use it in GitHub Desktop.
Save mortalius/00bdbec17e378428d4a93a69adc69dc6 to your computer and use it in GitHub Desktop.
Athena queries

ALB

SELECT *
FROM alb_logs_UAT
WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') 
     BETWEEN parse_datetime('2020-07-21-05:20:00','yyyy-MM-dd-HH:mm:ss') 
     AND parse_datetime('2020-07-21-05:45:00','yyyy-MM-dd-HH:mm:ss')
     AND elb_status_code like '4%'

Cloudfront

SELECT *
FROM cloudfront_logs_prod
WHERE (
  parse_datetime(concat(cast(date AS varchar),'T', cast(time AS varchar)), 'yyyy-MM-dd''T''HH:mm:ss')
  BETWEEN 
    parse_datetime('2020-07-14T01:20:00', 'yyyy-MM-dd''T''HH:mm:ss') AND
    parse_datetime('2020-07-14T01:30:40', 'yyyy-MM-dd''T''HH:mm:ss')
  
) AND
status > 400 AND status < 600
LIMIT 1000
SELECT date,time,location,bytes,request_ip,method,uri,status
FROM cloudfront_logs_prod
WHERE (
  parse_datetime(concat(cast(date AS varchar),'T', cast(time AS varchar)), 'yyyy-MM-dd''T''HH:mm:ss')
  BETWEEN 
    parse_datetime('2020-07-14T01:20:00', 'yyyy-MM-dd''T''HH:mm:ss') AND
    parse_datetime('2020-07-14T01:40:40', 'yyyy-MM-dd''T''HH:mm:ss')
)
LIMIT 100;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment