def db_write(attempt):
add_command = """
INSERT INTO `FYP Data`.`event` (`device_id`, `syslog detail`, `message`, `acted_on`)
VALUES ('{}', '192.168.0.2:<28>Jan 10 20:37:24 mib2d[960]: SNMP_TRAP_LINK_DOWN: ifIndex 507, ifAdminStatus up(1), ifOperStatus down(2), ifName kjhgvnajieuijfkjdjjkjkdukjhkjhjhjhjvjhkbhajzsdvmbc hjmnadhbfj,ksvcfkvmfxbcnlkdshcbm xnvb ./ljkc kjmnzdsbxc vhkz.xncbv kjz,cbv kz.shmjdnv kjsm',
'192.168.0.2:<28>Jan 10 20:37:24 mib2d[960]: SNMP_TRAP_LINK_DOWN: ifIndex 507, ifAdminStatus up(1), ifOperStatus down(2), ifName kjhgvnajieuijfkjdjjkjkdukjhkjhjhjhjvjhkbhajzsdvmbc hjmnadhbfj,ksvcfkvmfxbcnlkdshcbm xnvb ./ljkc kjmnzdsbxc vhkz.xncbv kjz,cbv kz.shmjdnv kjsm',
'192.168.0.2:<28>Jan 10 20:37:24 mib2d[960]: 2');
""".format(attempt)
set_data_mysql(add_command)
print "done, write number: {}".format(attempt)
def set_peer_status(peer_id, status):
"""
Author - Jonathan Steward
Function - send MYSQL command to set the configured state of peer
Inputs -
int - peer_id - peer_id of the peer acting on
int - status - the state of the peer
returns - n/a
"""
command = """
UPDATE `FYP Data`.BGP_peers
SET configured = {}
WHERE peer_id = {}
""".format(status, peer_id)
set_data_mysql(command)
def set_tool_log(self, success=False, variables=""):
"""
Author - Jonathan Steward
Function - Take the details of the tools exectuion and sends it to the database
Inputs - Success - Bool
"""
if variables:
self.variables = variables
self.success = success
command = """
INSERT INTO `FYP Data`.`tool_log` (`tool`, `user`, `success`, `vars`)
VALUES ("{}", "{}", "{}", "{}");
""".format(self.tool, self.user, self.success, self.variables)
print "logging the tool usage!"
set_data_mysql(command)
def update_interface_and_history(device_int):
"""
Author - Jonathan Steward
Function - update the interface details on the database
Inputs -
device_int - object - combined database and snmp gathered information
returns - n/a
"""
command = """
UPDATE `FYP Data`.`interfaces`
SET `traffic_out_counter`='{}', `last_updated` = '{}'
WHERE `interface_id`='{}';""".format(
int(device_int["current_counter"]),
device_int["update_time"],
device_int["db_id"])
set_data_mysql(command)
def add_command(device_int, device_id):
"""
Author - Jonathan Steward
Function - Adds a new interface into the DB
Inputs -
device_int - dictionary - details of the interface for the state update
device_id - int - device_id of the related device for linking in the DB
returns - n/a
"""
command = """
INSERT INTO `FYP Data`.`interfaces`
(`device_id`,`name`,`description`,`ip_address`,`state`, `speed`)
VALUES('{}','{}','{}','{}','{}', '{}');""".format(
device_id, device_int["name"], device_int["description"],
device_int["ip"], device_int["state"], device_int["speed"])
set_data_mysql(command)
def update_command(device_int, db_int):
"""
Author - Jonathan Steward
Function - updating database with new interface state
Inputs -
device_int - dictionary - details of the interface for the state update
db_int - list - details of the interface from the db, used for the id of the int record
returns - n/a
"""
command = """
UPDATE `FYP Data`.`interfaces`
SET `description`='{}', `ip_address`='{}', `state`='{}', `speed` ="{}"
WHERE `interface_id`='{}';""".format(device_int["description"],
device_int["ip"],
device_int["state"],
device_int["speed"], db_int[0])
set_data_mysql(command)
def close_event(device_int):
"""
Author - Jonathan Steward
Function - closes event that was created once automation has been triggered
Inputs -
device_int - object - combined database and snmp gathered information
returns - n/a
"""
command = """
SELECT * FROM `FYP Data`.interface_events
where interface_id = {} and state ='active' and issue = 'out utilization'""".format(device_int["db_id"])
event = get_data(command)
command = """
UPDATE `FYP Data`.`interface_events`
SET `state` = 'resolved'
WHERE event_id = {} ;""".format(event[0][0])
set_data_mysql(command)
update_interface_and_history(device_int)
def check_for_event(device_int, device_ip):
"""
Author - Jonathan Steward
Function - checks for an existing event and if one exists if its an old event or not
Inputs -
Global - timeout_minuets - defined at the top to identify how many minuets old an event
needs to be before closing it.
device_int - object - combined database and snmp gathered information
device_ip - string - ip address of the host for this event
returns -
"""
command = """
SELECT * FROM `FYP Data`.interface_events
where `interface_id` = '{}'
and `state` = 'active'
and `issue` = 'out utilization'""".format(device_int["db_id"])
events = get_data(command)
if events:
time_now = datetime.now()
time_diff = (time_now - events[0][4]).seconds
timeout_minuets = 5
if time_diff / 60 > timeout_minuets:
print_error("closing old event older than {} minuets".format(timeout_minuets))
command = """
UPDATE `FYP Data`.`interface_events`
SET `state` = 'resolved'
WHERE event_id = {} ;""".format(events[0][0])
set_data_mysql(command)
else:
print "event for {} on {} already exists will not act".format(device_int["name"], device_ip)
return False
command = """
INSERT INTO `FYP Data`.`interface_events` (`interface_id`, `state`, `issue`)
VALUES ('{}', 'active', 'out utilization');""".format(device_int["db_id"])
set_data_mysql(command)
return True
def record_actions(host, message, issue, acted):
"""
Author - Jonathan Steward
Function - sends details to db about event and what happened
Inputs -
host - string
message - string
issue - string
acted - bool
"""
device_id = get_device_id(host)
command = """
INSERT INTO `FYP Data`.`syslog_events` (`syslog detail`, `device_id`, `acted_on`, `message`)
VALUES ("{}", "{}", "{}", "{}");
""".format(issue, device_id, acted, message)
if "License" in message:
pass
elif "bgp" in message:
pass
else:
print_error(
"sending following command to log event\n{}".format(command))
set_data_mysql(command)
