def _full_query(cve_count, join_type, query_args):
return (CveAccountData.select(
cve_count.alias("systems_affected"),
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"),
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(CveAccountData.systems_status_divergent,
0).alias('systems_status_divergent')
).join(
RHAccount, on=(CveAccountData.rh_account_id == RHAccount.id)).join(
CveMetadata,
join_type,
on=((CveAccountData.cve_id == CveMetadata.id)
&
(RHAccount.name == query_args["rh_account_number"]))).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 _cve_details(cls, synopsis):
retval = cls._get_cve_details(synopsis)
cve_details = (CveAccountData.select(
BusinessRisk.name.alias('risk'),
CveAccountData.business_risk_id.alias('risk_id'),
CveAccountData.business_risk_text.alias('risk_text'),
Status.name.alias("status"),
CveAccountData.status_id.alias("status_id"),
CveAccountData.status_text.alias("status_text"),
CveAccountData.systems_status_divergent).join(
BusinessRisk,
on=(CveAccountData.business_risk_id == BusinessRisk.id)).join(
Status, on=(CveAccountData.status_id == Status.id)).join(
CveMetadata,
on=(CveAccountData.cve_id == CveMetadata.id)).where(
CveAccountData.rh_account_id == (
RHAccount.select(RHAccount.id).where(
RHAccount.name ==
connexion.context['user']))).where(
CveMetadata.cve == synopsis)).dicts()
if cve_details.count():
retval['business_risk'] = cve_details[0]['risk']
retval['business_risk_id'] = cve_details[0]['risk_id']
retval['business_risk_text'] = cve_details[0]['risk_text']
retval['status'] = cve_details[0]['status']
retval['status_id'] = cve_details[0]['status_id']
retval['status_text'] = cve_details[0]['status_text']
retval['systems_status_divergent'] = cve_details[0][
'systems_status_divergent']
else:
retval['business_risk'] = DEFAULT_BUSINESS_RISK
retval['business_risk_id'] = 0
retval['business_risk_text'] = None
retval['status'] = DEFAULT_STATUS
retval['status_id'] = 0
retval['status_text'] = None
retval['systems_status_divergent'] = 0
# add counts of systems with all statuses
retval['systems_status_detail'] = {}
# pylint: disable=singleton-comparison
status_detail = (
SystemVulnerabilities.select(
SystemVulnerabilities.status,
fn.Count(SystemVulnerabilities.status).alias('systems')).join(
SystemPlatform,
on=(SystemVulnerabilities.system_id == SystemPlatform.id)).
join(CveMetadata,
on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join(
RHAccount,
on=(SystemPlatform.rh_account_id == RHAccount.id)).
where(CveMetadata.cve == synopsis).where(
RHAccount.name == connexion.context['user']).where(
SystemVulnerabilities.when_mitigated.is_null(True)).where(
SystemPlatform.opt_out == False) # noqa: E712
.group_by(SystemVulnerabilities.status).dicts())
for row in status_detail:
retval['systems_status_detail'][row['status']] = row['systems']
return retval
def _prepare_data(data):
if 'inventory_id' in data:
in_inventory_id_list = parse_str_or_list(data['inventory_id'])
else:
# change status for all systems for given CVE(s)
in_inventory_id_list = None
in_cve_list = data['cve']
if isinstance(in_cve_list, str):
in_cve_list = [in_cve_list]
status_to_cves_map = {}
status_text_to_cves_map = {}
if 'status_id' in data:
# single status for all CVEs
status_to_cves_map[data['status_id']] = in_cve_list
if 'status_text' in data:
# single status for all CVEs
try:
key = data['status_text'].strip() if data['status_text'].strip(
) else None
except AttributeError:
key = None
status_text_to_cves_map[key] = in_cve_list
# if neither of status_id or status_text is set => inherit from CVE-level
if not status_to_cves_map and not status_text_to_cves_map:
# use CVE-level status if status not specified
cve_details = (CveAccountData.select(
CveMetadata.cve, CveAccountData.status,
CveAccountData.status_text).join(
CveMetadata,
on=(CveAccountData.cve_id == CveMetadata.id)).join(
RHAccount,
on=(CveAccountData.rh_account_id == RHAccount.id)).
where((RHAccount.name == connexion.context['user'])
& (CveMetadata.cve << in_cve_list)).dicts())
found_cves = set()
for cve in cve_details:
status_to_cves_map.setdefault(cve["status"],
[]).append(cve["cve"])
status_text_to_cves_map.setdefault(cve["status_text"],
[]).append(cve["cve"])
found_cves.add(cve["cve"])
# not found CVEs have 0 status by default, status_text is null
for cve in in_cve_list:
if cve not in found_cves:
status_to_cves_map.setdefault(0, []).append(cve)
status_text_to_cves_map.setdefault(None, []).append(cve)
return in_inventory_id_list, in_cve_list, status_to_cves_map, status_text_to_cves_map
def _id_query(join_type, query_args):
return (
CveAccountData.select(CveMetadata.cve.alias('cve_name'), ).join(
RHAccount,
on=(CveAccountData.rh_account_id == RHAccount.id)).join(
CveMetadata,
join_type,
on=((CveAccountData.cve_id == CveMetadata.id)
&
(RHAccount.name == query_args["rh_account_number"]))).
join(BusinessRisk,
JOIN.LEFT_OUTER,
on=(CveAccountData.business_risk_id == BusinessRisk.id)).join(
Status,
JOIN.LEFT_OUTER,
on=(CveAccountData.status_id == Status.id)))
def __init__(self, list_args, query_args, uri, args={}): # pylint: disable=dangerous-default-value
join_type = JOIN.INNER
cve_count = CveAccountData.systems_affected
if 'show_all' in args and args['show_all']:
join_type = JOIN.RIGHT_OUTER
cve_count = fn.COALESCE(cve_count, 0)
query = (CveAccountData.select(
cve_count.alias("systems_affected"),
CveMetadata.cve.alias("cve_name"), CveMetadata.cvss3_score,
CveMetadata.cvss2_score, CveMetadata.impact_id,
CveMetadata.public_date,
CveMetadata.description.alias("cve_description"),
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(CveAccountData.systems_status_divergent,
0).alias('systems_status_divergent')
).join(
RHAccount, on=(CveAccountData.rh_account_id == RHAccount.id)).join(
CveMetadata,
join_type,
on=((CveAccountData.cve_id == CveMetadata.id)
&
(RHAccount.name == query_args["rh_account_number"]))).join(
BusinessRisk,
JOIN.LEFT_OUTER,
on=(CveAccountData.business_risk_id == BusinessRisk.id
)).join(
Status,
JOIN.LEFT_OUTER,
on=(CveAccountData.status_id == Status.id)))
if 'show_all' not in args or not args['show_all']:
query = query.where(CveAccountData.systems_affected > 0)
if 'cvss_from' in args and args['cvss_from']:
query = query.where(
fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score)
>= args['cvss_from'])
if 'cvss_to' in args and args['cvss_to']:
query = query.where(
fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score)
<= args['cvss_to'])
if 'public_from' in args and args['public_from']:
query = query.where(CveMetadata.public_date >= args['public_from'])
if 'public_to' in args and args['public_to']:
query = query.where(CveMetadata.public_date <= args['public_to'])
if 'impact' in args and args['impact']:
query = query.where(CveMetadata.impact_id << args['impact'])
if 'business_risk_id' in args and args['business_risk_id']:
query = query.where(
fn.COALESCE(CveAccountData.business_risk_id, 0) <<
args['business_risk_id'])
if 'status_id' in args and args['status_id']:
query = query.where(
fn.COALESCE(CveAccountData.status_id, 0) << args['status_id'])
query = query.dicts()
sortable_columns = {
"systems_affected": SQL('systems_affected'),
"id": CveMetadata.id,
"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,
"business_risk_id": SQL('business_risk_id'),
"business_risk": SQL('business_risk_id'),
"status_id": SQL('status_id'),
"status": SQL('status_id'),
}
default_sort_columns = ['id']
filterable_columns = {
"synopsis": CveMetadata.cve,
"description": CveMetadata.description
}
super(CvesListView,
self).__init__(query, sortable_columns, default_sort_columns,
filterable_columns, list_args, args, uri)
def _cve_details(cls, synopsis, args):
retval = cls._get_cve_details(synopsis, args)
cve_details = (CveAccountData.select(
BusinessRisk.name.alias('risk'),
CveAccountData.business_risk_id.alias('risk_id'),
CveAccountData.business_risk_text.alias('risk_text'),
Status.name.alias("status"),
CveAccountData.status_id.alias("status_id"),
CveAccountData.status_text.alias("status_text")).join(
BusinessRisk,
on=(CveAccountData.business_risk_id == BusinessRisk.id)).join(
Status, on=(CveAccountData.status_id == Status.id)).join(
CveMetadata,
on=(CveAccountData.cve_id == CveMetadata.id)).where(
CveAccountData.rh_account_id == (
RHAccount.select(RHAccount.id).where(
RHAccount.name ==
connexion.context['user']))).where(
CveMetadata.cve == synopsis)).dicts()
if cve_details.count():
retval['business_risk'] = cve_details[0]['risk']
retval['business_risk_id'] = cve_details[0]['risk_id']
retval['business_risk_text'] = cve_details[0]['risk_text']
retval['status'] = cve_details[0]['status']
retval['status_id'] = cve_details[0]['status_id']
retval['status_text'] = cve_details[0]['status_text']
else:
retval['business_risk'] = DEFAULT_BUSINESS_RISK
retval['business_risk_id'] = 0
retval['business_risk_text'] = None
retval['status'] = DEFAULT_STATUS
retval['status_id'] = 0
retval['status_text'] = None
# add counts of systems with all statuses
retval['systems_status_detail'] = {}
# pylint: disable=singleton-comparison
rh_account_id, _, _ = get_account_data(connexion.context['user'])
status_detail = (SystemVulnerabilities.select(
SystemVulnerabilities.status_id,
fn.Count(SystemVulnerabilities.status_id).alias('systems')
).join(SystemPlatform,
on=(SystemVulnerabilities.system_id == SystemPlatform.id)).join(
CveMetadata,
on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join(
InsightsRule,
JOIN.LEFT_OUTER,
on=(InsightsRule.id == SystemVulnerabilities.rule_id)
).where(CveMetadata.cve == synopsis).where(
SystemVulnerabilities.rh_account_id == rh_account_id
).where(
(SystemVulnerabilities.mitigation_reason.is_null(True))
| (InsightsRule.active == False)
& (~InsightsRule.rule_only)).where(
(SystemVulnerabilities.when_mitigated.is_null(True))
| (InsightsRule.active == True)
& (~InsightsRule.rule_only)).where(
(SystemPlatform.opt_out == False)
& (SystemPlatform.stale == False)
& (SystemPlatform.when_deleted.is_null(True))).
group_by(SystemVulnerabilities.status_id).dicts())
divergent_systems = 0
status_detail = cyndi_join(status_detail)
for row in status_detail:
retval['systems_status_detail'][row['status_id']] = row['systems']
if row['status_id'] != retval['status_id']:
divergent_systems += row['systems']
retval['systems_status_divergent'] = divergent_systems
return retval
def handle_get(cls, **kwargs):
# pylint: disable=singleton-comparison
retval = {
'system_count': 0,
'cves_total': 0,
'cves_by_severity': {
'0to3.9': {
'percentage': 0,
'count': 0
},
'4to7.9': {
'percentage': 0,
'count': 0
},
'8to10': {
'percentage': 0,
'count': 0
}
},
'recent_cves': {
'last7days': 0,
'last30days': 0,
'last90days': 0
},
'top_cves': []
}
rh_account = RHAccount.select(RHAccount.id).where(RHAccount.name == connexion.context['user']).first()
if rh_account is None:
return retval
rh_account = rh_account.id
retval['system_count'] = SystemPlatform.select(fn.COUNT(SystemPlatform.id).alias('count')).where(
(SystemPlatform.rh_account_id == rh_account) & ((SystemPlatform.last_evaluation.is_null(False)) | (SystemPlatform.advisor_evaluated.is_null(False)))
& ((SystemPlatform.opt_out == False) & (SystemPlatform.stale == False))).first().count # noqa: E712
if retval['system_count'] == 0:
return retval
cves_total = CveAccountData.select(CveAccountData.cve_id).where((CveAccountData.rh_account_id == rh_account) & (CveAccountData.systems_affected > 0))
if cves_total.count() == 0:
return retval
retval['cves_total'] = cves_total.count()
cve_details = CveMetadata.select(fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score).alias('cvss'),
CveMetadata.public_date).where(CveMetadata.id << cves_total)
retval['cves_by_severity']['0to3.9']['count'] = cve_details.select_from(fn.COUNT('*').alias('count')).where(cve_details.c.cvss < 4).first().count
retval['cves_by_severity']['4to7.9']['count'] = cve_details.select_from(
fn.COUNT('*').alias('count')).where((cve_details.c.cvss >= 4) & (cve_details.c.cvss < 8)).first().count
retval['cves_by_severity']['8to10']['count'] = cve_details.select_from(fn.COUNT('*').alias('count')).where(cve_details.c.cvss >= 8).first().count
cve_count_by_severity = [v['count'] for v in retval['cves_by_severity'].values()]
rounded_percentage = round_to_100_percent(cve_count_by_severity)
for indx, keys in enumerate(retval['cves_by_severity']):
retval['cves_by_severity'][keys]['percentage'] = rounded_percentage[indx]
today = datetime.utcnow().date()
retval['recent_cves']['last7days'] = cve_details.select_from(
fn.COUNT('*').alias('count')).where(cve_details.c.public_date >= (today - timedelta(days=7))).first().count
retval['recent_cves']['last30days'] = cve_details.select_from(
fn.COUNT('*').alias('count')).where(cve_details.c.public_date >= (today - timedelta(days=30))).first().count
retval['recent_cves']['last90days'] = cve_details.select_from(
fn.COUNT('*').alias('count')).where(cve_details.c.public_date >= (today - timedelta(days=90))).first().count
# The algorithm searches for CVEs with cvss score between 8 and 10, and then sort by a number of affected
# systems if there are not 3 CVE in the 8 to 10 range, then it looks for CVEs in 4 to 8 range, sorted by a
# number of systems affected. The high-end range check is exclusive that is why 11 here.
cves_limit = 3
top_cves = cls._get_top_cves_by_cvss(8.0, 11, rh_account, limit=cves_limit)
cls._build_top_cves(top_cves, retval)
cves_count = top_cves.count()
if cves_count < cves_limit:
next_tier_top = cls._get_top_cves_by_cvss(4.0, 8.0, rh_account, limit=cves_limit-cves_count)
cls._build_top_cves(next_tier_top, retval)
next_cves_count = next_tier_top.count()
if next_cves_count < (cves_limit - cves_count):
last_tier_top = cls._get_top_cves_by_cvss(0.0, 4.0, rh_account, limit=cves_limit-(cves_count+next_cves_count))
cls._build_top_cves(last_tier_top, retval)
return retval
def 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