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