def get_traffic_monitors(traffic_monitor_table_name="traffic_monitor", link_table_name="link"): from utilities.utils import tuple_list_key_list_to_dict_list traffic_monitors = [] engine, cnn, metadata = connect_to_postgres(logging, postgres_url) query = ''' select public.{0}.*, public.{1}.server_ip, public.{1}.client_ip, public.{1}.link_name from public.{0}, public."{1}" where "{1}".link_id = "{0}".link_id order by {0}."datetime" Desc '''.format(traffic_monitor_table_name, link_table_name) res = cnn.execute(query) results = res.fetchall() print(results) res = tuple_list_key_list_to_dict_list(results, [ "id", "send_tx_max", "send_tx_avg", "send_tx_min", "receive_rx_max", "receive_rx_avg", "receive_rx_min", "both_tx_max", "both_tx_avg", "both_tx_min", "both_rx_max", "both_rx_avg", "both_rx_min", "datetime", "link_id", "status", "error_details", "server_ip", "client_ip", "link_name" ]) return res
def get_traffic_monitor_field(link_id, field_name, traffic_monitor_table_name="traffic_monitor", link_table_name="link"): from utilities.utils import tuple_list_key_list_to_dict_list traffic_monitors = [] engine, cnn, metadata = connect_to_postgres(logging, postgres_url) query = ''' select public.{0}.{2}, EXTRACT(EPOCH FROM public.{0}.datetime)::bigint, public.{1}.server_ip, public.{1}.client_ip, public.{1}.link_name from public.{0}, public."{1}" where "{1}".link_id = "{0}".link_id and "{0}".link_id = %(link_id)s and "{0}".status = 'done' order by {0}."datetime" Desc '''.format(traffic_monitor_table_name, link_table_name, field_name) res = cnn.execute(query, {"link_id": link_id}) results = res.fetchall() print(results) res = tuple_list_key_list_to_dict_list(results, [ field_name, "datetime", "link_id", "server_ip", "client_ip", "link_name" ]) return res
def get_add_cpe_request(self): query = ''' SELECT add_cpe_request.*, installer.username AS "installer_username", approver.username AS "approver_username" FROM public.{0} LEFT JOIN users installer ON installer.user_id = add_cpe_request.installer_id LEFT JOIN users approver ON approver.user_id = add_cpe_request.approver_id '''.format(self.add_cpe_request_table_name) res = self.cnn.execute(query) requests = res.fetchall() return utils.tuple_list_key_list_to_dict_list(requests, res.keys())
def get_from_postgres_basic(table_name, fields_list): from utilities.utils import tuple_list_key_list_to_dict_list traffic_monitors = [] engine, cnn, metadata = connect_to_postgres(logging, postgres_url) query = ''' SELECT * FROM {} '''.format(table_name) res = cnn.execute(query) results = res.fetchall() print(results) res = tuple_list_key_list_to_dict_list(results, fields_list) return res
def get_filtered_accounts(status='%', start_ip="0.0.0.0", end_ip="255.255.255.255", ip_pattern="x.x.x.x", task_id=None): import ipaddress from utilities.utils import tuple_list_key_list_to_dict_list filtered_accounts = [] engine, cnn, metadata = connect_to_postgres(logging, postgres_url) ip_pattern = ip_pattern.replace('.', '\.') ip_pattern = re.sub('x+', 'x', ip_pattern) ip_pattern = ip_pattern.replace('x', '[^\.]+?') query = ''' SELECT * FROM accounts WHERE "status" LIKE %(status)s and "IP" ~ %(ip_pattern)s ''' if (len(task_id) > 0): query = ''' SELECT * FROM accounts WHERE "status" LIKE %(status)s and "IP" ~ %(ip_pattern)s and "task_id" = %(task_id)s ''' res = cnn.execute(query, { "status": status, "ip_pattern": ip_pattern, "task_id": task_id }) accounts = res.fetchall() for account in accounts: in_range = int(ipaddress.IPv4Address(start_ip)) <= int( ipaddress.IPv4Address(account[2])) and int( ipaddress.IPv4Address(account[2])) < int( ipaddress.IPv4Address(end_ip)) print(in_range) if (in_range): filtered_accounts.append(list(account)) res = tuple_list_key_list_to_dict_list(filtered_accounts, [ "username", "password", "IP", "status", "date", "version", "task_id", "mac" ]) return res