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