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