def create_trigger(self, data):
sql = """
CREATE TRIGGER [dbo].trigger_{0} ON
[dbo].{1} AFTER INSERT AS BEGIN
INSERT INTO {2} ({3}, [{2}_source], [source_id], [source_table])
SELECT {4}, '{5}', [{1}_id], '{1}'
FROM dbo.{1} as t1 WITH (NOLOCK)
WHERE not exists (select * from {2} as t2
where t1.{1}_id = t2.source_id and t2.source_table = '{1}')
WAITFOR DELAY '00:00:01'
UPDATE {2} SET [date_dimension_id] = (SELECT date_id FROM date_dimension
WHERE [datetime] = cast({2}.datetime as date)) WHERE [date_dimension_id] IS NULL;
WAITFOR DELAY '00:00:01'
END;
""".format(
data['trigger_name'],
data['table'],
data['destination_table'],
','.join('[{}]'.format(dest) for dest in data['destination']),
','.join('[{}]'.format(targ) for targ in data['target']),
data['source']
)
try:
self.execute_raw_query(sql)
except Exception as error:
logger.log(sql)
logger.log("Encountered error : %s" % (str(error)), log_type='ERROR')
self.drop_trigger(data['trigger_name'])
raise ValueError(ErrorObject(type="StoredProcedureError", message="Please check the target tables and fields").to_json())
def drop_table(self, *table_name):
try:
for table in reversed(table_name):
sql = "DROP TABLE [{0}]".format(table)
self.execute_raw_query(sql)
except Exception as err:
logger.log("Encountered error : %s" % (err), log_type='ERROR')
raise ValueError(ErrorObject(type="ExceptionError", message="Table name doesn't exist").to_json())
def execute_raw_query(self, query):
try:
return db.engine.execute(query)
except Exception as err:
logger.log("Encountered error : %s" % (err), log_type='ERROR')
raise ValueError(ErrorObject(type="ExceptionError", message="Error on executing the query").to_json())
finally:
db.session.commit()
db.session.close()
db.engine.dispose()
def check_table_if_not_exist(self, table_name):
try:
sql = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '%s'" % (table_name)
sql_result = self.execute_raw_query(sql)
table = [row[0] for row in sql_result]
if len(table) is 0:
return True
else:
return False
except Exception as err:
logger.log("Encountered error while checking the table if not exists : %s" % (err), log_type='ERROR')
raise ValueError(ErrorObject(type="TableError", message="Error on checking the table if not exists").to_json())
def check_column_if_exist(self, table_name, column_name):
try:
sql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS \
where TABLE_NAME = '%s' and COLUMN_NAME = '%s'" % (table_name, column_name)
sql_result = self.execute_raw_query(sql)
column = [row[0] for row in sql_result]
if len(column) is not 0:
return True
raise ValueError(ErrorObject(type="FieldsError", message="Fields doesn't exist on Target table").to_json())
except Exception as err:
logger.log("Encountered error : %s" % (err), log_type='ERROR')
raise ValueError(ErrorObject(type="ExceptionError", message="Error on finding the columns on table").to_json())
def get(self, id=None, show=None, status=None):
args = self.api_utils.optional_parameters()
try:
poller_session = self.db_utils.model_session(self.main_model)
if id is not None:
poller_session = poller_session.filter(
self.main_model.id == id)
poller_session = poller_session.order_by(
self.main_model.id.desc()).all()
schema_option = self.api_utils.schema_options(
self.main_schema, args['columns'])
poller_result = self.main_schema(
**schema_option).dump(poller_session)
poller_result = self.api_utils.get_paginated_list(
poller_result, args['start'], args['limit'])
for poller_element in poller_result:
if 'blacklist' in args['include']:
blacklist = self.db_utils.select_with_filter(
Blacklist, BlacklistSchema,
{'snmp_poller_id': poller_element['id']})
poller_element['blacklist'] = blacklist
if 'selected_oid' in args['include']:
selected_oid = self.db_utils.select_with_filter(
SelectedOid, SelectedOidSchema,
{'snmp_poller_id': poller_element['id']})
poller_element['selected_oid'] = selected_oid
if 'selected_ips' in args['include']:
conn = DatabaseUtil(os.environ.get("DB_CONN"),
os.environ.get("DB_USER"),
os.environ.get("DB_PASSWORD"),
os.environ.get("DB_NAME"))
query_string = "Select {0}_id,ip_address,system_description,system_name,brand from {0}".format(
poller_element["table_name"])
result = conn.select_query(query_string)
poller_element['selected_ip'] = result
return {'data': poller_result}, 200
except Exception as err:
logger.log("Error encountered on Poller : %s" % str(err),
log_type='ERROR')
return eval(str(err)), 422
finally:
db.session.close()
db.engine.dispose()
def create_data_retention(self, table_name, retention=30):
sql = """
CREATE TRIGGER [dbo].trigger_{0} ON
[dbo].{0} AFTER INSERT AS BEGIN
DELETE FROM dbo.{0} WHERE {0}_id in (SELECT {0}_id
FROM dbo.{0}
where cast([datetime] as date) < CAST(DATEADD(DAY,-{1},GETDATE()) AS date))
END
""".format(table_name, retention)
try:
self.execute_raw_query(sql)
except Exception as error:
logger.log("Encountered error : %s" % (error), log_type='ERROR')
self.drop_trigger('data_retention_{0}'.format(table_name))
raise ValueError(ErrorObject(type="StoredProcedureError", message="Please check the target tables and fields").to_json())
def delete_data_using_id(self, Model, id):
try:
valid_query = db.session.query(Model).filter_by(**id).all()
if valid_query:
db.session.query(Model).filter_by(**id).delete(synchronize_session=False)
db.session.commit()
return True
else:
return False
except Exception as err:
db.session.rollback()
logger.log("Encountered value error while deleting the data : %s" % (err), log_type='ERROR')
raise ValueError(ErrorObject(type="ExceptionError",
message="Encountered error while deleting the data").to_json())
finally:
db.session.close()
db.engine.dispose()
def post(self):
args = self.api_utils.parameters_without_model(config='append')
if args["config"]:
try:
config = eval(args["config"][0])
config = NetworkDiscoverySchema().load(config)
scanner = NetworkDiscoveryScan(config['ip_address'],
config['subnet'],
config['community_string'])
result = scanner.run()
if result:
return result
else:
return {
'lists': [],
'type': 'Info',
'message': 'No SNMP Response'
}, 200
except ValidationError as value_error:
logger.log("Validation encountered on Network Discovery: %s" %
(str(value_error)),
log_type='ERROR')
return {
'lists': 'Payload error',
'type': 'ValidationError',
'message': 'Validation errors in your request'
}, 422
except Exception as err:
logger.log("Error encountered on Network Discovery : %s" %
str(err),
log_type='ERROR')
return {
'type': 'ValidationError',
'message': 'Invalid Payload'
}, 422
else:
return {
'lists': "No payload",
'type': 'ValidationError',
'message': 'Validation errors in your request'
}, 422
def delete(self, id=None):
try:
if id is None:
logger.log(
"Encountered error on poller service: ID doesn't exist",
log_type='ERROR')
return {
'message': "ID doesn't exist",
"type": "ReferenceError"
}, 422
get_pid = self.db_utils.select_with_filter(self.main_model,
self.main_schema,
{'id': id})
process = self.service.stop_service(pid=get_pid[0]['pid'])
self.db_utils.update_data(SnmpPoller, {'id': id}, {
'status': 0,
'pid': 0
},
commit=True)
logger.log("Stopped the poller service : (%s) %s" % (id, process))
return {'message': process}
except Exception as err:
logger.log("Encountered error on poller service :(%s) %s" %
(id, err),
log_type='ERROR')
return eval(str(err)), 422
def update_data(self, Model, filter_data, data, commit=False):
try:
valid_query = Model.query.filter_by(**filter_data).all()
if valid_query:
db.session.query(Model).filter_by(**filter_data).update(data)
db.session.flush()
if commit:
db.session.commit()
db.session.close()
db.engine.dispose()
return True
else:
return False
except exc.IntegrityError as integrity_err:
logger.log("Encountered Integrity error while inserting the data : %s" % (integrity_err), log_type='ERROR')
raise ValueError(ErrorObject(type="DuplicateEntryError", message="Encountered duplicate data").to_json())
except exc.DataError as data_err:
logger.log("Encountered Data error while inserting the data : %s" % (data_err), log_type='ERROR')
db.session.rollback()
raise ValueError(ErrorObject(type="MaxLengthError", message="Encountered max length").to_json())
except ValueError as value_err:
logger.log("Encountered value error while inserting the data : %s" % (value_err), log_type='ERROR')
db.session.rollback()
raise ValueError(ErrorObject(type="ValueError", message=str(value_err)).to_json())
def post(self):
args = self.api_utils.parameters_without_model(config='append')
try:
config = args['config'][0].replace('\'', '\"')
if len(config) == 0:
raise ValueError(
ErrorObject(type="Config cannot be blank",
message="ConfigError").to_json())
config = json.loads(config)
config_preview = {
'ip_list': config['ip_list'],
'community_string': config['community_string'],
'oid_list': config['oid_list']
}
status = config['ip_list'][0] if config['ip_list'] else ""
if self.api_utils.check_list_has_empty_string(
config_preview['ip_list']) or isinstance(status, str):
raise ValueError("Invalid IP list value")
for ip in config_preview['ip_list']:
IpListSchema().load(ip)
if not isinstance(config_preview['oid_list'], list):
logger.log(
"Validation encountered on polling data preview: %s" %
('Invalid oid list values'),
log_type='ERROR')
return {
'lists': 'Invalid oid list',
'type': 'ValidationError',
'message': 'Validation errors in your request'
}, 422
poll_preview = PollPreview(config_preview)
result = poll_preview.run()
return result
except ValidationError as value_error:
logger.log("Validation encountered on polling data preview: %s" %
(str(value_error)),
log_type='ERROR')
return {
'lists': value_error.messages,
'type': 'ValidationError',
'message': 'Validation errors in your request'
}, 422
except Exception as err:
logger.log("Error encountered on polling data preview: %s" %
str(err),
log_type='ERROR')
return {'type': 'ExceptionType', 'message': str(err)}, 422
def put(self, id=None):
try:
get_poller_service = self.db_utils.select_with_filter(
self.main_model, self.main_schema, {'id': id})
if (id is None) or (len(get_poller_service) == 0):
logger.log(
"Encountered error on poller service: ID is invalid",
log_type='ERROR')
return {
'message': "ID doesn't exist",
"type": "ReferenceError"
}, 422
# check_service = self.service.check_service(pid=get_poller_service[0]['pid'], file_name='poller.exe')
check_service = self.service.check_service(
pid=get_poller_service[0]['pid'], file_name='poller.py')
if 'image name' in str(check_service.decode("utf-8")).lower():
logger.log("Encountered error on poller service: \
poller service is already running",
log_type='ERROR')
return {
'message': "poller service is already running",
"type": "ServiceError"
}, 422
else:
# self.service.start_service(id, 'poller.exe')
self.service.start_service(id, 'poller.py')
logger.log(
"Starting the Service : (%s) Successfully running the service"
% (id))
return {'message': 'Successfully running the service'}, 200
except Exception as err:
logger.log("Encountered error on poller service : %s" % (str(err)))
return eval(str(err)), 422
def get(self, id=None, log='logs', level=None):
args = self.api_utils.optional_parameters()
try:
if id is None:
logger.log(
"Encountered error on poller service: ID is invalid",
log_type='ERROR')
return {
'message': "ID doesn't exist",
"type": "ReferenceError"
}, 422
logs = db.session.query(SnmpPollerLogs).filter(
SnmpPollerLogs.snmp_poller_id == id)
if level is not None:
logs = logs.filter(SnmpPollerLogs.log_level == level)
logger.log(
"Retrieving data from poller logs with filter : level %s" %
(level))
if args['start'] != 1 or args['limit'] != 0:
logs = logs.order_by(SnmpPollerLogs.id.asc()).paginate(
args['start'], args['limit'], False).items
logger.log(
"Retrieving data from poller logs with paginate : start %s and limit %s"
% (args['start'], args['limit']))
else:
logs = logs.order_by(SnmpPollerLogs.id.asc()).all()
logger.log("Retrieving data from poller logs. Status OK 200")
logs_data = SnmpPollerLogsSchema(many=True).dump(logs)
return {'data': logs_data}, 200
except Exception as err:
pass
finally:
db.session.close()
db.engine.dispose()
def delete(self, id=None):
try:
get_table_name = None
poller = self.db_utils.select_with_filter(self.main_model,
self.main_schema,
{'id': id})
if poller:
# poller_tasklist = self.service.check_service(poller[0]['pid'], 'poller.exe')
poller_tasklist = self.service.check_service(
poller[0]['pid'], 'poller.py')
if 'image name' in str(
poller_tasklist.decode("utf-8")).lower():
raise ValueError(
ErrorObject(type="ServiceError",
message="Poller is running.").to_json())
delete_status = self.db_utils.delete_data_using_id(
SnmpPoller, {'id': id})
get_table_name = poller[0]['table_name']
self.db_utils.drop_table(get_table_name)
logger.log("Deleting the data on SNMP Poller, id : %s" % (id))
return {'message': "Successfully deleted."}, 200
else:
logger.log("Encountered error on poller: ID doesn't exist",
log_type='ERROR')
return {
'message': "ID doesn't exist",
"type": "ReferenceError"
}, 422
except Exception as err:
logger.log("Error encountered on SNMP Poller : %s" % str(err),
log_type='ERROR')
return eval(str(err)), 422
def insert_data(self ,module_name, Model, data, commit=False):
try:
model = Model(**data)
db.session.add(model)
db.session.flush()
if commit:
db.session.commit()
return model.id
except exc.IntegrityError as integrity_err:
logger.log("Encountered Integrity error while inserting the data : %s" % (integrity_err), log_type='ERROR')
db.session.rollback()
raise ValueError(ErrorObject(type="DuplicateEntryError", message="%s already exists." % (module_name)).to_json())
except exc.DataError as data_err:
logger.log("Encountered Data error while inserting the data : %s" % (data_err), log_type='ERROR')
db.session.rollback()
raise ValueError(ErrorObject(type="MaxLengthError", message="Encountered max length").to_json())
except Exception as err:
logger.log("Encountered error while inserting the data : %s" % (err), log_type='ERROR')
db.session.rollback()
raise ValueError(ErrorObject(type="InsertError", message="Encountered while inserting the data").to_json())
finally:
db.session.close()
db.engine.dispose()
def put(self, id=None):
args = self.api_utils.parameters(self.main_model(),
blacklist="append",
selected_oid="append",
ip_list="append")
poller_id = id
conn = DatabaseUtil(os.environ.get("DB_CONN"),
os.environ.get("DB_USER"),
os.environ.get("DB_PASSWORD"),
os.environ.get("DB_NAME"))
poll_data = conn.select_query(
'Select status from snmp_poller where id = {0}'.format(poller_id))
if not poll_data:
return {
'message': "ID doesn't exist",
"type": "ReferenceError"
}, 422
poll_status = poll_data[0]['status']
list_of_ids = {}
if poll_status != 1:
try:
blacklist = list(
eval(items) for items in list(args['blacklist']))
selected_oid = list(filter(None, args['selected_oid']))
ip_list = list(eval(items) for items in list(args['ip_list']))
del args['ip_list']
del args['blacklist']
del args['selected_oid']
SnmpPollerSchema().load(args)
if (selected_oid and ip_list) and self.api_utils.ip_validator(
ip_list) and self.api_utils.ip_validator(
blacklist) and self.api_utils.oid_validator(
conn, selected_oid):
if blacklist is not None:
self.db_utils.delete_data_using_id(
Blacklist, {'snmp_poller_id': poller_id})
for blacklist_element in blacklist:
blacklist_obj = {}
blacklist_obj['ip_address'] = blacklist_element[
'ip_address']
blacklist_obj[
'system_description'] = blacklist_element[
'system_description']
blacklist_obj['system_name'] = blacklist_element[
'system_name']
blacklist_obj['brand'] = blacklist_element['brand']
blacklist_obj['snmp_poller_id'] = poller_id
blacklist_data = BlacklistSchema().load(
blacklist_obj)
blacklist_result = self.db_utils.insert_data(
self.module_name,
Blacklist,
blacklist_data,
commit=True)
list_of_ids['blacklist'] = blacklist_result
if selected_oid is not None:
self.db_utils.delete_data_using_id(
SelectedOid, {'snmp_poller_id': poller_id})
for selected_oid_element in selected_oid:
selected_oid_obj = {}
selected_oid_obj['oid_key'] = selected_oid_element
selected_oid_obj['snmp_poller_id'] = poller_id
selected_oid_data = SelectedOidSchema().load(
selected_oid_obj)
selected_oid_result = self.db_utils.insert_data(
self.module_name,
SelectedOid,
selected_oid_data,
commit=True)
list_of_ids['selected_oid'] = selected_oid_result
if ip_list is not None:
table_name = conn.select_query(
'Select table_name from snmp_poller where id = %s '
% (poller_id))[0]
new_table_name = table_name["table_name"]
conn.truncate_table('Truncate table %s ' %
(new_table_name))
query_string = 'INSERT INTO {0} (ip_address,system_description,brand,system_name) values ({1})'.format(
new_table_name,
'%(ip_address)s,%(system_description)s,%(brand)s ,%(system_name)s'
)
conn.insert_many_query(query_string, ip_list)
del args['table_name']
args['status'] = 0
args['pid'] = 0
self.db_utils.update_data(self.main_model,
{'id': poller_id},
args,
commit=True)
args['snmp_poller_id'] = poller_id
logger.log("Update SNMP Poller. Status Succesful 200")
return {
'message': 'Update Successful.',
'snmp_poller_payload': args
}, 200
else:
return {
'message': 'Payload verification failed.',
'snmp_poller_payload': args
}, 422
except ValidationError as value_error:
logger.log("Validation encountered on SNMP poller table: %s" %
(str(value_error)),
log_type='ERROR')
self.db_utils.data_rollback(list_of_ids)
return {
'lists': value_error.messages,
'type': 'ValidationError',
'message': 'Validation errors in your request'
}, 422
except Exception as err:
logger.log("Error encountered on SNMP poller : %s" % str(err),
log_type='ERROR')
self.db_utils.data_rollback(list_of_ids)
return eval(str(err)), 422
else:
return {'message': "Update unsuccessfull. Poller is running."}, 422
def post(self):
conn = DatabaseUtil(os.environ.get("DB_CONN"),
os.environ.get("DB_USER"),
os.environ.get("DB_PASSWORD"),
os.environ.get("DB_NAME"))
args = self.api_utils.parameters(self.main_model(),
blacklist="append",
selected_oid="append",
ip_list="append")
list_of_ids = {}
try:
blacklist = list(eval(items) for items in list(args['blacklist']))
selected_oid = list(filter(None, args['selected_oid']))
ip_list = list(eval(items) for items in list(args['ip_list']))
del args['ip_list']
del args['blacklist']
del args['selected_oid']
SnmpPollerSchema().load(args)
if (selected_oid and ip_list) and self.api_utils.ip_validator(
ip_list) and self.api_utils.ip_validator(
blacklist) and self.api_utils.oid_validator(
conn, selected_oid):
poller_data = self.main_schema().load(args)
poller_result = self.db_utils.insert_data(self.module_name,
self.main_model,
poller_data,
commit=True)
list_of_ids['snmp_poller'] = poller_result
if blacklist is not None:
for blacklist_element in blacklist:
blacklist_obj = {}
blacklist_obj['ip_address'] = blacklist_element[
'ip_address']
blacklist_obj['snmp_poller_id'] = poller_result
blacklist_obj[
'system_description'] = blacklist_element[
'system_description']
blacklist_obj['system_name'] = blacklist_element[
'system_name']
blacklist_obj['brand'] = blacklist_element['brand']
blacklist_data = BlacklistSchema().load(blacklist_obj)
blacklist_result = self.db_utils.insert_data(
self.module_name,
Blacklist,
blacklist_data,
commit=True)
list_of_ids['blacklist'] = blacklist_result
if selected_oid is not None:
for selected_oid_element in selected_oid:
selected_oid_obj = {}
selected_oid_obj['oid_key'] = selected_oid_element
selected_oid_obj['snmp_poller_id'] = poller_result
selected_oid_data = SelectedOidSchema().load(
selected_oid_obj)
selected_oid_result = self.db_utils.insert_data(
self.module_name,
SelectedOid,
selected_oid_data,
commit=True)
list_of_ids['selected_oid'] = selected_oid_result
oid_raw = conn.select_query('Select oid_key from oid_list')
oid_main = [oid['oid_key'] for oid in oid_raw]
oid_main.extend([
'ip_address', 'status', 'brand', 'system_description',
'system_name'
])
SchemaBuilder(args['table_name'],
fields=oid_main).create_table(default_date=True)
SchemaBuilder().create_data_retention(args['table_name'])
conn = DatabaseUtil(os.environ.get("DB_CONN"),
os.environ.get("DB_USER"),
os.environ.get("DB_PASSWORD"),
os.environ.get("DB_NAME"))
query_string = 'INSERT INTO {0} (ip_address,system_description,brand,system_name) values ({1})'.format(
args['table_name'],
'%(ip_address)s,%(system_description)s,%(brand)s ,%(system_name)s'
)
conn.insert_many_query(query_string, ip_list)
args['snmp_poller_id'] = poller_result
logger.log("Inserted data to SNMP Poller. Status CREATED 201")
return {
'message': 'Successfully added.',
'snmp_poller_payload': args
}, 201
else:
return {
'message': 'Payload verification failed.',
'snmp_poller_payload': args
}, 422
except ValidationError as value_error:
logger.log("Validation encountered on SNMP poller table: %s" %
(str(value_error)),
log_type='ERROR')
self.db_utils.data_rollback(list_of_ids)
return {
'lists': value_error.messages,
'type': 'ValidationError',
'message': 'Validation errors in your request'
}, 422
except Exception as err:
logger.log("Error encountered on SNMP poller : %s" % str(err),
log_type='ERROR')
self.db_utils.data_rollback(list_of_ids)
return eval(str(err)), 422
