def get_alert_info(dashboard_id):
alert_info_dict = {
'dashboard_id': '',
'device_name': '',
'severity': '',
'alert_details': '',
'occurred_datetime': '',
'resolved_hour': '',
'resolved_minute': ''
}
query = select_alert_info(dashboard_id)
alert_details_df = execute_query(query)
alert_details_df.columns = [
'dashboard_id', 'device_name', 'severity', 'alert_details',
'occurred_datetime'
]
alert_info_dict['dashboard_id'] = alert_details_df['dashboard_id'][0]
alert_info_dict['device_name'] = alert_details_df['device_name'][0]
alert_info_dict['severity'] = alert_details_df['severity'][0]
alert_info_dict['alert_details'] = alert_details_df['alert_details'][0]
alert_info_dict['occurred_datetime'] = timestamp_to_string(
alert_details_df['occurred_datetime'][0])
alert_info_dict['resolved_hour'] = datetime.datetime.now().time().hour
alert_info_dict['resolved_minute'] = datetime.datetime.now().time().minute
return alert_info_dict
def get_recent_tickets():
query = select_resolved_dashboard_tickets(50)
resolved_tickets_df = execute_query(query)
resolved_tickets_df.columns = [
'Device Name', 'Severity', 'Alert Details', 'Occurred Datetime',
'Resolved Comment', 'Resolved Datetime', 'Resolved By'
]
return resolved_tickets_df
def get_searched_ticket(searched_item):
query = select_searched_tickets(searched_item)
tickets_info_df = execute_query(query)
tickets_info_df.columns = [
'Device Name', 'Severity', 'Alert Details', 'Occurred Datetime',
'Resolved Comment', 'Resolved Datetime', 'Resolved By'
]
return tickets_info_df
def total_alerts_for_notification_icon():
query = select_total_alerts()
total_alerts_df = execute_query(query)
if total_alerts_df.empty:
alerts = ''
else:
alerts = int(total_alerts_df[0][0])
return alerts
def get_connection_details(connection_id):
query = select_connection_for_edit(connection_id)
connection_details_df = execute_query(query)
connection_details_df.columns = [
'connection_id', 'local_device', 'local_port', 'remote_device',
'remote_port', 'interconnect_1', 'interconnect_2'
]
return connection_details_df
def port_checker(device_list, severity=SEVERITY['port_error']):
"""
***IMPORTANT: this function requires to run right after "details_info_collector.py" scripts which collects all the port
error of a given device***
This function collects the most recent port errors from database
then pass the data to another function to insert to a alert database from where the dashboard page will
read its required data
:param severity: warning
:param device_list: list of all networking devices whose data is collected and stored in database
:return: NONE
"""
formatted_output = []
for device_name in device_list:
query = select_input_error(device_name)
input_error_df = execute_query(query)
formatted_output = format_db_output(input_error_df, 'input error',
severity, formatted_output)
query = select_crc_error(device_name)
crc_error_df = execute_query(query)
formatted_output = format_db_output(crc_error_df, 'crc error',
severity, formatted_output)
query = select_frame_error(device_name)
frame_error_df = execute_query(query)
formatted_output = format_db_output(frame_error_df, 'frame error',
severity, formatted_output)
query = select_overrun_error(device_name)
overrun_error_df = execute_query(query)
formatted_output = format_db_output(overrun_error_df, 'overrun error',
severity, formatted_output)
query = select_ignored_error(device_name)
ignored_error_df = execute_query(query)
formatted_output = format_db_output(ignored_error_df, 'ignored error',
severity, formatted_output)
error_df = pd.DataFrame(formatted_output,
columns=[
'device_name', 'code', 'error_severity',
'message', 'date_time'
])
return error_df
def all_alerts_info():
query = select_all_alerts_info()
alerts_info_df = execute_query(query)
if not alerts_info_df.empty:
alerts_info_df.columns = [
'id', 'device_name', 'severity', 'error_details', 'date_time'
]
alerts_info_df['date_time'] = alerts_info_df['date_time'].apply(
timestamp_to_string)
alerts_info_dict = alerts_info_df.to_dict('records')
return alerts_info_dict
return {}
def disconnected_connection_checker(device_list, error_severity):
alert_details = []
for device in device_list:
previous_datetime = get_second_highest_datetime(device)
query = select_disconnected_connections(device, previous_datetime)
disconnected_connections_df = execute_query(query)
if not disconnected_connections_df.empty:
disconnected_connections_df.columns = ['local_device', 'local_port', 'remote_device', 'remote_port']
for index, row in disconnected_connections_df.iterrows():
code = 'disconnected_connection_' + device + '_' + row['local_port']
message = 'Connection Disconnected: ' + row['local_port'] + ' of ' + row['local_device'] + ' was ' + \
'connected to ' + row['remote_port'] + ' of ' + row['remote_device']
alert_founded = get_date_time()
alert_details.append([device, code, error_severity, message, alert_founded])
error_df = pd.DataFrame(alert_details, columns=['device_name', 'code', 'error_severity', 'message', 'date_time'])
return error_df
def utilization_checker(device_list, ram_threshold, cpu_threshold,
ram_severity, cpu_severity):
alert_details = []
for device in device_list:
query = select_latest_memory_usage(device)
utilization_df = execute_query(query)
if not utilization_df.empty:
utilization_df.columns = [
'device_name', 'ram_usage', 'cpu_usage', 'date_time'
]
for index, row in utilization_df.iterrows():
if row['ram_usage'] > ram_threshold:
code = 'high_ram_util'
message = 'RAM utilization: ' + str(row['ram_usage']) + '%'
alert_founded = str(row['date_time'].date().day) + '-' + \
str(row['date_time'].date().month) + '-' + \
str(row['date_time'].date().year) + ' ' + \
str(row['date_time'].time().hour) + ':' + \
str(row['date_time'].time().minute)
alert_details.append(
[device, code, ram_severity, message, alert_founded])
if row['cpu_usage'] > cpu_threshold:
code = 'high_cpu_util'
message = 'CPU utilization: ' + str(row['cpu_usage']) + '%'
alert_founded = str(row['date_time'].date().day) + '-' + \
str(row['date_time'].date().month) + '-' + \
str(row['date_time'].date().year) + ' ' + \
str(row['date_time'].time().hour) + ':' + \
str(row['date_time'].time().minute)
alert_details.append(
[device, code, cpu_severity, message, alert_founded])
error_df = pd.DataFrame(alert_details,
columns=[
'device_name', 'code', 'error_severity',
'message', 'date_time'
])
return error_df
def insert_alerts_to_db(error_df):
for index, row in error_df.iterrows():
execution_status = execute_procedure('prc_insert_dashboard_alert', [
row['device_name'], row['code'], row['error_severity'],
row['message'], row['date_time']
])
if execution_status != 'success':
print('prc_insert_dashboard_alert failed ' + execution_status)
query = select_notification_status(row['device_name'], row['code'])
notification_df = execute_query(query)
notification_df.columns = ['notification_id', 'text_message', 'email']
notification_id = int(notification_df['notification_id'][0])
if row['error_severity'] == 'danger':
if notification_df['text_message'][0] == 0:
for each_number in MONITOR_PHONE:
message_body = 'Notification from Network Manager : \nDevice- ' + row['device_name'] + \
'\nDetails: ' + row['message']
send_message(each_number, message_body)
execution_status = execute_procedure(
'prc_update_message_status', [notification_id])
if execution_status != 'success':
print('prc_update_message_status failed ' +
execution_status)
if row['error_severity'] == 'danger' or row[
'error_severity'] == 'warning':
if notification_df['email'][0] == 0:
for each_email in MONITOR_EMAIL:
email_subject = 'Notification from Network Manager regarding ' + row[
'device_name']
email_body = 'Device- ' + row[
'device_name'] + '\nDetails: ' + row['message']
send_email(each_email, email_subject, email_body)
execution_status = execute_procedure(
'prc_update_email_status', [notification_id])
if execution_status != 'success':
print('prc_update_email_status failed ' + execution_status)
def get_connections(searched_item):
query = select_all_connections(searched_item.lower())
connections_df = execute_query(query)
if connections_df.empty:
return '''<div class="form-inline justify-content-center">
<h6 class="text-secondary">No data</h6>
</div>'''
connections_df.columns = [
'Id', 'Local Device', 'Local Port', 'Remote Device', 'Remote Port',
'Interconnect 1', 'Interconnect 2'
]
connections_df['Edit'] = ''
connections_df['Edit'] = connections_df['Id'].apply(
lambda x: '<a href="/connections/edit/{}">Edit</a>'.format(x))
connections_df.drop('Id', axis=1, inplace=True)
connections_table = connections_df.to_html(
classes=['table table-bordered'],
table_id='dataTable',
header=True,
index=False,
escape=False)
return connections_table
def get_second_highest_datetime(device_name):
query = select_second_highest_date(device_name)
datetime_df = execute_query(query)
datetime = datetime_df[0].dt.strftime('%d-%m-%Y %H:%M:%S').tolist()[0]
return datetime
def collect_data_for_device(device_name):
# setting default values to device_data
# this function will return these default values if any device is not been found in database
device_data = {
'system_name': 'Device not Found',
'os_details': 'n/a',
'up_time': 'n/a',
'total_ram': 'n/a',
'memory_last_updated': 'n/a',
'ram_usages': [],
'cpu_usages': [],
'memory_timeline': [],
'lldp_last_updated': 'n/a',
'lldp_connections_table': 'n/a',
'unused_ports_last_updated': 'n/a',
'unused_ports': 'n/a',
'port_error_last_updated': 'n/a',
'port_error_table': 'n/a'
}
query = select_device_info(device_name)
device_info_df = execute_query(query)
if not device_info_df.empty:
device_info_df.columns = ['system_name', 'os_details', 'up_time', 'total_ram']
device_data['system_name'] = device_info_df['system_name'][0]
device_data['os_details'] = device_info_df['os_details'][0]
device_data['up_time'] = device_info_df['up_time'][0]
device_data['total_ram'] = device_info_df['total_ram'][0]
query = select_memory_usage(device_name)
memory_df = execute_query(query)
if not memory_df.empty:
memory_df = memory_df.iloc[::-1]
memory_df.columns = ['device_name', 'ram_usage', 'cpu_usage', 'date_time']
device_data['memory_last_updated'] = str(memory_df['date_time'][0])
device_data['ram_usages'] = memory_df['ram_usage'].to_list()
device_data['cpu_usages'] = memory_df['cpu_usage'].to_list()
device_data['memory_timeline'] = memory_df['date_time'].dt.strftime('%H:%M').to_list()
query = select_lldp_connections(device_name)
lldp_connections_df = execute_query(query)
if not lldp_connections_df.empty:
lldp_connections_df.columns = ['Local Device', 'Local Port', 'Remote Device', 'Remote Port', 'date_time']
device_data['lldp_last_updated'] = str(lldp_connections_df['date_time'][0])
lldp_connections_df = lldp_connections_df.drop('date_time', 1) # 1 is for dropping column and 0 for dropping row
device_data['lldp_connections_table'] = lldp_connections_df.to_html(classes=['table table-bordered'],
table_id='dataTable', header=True,
index=False)
query = select_unused_ports(device_name)
unused_ports_df = execute_query(query)
if not unused_ports_df.empty:
unused_ports_df.columns = ['device_name', 'port_name', 'date_time']
device_data['unused_ports_last_updated'] = str(unused_ports_df['date_time'][0])
port_names = unused_ports_df['port_name'].to_list()
device_data['unused_ports'] = ' ,'.join(port_names)
query = select_port_error(device_name)
port_error_df = execute_query(query)
if not port_error_df.empty:
port_error_df.columns = ['Port Name', 'Input Error', 'CRC', 'Frame', 'Overrun', 'Ignored', 'date_time']
device_data['port_error_last_updated'] = str(port_error_df['date_time'][0])
port_error_df = port_error_df.drop('date_time', 1) # 1 is for dropping column and 0 for dropping row
device_data['port_error_table'] = port_error_df.to_html(classes=['table table-bordered'], header=True,
index=False)
return device_data