def get_class_of_ips(name):
connection = get_connection()
cursor = connection.cursor(cursor_factory=RealDictCursor)
query = 'select * from class_of_ips where name = {name}'.format(
name=surround_with_quotes(name), )
cursor.execute(query)
return cursor.fetchall()
def set_up():
os.environ['IS_TEST'] = 'True'
print("setting up database stuff...")
connection = get_connection()
connection.autocommit = True
global db_cursor
db_cursor = connection.cursor()
cursor = db_cursor
cursor.execute("CREATE SCHEMA IF NOT EXISTS test")
cursor.execute("SET search_path TO test")
cursor.execute("DROP TABLE IF EXISTS ip_traffic;")
cursor.execute("""
create table ip_traffic
(
event_type varchar(255),
ip_src varchar(255),
ip_dst varchar(255),
port_src varchar(255),
port_dst varchar(255),
timestamp_start timestamp,
timestamp_end timestamp,
packets integer,
bytes integer,
writer_id varchar(255),
mac_src varchar(255),
mac_dst varchar(255),
ip_proto varchar(255),
src_hostname varchar(255),
dst_hostname varchar(255),
incoming_outgoing varchar(255)
);
""")
print("Finished setup!")
def delete_class_of_ips(name):
connection = get_connection()
connection.autocommit = True
cursor = connection.cursor(cursor_factory=RealDictCursor)
query = 'delete from class_of_ips where name = {name}'.format(
name=surround_with_quotes(name), )
print(query)
cursor.execute(query)
def test_after_performance_test_execution(self):
os.environ['IS_TEST'] = 'True'
print("setting up database stuff...")
self.connection = get_connection()
self.cursor = self.connection.cursor()
self.cursor.execute("SELECT COUNT(1) FROM ip_traffic")
result = self.cursor.fetchall()
self.assertEqual(result[0][0], HOW_MANY_RECORDS_FOR_TEST)
def get_all_data():
connection = get_connection()
cursor = connection.cursor(cursor_factory=RealDictCursor)
query = """SELECT * FROM {0};""".format(TABLE_NAME)
cursor.execute(query)
return cursor.fetchall()
def get_all_data_paginated(page, limit):
connection = get_connection()
cursor = connection.cursor(cursor_factory=RealDictCursor)
query = """SELECT * FROM {0} OFFSET %s LIMIT %s;""".format(TABLE_NAME)
cursor.execute(query, (page * limit, limit))
return cursor.fetchall()
def edit_class_of_ips(name, ips):
connection = get_connection()
connection.autocommit = True
cursor = connection.cursor(cursor_factory=RealDictCursor)
query = 'update class_of_ips set ips = {ips} where name = {name}'.format(
name=surround_with_quotes(name),
ips=surround_with_quotes(list_to_pgarray(ips)))
print(query)
cursor.execute(query)
def insert_class_of_ips(name, ips):
connection = get_connection()
connection.autocommit = True
cursor = connection.cursor(cursor_factory=RealDictCursor)
query = 'insert into class_of_ips(name, ips) values ({name}, {ips})'.format(
name=surround_with_quotes(name),
ips=surround_with_quotes(list_to_pgarray(ips)))
print(query)
cursor.execute(query)
def main():
connection = get_connection()
connection.autocommit = True
EXCHANGE_NAME = 'fanoucik'
queue_connection = pika.BlockingConnection(
pika.ConnectionParameters(host='localhost'))
channel = queue_connection.channel()
channel.exchange_declare(exchange=EXCHANGE_NAME, exchange_type='fanout')
result = channel.queue_declare(queue='', exclusive=True)
queue_name = result.method.queue
channel.queue_bind(exchange=EXCHANGE_NAME, queue=queue_name)
channel.basic_consume(queue=queue_name,
on_message_callback=consume_rabbit_data_callback,
auto_ack=True)
channel.start_consuming()
from src.rest_api.db.db_configuration import get_connection
import re
import sh
connection = get_connection()
def get_local_network_ip_addresses():
ifconfig_result = sh.ifconfig()
ifconfig_result_str = ifconfig_result.stdout.decode("utf-8")
ips = re.findall(r'[0-9]+(?:\.[0-9]+){3}', ifconfig_result_str)
return ips
connection.autocommit = True
def fill_incoming_outgoing():
cursor = connection.cursor()
local_network_ip_addresses = get_local_network_ip_addresses()
query = """UPDATE ip_traffic
SET
incoming_outgoing = 'outgoing'
WHERE
ip_src IN %s """
cursor.execute(query, (tuple(local_network_ip_addresses), ))
# OUTGOING ---
def get_filtered_data(kwargs):
connection = get_connection()
cursor = connection.cursor(cursor_factory=RealDictCursor)
conditions = []
parameters = []
in_parameters = [
'port_src_in', 'port_dst_in', 'ip_proto_in', 'incoming_outgoing_in'
]
contain_parameters = ['ip_src_in', 'ip_dst_in']
for contain_parameter in contain_parameters:
if not is_parameter_empty(kwargs[contain_parameter]):
conditions.append("({} SIMILAR TO %s)".format(
contain_parameter.replace("_in", "")))
parameter = '%({})%'.format('|'.join(kwargs[contain_parameter]))
'%(foo|bar|baz)%'
parameters.append(parameter)
for in_parameter in in_parameters:
print(in_parameter)
if not is_parameter_empty(kwargs[in_parameter]):
conditions.append("({} IN %s)".format(
in_parameter.replace("_in", "")))
parameters.append(tuple(kwargs[in_parameter]))
between_numeric_parameters = ['packets_between', 'bytes_between']
for between_numeric_parameter in between_numeric_parameters:
extracted_parameter = kwargs[between_numeric_parameter]
if not is_parameter_empty(extracted_parameter):
is_upper_unbound = extracted_parameter[1] is None
conditions.append("({0} BETWEEN %s AND %s{1})".format(
between_numeric_parameter.replace("_between", ""),
'::FLOAT8' if is_upper_unbound else ''))
parameters.append(extracted_parameter[0])
parameters.append(
"+infinity" if is_upper_unbound else extracted_parameter[1])
stamp_between = kwargs["stamp_between"]
if not is_parameter_empty(stamp_between):
conditions.append("({} BETWEEN %s AND %s)".format(TIMESTAMP_COLUMN))
parameters.append(stamp_between[0])
parameters.append(stamp_between[1] if stamp_between[1] is not None else
"'9999-03-27 21:48:02.000000'")
page = kwargs["page"]
limit = kwargs["limit"]
parameters.append(page * limit if limit != DEFAULTS["limit"] else 0)
parameters.append(limit)
where_condition = "" if is_array_empty(conditions) else " AND ".join(
conditions)
query = """SELECT * FROM {table_name}
{where}
{where_condition}
ORDER BY {timestamp_column}
OFFSET %s LIMIT %s;""".format(
table_name=TABLE_NAME,
timestamp_column=TIMESTAMP_COLUMN,
where='WHERE' if not is_array_empty(conditions) else '',
where_condition=where_condition)
query_parameters = tuple(parameters)
cursor.execute(query, query_parameters)
return cursor.fetchall()
def get_all_classes_of_ips():
connection = get_connection()
cursor = connection.cursor(cursor_factory=RealDictCursor)
query = 'select * from class_of_ips'
cursor.execute(query)
return cursor.fetchall()
