def get_or_create_account(): """In some cases rh_account might not have already been created by listener, create it for usage in manager""" rh_account = RHAccount.select(RHAccount.id).where(RHAccount.name == connexion.context['user']) if rh_account.count() == 0: rh_account = RHAccount.insert(name=connexion.context['user']).on_conflict( conflict_target=[RHAccount.name], update={RHAccount.name: connexion.context['user']}).returning(RHAccount) return rh_account
def handle_patch(cls, **kwargs): """Bulk/single system change of opt_out status""" args_desc = [{ 'arg_name': 'opt_out', 'convert_func': None }, { 'arg_name': 'inventory_id', 'convert_func': parse_str_or_list }] args = cls._parse_arguments(kwargs['data'], args_desc) opt_out = args['opt_out'] system_list = args['inventory_id'] update = (SystemPlatform.update(opt_out=opt_out).where( ((RHAccount.select(RHAccount.id).where( RHAccount.name == connexion.context['user']).get() ).id == SystemPlatform.rh_account_id) & (SystemPlatform.inventory_id << system_list)).returning( SystemPlatform)) updated = [] for system in update.execute(): updated.append(system.inventory_id) if not updated: raise ApplicationException( 'inventory_id must exist and inventory_id must be visible to user', 404) return {'updated': updated}
def _cve_details(cls, synopsis): retval = cls._get_cve_details(synopsis) cve_details = (CveAccountData.select( BusinessRisk.name.alias('risk'), CveAccountData.business_risk_id.alias('risk_id'), CveAccountData.business_risk_text.alias('risk_text'), Status.name.alias("status"), CveAccountData.status_id.alias("status_id"), CveAccountData.status_text.alias("status_text"), CveAccountData.systems_status_divergent).join( BusinessRisk, on=(CveAccountData.business_risk_id == BusinessRisk.id)).join( Status, on=(CveAccountData.status_id == Status.id)).join( CveMetadata, on=(CveAccountData.cve_id == CveMetadata.id)).where( CveAccountData.rh_account_id == ( RHAccount.select(RHAccount.id).where( RHAccount.name == connexion.context['user']))).where( CveMetadata.cve == synopsis)).dicts() if cve_details.count(): retval['business_risk'] = cve_details[0]['risk'] retval['business_risk_id'] = cve_details[0]['risk_id'] retval['business_risk_text'] = cve_details[0]['risk_text'] retval['status'] = cve_details[0]['status'] retval['status_id'] = cve_details[0]['status_id'] retval['status_text'] = cve_details[0]['status_text'] retval['systems_status_divergent'] = cve_details[0][ 'systems_status_divergent'] else: retval['business_risk'] = DEFAULT_BUSINESS_RISK retval['business_risk_id'] = 0 retval['business_risk_text'] = None retval['status'] = DEFAULT_STATUS retval['status_id'] = 0 retval['status_text'] = None retval['systems_status_divergent'] = 0 # add counts of systems with all statuses retval['systems_status_detail'] = {} # pylint: disable=singleton-comparison status_detail = ( SystemVulnerabilities.select( SystemVulnerabilities.status, fn.Count(SystemVulnerabilities.status).alias('systems')).join( SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id)). join(CveMetadata, on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join( RHAccount, on=(SystemPlatform.rh_account_id == RHAccount.id)). where(CveMetadata.cve == synopsis).where( RHAccount.name == connexion.context['user']).where( SystemVulnerabilities.when_mitigated.is_null(True)).where( SystemPlatform.opt_out == False) # noqa: E712 .group_by(SystemVulnerabilities.status).dicts()) for row in status_detail: retval['systems_status_detail'][row['status']] = row['systems'] return retval
def get_account_data(acc_name): """Translates account name to RH account data""" result = RHAccount.select(RHAccount.id, RHAccount.cve_cache_from, RHAccount.cve_cache_keepalive) \ .where(RHAccount.name == acc_name) \ .tuples() \ .first() if result: return result return None, None, None
def handle_patch(cls, **kwargs): """Set status for a CVE""" data = kwargs['data'] cve_list = data['cve'] if isinstance(cve_list, str): cve_list = [cve_list] values = {} updated = [] if 'status_id' in data: values['status_id'] = data['status_id'] if 'status_text' in data: try: values['status_text'] = data['status_text'].strip() \ if data['status_text'].strip() else None except AttributeError: values['status_text'] = None if not values: return cls.format_exception( 'At least one of the "status_id" or "status_text" parameters is required.', 400) try: to_insert = [] cves = CveMetadata.select(CveMetadata.id, CveMetadata.cve).where(CveMetadata.cve << cve_list) rh_account = RHAccount.select(RHAccount.id).where(RHAccount.name == connexion.context['user']) for cve in cves: updated.append(cve.cve) if 'status_id' in data: # refresh number of divergent statuses in CVE-system pairs # pylint: disable=singleton-comparison query = (SystemVulnerabilities.select(fn.Count(SystemVulnerabilities.id).alias('systems')) .join(SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id)) .where(SystemPlatform.rh_account_id == rh_account[0].id) .where(SystemPlatform.opt_out == False) # noqa: E712 .where(SystemVulnerabilities.cve_id == cve.id) .where(SystemVulnerabilities.when_mitigated.is_null(True)) .where(SystemVulnerabilities.status_id != values.get('status_id', 0)) .dicts()) values['systems_status_divergent'] = query[0]['systems'] to_insert.append((cve.id, rh_account[0].id, values.get('status_id', 0), values.get('status_text', None), values.get('systems_status_divergent', 0))) if not to_insert: return cls.format_exception('At least one given CVE must exist', 404) (CveAccountData.insert_many(to_insert, fields=cls._fields) .on_conflict(conflict_target=cls._conflict_target, preserve=[], update=values).execute()) except (IntegrityError, psycopg2IntegrityError, DataError): # usually means bad status LOGGER.exception('Error during setting status (IntegrityError):') DB.rollback() return cls.format_exception(f'status_id=%s is invalid' % data['status_id'], 400) except ValueError as value_error: LOGGER.exception('Error during setting status (ValueError):') DB.rollback() return cls.format_exception(f'status_text or other key value is invalid ({value_error})', 400) return {'updated': updated}
def handle_patch(cls, **kwargs): """Logic of the opt out feature""" inventory_id = kwargs['inventory_id'] value = kwargs['value'] update = (SystemPlatform.update(opt_out=value) .where(((RHAccount.select(RHAccount.id) .where(RHAccount.name == connexion.context['user']) .get()).id == SystemPlatform.rh_account_id) & (SystemPlatform.inventory_id == inventory_id))) rows_affected = update.execute() if rows_affected == 0: raise ApplicationException('inventory_id must exist and inventory_id must be visible to user', 404)
def handle_patch(cls, **kwargs): """Set business risk for a CVE""" data = kwargs['data'] cve_list = data['cve'] if isinstance(cve_list, str): cve_list = [cve_list] values = {} updated = [] if 'business_risk_id' in data: values['business_risk_id'] = data['business_risk_id'] if 'business_risk_text' in data: values['business_risk_text'] = data['business_risk_text'].strip() \ if data['business_risk_text'].strip() else None if not values: return cls.format_exception( 'At least one of the "business_risk_id" or "business_risk_text" parameters is required.', 400) try: to_insert = [] cves = CveMetadata.select( CveMetadata.id, CveMetadata.cve).where(CveMetadata.cve << cve_list) rh_account = RHAccount.select(RHAccount.id).where( RHAccount.name == connexion.context['user']) for cve in cves: updated.append(cve.cve) to_insert.append((cve.id, rh_account[0].id, values.get('business_risk_id', 0), values.get('business_risk_text', None))) if not to_insert: return cls.format_exception( 'At least one given CVE must exist', 404) (CveAccountData.insert_many( to_insert, fields=cls._fields).on_conflict( conflict_target=cls._conflict_target, preserve=[], update=values).execute()) except (IntegrityError, psycopg2IntegrityError): # usually means bad business_risk_id LOGGER.exception( 'Error during setting business risk (IntegrityError):') DB.rollback() return cls.format_exception( f'business_risk_id=%s is invalid' % data['business_risk_id'], 400) except ValueError as value_error: LOGGER.exception( 'Error during setting business risk (ValueError):') DB.rollback() return cls.format_exception(str(value_error), 500) return {'updated': updated}
def handle_patch(cls, **kwargs): """Logic of the opt out feature""" inventory_id = kwargs['inventory_id'] value = kwargs['value'] try: account = (RHAccount.select(RHAccount.id).where( RHAccount.name == connexion.context['user']).get()) except DoesNotExist as exc: raise ApplicationException('user does not exist', 403) from exc update = (SystemPlatform.update(opt_out=value).where( (account.id == SystemPlatform.rh_account_id) & (SystemPlatform.inventory_id == inventory_id) & (SystemPlatform.when_deleted.is_null(True)))) rows_affected = update.execute() if rows_affected == 0: raise ApplicationException( 'inventory_id must exist and inventory_id must be visible to user', 404)
def handle_patch(cls, **kwargs): """Bulk/single system change of opt_out status""" opt_out = kwargs['data']['opt_out'] system_list = kwargs['data']['inventory_id'] if isinstance(system_list, str): system_list = [system_list] update = (SystemPlatform.update(opt_out=opt_out).where( ((RHAccount.select(RHAccount.id).where( RHAccount.name == connexion.context['user']).get() ).id == SystemPlatform.rh_account_id) & (SystemPlatform.inventory_id << system_list)).returning( SystemPlatform)) updated = [] for system in update.execute(): updated.append(system.inventory_id) if not updated: raise ApplicationException( 'inventory_id must exist and inventory_id must be visible to user', 404) return {'updated': updated}
def get_account_id(acc_name): """Translates account name to RH account ID""" return RHAccount.select(RHAccount.id) \ .where(RHAccount.name == acc_name) \ .first()
def _cve_details(cls, synopsis, args): retval = cls._get_cve_details(synopsis, args) cve_details = (CveAccountData.select( BusinessRisk.name.alias('risk'), CveAccountData.business_risk_id.alias('risk_id'), CveAccountData.business_risk_text.alias('risk_text'), Status.name.alias("status"), CveAccountData.status_id.alias("status_id"), CveAccountData.status_text.alias("status_text")).join( BusinessRisk, on=(CveAccountData.business_risk_id == BusinessRisk.id)).join( Status, on=(CveAccountData.status_id == Status.id)).join( CveMetadata, on=(CveAccountData.cve_id == CveMetadata.id)).where( CveAccountData.rh_account_id == ( RHAccount.select(RHAccount.id).where( RHAccount.name == connexion.context['user']))).where( CveMetadata.cve == synopsis)).dicts() if cve_details.count(): retval['business_risk'] = cve_details[0]['risk'] retval['business_risk_id'] = cve_details[0]['risk_id'] retval['business_risk_text'] = cve_details[0]['risk_text'] retval['status'] = cve_details[0]['status'] retval['status_id'] = cve_details[0]['status_id'] retval['status_text'] = cve_details[0]['status_text'] else: retval['business_risk'] = DEFAULT_BUSINESS_RISK retval['business_risk_id'] = 0 retval['business_risk_text'] = None retval['status'] = DEFAULT_STATUS retval['status_id'] = 0 retval['status_text'] = None # add counts of systems with all statuses retval['systems_status_detail'] = {} # pylint: disable=singleton-comparison rh_account_id, _, _ = get_account_data(connexion.context['user']) status_detail = (SystemVulnerabilities.select( SystemVulnerabilities.status_id, fn.Count(SystemVulnerabilities.status_id).alias('systems') ).join(SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id)).join( CveMetadata, on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join( InsightsRule, JOIN.LEFT_OUTER, on=(InsightsRule.id == SystemVulnerabilities.rule_id) ).where(CveMetadata.cve == synopsis).where( SystemVulnerabilities.rh_account_id == rh_account_id ).where( (SystemVulnerabilities.mitigation_reason.is_null(True)) | (InsightsRule.active == False) & (~InsightsRule.rule_only)).where( (SystemVulnerabilities.when_mitigated.is_null(True)) | (InsightsRule.active == True) & (~InsightsRule.rule_only)).where( (SystemPlatform.opt_out == False) & (SystemPlatform.stale == False) & (SystemPlatform.when_deleted.is_null(True))). group_by(SystemVulnerabilities.status_id).dicts()) divergent_systems = 0 status_detail = cyndi_join(status_detail) for row in status_detail: retval['systems_status_detail'][row['status_id']] = row['systems'] if row['status_id'] != retval['status_id']: divergent_systems += row['systems'] retval['systems_status_divergent'] = divergent_systems return retval
def handle_get(cls, **kwargs): # pylint: disable=singleton-comparison retval = { 'system_count': 0, 'cves_total': 0, 'cves_by_severity': { '0to3.9': { 'percentage': 0, 'count': 0 }, '4to7.9': { 'percentage': 0, 'count': 0 }, '8to10': { 'percentage': 0, 'count': 0 } }, 'recent_cves': { 'last7days': 0, 'last30days': 0, 'last90days': 0 }, 'top_cves': [] } rh_account = RHAccount.select(RHAccount.id).where(RHAccount.name == connexion.context['user']).first() if rh_account is None: return retval rh_account = rh_account.id retval['system_count'] = SystemPlatform.select(fn.COUNT(SystemPlatform.id).alias('count')).where( (SystemPlatform.rh_account_id == rh_account) & ((SystemPlatform.last_evaluation.is_null(False)) | (SystemPlatform.advisor_evaluated.is_null(False))) & ((SystemPlatform.opt_out == False) & (SystemPlatform.stale == False))).first().count # noqa: E712 if retval['system_count'] == 0: return retval cves_total = CveAccountData.select(CveAccountData.cve_id).where((CveAccountData.rh_account_id == rh_account) & (CveAccountData.systems_affected > 0)) if cves_total.count() == 0: return retval retval['cves_total'] = cves_total.count() cve_details = CveMetadata.select(fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score).alias('cvss'), CveMetadata.public_date).where(CveMetadata.id << cves_total) retval['cves_by_severity']['0to3.9']['count'] = cve_details.select_from(fn.COUNT('*').alias('count')).where(cve_details.c.cvss < 4).first().count retval['cves_by_severity']['4to7.9']['count'] = cve_details.select_from( fn.COUNT('*').alias('count')).where((cve_details.c.cvss >= 4) & (cve_details.c.cvss < 8)).first().count retval['cves_by_severity']['8to10']['count'] = cve_details.select_from(fn.COUNT('*').alias('count')).where(cve_details.c.cvss >= 8).first().count cve_count_by_severity = [v['count'] for v in retval['cves_by_severity'].values()] rounded_percentage = round_to_100_percent(cve_count_by_severity) for indx, keys in enumerate(retval['cves_by_severity']): retval['cves_by_severity'][keys]['percentage'] = rounded_percentage[indx] today = datetime.utcnow().date() retval['recent_cves']['last7days'] = cve_details.select_from( fn.COUNT('*').alias('count')).where(cve_details.c.public_date >= (today - timedelta(days=7))).first().count retval['recent_cves']['last30days'] = cve_details.select_from( fn.COUNT('*').alias('count')).where(cve_details.c.public_date >= (today - timedelta(days=30))).first().count retval['recent_cves']['last90days'] = cve_details.select_from( fn.COUNT('*').alias('count')).where(cve_details.c.public_date >= (today - timedelta(days=90))).first().count # The algorithm searches for CVEs with cvss score between 8 and 10, and then sort by a number of affected # systems if there are not 3 CVE in the 8 to 10 range, then it looks for CVEs in 4 to 8 range, sorted by a # number of systems affected. The high-end range check is exclusive that is why 11 here. cves_limit = 3 top_cves = cls._get_top_cves_by_cvss(8.0, 11, rh_account, limit=cves_limit) cls._build_top_cves(top_cves, retval) cves_count = top_cves.count() if cves_count < cves_limit: next_tier_top = cls._get_top_cves_by_cvss(4.0, 8.0, rh_account, limit=cves_limit-cves_count) cls._build_top_cves(next_tier_top, retval) next_cves_count = next_tier_top.count() if next_cves_count < (cves_limit - cves_count): last_tier_top = cls._get_top_cves_by_cvss(0.0, 4.0, rh_account, limit=cves_limit-(cves_count+next_cves_count)) cls._build_top_cves(last_tier_top, retval) return retval
def get_rules_for_cves(cves: list, rh_account=None) -> dict: """Return associated rules for a CVE""" # pylint: disable=singleton-comparison, unsubscriptable-object rules_map = {} rule_cve_mapping = {} rh_account_id = None if rh_account is not None: try: rh_account_id = RHAccount.select( RHAccount.id).where(RHAccount.name == rh_account)[0] except IndexError: pass if rh_account_id is not None: subquery = ( SystemVulnerabilities.select( SystemVulnerabilities.id, SystemVulnerabilities.rule_id, SystemVulnerabilities.cve_id).join( SystemPlatform, on=((SystemVulnerabilities.system_id == SystemPlatform.id) & (SystemPlatform.when_deleted.is_null(True)) & (SystemPlatform.stale == False) & (SystemPlatform.opt_out == False))) # noqa: E712 .where(SystemVulnerabilities.cve_id.in_(cves)).where( (SystemVulnerabilities.rh_account_id == rh_account_id) & (SystemVulnerabilities.mitigation_reason.is_null(True)))) subquery = cyndi_join(subquery) mapping = (CveRuleMapping.select(CveRuleMapping.cve_id, InsightsRule.name, InsightsRule.description_text, InsightsRule.summary_text, InsightsRule.reboot_required, InsightsRule.playbook_count, InsightsRule.change_risk, InsightsRule.kbase_node_id, InsightsRule.active, CveMetadata.cve, InsightsRule.rule_impact, InsightsRule.publish_date) .join(InsightsRule, on=(CveRuleMapping.rule_id == InsightsRule.id)) .join(CveMetadata, on=(CveRuleMapping.cve_id == CveMetadata.id)) .where((InsightsRule.active == True) & (~InsightsRule.rule_only)) # noqa: E712 .where(CveRuleMapping.rule_id.in_(CveRuleMapping.select(CveRuleMapping.rule_id).where(CveRuleMapping.cve_id.in_(cves)))).dicts()) \ if rh_account_id is None else \ (CveRuleMapping.select(CveRuleMapping.cve_id, InsightsRule.name, InsightsRule.description_text, InsightsRule.summary_text, InsightsRule.reboot_required, InsightsRule.playbook_count, InsightsRule.change_risk, InsightsRule.kbase_node_id, InsightsRule.active, CveMetadata.cve, InsightsRule.rule_impact, InsightsRule.publish_date, fn.COUNT(subquery.c.id).alias('systems_affected')) .join(InsightsRule, on=(CveRuleMapping.rule_id == InsightsRule.id)) .join(CveMetadata, on=(CveRuleMapping.cve_id == CveMetadata.id)) .join(subquery, JOIN.LEFT_OUTER, on=((subquery.c.rule_id == InsightsRule.id) & (subquery.c.cve_id == CveRuleMapping.cve_id))) .where((InsightsRule.active == True) & (~InsightsRule.rule_only)) # noqa: E712 .where(CveRuleMapping.rule_id.in_(CveRuleMapping.select(CveRuleMapping.rule_id).where(CveRuleMapping.cve_id.in_(cves)))) .group_by(CveRuleMapping.cve_id, InsightsRule.name, InsightsRule.description_text, InsightsRule.summary_text, InsightsRule.reboot_required, InsightsRule.playbook_count, InsightsRule.change_risk, InsightsRule.kbase_node_id, InsightsRule.active, CveMetadata.cve, InsightsRule.rule_impact, InsightsRule.publish_date) .dicts()) for row in mapping: rule_cve_mapping.setdefault(row['name'], []).append(row['cve']) for row in mapping: rule_detail = { 'rule_id': row['name'], 'description': row['description_text'], 'summary': row['summary_text'], 'reboot_required': row['reboot_required'], 'playbook_count': row['playbook_count'], 'change_risk': row['change_risk'], 'kbase_node_id': row['kbase_node_id'], 'associated_cves': rule_cve_mapping[row['name']], 'rule_impact': row['rule_impact'], 'publish_date': format_datetime(row['publish_date']) } if rh_account_id is not None: rule_detail['systems_affected'] = row['systems_affected'] rules_map.setdefault(row['cve_id'], []).append(rule_detail) return rules_map
def handle_get(cls, **kwargs): # pylint: disable=singleton-comparison cve_count_by_severity = [] retval = { 'system_count': 0, 'cves_total': 0, 'cves_by_severity': { '0to3.9': { 'percentage': 0, 'count': 0 }, '4to7.9': { 'percentage': 0, 'count': 0 }, '8to10': { 'percentage': 0, 'count': 0 } }, 'recent_cves': { 'last7days': 0, 'last30days': 0, 'last90days': 0 }, 'top_cves': [] } rh_account = RHAccount.select(RHAccount.id).where(RHAccount.name == connexion.context['user']).first() if rh_account is None: return retval rh_account = rh_account.id retval['system_count'] = SystemPlatform.select(fn.COUNT(SystemPlatform.id).alias('count')).where( (SystemPlatform.rh_account_id == rh_account) & (SystemPlatform.last_evaluation.is_null(False)) & (SystemPlatform.opt_out == False)).first().count # noqa: E712 if retval['system_count'] == 0: return retval cves_total = CveAccountData.select(CveAccountData.cve_id).where((CveAccountData.rh_account_id == rh_account) & (CveAccountData.systems_affected > 0)) if cves_total.count() == 0: return retval retval['cves_total'] = cves_total.count() cve_details = CveMetadata.select(fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score).alias('cvss'), CveMetadata.public_date).where(CveMetadata.id << cves_total) retval['cves_by_severity']['0to3.9']['count'] = cve_details.select_from(fn.COUNT('*').alias('count')).where(cve_details.c.cvss < 4).first().count retval['cves_by_severity']['4to7.9']['count'] = cve_details.select_from( fn.COUNT('*').alias('count')).where((cve_details.c.cvss >= 4) & (cve_details.c.cvss < 8)).first().count retval['cves_by_severity']['8to10']['count'] = cve_details.select_from(fn.COUNT('*').alias('count')).where(cve_details.c.cvss >= 8).first().count cve_count_by_severity = [v['count'] for v in retval['cves_by_severity'].values()] rounded_percentage = round_to_100_percent(cve_count_by_severity) for indx, keys in enumerate(retval['cves_by_severity']): retval['cves_by_severity'][keys]['percentage'] = rounded_percentage[indx] today = datetime.utcnow().date() retval['recent_cves']['last7days'] = cve_details.select_from( fn.COUNT('*').alias('count')).where(cve_details.c.public_date >= (today - timedelta(days=7))).first().count retval['recent_cves']['last30days'] = cve_details.select_from( fn.COUNT('*').alias('count')).where(cve_details.c.public_date >= (today - timedelta(days=30))).first().count retval['recent_cves']['last90days'] = cve_details.select_from( fn.COUNT('*').alias('count')).where(cve_details.c.public_date >= (today - timedelta(days=90))).first().count top_cves = CveMetadata.select(CveMetadata.cve, CveMetadata.cvss3_score, CveMetadata.cvss2_score, CveMetadata.description, CveAccountData.systems_affected).join(CveAccountData, on=(CveMetadata.id == CveAccountData.cve_id)).where( (CveAccountData.rh_account_id == rh_account) & (CveAccountData.systems_affected > 0)).order_by( CveAccountData.systems_affected.desc(), fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score).desc(nulls='LAST'), CveMetadata.id).limit(3) for cve in top_cves: cve_dict = {} cve_dict['synopsis'] = cve.cve cve_dict['cvss2_score'] = str(cve.cvss2_score) cve_dict['cvss3_score'] = str(cve.cvss3_score) cve_dict['description'] = cve.description cve_dict['systems_affected'] = cve.cveaccountdata.systems_affected retval['top_cves'].append(cve_dict) return retval
def handle_patch(cls, **kwargs): """Update the 'status' field for a system/cve combination""" # pylint: disable=singleton-comparison data = kwargs['data'] in_inventory_id_list, in_cve_list, status_to_cves_map, status_text_to_cves_map = cls._prepare_data( data) try: rh_account = RHAccount.select(RHAccount.id).where( RHAccount.name == connexion.context['user']) systems = (SystemPlatform.select(SystemPlatform.id).where( SystemPlatform.rh_account_id == rh_account[0].id) ) # noqa: E712 if in_inventory_id_list is not None: systems = systems.where( SystemPlatform.inventory_id << in_inventory_id_list) rows_modified = set() # set statuses and their CVE lists for status_id, status_cve_list in status_to_cves_map.items(): status_id_update = (SystemVulnerabilities.update( status_id=status_id).where( (SystemVulnerabilities.system_id << systems) & (SystemVulnerabilities.cve_id << (CveMetadata.select(CveMetadata.id).where( CveMetadata.cve << status_cve_list)))).returning( SystemVulnerabilities.id)) rows_modified.update([row.id for row in status_id_update]) cls._update_divergent_status_count(in_cve_list, rh_account[0].id) for status_text, status_cve_list in status_text_to_cves_map.items( ): status_text_update = (SystemVulnerabilities.update( status_text=status_text).where( (SystemVulnerabilities.system_id << systems) & (SystemVulnerabilities.cve_id << (CveMetadata.select(CveMetadata.id).where( CveMetadata.cve << status_cve_list)))).returning( SystemVulnerabilities.id)) rows_modified.update([row.id for row in status_text_update]) updated_details = (SystemVulnerabilities.select( SystemPlatform.inventory_id, CveMetadata.cve).join( CveMetadata, on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join( SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform. id)).where(SystemVulnerabilities.id << list( rows_modified)).dicts()) updated = [] for updated_row in updated_details: updated.append({ "inventory_id": updated_row["inventory_id"], "cve": updated_row["cve"] }) if not updated: # sysid/cve/acct combination does not exist return cls.format_exception( 'inventory_id/cve must exist and inventory_id must be visible to user', 404) except (IntegrityError, psycopg2IntegrityError, DataError) as value_error: # usually means bad-status-id LOGGER.error(str(value_error)) DB.rollback() return cls.format_exception( f'status_id={list(status_to_cves_map.keys())} is invalid', 400) except ValueError as value_error: LOGGER.exception('Error during setting status (ValueError):') DB.rollback() return cls.format_exception( f'status_text or other key value is invalid ({value_error})', 400) return {"updated": updated}