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 _full_query(query_args): # pylint: disable=singleton-comparison return ( SystemVulnerabilities.select( CveMetadata.cve.alias('cve_name'), CveMetadata.cvss3_score, CveMetadata.cvss2_score, CveImpact.name.alias('impact'), CveMetadata.public_date, CveMetadata.description.alias('cve_description'), fn.COALESCE(CveAccountData.status_id, 0).alias('cve_status_id'), Status.id.alias('status_id'), Status.name.alias('status_name'), SystemVulnerabilities.status_text.alias('status_text'), SystemVulnerabilities.when_mitigated, CveAccountData.status_text.alias('cve_status_text'), fn.COALESCE(CveAccountData.business_risk_id, 0).alias('business_risk_id'), CveAccountData.business_risk_text.alias('business_risk_text'), fn.COALESCE(BusinessRisk.name, DEFAULT_BUSINESS_RISK).alias('business_risk'), InsightsRule.name.alias('rule_id'), InsightsRule.description_text, InsightsRule.summary_text, InsightsRule.generic_text, InsightsRule.reboot_required, InsightsRule.playbook_count, InsightsRule.change_risk, InsightsRule.kbase_node_id, InsightsRule.active.alias('rule_active')).join( SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id)). join(CveMetadata, on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join( CveImpact, on=(CveMetadata.impact_id == CveImpact.id)).join( Status, on=(SystemVulnerabilities.status_id == Status.id)). join(RHAccount, on=(SystemPlatform.rh_account_id == RHAccount.id)).join( CveAccountData, JOIN.LEFT_OUTER, on=((CveAccountData.cve_id == CveMetadata.id) & (CveAccountData.rh_account_id == RHAccount.id))). join(BusinessRisk, JOIN.LEFT_OUTER, on=(CveAccountData.business_risk_id == BusinessRisk.id)).join( InsightsRule, JOIN.LEFT_OUTER, on=(InsightsRule.id == SystemVulnerabilities.rule_id)). where((SystemVulnerabilities.mitigation_reason.is_null(True)) | (InsightsRule.active == False)). where((SystemVulnerabilities.when_mitigated.is_null(True)) | (InsightsRule.active == True)).where( RHAccount.name == query_args['rh_account_number']).where( SystemPlatform.inventory_id == query_args['inventory_id']).where( SystemPlatform.opt_out == False) # noqa: E712 .where(SystemPlatform.when_deleted.is_null(True)))
def handle_patch(cls, **kwargs): """Update the 'status' field for a system/cve combination""" # pylint: disable=singleton-comparison data = kwargs['data'] try: in_inventory_id_list, status_to_cves_map, status_text_to_cves_map = cls._prepare_data(data) rh_account = get_or_create_account() systems = (SystemPlatform.select(SystemPlatform.id) .where((SystemPlatform.rh_account_id == rh_account[0].id) & (SystemPlatform.opt_out == False) & (SystemPlatform.stale == False) & (SystemPlatform.when_deleted.is_null(True)))) 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(cls._build_update_condition(rh_account[0].id, systems, status_cve_list)) .returning(SystemVulnerabilities.id)) rows_modified.update([row.id for row in status_id_update]) for status_text, status_cve_list in status_text_to_cves_map.items(): status_text_update = (SystemVulnerabilities.update(status_text=status_text) .where(cls._build_update_condition(rh_account[0].id, systems, status_cve_list)) .returning(SystemVulnerabilities.id)) rows_modified.update([row.id for row in status_text_update]) if rows_modified: RHAccount.update(last_status_change=datetime.now(timezone.utc)).where(RHAccount.id == rh_account[0].id).execute() 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)) & (SystemVulnerabilities.rh_account_id == rh_account[0].id)) .dicts()) updated_details = cyndi_join(updated_details) 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}
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, status_to_cves_map, status_text_to_cves_map = cls._prepare_data(data) try: systems = (SystemPlatform.select(SystemPlatform.id) .join(RHAccount, on=(SystemPlatform.rh_account_id == RHAccount.id)) .where(RHAccount.name == connexion.context['user'])) # 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) .from_(systems) .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]) for status_text, status_cve_list in status_text_to_cves_map.items(): status_text_update = (SystemVulnerabilities.update(status_text=status_text) .from_(systems) .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) 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: DB.rollback() return cls.format_exception(str(value_error), 500) return {"updated": updated}
def _full_query(rh_account_id, synopsis): # pylint: disable=singleton-comparison selectables = [ fn.COALESCE(CveAccountData.status_id, 0).alias('cve_status_id'), SystemPlatform.inventory_id, SystemPlatform.display_name, SystemPlatform.last_evaluation, SystemPlatform.advisor_evaluated.alias('rules_evaluation'), SystemPlatform.last_upload, SystemPlatform.stale_timestamp, SystemPlatform.stale_warning_timestamp, SystemPlatform.culled_timestamp, Status.id.alias('status_id'), Status.name.alias('status_name'), SystemVulnerabilities.status_text.alias('status_text'), SystemVulnerabilities.mitigation_reason, SystemVulnerabilities.when_mitigated, InsightsRule.name.alias('rule_id'), InventoryHosts.tags, InventoryHosts.updated, InventoryHosts.insights_id, OS_INFO_QUERY.alias('os'), fn.COALESCE(SystemVulnerabilities.advisory_available, True).alias('advisory_available'), fn.COALESCE( SystemVulnerabilities.remediation_type_id, remediation.PLAYBOOK.value).alias('remediation_type_id'), ] return (SystemVulnerabilities.select(*selectables).join( SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id)).join( Status, on=(SystemVulnerabilities.status_id == Status.id)).join( CveMetadata, on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join( CveAccountData, JOIN.LEFT_OUTER, on=((CveAccountData.rh_account_id == rh_account_id) & (CveMetadata.id == CveAccountData.cve_id))).join( InsightsRule, on=(InsightsRule.id == SystemVulnerabilities.rule_id)). where(CveMetadata.cve == synopsis).where( SystemVulnerabilities.rh_account_id == rh_account_id). where((SystemVulnerabilities.mitigation_reason.is_null(False)) & (InsightsRule.active == True)).where( (SystemPlatform.opt_out == False) & (SystemPlatform.stale == False) & (SystemPlatform.when_deleted.is_null(True)) & (fn.COALESCE(SystemPlatform.host_type, 'null') != HostType.EDGE)))
def __init__(self, synopsis, list_args, query_args, filter_args, parsed_args, uri): # pylint: disable=singleton-comparison query = ( SystemVulnerabilities.select( CveAccountData.status_id.alias('cve_status_id'), SystemPlatform.inventory_id, SystemPlatform.display_name, SystemPlatform.last_evaluation, SystemPlatform.advisor_evaluated.alias('rules_evaluation'), Status.id.alias('status_id'), Status.name.alias('status_name'), SystemVulnerabilities.status_text.alias('status_text'), SystemVulnerabilities.rule_id).join( SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id )).join( Status, on=(SystemVulnerabilities.status_id == Status.id)). join(CveMetadata, on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join( RHAccount, on=(SystemPlatform.rh_account_id == RHAccount.id)).join( CveAccountData, on=((RHAccount.id == CveAccountData.rh_account_id) & (CveMetadata.id == CveAccountData.cve_id))). where(CveMetadata.cve == synopsis).where( RHAccount.name == query_args['rh_account_number']).where( SystemVulnerabilities.when_mitigated.is_null(True)).where( SystemPlatform.opt_out == False) # noqa: E712 ) if 'status_id' in filter_args and filter_args['status_id']: query = query.where(Status.id << filter_args['status_id']) query = query.dicts() sortable_columns = { 'id': SystemPlatform.id, 'inventory_id': SystemPlatform.inventory_id, 'last_evaluation': SystemPlatform.last_evaluation, 'rules_evaluation': SystemPlatform.advisor_evaluated, 'status_id': Status.id, 'status': Status.id } default_sort_columns = ['id'] filterable_columns = { 'inventory_id': SystemPlatform.inventory_id, 'display_name': SystemPlatform.display_name } super(AffectedSystemsView, self).__init__(query, sortable_columns, default_sort_columns, filterable_columns, list_args, parsed_args, uri)
def _update_divergent_status_count(in_cve_list, rh_account_id): """Update cached count how many systems-CVE pairs have different status than CVE-level status is""" cve_ids = CveMetadata.select( CveMetadata.id).where(CveMetadata.cve << in_cve_list) # pylint: disable=singleton-comparison div_counts = ( SystemVulnerabilities.select( SystemVulnerabilities.cve_id, fn.Count(SystemVulnerabilities.id). alias('systems_status_divergent')).join( SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id)). join(InsightsRule, JOIN.LEFT_OUTER, on=(InsightsRule.id == SystemVulnerabilities.rule_id)).where( SystemPlatform.rh_account_id == rh_account_id).where( (SystemPlatform.opt_out == False) # noqa: E712 & (SystemPlatform.stale == False) # noqa: E712 & (SystemPlatform.when_deleted.is_null(True))).where( SystemVulnerabilities.cve_id << cve_ids). where((SystemVulnerabilities.mitigation_reason.is_null(True)) | (InsightsRule.active == False)).where( (SystemVulnerabilities.when_mitigated.is_null(True)) | (InsightsRule.active == True)). where(SystemVulnerabilities.status_id != fn.COALESCE( (CveAccountData.select(CveAccountData.status_id).where( CveAccountData.rh_account_id == rh_account_id).where( CveAccountData.cve_id == SystemVulnerabilities.cve_id) ), 0)).group_by(SystemVulnerabilities.cve_id)) div_counts = cyndi_query(div_counts) divergent_systems = {} for cve_id in cve_ids: divergent_systems[cve_id] = 0 for div_count in div_counts: divergent_systems[ div_count.cve_id] = div_count.systems_status_divergent if divergent_systems: values_to_update = ValuesList(list(divergent_systems.items()), columns=('cve_id', 'systems_status_divergent')) query = (CveAccountData.update( systems_status_divergent=values_to_update.c. systems_status_divergent).from_(values_to_update).where( CveAccountData.cve_id == values_to_update.c.cve_id).where( CveAccountData.rh_account_id == rh_account_id)) query.execute()
def _full_query(synopsis, query_args): # pylint: disable=singleton-comparison selectables = [ fn.COALESCE(CveAccountData.status_id, 0).alias('cve_status_id'), SystemPlatform.inventory_id, SystemPlatform.display_name, SystemPlatform.last_evaluation, SystemPlatform.advisor_evaluated.alias('rules_evaluation'), Status.id.alias('status_id'), Status.name.alias('status_name'), SystemVulnerabilities.status_text.alias('status_text'), SystemVulnerabilities.rule_hit_details, SystemVulnerabilities.when_mitigated, InsightsRule.name.alias('rule_id'), InsightsRule.active.alias('rule_active'), InsightsRule.description_text, InsightsRule.reason_text, InsightsRule.resolution_text, InsightsRule.kbase_node_id, InsightsRule.more_info_text ] if CYNDI_ENABLED: selectables.append(InventoryHosts.tags) return (SystemVulnerabilities.select(*selectables).join( SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id)).join( Status, on=(SystemVulnerabilities.status_id == Status.id) ).join(CveMetadata, on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join( RHAccount, on=(SystemPlatform.rh_account_id == RHAccount.id)).join( CveAccountData, JOIN.LEFT_OUTER, on=((RHAccount.id == CveAccountData.rh_account_id) & (CveMetadata.id == CveAccountData.cve_id))). join( InsightsRule, JOIN.LEFT_OUTER, on=(InsightsRule.id == SystemVulnerabilities.rule_id )).where(CveMetadata.cve == synopsis).where( RHAccount.name == query_args['rh_account_number']). where((SystemVulnerabilities.mitigation_reason.is_null(True)) | (InsightsRule.active == False)).where( (SystemVulnerabilities.when_mitigated.is_null(True)) | (InsightsRule.active == True)).where( (SystemPlatform.opt_out == False) # noqa: E712 & (SystemPlatform.stale == False) # noqa: E712 & (SystemPlatform.when_deleted.is_null(True))))
def _id_query(rh_account_id, synopsis): # pylint: disable=singleton-comparison return (SystemVulnerabilities.select( SystemPlatform.inventory_id, InsightsRule.name.alias('rule_id'), SystemVulnerabilities.status_id.alias('status_id'), SystemVulnerabilities.status_text.alias('status_text'), ).join(SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id)).join( CveMetadata, on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join( InsightsRule, on=(InsightsRule.id == SystemVulnerabilities.rule_id)). where(CveMetadata.cve == synopsis).where( SystemVulnerabilities.rh_account_id == rh_account_id). where((SystemVulnerabilities.mitigation_reason.is_null(False)) & (InsightsRule.active == True)).where( (SystemPlatform.opt_out == False) & (SystemPlatform.stale == False) & (SystemPlatform.when_deleted.is_null(True))))
def _id_query(synopsis, query_args): # pylint: disable=singleton-comparison return (SystemVulnerabilities.select(SystemPlatform.inventory_id).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)).join( InsightsRule, JOIN.LEFT_OUTER, on=(InsightsRule.id == SystemVulnerabilities.rule_id)). where(CveMetadata.cve == synopsis).where( RHAccount.name == query_args['rh_account_number']). where((SystemVulnerabilities.mitigation_reason.is_null(True)) | (InsightsRule.active == False)).where( (SystemVulnerabilities.when_mitigated.is_null(True)) | (InsightsRule.active == True)).where( (SystemPlatform.opt_out == False) # noqa: E712 & (SystemPlatform.stale == False) # noqa: E712 & (SystemPlatform.when_deleted.is_null(True))))
def __init__(self, list_args, query_args, filter_args, parsed_args, uri): # pylint: disable=singleton-comparison query = ( SystemVulnerabilities.select( CveMetadata.cve.alias('cve_name'), CveMetadata.cvss3_score, CveMetadata.cvss2_score, CveImpact.name.alias('impact'), CveMetadata.public_date, CveMetadata.description.alias('cve_description'), fn.COALESCE(CveAccountData.status_id, 0).alias('cve_status_id'), Status.id.alias('status_id'), Status.name.alias('status_name'), SystemVulnerabilities.status_text.alias('status_text'), fn.COALESCE(CveAccountData.business_risk_id, 0).alias('business_risk_id'), CveAccountData.business_risk_text.alias('business_risk_text'), fn.COALESCE(BusinessRisk.name, DEFAULT_BUSINESS_RISK).alias('business_risk'), InsightsRule.name.alias('rule_id'), InsightsRule.description_text, InsightsRule.summary_text, InsightsRule.generic_text, InsightsRule.reboot_required, InsightsRule.playbook_count, InsightsRule.change_risk, InsightsRule.kbase_node_id, InsightsRule.active.alias('rule_active')).join( SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id)). join(CveMetadata, on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join( CveImpact, on=(CveMetadata.impact_id == CveImpact.id)).join( Status, on=(SystemVulnerabilities.status_id == Status.id)). join(RHAccount, on=(SystemPlatform.rh_account_id == RHAccount.id)).join( CveAccountData, JOIN.LEFT_OUTER, on=((CveAccountData.cve_id == CveMetadata.id) & (CveAccountData.rh_account_id == RHAccount.id))). join(BusinessRisk, JOIN.LEFT_OUTER, on=(CveAccountData.business_risk_id == BusinessRisk.id)).join( InsightsRule, JOIN.LEFT_OUTER, on=(InsightsRule.id == SystemVulnerabilities.rule_id)). where((SystemVulnerabilities.when_mitigated.is_null(True)) | (InsightsRule.active == True)).where( RHAccount.name == query_args['rh_account_number']).where( SystemPlatform.inventory_id == query_args['inventory_id']).where( SystemPlatform.opt_out == False) # noqa: E712 ) if 'cvss_from' in filter_args and filter_args['cvss_from']: query = query.where( fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score) >= filter_args['cvss_from']) if 'cvss_to' in filter_args and filter_args['cvss_to']: query = query.where( fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score) <= filter_args['cvss_to']) if 'public_from' in filter_args and filter_args['public_from']: query = query.where( CveMetadata.public_date >= filter_args['public_from']) if 'public_to' in filter_args and filter_args['public_to']: query = query.where( CveMetadata.public_date <= filter_args['public_to']) if 'status_id' in filter_args and filter_args['status_id']: query = query.where(Status.id << filter_args['status_id']) if 'impact' in filter_args and filter_args['impact']: query = query.where(CveMetadata.impact_id << filter_args['impact']) if 'business_risk_id' in filter_args and filter_args[ 'business_risk_id']: query = query.where( fn.COALESCE(CveAccountData.business_risk_id, 0) << filter_args['business_risk_id']) if 'stale' in filter_args and filter_args['stale']: query = query.where(SystemPlatform.stale == filter_args['stale']) else: query = query.where(SystemPlatform.stale == False) # noqa: E712 if 'security_rule' in filter_args and filter_args[ 'security_rule'] is not None: if filter_args['security_rule']: query = query.where(InsightsRule.active == True) # noqa: E712 else: query = query.where( (InsightsRule.active == False) | (InsightsRule.active == None)) # noqa: E712 query = query.dicts() sortable_columns = { 'id': CveMetadata.id, 'cve': CVE_SYNOPSIS_SORT, 'synopsis': CVE_SYNOPSIS_SORT, 'public_date': CveMetadata.public_date, # This assumes we only show one score, and that cvss3 wins over cvss2 'cvss_score': Case(None, ((CveMetadata.cvss3_score.is_null(True), CveMetadata.cvss2_score),), \ CveMetadata.cvss3_score), "cvss3_score": CveMetadata.cvss3_score, "cvss2_score": CveMetadata.cvss2_score, 'impact_id': CveMetadata.impact_id, 'impact': CveMetadata.impact_id, 'status_id': Status.id, 'status': Status.id, 'business_risk_id': SQL('business_risk_id'), 'business_risk': SQL('business_risk_id'), } default_sort_columns = ['id'] filterable_columns = { 'cve': CveMetadata.cve, 'description': CveMetadata.description, 'status': Status.name } super(SystemCvesView, self).__init__(query, sortable_columns, default_sort_columns, filterable_columns, list_args, parsed_args, uri)
def handle_get(cls, **kwargs): # pylint: disable=too-many-statements,too-many-branches """Gets affected CVEs for a system""" inventory_id = kwargs['inventory_id'] args_desc = [ { 'arg_name': 'cvss_from', 'convert_func': None }, { 'arg_name': 'cvss_to', 'convert_func': None }, { 'arg_name': 'public_from', 'convert_func': dateutil.parser.parse }, { 'arg_name': 'public_to', 'convert_func': dateutil.parser.parse }, { 'arg_name': 'impact', 'convert_func': parse_int_list }, { 'arg_name': 'status_id', 'convert_func': parse_int_list }, { 'arg_name': 'business_risk_id', 'convert_func': parse_int_list }, { 'arg_name': 'rule_presence', 'convert_func': unique_bool_list }, { 'arg_name': 'show_advisories', 'convert_func': None }, { 'arg_name': 'advisory', 'convert_func': None }, { 'arg_name': 'rule_key', 'convert_func': None }, { 'arg_name': 'known_exploit', 'convert_func': unique_bool_list }, { 'arg_name': 'first_reported_from', 'convert_func': dateutil.parser.parse }, { 'arg_name': 'first_reported_to', 'convert_func': dateutil.parser.parse }, { 'arg_name': 'advisory_available', 'convert_func': unique_bool_list }, { 'arg_name': 'remediation', 'convert_func': parse_int_list }, ] args = cls._parse_arguments(kwargs, args_desc) list_arguments = cls._parse_list_arguments(kwargs) # check if advisory parameter comes with show_advisories parameter if 'advisory' in args and args['advisory'] and ( 'show_advisories' not in args or not args['show_advisories']): raise ApplicationException( "Advisory parameter shouldn't be used without show_advisories parameter", 400) # check if system was evaluated try: system = (SystemPlatform.select( SystemPlatform.last_evaluation, SystemPlatform.advisor_evaluated, SystemPlatform.opt_out).join( RHAccount, on=(SystemPlatform.rh_account_id == RHAccount.id)). where(SystemPlatform.inventory_id == inventory_id).where( RHAccount.name == connexion.context['user']).where( SystemPlatform.when_deleted.is_null(True)).get()) except DoesNotExist as exc: raise ApplicationException( 'inventory_id must exist and inventory_id must be visible to user', 404) from exc if system.last_evaluation is None and system.advisor_evaluated is None: raise ApplicationException( 'inventory_id exists but is not evaluated', 404) rh_account_id, _, _ = get_account_data(connexion.context['user']) cve_advisories = {} patch_access = None if 'show_advisories' in args and args[ 'show_advisories'] and 'advisory' in args and args['advisory']: raw_cve_names = (SystemVulnerabilities.select( CveMetadata.cve.alias('cve_name'), ).join( SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id) ).join( CveMetadata, on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join( CveAccountData, JOIN.LEFT_OUTER, on=((CveAccountData.cve_id == CveMetadata.id) & (CveAccountData.rh_account_id == rh_account_id)) ).where( SystemVulnerabilities.rh_account_id == rh_account_id ).where( SystemPlatform.inventory_id == inventory_id)).dicts() cve_names = [cve['cve_name'] for cve in raw_cve_names] # get advisory data from PATCH cve_advisories, patch_access = cls._get_advisories_per_cve( cve_names, inventory_id, adv=args['advisory']) # filter advisories by advisory name cve_advisories = filter_by_advisory(args['advisory'], cve_advisories) cves_view = SystemCvesView(rh_account_id, list_arguments, {'inventory_id': inventory_id}, args, args, connexion.request.path, cls._ids_only, cve_advisories) cves = list(cves_view) elif 'show_advisories' in args and args['show_advisories'] and ( 'advisory' not in args or not args['advisory']): cves_view = SystemCvesView(rh_account_id, list_arguments, {'inventory_id': inventory_id}, args, args, connexion.request.path, cls._ids_only) cves = list(cves_view) cve_names = [ cve['cve_name'] if not cls._ids_only else cve['id'] for cve in cves ] # get advisory data from PATCH cve_advisories, patch_access = cls._get_advisories_per_cve( cve_names, inventory_id) else: cves_view = SystemCvesView(rh_account_id, list_arguments, {'inventory_id': inventory_id}, args, args, connexion.request.path, cls._ids_only) cves = list(cves_view) response = {} result = [] if not cls._ids_only: for cve in cves: if 'show_advisories' in args and args['show_advisories']: record = cls._build_attributes( cve, cve_advisories.get(cve['cve_name'], [])) result.append({ 'type': 'cve', 'id': cve['cve_name'], 'attributes': record }) else: record = cls._build_attributes(cve) result.append({ 'type': 'cve', 'id': cve['cve_name'], 'attributes': record }) else: result = cves response['meta'] = cves_view.get_metadata() response['meta'][ 'patch_access'] = patch_access.status if patch_access else None response['links'] = cves_view.get_pagination_links() response['data'] = cls._format_data(list_arguments["data_format"], result) return response
def handle_get(cls, **kwargs): # pylint: disable=singleton-comparison, too-many-branches, too-many-statements retval = { 'system_count': 0, 'cves_total': 0, 'cves_by_severity': { '0to3.9': { 'percentage': 0, 'count': 0, 'known_exploit_count': 0 }, '4to7.9': { 'percentage': 0, 'count': 0, 'known_exploit_count': 0 }, '8to10': { 'percentage': 0, 'count': 0, 'known_exploit_count': 0 }, 'na': { 'percentage': 0, 'count': 0, 'known_exploit_count': 0 } }, 'recent_cves': { 'last7days': 0, 'last30days': 0, 'last90days': 0 }, 'rules_total': 0, 'rules_by_severity': { 1: { 'rule_count': 0, 'systems_affected': 0 }, 2: { 'rule_count': 0, 'systems_affected': 0 }, 3: { 'rule_count': 0, 'systems_affected': 0 }, 4: { 'rule_count': 0, 'systems_affected': 0 }, }, 'top_cves': [], 'top_rules': [], } rh_account, cve_cache_from, cve_cache_keepalive = get_account_data( connexion.context['user']) if rh_account is None: return retval retval['system_count'] = get_system_count(rh_account) if retval['system_count'] == 0: return retval # API using cache, set keepalive for account to enable maintaining cache update_cve_cache_keepalive(rh_account, cve_cache_keepalive) # Use cache if not disabled + cache exists if not DISABLE_ACCOUNT_CACHE and cve_cache_from: count_query = (CveAccountCache.select( CveAccountCache.cve_id.alias("cve_id_"), CveAccountCache.systems_affected.alias("systems_affected_")). where(CveAccountCache.rh_account_id == rh_account)) else: count_query = ( SystemVulnerabilities.select( SystemVulnerabilities.cve_id.alias("cve_id_"), fn.Count( SystemVulnerabilities.id).alias("systems_affected_")). join(SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id) & (SystemPlatform.rh_account_id == rh_account) & (SystemPlatform.opt_out == False) & (SystemPlatform.stale == False) & (SystemPlatform.when_deleted.is_null(True))).where( SystemVulnerabilities.rh_account_id == rh_account). where((SystemVulnerabilities.mitigation_reason.is_null(True)) | (SystemVulnerabilities.rule_id << InsightsRule.select( InsightsRule.id).where((InsightsRule.active == False) & (~InsightsRule.rule_only)))) .where((SystemVulnerabilities.when_mitigated.is_null(True)) | (SystemVulnerabilities.rule_id << InsightsRule.select( InsightsRule.id).where( (InsightsRule.active == True) & (~InsightsRule.rule_only)))).group_by( SystemVulnerabilities.cve_id)) count_query = cyndi_join(count_query) cve_query = (CveMetadata.select( CveMetadata.id.alias("cve_id"), fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score).alias('cvss_score'), CveMetadata.public_date, CveMetadata.exploits).join( count_query, JOIN.INNER, on=(CveMetadata.id == count_query.c.cve_id_)).dicts()) cve_data = [(cve["cvss_score"], cve["public_date"], cve["exploits"]) for cve in cve_query] cves_total = len(cve_data) if cves_total == 0: return retval retval['cves_total'] = cves_total today = datetime.now(timezone.utc).replace( hour=0, minute=0, second=0, microsecond=0) # offset-aware last7 = today - timedelta(days=7) last30 = today - timedelta(days=30) last90 = today - timedelta(days=90) for cvss_score, public_date, exploits in cve_data: if cvss_score is None: retval["cves_by_severity"]["na"]["count"] += 1 if exploits: retval["cves_by_severity"]["na"][ "known_exploit_count"] += 1 elif cvss_score < 4: retval["cves_by_severity"]["0to3.9"]["count"] += 1 if exploits: retval["cves_by_severity"]["0to3.9"][ "known_exploit_count"] += 1 elif 4 <= cvss_score < 8: retval["cves_by_severity"]["4to7.9"]["count"] += 1 if exploits: retval["cves_by_severity"]["4to7.9"][ "known_exploit_count"] += 1 elif cvss_score >= 8: retval["cves_by_severity"]["8to10"]["count"] += 1 if exploits: retval["cves_by_severity"]["8to10"][ "known_exploit_count"] += 1 if public_date is not None: if public_date >= last7: retval["recent_cves"]["last7days"] += 1 if public_date >= last30: retval["recent_cves"]["last30days"] += 1 if public_date >= last90: retval["recent_cves"]["last90days"] += 1 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] # 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, count_query, 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, count_query, 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, count_query, limit=cves_limit - (cves_count + next_cves_count)) cls._build_top_cves(last_tier_top, retval) rules_breakdown = (SystemVulnerabilities.select( fn.COUNT(fn.Distinct(InsightsRule.id)).alias('rule_count'), InsightsRule.rule_impact.alias('severity'), fn.COUNT(fn.Distinct( SystemVulnerabilities.system_id)).alias('systems_affected') ).join( InsightsRule, on=(SystemVulnerabilities.rule_id == InsightsRule.id) ).join( SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id) & (SystemPlatform.rh_account_id == rh_account) & (SystemPlatform.opt_out == False) & (SystemPlatform.stale == False) & (SystemPlatform.when_deleted.is_null(True)) & (SystemPlatform.last_evaluation.is_null(False) | SystemPlatform.advisor_evaluated.is_null(False))).where( SystemVulnerabilities.rh_account_id == rh_account).where( (SystemVulnerabilities.mitigation_reason.is_null(True)) & (SystemVulnerabilities.rule_id << InsightsRule.select( InsightsRule.id).where( (InsightsRule.active == True) & (~InsightsRule.rule_only)))).group_by( InsightsRule.rule_impact).dicts()) rules_breakdown = cyndi_join(rules_breakdown) for section in rules_breakdown: retval['rules_by_severity'][ section['severity']]['rule_count'] = section['rule_count'] retval['rules_by_severity'][section['severity']][ 'systems_affected'] = section['systems_affected'] retval['rules_total'] = sum( [item['rule_count'] for item in rules_breakdown]) top_rules = (SystemVulnerabilities.select( InsightsRule.name.alias('rule_id'), InsightsRule.description_text.alias('name'), InsightsRule.rule_impact.alias('severity'), InsightsRule.summary_text.alias('description'), fn.COUNT(fn.Distinct( SystemVulnerabilities.system_id)).alias('systems_affected'), fn.ARRAY_AGG(fn.Distinct(CveMetadata.cve)).alias('associated_cves') ).join( InsightsRule, on=(SystemVulnerabilities.rule_id == InsightsRule.id)).join( CveRuleMapping, on=(InsightsRule.id == CveRuleMapping.rule_id)).join( CveMetadata, on=(CveRuleMapping.cve_id == CveMetadata.id) ).join( SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id) & (SystemPlatform.rh_account_id == rh_account) & (SystemPlatform.opt_out == False) & (SystemPlatform.stale == False) & (SystemPlatform.when_deleted.is_null(True)) & (SystemPlatform.last_evaluation.is_null(False) | SystemPlatform.advisor_evaluated.is_null(False))).where( SystemVulnerabilities.rh_account_id == rh_account). where(( SystemVulnerabilities.mitigation_reason.is_null(True)) & (SystemVulnerabilities.rule_id << InsightsRule.select(InsightsRule.id).where( (InsightsRule.active == True) & (~InsightsRule.rule_only)))).group_by( InsightsRule.name, InsightsRule.description_text, InsightsRule.rule_impact, InsightsRule.summary_text).order_by( InsightsRule.rule_impact.desc(), SQL('systems_affected desc'), InsightsRule.description_text, InsightsRule.name).limit(3).dicts()) top_rules = cyndi_join(top_rules) for top_rule in top_rules: retval['top_rules'].append(top_rule) return retval
def handle_get(cls, **kwargs): """Gets systems affected by a CVE""" synopsis = kwargs['cve_id'] cls._cve_exists(synopsis) args_desc = [ { 'arg_name': 'status_id', 'convert_func': parse_int_list }, { 'arg_name': 'uuid', 'convert_func': None }, { 'arg_name': 'rule_presence', 'convert_func': unique_bool_list }, { 'arg_name': 'rule_key', 'convert_func': None }, { 'arg_name': 'tags', 'convert_func': parse_tags }, { 'arg_name': 'sap_system', 'convert_func': None }, { 'arg_name': 'show_advisories', 'convert_func': None }, { 'arg_name': 'advisory', 'convert_func': None }, { 'arg_name': 'sap_sids', 'convert_func': None }, { 'arg_name': 'rhel_version', 'convert_func': parse_str_list }, { 'arg_name': 'first_reported_from', 'convert_func': dateutil.parser.parse }, { 'arg_name': 'first_reported_to', 'convert_func': dateutil.parser.parse }, { 'arg_name': 'rule', 'convert_func': None }, { 'arg_name': 'advisory_available', 'convert_func': unique_bool_list }, { 'arg_name': 'remediation', 'convert_func': parse_int_list }, ] args = cls._parse_arguments(kwargs, args_desc) list_arguments = cls._parse_list_arguments(kwargs) # check if advisory parameter comes with show_advisories parameter if 'advisory' in args and args['advisory'] and ( 'show_advisories' not in args or not args['show_advisories']): raise ApplicationException( "Advisory parameter shouldn't be used without show_advisories parameter", 400) rh_account_id, _, _ = get_account_data(connexion.context['user']) system_advisories = {} patch_access = None if 'show_advisories' in args and args[ 'show_advisories'] and 'advisory' in args and args['advisory']: # pylint: disable=singleton-comparison raw_inv_ids = ( SystemVulnerabilities.select(SystemPlatform.inventory_id).join( SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id)). join(CveMetadata, on=(SystemVulnerabilities.cve_id == CveMetadata.id) ).where(CveMetadata.cve == synopsis).where( SystemVulnerabilities.rh_account_id == rh_account_id). where((SystemPlatform.opt_out == False) # noqa: E712 & (SystemPlatform.stale == False) # noqa: E712 & (SystemPlatform.when_deleted.is_null(True)))).dicts() raw_inv_ids = [inv_id['inventory_id'] for inv_id in raw_inv_ids] # Pass advisory argument for filtering advisories list by advisory to prevent redundant patch requests advisories_list = get_advisories_per_cve_from_db(synopsis) if not has_advisory(args['advisory'], advisories_list): advisories_list = [] # get advisory data from PATCH system_advisories, patch_access = cls._get_advisories_per_system( advisories_list, raw_inv_ids) # Filter systems by advisory system_advisories = filter_by_advisory(args['advisory'], system_advisories) asys_view = cls._DB_View(rh_account_id, synopsis, list_arguments, args, args, connexion.request.path, cls._ids_only, system_advisories) systems = list(asys_view) # execute query elif 'show_advisories' in args and args['show_advisories'] and ( 'advisory' not in args or not args['advisory']): asys_view = cls._DB_View(rh_account_id, synopsis, list_arguments, args, args, connexion.request.path, cls._ids_only) systems = list(asys_view) # execute query inv_ids = [sys['inventory_id'] for sys in systems] # get advisory data from PATCH advisories_list = get_advisories_per_cve_from_db(synopsis) system_advisories, patch_access = cls._get_advisories_per_system( advisories_list, inv_ids) else: asys_view = cls._DB_View(rh_account_id, synopsis, list_arguments, args, args, connexion.request.path, cls._ids_only) systems = list(asys_view) # execute query response = {} result = [] if cls._ids_only: result = systems else: for sys in systems: if 'show_advisories' in args and args['show_advisories']: record = cls._build_attributes( sys, system_advisories.get(sys['inventory_id'], [])) result.append({ 'type': 'system', 'id': sys['inventory_id'], 'attributes': record }) else: record = cls._build_attributes(sys) result.append({ 'type': 'system', 'id': sys['inventory_id'], 'attributes': record }) response['meta'] = asys_view.get_metadata() response['meta'][ 'patch_access'] = patch_access.status if patch_access else None response['links'] = asys_view.get_pagination_links() response['data'] = cls._format_data(list_arguments["data_format"], result) return response
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 args_desc = [{ 'arg_name': 'tags', 'convert_func': parse_tags }, { 'arg_name': 'sap_system', 'convert_func': None }, { 'arg_name': 'sap_sids', 'convert_func': None }] args = cls._parse_arguments(kwargs, args_desc) cyndi_reuest = is_cyndi_request(args) filters = [ filter_types.SYSTEM_TAGS, filter_types.SYSTEM_SAP, filter_types.SYSTEM_SAP_SIDS ] rh_account, cve_cache_from, cve_cache_keepalive = get_account_data( connexion.context["user"]) update_cve_cache_keepalive(rh_account, cve_cache_keepalive) if not DISABLE_ACCOUNT_CACHE and not cyndi_reuest and cve_cache_from: query = (CveAccountCache.select( fn.Count( fn.Distinct( Case(CveMetadata.exploits, ((True, CveMetadata.id), )))).alias("exploitable_cves"), fn.Count( fn.Distinct( Case(CveMetadata.impact_id, ((7, CveMetadata.id), )))).alias("critical_cves"), fn.Count( fn.Distinct( Case( CveMetadata.impact_id, ((5, CveMetadata.id), )))).alias("important_cves"), fn.Count( fn.Distinct( Case(None, ((InsightsRule.id.is_null(False), CveMetadata.id), )))).alias("cves_with_rule") ).join(CveMetadata, on=(CveAccountCache.cve_id == CveMetadata.id)).join( CveRuleMapping, JOIN.LEFT_OUTER, on=((CveAccountCache.cve_id == CveRuleMapping.cve_id))). join(InsightsRule, JOIN.LEFT_OUTER, on=((CveRuleMapping.rule_id == InsightsRule.id) & (InsightsRule.active == True) & (~InsightsRule.rule_only))).where( CveAccountCache.rh_account_id == rh_account)) else: query = (SystemVulnerabilities.select( fn.Count( fn.Distinct( Case(CveMetadata.exploits, ((True, CveMetadata.id), )))).alias("exploitable_cves"), fn.Count( fn.Distinct( Case(CveMetadata.impact_id, ((7, CveMetadata.id), )))).alias("critical_cves"), fn.Count( fn.Distinct( Case( CveMetadata.impact_id, ((5, CveMetadata.id), )))).alias("important_cves"), fn.Count( fn.Distinct( Case(None, ((InsightsRule.id.is_null(False), CveMetadata.id), )))).alias("cves_with_rule") ).join( CveMetadata, on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join( CveRuleMapping, JOIN.LEFT_OUTER, on=(( SystemVulnerabilities.cve_id == CveRuleMapping.cve_id)) ).join( InsightsRule, JOIN.LEFT_OUTER, on=((CveRuleMapping.rule_id == InsightsRule.id) & (InsightsRule.active == True) & (~InsightsRule.rule_only)) ).join( SystemPlatform, on=((SystemVulnerabilities.system_id == SystemPlatform.id) & (SystemPlatform.rh_account_id == rh_account) & (SystemPlatform.opt_out == False) & (SystemPlatform.stale == False) & (SystemPlatform.when_deleted.is_null(True)) & (fn.COALESCE(SystemPlatform.host_type, 'null') != HostType.EDGE)) ).where( SystemVulnerabilities.rh_account_id == rh_account).where( SystemVulnerabilities.when_mitigated.is_null(True))) if cyndi_reuest: query = cyndi_join(query) query = apply_filters(query, args, filters, {}) res = query.first() return { "exploitable_cves": res.exploitable_cves, "critical_cves": res.critical_cves, "important_cves": res.important_cves, "cves_with_rule": res.cves_with_rule }
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, too-many-branches, too-many-statements retval = { 'cves_total': 0, 'cves_by_severity': { '0to3.9': { 'percentage': 0, 'count': 0, 'known_exploits': 0 }, '4to7.9': { 'percentage': 0, 'count': 0, 'known_exploits': 0 }, '8to10': { 'percentage': 0, 'count': 0, 'known_exploits': 0 }, 'na': { 'percentage': 0, 'count': 0, 'known_exploits': 0 } }, 'exploited_cves_count': 0, 'system_count': 0, 'recent_cves': { 'last7days': 0, 'last30days': 0, 'last90days': 0 }, 'recent_rules': [], 'rules_cves_total': 0, } args_desc = [ {'arg_name': 'tags', 'convert_func': parse_tags}, {'arg_name': 'sap_system', 'convert_func': None}, {'arg_name': 'sap_sids', 'convert_func': None} ] args = cls._parse_arguments(kwargs, args_desc) cyndi_request = is_cyndi_request(args) rh_account, cve_cache_from, cve_cache_keepalive = get_account_data(connexion.context['user']) retval['system_count'] = get_system_count(rh_account, True, [filter_types.SYSTEM_TAGS, filter_types.SYSTEM_SAP, filter_types.SYSTEM_SAP_SIDS], args) # API using cache, set keepalive for account to enable maintaining cache update_cve_cache_keepalive(rh_account, cve_cache_keepalive) # Use cache if not disabled + systems are not filtered + cache exists if not DISABLE_ACCOUNT_CACHE and not cyndi_request and cve_cache_from: active_cves_subquery = (CveAccountCache .select(CveAccountCache.cve_id.alias("cve_id_")) .where(CveAccountCache.rh_account_id == rh_account)) else: active_cves_subquery = (SystemVulnerabilities .select(fn.Distinct(SystemVulnerabilities.cve_id).alias("cve_id_")) .join(SystemPlatform, on=((SystemVulnerabilities.system_id == SystemPlatform.id) & (SystemPlatform.rh_account_id == rh_account) & (SystemPlatform.opt_out == False) & # noqa: E712 (SystemPlatform.stale == False) & # noqa: E712 (SystemPlatform.when_deleted.is_null(True)) & (fn.COALESCE(SystemPlatform.host_type, 'null') != HostType.EDGE))) .where(SystemVulnerabilities.rh_account_id == rh_account) .where((SystemVulnerabilities.mitigation_reason.is_null(True)) | (SystemVulnerabilities.rule_id << InsightsRule.select(InsightsRule.id) .where((InsightsRule.active == False) & (~InsightsRule.rule_only)))) .where((SystemVulnerabilities.when_mitigated.is_null(True)) | (SystemVulnerabilities.rule_id << InsightsRule.select(InsightsRule.id) .where((InsightsRule.active == True) & (~InsightsRule.rule_only))))) if cyndi_request: active_cves_subquery = cyndi_join(active_cves_subquery) active_cves_subquery = apply_filters(active_cves_subquery, args, [filter_types.SYSTEM_TAGS, filter_types.SYSTEM_SAP, filter_types.SYSTEM_SAP_SIDS], {}) query = (CveMetadata .select(CveMetadata.cve, fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score).alias('cvss_score'), CveMetadata.public_date, CveMetadata.id, CveMetadata.exploits) .join(active_cves_subquery, on=(CveMetadata.id == active_cves_subquery.c.cve_id_)) .dicts()) cve_data = [(cve["cvss_score"], cve["public_date"], cve["exploits"]) for cve in query] retval["cves_total"] = len(cve_data) retval["exploited_cves_count"] = len([row[2] for row in cve_data if row[2] is True]) today = datetime.now(timezone.utc).replace(hour=0, minute=0, second=0, microsecond=0) # offset-aware last7 = today - timedelta(days=7) last30 = today - timedelta(days=30) last90 = today - timedelta(days=90) rules_date = today - timedelta(days=CFG.dashboard_rules_age) for cvss_score, public_date, exploit in cve_data: if cvss_score is None: retval["cves_by_severity"]["na"]["count"] += 1 if exploit: retval["cves_by_severity"]["na"]["known_exploits"] += 1 elif cvss_score < 4: retval["cves_by_severity"]["0to3.9"]["count"] += 1 if exploit: retval["cves_by_severity"]["0to3.9"]["known_exploits"] += 1 elif 4 <= cvss_score < 8: retval["cves_by_severity"]["4to7.9"]["count"] += 1 if exploit: retval["cves_by_severity"]["4to7.9"]["known_exploits"] += 1 elif cvss_score >= 8: retval["cves_by_severity"]["8to10"]["count"] += 1 if exploit: retval["cves_by_severity"]["8to10"]["known_exploits"] += 1 if public_date is not None: if public_date >= last7: retval["recent_cves"]["last7days"] += 1 if public_date >= last30: retval["recent_cves"]["last30days"] += 1 if public_date >= last90: retval["recent_cves"]["last90days"] += 1 rounded_percentage = round_to_100_percent([v['count'] for v in retval['cves_by_severity'].values()]) for indx, keys in enumerate(retval['cves_by_severity']): retval['cves_by_severity'][keys]['percentage'] = rounded_percentage[indx] if not DISABLE_ACCOUNT_CACHE and not cyndi_request and cve_cache_from: rules_breakdown = (CveAccountCache.select(fn.COUNT(fn.Distinct(CveAccountCache.cve_id)).alias("rules_cves_count")) .join(CveRuleMapping, on=(CveAccountCache.cve_id == CveRuleMapping.cve_id)) .join(InsightsRule, on=((CveRuleMapping.rule_id == InsightsRule.id) & (InsightsRule.active == True) & (~InsightsRule.rule_only))) .where(CveAccountCache.rh_account_id == rh_account)) else: rules_breakdown = (SystemVulnerabilities.select(fn.COUNT(fn.Distinct(SystemVulnerabilities.cve_id)).alias('rules_cves_count')) .join(CveRuleMapping, on=(SystemVulnerabilities.cve_id == CveRuleMapping.cve_id)) .join(InsightsRule, on=((CveRuleMapping.rule_id == InsightsRule.id) & (InsightsRule.active == True) & (~InsightsRule.rule_only))) .join(SystemPlatform, on=((SystemVulnerabilities.system_id == SystemPlatform.id) & (SystemPlatform.rh_account_id == rh_account) & (SystemPlatform.when_deleted.is_null(True)) & (SystemPlatform.stale == False) & (SystemPlatform.opt_out == False) & # noqa: E712 (SystemPlatform.last_evaluation.is_null(False) | SystemPlatform.advisor_evaluated.is_null(False)) & (fn.COALESCE(SystemPlatform.host_type, 'null') != HostType.EDGE))) .where(SystemVulnerabilities.rh_account_id == rh_account) .where(SystemVulnerabilities.mitigation_reason.is_null(True)) ) if cyndi_request: rules_breakdown = cyndi_join(rules_breakdown) rules_breakdown = apply_filters(rules_breakdown, args, [filter_types.SYSTEM_TAGS, filter_types.SYSTEM_SAP, filter_types.SYSTEM_SAP_SIDS], {}) rules_breakdown = rules_breakdown.first() retval['rules_cves_total'] = rules_breakdown.rules_cves_count if not DISABLE_ACCOUNT_CACHE and not cyndi_request and cve_cache_from: counts_query = (RuleAccountCache.select(RuleAccountCache.rule_id.alias("rule_id_"), RuleAccountCache.systems_affected.alias("systems_affected_")) .where(RuleAccountCache.rh_account_id == rh_account)) else: counts_query = (SystemVulnerabilities .select(SystemVulnerabilities.rule_id.alias("rule_id_"), fn.Count(fn.Distinct(SystemVulnerabilities.system_id)).alias("systems_affected_")) .join(SystemPlatform, on=((SystemVulnerabilities.system_id == SystemPlatform.id) & (SystemPlatform.rh_account_id == rh_account) & (SystemPlatform.opt_out == False) & (SystemPlatform.stale == False) & (SystemPlatform.when_deleted.is_null(True)) & (fn.COALESCE(SystemPlatform.host_type, 'null') != HostType.EDGE))) .where(SystemVulnerabilities.rh_account_id == rh_account) .where((SystemVulnerabilities.rule_id << InsightsRule.select(InsightsRule.id).where((InsightsRule.active == True) & (~InsightsRule.rule_only))) & (SystemVulnerabilities.mitigation_reason.is_null(True))) .group_by(SystemVulnerabilities.rule_id)) if cyndi_request: counts_query = cyndi_join(counts_query) counts_query = apply_filters(counts_query, args, [filter_types.SYSTEM_TAGS, filter_types.SYSTEM_SAP, filter_types.SYSTEM_SAP_SIDS], {}) recent_rules = (InsightsRule.select(InsightsRule.description_text.alias('name'), InsightsRule.summary_text.alias('description'), counts_query.c.systems_affected_.alias('systems_affected'), InsightsRule.rule_impact.alias('severity'), InsightsRule.kbase_node_id.alias('node_id'), fn.ARRAY_AGG(fn.Distinct(CveMetadata.cve)).alias('associated_cves'), InsightsRule.name.alias('id'), InsightsRule.publish_date.alias('public_date')) .join(CveRuleMapping, on=(InsightsRule.id == CveRuleMapping.rule_id)) .join(counts_query, on=(InsightsRule.id == counts_query.c.rule_id_)) .join(CveMetadata, on=(CveRuleMapping.cve_id == CveMetadata.id)) .where((InsightsRule.publish_date >= rules_date) & (InsightsRule.active == True) & (~InsightsRule.rule_only)) # noqa: E712 .group_by(InsightsRule.description_text, InsightsRule.publish_date, InsightsRule.rule_impact, InsightsRule.kbase_node_id, SQL('systems_affected'), InsightsRule.name, InsightsRule.publish_date, InsightsRule.summary_text) .order_by(InsightsRule.publish_date.desc(), InsightsRule.rule_impact, InsightsRule.description_text) .dicts()) recent_rules = apply_filters(recent_rules, args, [], {"count_subquery": counts_query}) for rule in recent_rules: retval['recent_rules'].append(rule) return retval