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, in_cve_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))) # noqa: E712
if in_inventory_id_list is not None:
systems = systems.where(SystemPlatform.inventory_id << in_inventory_id_list)
rows_modified = set()
# set statuses and their CVE lists
for status_id, status_cve_list in status_to_cves_map.items():
status_id_update = (SystemVulnerabilities.update(status_id=status_id)
.where((SystemVulnerabilities.system_id << systems) &
(SystemVulnerabilities.cve_id <<
(CveMetadata.select(CveMetadata.id).where(
CveMetadata.cve << status_cve_list))))
.returning(SystemVulnerabilities.id))
rows_modified.update([row.id for row in status_id_update])
cls._update_divergent_status_count(in_cve_list, rh_account[0].id)
for status_text, status_cve_list in status_text_to_cves_map.items():
status_text_update = (SystemVulnerabilities.update(status_text=status_text)
.where((SystemVulnerabilities.system_id << systems) &
(SystemVulnerabilities.cve_id <<
(CveMetadata.select(CveMetadata.id).where(
CveMetadata.cve << status_cve_list))))
.returning(SystemVulnerabilities.id))
rows_modified.update([row.id for row in status_text_update])
updated_details = (SystemVulnerabilities.select(SystemPlatform.inventory_id, CveMetadata.cve)
.join(CveMetadata, on=(SystemVulnerabilities.cve_id == CveMetadata.id))
.join(SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id))
.where(SystemVulnerabilities.id << list(rows_modified))
.dicts())
updated = []
for updated_row in updated_details:
updated.append({"inventory_id": updated_row["inventory_id"], "cve": updated_row["cve"]})
if not updated:
# sysid/cve/acct combination does not exist
return cls.format_exception('inventory_id/cve must exist and inventory_id must be visible to user', 404)
except (IntegrityError, psycopg2IntegrityError, DataError) as value_error:
# usually means bad-status-id
LOGGER.error(str(value_error))
DB.rollback()
return cls.format_exception(f'status_id={list(status_to_cves_map.keys())} is invalid', 400)
except ValueError as value_error:
LOGGER.exception('Error during setting status (ValueError):')
DB.rollback()
return cls.format_exception(f'status_text or other key value is invalid ({value_error})', 400)
return {"updated": updated}
def _cve_exists(synopsis):
try:
(CveMetadata.select(CveMetadata.cve)
.where(CveMetadata.cve == synopsis)
.get())
except DoesNotExist:
raise ApplicationException('No such CVE ID', 404)
def __init__(self, list_args, cve_list, uri):
cve_list = [remove_str_nulls(elem) for elem in cve_list]
query = (CveMetadata.select(CveMetadata.cve,
fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score).alias('cvss_score'),
CveImpact.name.alias('impact'))
.join(CveImpact, on=(CveMetadata.impact_id == CveImpact.id))
.where(CveMetadata.cve.in_(cve_list))
.dicts())
sortable_columns = {
'synopsis': CVE_SYNOPSIS_SORT,
# 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),
'impact_id': CveMetadata.impact_id,
'impact': CveMetadata.impact_id,
}
filterable_columns = {
'synopsis': CveMetadata.cve,
}
default_sort_columns = {
'default': 'synopsis'
}
super().__init__(query, sortable_columns, default_sort_columns, filterable_columns, {}, list_args, [], uri)
def _get_cve_details(synopsis):
try:
data = (CveMetadata.select(CveImpact.name.alias('impact'), CveMetadata.cve.alias('synopsis'),
CveMetadata.description, CveMetadata.cvss2_metrics,
CveMetadata.cvss2_score, CveMetadata.cvss3_score,
CveMetadata.cvss3_metrics, CveMetadata.modified_date,
CveMetadata.public_date, CveMetadata.redhat_url,
CveMetadata.secondary_url, CveMetadata.id)
.join(CveImpact, on=(CveMetadata.impact_id == CveImpact.id))
.where(CveMetadata.cve == synopsis)).dicts()[0]
rules_map = get_rules_for_cves([data['id']])
return {
"cvss2_metrics": str_or_none(data['cvss2_metrics']),
"cvss2_score": str_or_none(data['cvss2_score']),
"cvss3_metrics": str_or_none(data['cvss3_metrics']),
"cvss3_score": str_or_none(data['cvss3_score']),
"description": data['description'],
"impact": data['impact'],
"public_date": format_datetime(data['public_date']),
"modified_date": format_datetime(data['modified_date']),
"redhat_url": data['redhat_url'],
"rules": rules_map.get(data['id'], []),
"secondary_url": data['secondary_url'],
"synopsis": data['synopsis'],
}
except IndexError:
raise ApplicationException('No such CVE ID', 404)
def _get_top_cves_by_cvss(cvss_from, cvss_to, count_query, limit=3):
# pylint: disable=singleton-comparison
rules = (CveRuleMapping.select(
CveRuleMapping.cve_id, CveRuleMapping.rule_id).join(
InsightsRule,
on=((CveRuleMapping.rule_id == InsightsRule.id)
& (InsightsRule.active == True) &
(~InsightsRule.rule_only))))
return (CveMetadata.select(
CveMetadata.cve, CveMetadata.cvss3_score, CveMetadata.cvss2_score,
CveMetadata.description, CveMetadata.exploits,
count_query.c.systems_affected_.alias("systems_affected"),
fn.ARRAY_AGG(rules.c.rule_id).alias('has_rule')).join(
count_query,
on=(CveMetadata.id == count_query.c.cve_id_)).join(
rules,
JOIN.LEFT_OUTER,
on=(CveMetadata.id == rules.c.cve_id)).where(
(fn.COALESCE(CveMetadata.cvss3_score,
CveMetadata.cvss2_score) >= cvss_from)
& (fn.COALESCE(CveMetadata.cvss3_score,
CveMetadata.cvss2_score) < cvss_to)).
order_by(
count_query.c.systems_affected_.desc(),
fn.COALESCE(CveMetadata.cvss3_score,
CveMetadata.cvss2_score).desc(nulls='LAST'),
CveMetadata.id).group_by(
CveMetadata.id, CveMetadata.cve,
CveMetadata.cvss3_score, CveMetadata.cvss2_score,
CveMetadata.description,
count_query.c.systems_affected_.alias(
"systems_affected")).limit(limit).dicts())
def _full_query(rh_account_id, join_type, count_subquery):
return (CveMetadata.select(
CveMetadata.id.alias("cve_id"), CveMetadata.cve.alias("cve_name"),
CveMetadata.cvss3_score, CveMetadata.cvss2_score,
CveMetadata.impact_id, CveMetadata.public_date,
CveMetadata.description.alias("cve_description"),
CveMetadata.exploits,
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"),
fn.COALESCE(CveAccountData.status_id, 0).alias("status_id"),
CveAccountData.status_text.alias("status_text"),
fn.COALESCE(Status.name, DEFAULT_STATUS).alias("status"),
fn.COALESCE(count_subquery.c.systems_affected_,
0).alias("systems_affected"),
fn.COALESCE(
count_subquery.c.systems_status_divergent_,
0).alias("systems_status_divergent")).join(
count_subquery,
join_type,
on=(CveMetadata.id == count_subquery.c.cve_id_)).join(
CveAccountData,
JOIN.LEFT_OUTER,
on=((CveMetadata.id == CveAccountData.cve_id)
&
(CveAccountData.rh_account_id == rh_account_id))).
join(BusinessRisk,
JOIN.LEFT_OUTER,
on=(CveAccountData.business_risk_id == BusinessRisk.id
)).join(Status,
JOIN.LEFT_OUTER,
on=(CveAccountData.status_id == Status.id)))
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))
.where(SystemPlatform.rh_account_id == rh_account_id)
.where(SystemPlatform.opt_out == False) # noqa: E712
.where(SystemVulnerabilities.cve_id << cve_ids)
.where(SystemVulnerabilities.when_mitigated.is_null(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))
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([(cve_id, systems_status_divergent) for cve_id, systems_status_divergent
in 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 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 = get_or_create_account()
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))
.join(InsightsRule, JOIN.LEFT_OUTER, on=(InsightsRule.id == SystemVulnerabilities.rule_id))
.where(SystemPlatform.rh_account_id == rh_account[0].id)
.where((SystemPlatform.opt_out == False) # noqa: E712
& (SystemPlatform.stale == False) # noqa: E712
& (SystemPlatform.when_deleted.is_null(True)))
.where(SystemVulnerabilities.cve_id == cve.id)
.where((SystemVulnerabilities.mitigation_reason.is_null(True)) | (InsightsRule.active == False))
.where((SystemVulnerabilities.when_mitigated.is_null(True)) | (InsightsRule.active == 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={data['status_id']} 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 _get_advisories_per_cve_from_db(synopsis: str):
"""Get advisories per CVE from DB"""
try:
data = (CveMetadata.select(CveMetadata.advisories_list)
.where(CveMetadata.cve == synopsis)).dicts()[0]['advisories_list']
except IndexError:
data = []
return data
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 = get_or_create_account()
for cve in cves: # pylint: disable=not-an-iterable
updated.append(cve.cve)
to_insert.append(
(cve.id, rh_account[0].id, values.get('status_id', 0),
values.get('status_text', None)))
if not to_insert:
return cls.format_exception(
'At least one given CVE must exist', 404)
(CveAccountData.insert_many(
to_insert, fields=cls._fields).on_conflict(
conflict_target=cls._conflict_target,
preserve=[],
update=values).execute())
RHAccount.update(
last_status_change=datetime.now(timezone.utc)).where(
RHAccount.id == rh_account[0].id).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={data['status_id']} 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):
"""Set business risk for a CVE"""
data = kwargs['data']
cve_list = data['cve']
if isinstance(cve_list, str):
cve_list = [cve_list]
values = {}
updated = []
if 'business_risk_id' in data:
values['business_risk_id'] = data['business_risk_id']
if 'business_risk_text' in data:
try:
values['business_risk_text'] = data['business_risk_text'].strip() \
if data['business_risk_text'].strip() else None
except AttributeError:
values['business_risk_text'] = None
if not values:
return cls.format_exception(
'At least one of the "business_risk_id" or "business_risk_text" parameters is required.',
400)
try:
to_insert = []
cves = CveMetadata.select(
CveMetadata.id,
CveMetadata.cve).where(CveMetadata.cve << cve_list)
rh_account = get_or_create_account()
for cve in cves:
updated.append(cve.cve)
to_insert.append((cve.id, rh_account[0].id,
values.get('business_risk_id', 0),
values.get('business_risk_text', None)))
if not to_insert:
return cls.format_exception(
'At least one given CVE must exist', 404)
(CveAccountData.insert_many(
to_insert, fields=cls._fields).on_conflict(
conflict_target=cls._conflict_target,
preserve=[],
update=values).execute())
except (IntegrityError, psycopg2IntegrityError, DataError):
# usually means bad business_risk_id
LOGGER.exception(
'Error during setting business risk (IntegrityError):')
DB.rollback()
return cls.format_exception(
f'business_risk_id=%s is invalid' % data['business_risk_id'],
400)
except ValueError as value_error:
LOGGER.exception(
'Error during setting business risk (ValueError):')
DB.rollback()
return cls.format_exception(
f'business_risk_text or other key value is invalid ({value_error})',
400)
return {'updated': updated}
def _get_top_cves_by_cvss(cvss_from, cvss_to, rh_account, limit=3):
return CveMetadata.select(CveMetadata.cve, CveMetadata.cvss3_score, CveMetadata.cvss2_score,
CveMetadata.description, CveAccountData.systems_affected)\
.join(CveAccountData, on=(CveMetadata.id == CveAccountData.cve_id))\
.where((CveAccountData.rh_account_id == rh_account) & (CveAccountData.systems_affected > 0) & (
(fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score) >= cvss_from) &
(fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score) < cvss_to)))\
.order_by(CveAccountData.systems_affected.desc(),
fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score).desc(nulls='LAST'), CveMetadata.id)\
.limit(limit)
def _build_update_condition(systems, status_cve_list):
# pylint: disable=singleton-comparison
return ((SystemVulnerabilities.system_id << systems) &
(SystemVulnerabilities.cve_id << (CveMetadata.select(
CveMetadata.id).where(CveMetadata.cve << status_cve_list)))
& ((SystemVulnerabilities.mitigation_reason.is_null(True)) |
(SystemVulnerabilities.rule_id << (InsightsRule.select(
InsightsRule.id).where(InsightsRule.active == True)))) &
((SystemVulnerabilities.when_mitigated.is_null(True)) |
(SystemVulnerabilities.rule_id <<
(InsightsRule.select(InsightsRule.id).where(
InsightsRule.active == True))) # noqa: E712
))
def get_advisories_per_cve_from_db(synopsis: str):
"""Get advisories per CVE from DB
Args:
synopsis (str): cve name
Returns:
list: List with advisories for CVE
"""
try:
query = (CveMetadata.select(
CveMetadata.advisories_list).where(CveMetadata.cve == synopsis))
data = query.dicts()[0]['advisories_list']
except IndexError:
data = []
return data or []
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:
values['status_text'] = data['status_text'].strip(
) if data['status_text'].strip() else 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)
to_insert.append(
(cve.id, rh_account[0].id, values.get('status_id', 0),
values.get('status_text', None)))
if not to_insert:
return cls.format_exception(
'At least one given CVE must exist', 404)
(CveAccountData.insert_many(
to_insert, fields=cls._fields).on_conflict(
conflict_target=cls._conflict_target,
preserve=[],
update=values).execute())
except (IntegrityError, psycopg2IntegrityError):
# usually means bad 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(str(value_error), 500)
return {'updated': updated}
def _get_cve_details(synopsis, args):
try:
data = (CveMetadata.select(
CveImpact.name.alias('impact'),
CveMetadata.cve.alias('synopsis'), CveMetadata.description,
CveMetadata.cvss2_metrics, CveMetadata.cvss2_score,
CveMetadata.cvss3_score, CveMetadata.cvss3_metrics,
CveMetadata.modified_date, CveMetadata.celebrity_name,
CveMetadata.public_date, CveMetadata.redhat_url,
CveMetadata.secondary_url, CveMetadata.id,
CveMetadata.advisories_list, CveMetadata.exploits).join(
CveImpact,
on=(CveMetadata.impact_id == CveImpact.id)).where(
CveMetadata.cve == synopsis)).dicts()[0]
rules_map = get_rules_for_cves([data['id']],
connexion.context['user'])
retval = {
"celebrity_name": str_or_none(data['celebrity_name']),
"cvss2_metrics": str_or_none(data['cvss2_metrics']),
"cvss2_score": str_or_none(data['cvss2_score']),
"cvss3_metrics": str_or_none(data['cvss3_metrics']),
"cvss3_score": str_or_none(data['cvss3_score']),
"description": data['description'],
"impact": data['impact'],
"public_date": format_datetime(data['public_date']),
"modified_date": format_datetime(data['modified_date']),
"redhat_url": data['redhat_url'],
"rules": rules_map.get(data['id'], []),
"secondary_url": data['secondary_url'],
"synopsis": data['synopsis'],
"known_exploit": bool(data['exploits'])
}
advisories_list = [] if data.get(
'advisories_list') is None else data['advisories_list']
if 'show_advisories_details' in args and args[
'show_advisories_details'] is True and advisories_list:
retval["advisories_list"] = GetCves._patch_request_advisories(
advisories_list)
else:
retval["advisories_list"] = advisories_list
except IndexError as exc:
raise ApplicationException('No such CVE ID', 404) from exc
return retval
def handle_get(cls, **kwargs):
# pylint: disable=singleton-comparison
cve_count_by_severity = []
retval = {
'system_count': 0,
'cves_total': 0,
'cves_by_severity': {
'0to3.9': {
'percentage': 0,
'count': 0
},
'4to7.9': {
'percentage': 0,
'count': 0
},
'8to10': {
'percentage': 0,
'count': 0
}
},
'recent_cves': {
'last7days': 0,
'last30days': 0,
'last90days': 0
},
'top_cves': []
}
rh_account = RHAccount.select(RHAccount.id).where(RHAccount.name == connexion.context['user']).first()
if rh_account is None:
return retval
rh_account = rh_account.id
retval['system_count'] = SystemPlatform.select(fn.COUNT(SystemPlatform.id).alias('count')).where(
(SystemPlatform.rh_account_id == rh_account) & (SystemPlatform.last_evaluation.is_null(False))
& (SystemPlatform.opt_out == False)).first().count # noqa: E712
if retval['system_count'] == 0:
return retval
cves_total = CveAccountData.select(CveAccountData.cve_id).where((CveAccountData.rh_account_id == rh_account) & (CveAccountData.systems_affected > 0))
if cves_total.count() == 0:
return retval
retval['cves_total'] = cves_total.count()
cve_details = CveMetadata.select(fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score).alias('cvss'),
CveMetadata.public_date).where(CveMetadata.id << cves_total)
retval['cves_by_severity']['0to3.9']['count'] = cve_details.select_from(fn.COUNT('*').alias('count')).where(cve_details.c.cvss < 4).first().count
retval['cves_by_severity']['4to7.9']['count'] = cve_details.select_from(
fn.COUNT('*').alias('count')).where((cve_details.c.cvss >= 4) & (cve_details.c.cvss < 8)).first().count
retval['cves_by_severity']['8to10']['count'] = cve_details.select_from(fn.COUNT('*').alias('count')).where(cve_details.c.cvss >= 8).first().count
cve_count_by_severity = [v['count'] for v in retval['cves_by_severity'].values()]
rounded_percentage = round_to_100_percent(cve_count_by_severity)
for indx, keys in enumerate(retval['cves_by_severity']):
retval['cves_by_severity'][keys]['percentage'] = rounded_percentage[indx]
today = datetime.utcnow().date()
retval['recent_cves']['last7days'] = cve_details.select_from(
fn.COUNT('*').alias('count')).where(cve_details.c.public_date >= (today - timedelta(days=7))).first().count
retval['recent_cves']['last30days'] = cve_details.select_from(
fn.COUNT('*').alias('count')).where(cve_details.c.public_date >= (today - timedelta(days=30))).first().count
retval['recent_cves']['last90days'] = cve_details.select_from(
fn.COUNT('*').alias('count')).where(cve_details.c.public_date >= (today - timedelta(days=90))).first().count
top_cves = CveMetadata.select(CveMetadata.cve, CveMetadata.cvss3_score, CveMetadata.cvss2_score, CveMetadata.description,
CveAccountData.systems_affected).join(CveAccountData, on=(CveMetadata.id == CveAccountData.cve_id)).where(
(CveAccountData.rh_account_id == rh_account) & (CveAccountData.systems_affected > 0)).order_by(
CveAccountData.systems_affected.desc(),
fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score).desc(nulls='LAST'),
CveMetadata.id).limit(3)
for cve in top_cves:
cve_dict = {}
cve_dict['synopsis'] = cve.cve
cve_dict['cvss2_score'] = str(cve.cvss2_score)
cve_dict['cvss3_score'] = str(cve.cvss3_score)
cve_dict['description'] = cve.description
cve_dict['systems_affected'] = cve.cveaccountdata.systems_affected
retval['top_cves'].append(cve_dict)
return retval
def handle_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 _get_advisories_per_cve_from_db(synopsis):
"""Get advisories per CVE from DB"""
query = (CveMetadata.select(
CveMetadata.advisories_list).where(CveMetadata.cve == synopsis))
return query
def _cve_exists(cls, cve):
if cve is None:
raise ApplicationException("invalid cve_id", 400)
cve = (CveMetadata.select().where(CveMetadata.cve == cve))
if not cve.exists():
raise ApplicationException("cve_id must exist", 404)
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)))).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]
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)))
).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
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)))).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
def handle_get(cls, **kwargs):
# pylint: disable=singleton-comparison
retval = {
'system_count': 0,
'cves_total': 0,
'cves_by_severity': {
'0to3.9': {
'percentage': 0,
'count': 0
},
'4to7.9': {
'percentage': 0,
'count': 0
},
'8to10': {
'percentage': 0,
'count': 0
}
},
'recent_cves': {
'last7days': 0,
'last30days': 0,
'last90days': 0
},
'top_cves': []
}
rh_account = RHAccount.select(RHAccount.id).where(RHAccount.name == connexion.context['user']).first()
if rh_account is None:
return retval
rh_account = rh_account.id
retval['system_count'] = SystemPlatform.select(fn.COUNT(SystemPlatform.id).alias('count')).where(
(SystemPlatform.rh_account_id == rh_account) & ((SystemPlatform.last_evaluation.is_null(False)) | (SystemPlatform.advisor_evaluated.is_null(False)))
& ((SystemPlatform.opt_out == False) & (SystemPlatform.stale == False))).first().count # noqa: E712
if retval['system_count'] == 0:
return retval
cves_total = CveAccountData.select(CveAccountData.cve_id).where((CveAccountData.rh_account_id == rh_account) & (CveAccountData.systems_affected > 0))
if cves_total.count() == 0:
return retval
retval['cves_total'] = cves_total.count()
cve_details = CveMetadata.select(fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score).alias('cvss'),
CveMetadata.public_date).where(CveMetadata.id << cves_total)
retval['cves_by_severity']['0to3.9']['count'] = cve_details.select_from(fn.COUNT('*').alias('count')).where(cve_details.c.cvss < 4).first().count
retval['cves_by_severity']['4to7.9']['count'] = cve_details.select_from(
fn.COUNT('*').alias('count')).where((cve_details.c.cvss >= 4) & (cve_details.c.cvss < 8)).first().count
retval['cves_by_severity']['8to10']['count'] = cve_details.select_from(fn.COUNT('*').alias('count')).where(cve_details.c.cvss >= 8).first().count
cve_count_by_severity = [v['count'] for v in retval['cves_by_severity'].values()]
rounded_percentage = round_to_100_percent(cve_count_by_severity)
for indx, keys in enumerate(retval['cves_by_severity']):
retval['cves_by_severity'][keys]['percentage'] = rounded_percentage[indx]
today = datetime.utcnow().date()
retval['recent_cves']['last7days'] = cve_details.select_from(
fn.COUNT('*').alias('count')).where(cve_details.c.public_date >= (today - timedelta(days=7))).first().count
retval['recent_cves']['last30days'] = cve_details.select_from(
fn.COUNT('*').alias('count')).where(cve_details.c.public_date >= (today - timedelta(days=30))).first().count
retval['recent_cves']['last90days'] = cve_details.select_from(
fn.COUNT('*').alias('count')).where(cve_details.c.public_date >= (today - timedelta(days=90))).first().count
# The algorithm searches for CVEs with cvss score between 8 and 10, and then sort by a number of affected
# systems if there are not 3 CVE in the 8 to 10 range, then it looks for CVEs in 4 to 8 range, sorted by a
# number of systems affected. The high-end range check is exclusive that is why 11 here.
cves_limit = 3
top_cves = cls._get_top_cves_by_cvss(8.0, 11, rh_account, limit=cves_limit)
cls._build_top_cves(top_cves, retval)
cves_count = top_cves.count()
if cves_count < cves_limit:
next_tier_top = cls._get_top_cves_by_cvss(4.0, 8.0, rh_account, limit=cves_limit-cves_count)
cls._build_top_cves(next_tier_top, retval)
next_cves_count = next_tier_top.count()
if next_cves_count < (cves_limit - cves_count):
last_tier_top = cls._get_top_cves_by_cvss(0.0, 4.0, rh_account, limit=cves_limit-(cves_count+next_cves_count))
cls._build_top_cves(last_tier_top, retval)
return retval