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