In [ ]:

    

import psycopg2
from auth_gl import connection_string_gl #saved my username password


    

In [ ]:

    

#connect to rds
conn = psycopg2.connect(connection_string_gl)
cur = conn.cursor()


    

In [ ]:

    

#helper function to run sql command and fetchall
def run_sql(cur, command):
    cur.execute(command)
    return cur.fetchall()


    

In [ ]:

    

# TAG REDTEAM ACTIVITIES IN AUTH - to run
sql_cmd = ("UPDATE auth SET redteam = 'RED' FROM redteam "
           "WHERE auth.tstamp = redteam.tstamp::integer " #redteam tstamp is varchar
           "AND auth.srcuserdomain = redteam.userdomain "
           "AND auth.srccomputer = redteam.src "
           "AND auth.dstcomputer = redteam.dst ")
%time re = run_sql(cur, sql_cmd)


    

In [ ]:

    

#need to rollback if error
cur.execute("rollback")


    

In [ ]:

    

#template - query column name and data type
run_sql(cur, "select column_name, data_type from information_schema.columns where table_name='auth'")


    

In [ ]:

    

#template to time a command using jupyter
sql_cmd = "select * from auth limit 100"
%timeit run_sql(cur, sql_cmd)


    

In [ ]:

    

#template - sql explain analyze
run_sql(cur, "explain analyze select redteam, count(*) from flows group by redteam")


    

In [ ]:

    

#disconnect
cur.close()
conn.close()