elb_status_code_5xx_group_by_elb.sql

select
  elb,
  count(1, elb_status_code like "5%") as count_5xx
from elblogs.win:time_batch(5 min)
group by elb

elb_status_code_group_by_elb.sql

select
  elb,
  count(1, elb_status_code like "2%") as count_2xx,
  count(1, elb_status_code like "3%") as count_3xx,
  count(1, elb_status_code like "4%") as count_4xx,
  count(1, elb_status_code like "5%") as count_5xx
from elblogs.win:time_batch(5 min)
group by elb

norikra_to_mackerel.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import json
import logging

import requests

logger = logging.getLogger()
logger.setLevel(logging.INFO)

NORIKRA_EVENT_API_URL = 'http://YOUR_NORIKRA_HOST:26578/api/event'
NORIKRA_QUERY_NAME = 'elblogs_5xx_count_group_by_elb'

MACKEREL_SERVICE_NAME = 'elblogs'
MACKEREL_API_KEY = 'YOUR_MACKEREL_API_KEY'
MACKEREL_API_URL = 'https://mackerel.io/api/v0/services/{0}/tsdb'


def post_mackerel(service_name, metrics):
    response = requests.post(MACKEREL_API_URL.format(service_name),
                             data=json.dumps(metrics),
                             headers={
                                 'X-Api-Key': MACKEREL_API_KEY,
                                 'Content-Type': 'application/json'
                             })
    logging.info(response)
    if response.status_code != 200:
        raise Exception


def get_norikra_event(query_name):
    response = requests.post(NORIKRA_EVENT_API_URL,
                             data=json.dumps({
                                 'query_name': query_name
                             }),
                             headers={
                                 'Content-Type': 'application/json'
                             }).json()
    logging.info(response)
    return response


def main():
    events = get_norikra_event(NORIKRA_QUERY_NAME)
    if not (len(events) > 0):
        return

    metrics = []
    for event in events:
        metrics.append({
            'name': 'elblogs.5xx_count.{0}'.format(event[1]['elb']),
            'time': event[0],
            'value': event[1]['count_5xx']
        })
    post_mackerel(MACKEREL_SERVICE_NAME, metrics)


def lambda_handler(event, context):
    try:
        return main()
    except Exception as e:
        logger.exception('Failed to processed lambda function.')
        raise e

request_processing_time_group_by_elb.sql

select
  elb,
  min(Double.valueOf(response_processing_time)) as response_processing_time_min,
  max(Double.valueOf(response_processing_time)) as response_processing_time_max,
  avg(Double.valueOf(response_processing_time)) as response_processing_time_avg
from elblogs.win:time_batch(5 min)
group by elb

s3_to_norikra.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import csv
import json
import logging
import uuid

import boto3
import requests

logger = logging.getLogger()
logger.setLevel(logging.INFO)

NORIKRA_SEND_API_URL = 'http://YOUR_NORIKRA_HOST:26578/api/send'
NORIKRA_TARGET_NAME = 'elblogs'


class ElbLog(object):

    def __init__(self, lines):
        self.timestamp = lines[0]
        self.elb = lines[1]
        self.client = lines[2]
        self.backend = lines[3]
        self.request_processing_time = lines[4]
        self.backend_processing_time = lines[5]
        self.response_processing_time = lines[6]
        self.elb_status_code = lines[7]
        self.backend_status_code = lines[8]
        self.received_bytes = lines[9]
        self.sent_bytes = lines[10]
        self.request = lines[11]
        self.user_agent = lines[12]
        self.ssl_cipher = lines[13]
        self.ssl_protocol = lines[14]


def send_norikra(events):
    response = requests.post(NORIKRA_SEND_API_URL,
                             data=json.dumps({
                                 'target': NORIKRA_TARGET_NAME,
                                 'events': events
                             }),
                             headers={
                                 'Content-Type': 'application/json'
                             },
                             timeout=None)
    logging.info(response)


def parse_event(events):
    logging.info('Event count: %s', len(events))
    parsed_events = []
    for event in events:
        with open(event, 'rb') as fp:
            for row in csv.reader(fp, delimiter=' ', quotechar='"'):
                elblog = ElbLog(row)
                parsed_events.append(elblog.__dict__)
    return parsed_events


def main(event):
    s3_client = boto3.client('s3')
    events = []
    for record in event['Records']:
        bucket = record['s3']['bucket']['name']
        key = record['s3']['object']['key']
        download_path = '/tmp/{0}-{1}'.format(uuid.uuid4(), key.split('/')[-1:])

        s3_client.download_file(bucket, key, download_path)
        events.append(download_path)

    events = parse_event(events)
    send_norikra(events)
    return 'Successfully processed {} records.'.format(len(event['Records']))


def lambda_handler(event, context):
    try:
        return main(event)
    except Exception as e:
        logger.exception('Failed to processed lambda function.')
        raise e