Redshift の接続ログを取得する Python スクリプト

コード

• redshift_get_connection_log.py

#!/usr/bin/env python3
import os
import csv
import psycopg2
import datetime

now = datetime.datetime.now()
begin_time='{0:%Y-%m-%d %H:00:00}'.format(now - datetime.timedelta(hours=1))
end_time='{0:%Y-%m-%d %H:00:00}'.format(now)

def get_connection():
    return psycopg2.connect(
        host=os.environ['RS_HOST'],
        dbname=os.environ['RS_DBNAME'], # dev
        port=os.environ['RS_PORT'], # 5439
        user=os.environ['RS_USER'], # awsuser
        password=os.environ['RS_PASSWORD']
    )

with get_connection() as conn:
    with conn.cursor() as cur:
        cur.execute("select to_char(recordtime, 'YYYY/MM/DD HH24:MI:SS'),remotehost,remoteport,event,'Redshift', pid, application_name, username from STL_CONNECTION_LOG where recordtime >= %s and recordtime < %s;", [begin_time, end_time])
        with open('stl_connection_log.csv', 'w') as f:
            writer = csv.writer(f, quoting=csv.QUOTE_NONNUMERIC)
            for row in cur:
                new_row=map(lambda s:str(s).strip(), row)
                writer.writerow(new_row)

実行する

export RS_HOST=redshift-dc2l-1node.******.ap-northeast-1.redshift.amazonaws.com
export RS_DBNAME=dev
export RS_PORT=5439
export RS_USER=awsuser
export RS_PASSWORD=********

python redshift_query_sample.py 

実行結果

"2020/04/25 07:46:15","::ffff:3.112.53.130","54408","authenticated","Redshift","30054","","awsuser"
"2020/04/25 07:46:15","::ffff:3.112.53.130","54408","initiating session","Redshift","30054","","awsuser"
"2020/04/25 07:46:15","::ffff:3.112.53.130","54408","disconnecting session","Redshift","30054","","awsuser"
"2020/04/25 07:58:53","::ffff:3.112.53.130","54492","authenticated","Redshift","31116","","awsuser"
"2020/04/25 07:58:53","::ffff:3.112.53.130","54492","initiating session","Redshift","31116","","awsuser"
"2020/04/25 07:58:53","::ffff:3.112.53.130","54492","disconnecting session","Redshift","31116","","awsuser"