def _filter_cve_by_cvss(query, args, _kwargs):
"""
Filters list of CVEs based on their CVSS score. Query has to contain CveMetadata table.
Args:
query (object): Query object to apply filters to
args (dict): Query arguemnts
Returns:
object: Modified query with CVSS filter applied
"""
# pylint: disable=too-many-boolean-expressions
if 'cvss_from' in args and (args['cvss_from'] or args['cvss_from'] == 0) and 'cvss_to' in args and (
args['cvss_to'] or args['cvss_to'] == 0):
if args['cvss_from'] == -1:
query = query.where((CveMetadata.cvss2_score.is_null() & CveMetadata.cvss3_score.is_null()) |
(fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score) <= args['cvss_to']))
else:
query = query.where((fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score) >= args['cvss_from']) &
(fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score) <= args['cvss_to']))
elif 'cvss_from' in args and (args['cvss_from'] or args['cvss_from'] == 0):
query = query.where(fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score) >= args['cvss_from'])
elif 'cvss_to' in args and (args['cvss_to'] or args['cvss_to'] == 0):
query = query.where(fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score) <= args['cvss_to'])
return query
def build_query(self, version_id, query_region=None):
a_large_value = 1e30
pars = self.parameters
query = (Skies_v2.select(
CatalogToSkies_v2.catalogid,
Skies_v2.ra,
Skies_v2.dec,
Skies_v2.pix_32768,
Skies_v2.tile_32,
).join(CatalogToSkies_v2).where(
CatalogToSkies_v2.version_id == version_id,
CatalogToSkies_v2.best >> True).where(
Skies_v2.valid_gaia >> True, Skies_v2.valid_tmass >> True,
Skies_v2.valid_tycho2 >> True,
Skies_v2.valid_tmass_xsc >> True).where(
fn.COALESCE(Skies_v2.sep_neighbour_ls8,
a_large_value) > pars['min_sep_ls8'],
fn.COALESCE(Skies_v2.sep_neighbour_ps1dr2,
a_large_value) > pars['min_sep_ps1dr2'],
((Skies_v2.valid_ls8 >> True) &
(Skies_v2.sep_neighbour_ls8 < pars['max_sep_ls8'])) |
((Skies_v2.valid_ps1dr2 >> True) &
(Skies_v2.sep_neighbour_ps1dr2 < pars['max_sep_ps1dr2'])))
)
if query_region:
query = (query.where(
peewee.fn.q3c_radial_query(Skies_v2.ra, Skies_v2.dec,
query_region[0], query_region[1],
query_region[2])))
return query
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(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 _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 _full_query(query_args):
# pylint: disable=singleton-comparison
return (
SystemVulnerabilities.select(
CveMetadata.cve.alias('cve_name'),
CveMetadata.cvss3_score, CveMetadata.cvss2_score,
CveImpact.name.alias('impact'), CveMetadata.public_date,
CveMetadata.description.alias('cve_description'),
fn.COALESCE(CveAccountData.status_id,
0).alias('cve_status_id'),
Status.id.alias('status_id'), Status.name.alias('status_name'),
SystemVulnerabilities.status_text.alias('status_text'),
SystemVulnerabilities.when_mitigated,
CveAccountData.status_text.alias('cve_status_text'),
fn.COALESCE(CveAccountData.business_risk_id,
0).alias('business_risk_id'),
CveAccountData.business_risk_text.alias('business_risk_text'),
fn.COALESCE(BusinessRisk.name,
DEFAULT_BUSINESS_RISK).alias('business_risk'),
InsightsRule.name.alias('rule_id'),
InsightsRule.description_text, InsightsRule.summary_text,
InsightsRule.generic_text, InsightsRule.reboot_required,
InsightsRule.playbook_count, InsightsRule.change_risk,
InsightsRule.kbase_node_id,
InsightsRule.active.alias('rule_active')).join(
SystemPlatform,
on=(SystemVulnerabilities.system_id == SystemPlatform.id)).
join(CveMetadata,
on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join(
CveImpact,
on=(CveMetadata.impact_id == CveImpact.id)).join(
Status,
on=(SystemVulnerabilities.status_id == Status.id)).
join(RHAccount,
on=(SystemPlatform.rh_account_id == RHAccount.id)).join(
CveAccountData,
JOIN.LEFT_OUTER,
on=((CveAccountData.cve_id == CveMetadata.id)
& (CveAccountData.rh_account_id == RHAccount.id))).
join(BusinessRisk,
JOIN.LEFT_OUTER,
on=(CveAccountData.business_risk_id == BusinessRisk.id)).join(
InsightsRule,
JOIN.LEFT_OUTER,
on=(InsightsRule.id == SystemVulnerabilities.rule_id)).
where((SystemVulnerabilities.mitigation_reason.is_null(True))
| (InsightsRule.active == False)).
where((SystemVulnerabilities.when_mitigated.is_null(True))
| (InsightsRule.active == True)).where(
RHAccount.name == query_args['rh_account_number']).where(
SystemPlatform.inventory_id ==
query_args['inventory_id']).where(
SystemPlatform.opt_out == False) # noqa: E712
.where(SystemPlatform.when_deleted.is_null(True)))
def _full_query(rh_account_id, synopsis):
# pylint: disable=singleton-comparison
selectables = [
fn.COALESCE(CveAccountData.status_id, 0).alias('cve_status_id'),
SystemPlatform.inventory_id,
SystemPlatform.display_name,
SystemPlatform.last_evaluation,
SystemPlatform.advisor_evaluated.alias('rules_evaluation'),
SystemPlatform.last_upload,
SystemPlatform.stale_timestamp,
SystemPlatform.stale_warning_timestamp,
SystemPlatform.culled_timestamp,
Status.id.alias('status_id'),
Status.name.alias('status_name'),
SystemVulnerabilities.status_text.alias('status_text'),
SystemVulnerabilities.mitigation_reason,
SystemVulnerabilities.when_mitigated,
InsightsRule.name.alias('rule_id'),
InventoryHosts.tags,
InventoryHosts.updated,
InventoryHosts.insights_id,
OS_INFO_QUERY.alias('os'),
fn.COALESCE(SystemVulnerabilities.advisory_available,
True).alias('advisory_available'),
fn.COALESCE(
SystemVulnerabilities.remediation_type_id,
remediation.PLAYBOOK.value).alias('remediation_type_id'),
]
return (SystemVulnerabilities.select(*selectables).join(
SystemPlatform,
on=(SystemVulnerabilities.system_id == SystemPlatform.id)).join(
Status,
on=(SystemVulnerabilities.status_id == Status.id)).join(
CveMetadata,
on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join(
CveAccountData,
JOIN.LEFT_OUTER,
on=((CveAccountData.rh_account_id == rh_account_id)
& (CveMetadata.id == CveAccountData.cve_id))).join(
InsightsRule,
on=(InsightsRule.id ==
SystemVulnerabilities.rule_id)).
where(CveMetadata.cve == synopsis).where(
SystemVulnerabilities.rh_account_id == rh_account_id).
where((SystemVulnerabilities.mitigation_reason.is_null(False))
& (InsightsRule.active == True)).where(
(SystemPlatform.opt_out == False)
& (SystemPlatform.stale == False)
& (SystemPlatform.when_deleted.is_null(True))
& (fn.COALESCE(SystemPlatform.host_type, 'null') !=
HostType.EDGE)))
def __init__(self, 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 _full_query(rh_account_id):
# pylint: disable=singleton-comparison
selectables = [SystemPlatform.inventory_id,
SystemPlatform.display_name,
SystemPlatform.last_evaluation,
SystemPlatform.advisor_evaluated.alias('rules_evaluation'),
SystemPlatform.opt_out,
SystemPlatform.last_upload,
SystemPlatform.stale_timestamp,
SystemPlatform.stale_warning_timestamp,
SystemPlatform.culled_timestamp,
Case(None, (((SystemPlatform.opt_out == False), SystemPlatform.cve_count_cache),), None).alias("cve_count"),
InventoryHosts.tags,
InventoryHosts.updated,
InventoryHosts.insights_id,
OS_INFO_QUERY.alias('os'),
]
return (SystemPlatform
.select(*selectables)
.where(SystemPlatform.rh_account_id == rh_account_id)
.where(SystemPlatform.last_evaluation.is_null(False) | SystemPlatform.advisor_evaluated.is_null(False))
.where(SystemPlatform.when_deleted.is_null(True))
.where(fn.COALESCE(SystemPlatform.host_type, 'null') != HostType.EDGE)
.dicts())
def _id_query(rh_account_id, synopsis):
# pylint: disable=singleton-comparison
return (SystemVulnerabilities.select(
SystemPlatform.inventory_id,
InsightsRule.name.alias('rule_id'),
SystemVulnerabilities.status_id.alias('status_id'),
SystemVulnerabilities.status_text.alias('status_text'),
).join(SystemPlatform,
on=(SystemVulnerabilities.system_id == SystemPlatform.id)).join(
CveMetadata,
on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join(
InsightsRule,
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))
& (fn.COALESCE(SystemPlatform.host_type, 'null')
!= HostType.EDGE)))
def _find_next_steak(self):
# Seems like queries cannot be reused...
## Prepare query to search for the next available steak
# Create subquery that counts the number of grills (max 1) to hold a spice
subquery = SteakSpice.select(
SteakSpice.spice.alias('spice'),
fn.COUNT(GrillSpice.id.distinct()).alias('in_use'))
subquery = subquery.join(GrillSpice,
JOIN.LEFT_OUTER,
on=(SteakSpice.spice == GrillSpice.spice))
subquery = subquery.group_by(SteakSpice.spice)
# Select all raw steaks without a grill and attach a flag whether any spices are in use
query = Steak.select(
Steak.id.alias('steak_id'),
fn.COALESCE(fn.SUM(subquery.c.in_use), 0).alias('spice_in_use'))
query = query.join(SteakGrill, JOIN.LEFT_OUTER)
query = query.switch(Steak).join(SteakSpice, JOIN.LEFT_OUTER)
query = query.join(subquery,
JOIN.LEFT_OUTER,
on=subquery.c.spice == SteakSpice.spice).group_by(
Steak.id)
query = query.where(Steak.status == STATUS_RAW).where(
SteakGrill.grill == None)
query = query.where(Steak.recipe.in_(self.recipe_names))
# Select the steaks with no spices in use
steak_query = Steak.select().join(
query, JOIN.INNER,
on=(Steak.id == query.c.steak_id)).where(query.c.spice_in_use == 0)
# Return oldest steak
return steak_query.order_by(Steak.created).first()
def __init__(self, list_args, query_args, uri, args, ids_only=False):
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 = self._full_query(cve_count, join_type, query_args) if not ids_only else self._id_query(join_type, query_args)
query = apply_filters(query, args, [filter_types.CVE_BUSINESS_RISK, filter_types.CVE_CVSS, filter_types.CVE_IMPACT, filter_types.CVE_PUBLIC_DATE,
filter_types.CVE_RULE_PRESENCE, filter_types.CVE_SHOW_ALL, filter_types.CVE_STATUS,
filter_types.CVE_RULE_PRESENCE_OLD])
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 _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 retainedHaulWeight(self):
"""
assumes fishing_activity_id is loaded, catch if not???
left joins, should always return a number, even if no retained
:return: int (sum of retained catch weights for haul)
"""
return FishingActivities.select(fn.COALESCE(fn.sum(Catches.catch_weight), 0))\
.join(Catches, JOIN.LEFT_OUTER).where(
(Catches.fishing_activity == self._current_haul.fishing_activity) &
(Catches.catch_disposition == 'R')
).scalar()
def withdrawals():
withdrawals = Withdrawal.select(Withdrawal, User) \
.where(Withdrawal.approved == False).order_by(Withdrawal.created_at).join(User)
total_sum = Withdrawal.select(fn.COALESCE(fn.SUM(Withdrawal.amount), 0).alias('total_sum')) \
.where(Withdrawal.approved == False).execute()
return render_template(
'withdrawals.html',
total_sum=total_sum[0].total_sum,
withdrawals=withdrawals,
)
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 _filter_cve_by_business_risk(query, args, _kwargs):
"""
Filters list of CVEs based on their business risk. Query has to contain CveAccountData table.
Args:
query (object): Query object to apply filters to
args (dict): Query arguemnts
Returns:
object: Modified query with business risk filter applied
"""
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'])
return query
def _filter_cve_by_status(query, args, _kwargs):
"""
Filters list of CVEs based on their global status. Query has to contain CveAccountData table.
Args:
query (object): Query object to apply filters to
args (dict): Query arguemnts
Returns:
object: Modified query with CVE status filter applied
"""
if 'status_id' in args and args['status_id']:
query = query.where(fn.COALESCE(CveAccountData.status_id, 0) << args['status_id'])
return query
def __init__(self, list_args, query_args, uri, args={}): # pylint: disable=dangerous-default-value
join_type = JOIN.INNER
cve_count = CveAffectedSystemsCache.systems_affected
if query_args['hide_satellite_managed']:
cve_count = CveAffectedSystemsCache.direct_systems_affected
if 'show_all' in args and args['show_all']:
join_type = JOIN.RIGHT_OUTER
cve_count = fn.COALESCE(cve_count, 0)
query = (
CveAffectedSystemsCache
.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"))
.join(CveMetadata, join_type,
on=((CveAffectedSystemsCache.cve == CveMetadata.cve)
& (CveAffectedSystemsCache.rh_account == query_args["rh_account_number"])))
)
if query_args['hide_satellite_managed'] and not ('show_all' in args and args['show_all']):
query = query.where(CveAffectedSystemsCache.direct_systems_affected > 0)
if 'cvss_from' in args and args['cvss_from']:
query = query.where(CveMetadata.cvss3_score >= args['cvss_from'])
if 'cvss_to' in args and args['cvss_to']:
query = query.where(CveMetadata.cvss3_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 'severity' in args and args['severity']:
query = query.where(CveMetadata.impact_id << args['severity'])
query = query.dicts()
sortable_columns = {
"systems_affected": SQL('systems_affected'),
"synopsis": CveMetadata.cve,
"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": CveMetadata.impact_id
}
filterable_columns = {
"synopsis": CveMetadata.cve,
"description": CveMetadata.description
}
super(CvesListView, self).__init__(query, sortable_columns, filterable_columns, list_args, args, uri)
def _filter_system_cve_by_remediation(query, args, _kwargs):
"""
Filters list of CVEs based on their remediation. Query has to contain SystemVulnerabilities table.
Args:
query (object): Query object to apply filters to
args (dict): Query arguemnts
Returns:
object: Modified query with system CVE remediation filter applied
"""
if 'remediation' in args and args['remediation']:
query = query.where(fn.COALESCE(SystemVulnerabilities.remediation_type_id, remediation.PLAYBOOK.value) << args['remediation'])
return query
def _full_query(synopsis, query_args):
# pylint: disable=singleton-comparison
selectables = [
fn.COALESCE(CveAccountData.status_id,
0).alias('cve_status_id'), SystemPlatform.inventory_id,
SystemPlatform.display_name, SystemPlatform.last_evaluation,
SystemPlatform.advisor_evaluated.alias('rules_evaluation'),
Status.id.alias('status_id'),
Status.name.alias('status_name'),
SystemVulnerabilities.status_text.alias('status_text'),
SystemVulnerabilities.rule_hit_details,
SystemVulnerabilities.when_mitigated,
InsightsRule.name.alias('rule_id'),
InsightsRule.active.alias('rule_active'),
InsightsRule.description_text, InsightsRule.reason_text,
InsightsRule.resolution_text, InsightsRule.kbase_node_id,
InsightsRule.more_info_text
]
if CYNDI_ENABLED:
selectables.append(InventoryHosts.tags)
return (SystemVulnerabilities.select(*selectables).join(
SystemPlatform,
on=(SystemVulnerabilities.system_id == SystemPlatform.id)).join(
Status, on=(SystemVulnerabilities.status_id == Status.id)
).join(CveMetadata,
on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join(
RHAccount,
on=(SystemPlatform.rh_account_id == RHAccount.id)).join(
CveAccountData,
JOIN.LEFT_OUTER,
on=((RHAccount.id == CveAccountData.rh_account_id)
& (CveMetadata.id == CveAccountData.cve_id))).
join(
InsightsRule,
JOIN.LEFT_OUTER,
on=(InsightsRule.id == SystemVulnerabilities.rule_id
)).where(CveMetadata.cve == synopsis).where(
RHAccount.name == query_args['rh_account_number']).
where((SystemVulnerabilities.mitigation_reason.is_null(True))
| (InsightsRule.active == False)).where(
(SystemVulnerabilities.when_mitigated.is_null(True))
| (InsightsRule.active == True)).where(
(SystemPlatform.opt_out == False) # noqa: E712
& (SystemPlatform.stale == False) # noqa: E712
& (SystemPlatform.when_deleted.is_null(True))))
def _id_query(rh_account_id, list_args):
selectables = [SystemPlatform.inventory_id,
SystemPlatform.opt_out]
# pylint: disable=singleton-comparison
if list_args["sort"] and "cve_count" in list_args["sort"]:
selectables.append(Case(None, (((SystemPlatform.opt_out == False), SystemPlatform.cve_count_cache),), None).alias("cve_count"))
query = (SystemPlatform
.select(*selectables)
.where(SystemPlatform.rh_account_id == rh_account_id)
.where(SystemPlatform.last_evaluation.is_null(False) | SystemPlatform.advisor_evaluated.is_null(False))
.where(SystemPlatform.when_deleted.is_null(True))
.where(fn.COALESCE(SystemPlatform.host_type, 'null') != HostType.EDGE)
.dicts())
return query
def get_infected_increase_log() -> typing.Iterable[dict]:
CoronaLogPrevieous = CoronaLog.alias()
previous_query = CoronaLogPrevieous.select()
previous_query = previous_query.alias("clp")
return (CoronaLog.select(
CoronaLog.date,
Value(CoronaLog.infected -
fn.COALESCE(previous_query.c.infected, 0)).alias(
"infected_increase"),
Value(CoronaLog.cured -
fn.COALESCE(previous_query.c.cured, 0)).alias("cured_increase"),
Value(CoronaLog.tests -
fn.COALESCE(previous_query.c.tests, 0)).alias("tests_increase"),
Value(CoronaLog.deaths -
fn.COALESCE(previous_query.c.deaths, 0)).alias(
"deaths_increase"),
Value(CoronaLog.median -
fn.COALESCE(previous_query.c.median, 0)).alias(
"median_increase"),
Value(CoronaLog.hospitalized -
fn.COALESCE(previous_query.c.hospitalized, 0)).alias(
"hospitalized_increase"),
Value(CoronaLog.confirmed_hospitalized -
fn.COALESCE(previous_query.c.confirmed_hospitalized, 0)).alias(
"confirmed_hospitalized_increase"),
Value(CoronaLog.confirmed_hospitalized_icu -
fn.COALESCE(previous_query.c.confirmed_hospitalized_icu, 0)).
alias("confirmed_hospitalized_icu_increase"),
Value(CoronaLog.confirmed_hospitalized_ventilation -
fn.COALESCE(previous_query.c.confirmed_hospitalized_ventilation,
0)).alias("confirmed_hospitalized_ventilation"),
).join(
previous_query,
JOIN.LEFT_OUTER,
on=(fn.julianday(
previous_query.c.date) == fn.julianday(CoronaLog.date) - 1),
).dicts())
def _count_subquery(rh_account_id):
# pylint: disable=singleton-comparison
return (SystemVulnerabilities
.select(SystemVulnerabilities.cve_id.alias("cve_id_"),
fn.Count(SystemVulnerabilities.id).alias("systems_affected_"),
fn.Sum(Case(None, ((SystemVulnerabilities.status_id != CveAccountData.status_id, 1),), 0)).alias("systems_status_divergent_"))
.join(SystemPlatform, on=((SystemVulnerabilities.system_id == SystemPlatform.id) &
(SystemPlatform.rh_account_id == rh_account_id) &
(SystemPlatform.opt_out == False) & # noqa: E712
(SystemPlatform.stale == False) & # noqa: E712
(SystemPlatform.when_deleted.is_null(True)) &
(fn.COALESCE(SystemPlatform.host_type, 'null') != HostType.EDGE)))
.join(CveAccountData, JOIN.LEFT_OUTER, on=((SystemVulnerabilities.cve_id == CveAccountData.cve_id)
& (CveAccountData.rh_account_id == rh_account_id)))
.where(SystemVulnerabilities.rh_account_id == rh_account_id)
.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))
def get_system_count(rh_account,
include_cyndi=True,
filters=None,
filters_args=None):
"""Get count of nonstale, nonoptouted, evaluated user systems"""
# pylint: disable=singleton-comparison
query = 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) & (SystemPlatform.when_deleted.is_null(True)))
& (fn.COALESCE(SystemPlatform.host_type, 'null') != HostType.EDGE))
if include_cyndi:
query = query.join(
InventoryHosts,
JOIN.INNER,
on=(SystemPlatform.inventory_id == InventoryHosts.id))
if filters:
query = apply_filters(query, filters_args, filters, {})
return query.first().count
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 __init__(self, list_args, query_args, filter_args, parsed_args, uri):
# pylint: disable=singleton-comparison
query = (
SystemVulnerabilities.select(
CveMetadata.cve.alias('cve_name'),
CveMetadata.cvss3_score, CveMetadata.cvss2_score,
CveImpact.name.alias('impact'), CveMetadata.public_date,
CveMetadata.description.alias('cve_description'),
fn.COALESCE(CveAccountData.status_id,
0).alias('cve_status_id'),
Status.id.alias('status_id'), Status.name.alias('status_name'),
SystemVulnerabilities.status_text.alias('status_text'),
fn.COALESCE(CveAccountData.business_risk_id,
0).alias('business_risk_id'),
CveAccountData.business_risk_text.alias('business_risk_text'),
fn.COALESCE(BusinessRisk.name,
DEFAULT_BUSINESS_RISK).alias('business_risk'),
InsightsRule.name.alias('rule_id'),
InsightsRule.description_text, InsightsRule.summary_text,
InsightsRule.generic_text, InsightsRule.reboot_required,
InsightsRule.playbook_count, InsightsRule.change_risk,
InsightsRule.kbase_node_id,
InsightsRule.active.alias('rule_active')).join(
SystemPlatform,
on=(SystemVulnerabilities.system_id == SystemPlatform.id)).
join(CveMetadata,
on=(SystemVulnerabilities.cve_id == CveMetadata.id)).join(
CveImpact,
on=(CveMetadata.impact_id == CveImpact.id)).join(
Status,
on=(SystemVulnerabilities.status_id == Status.id)).
join(RHAccount,
on=(SystemPlatform.rh_account_id == RHAccount.id)).join(
CveAccountData,
JOIN.LEFT_OUTER,
on=((CveAccountData.cve_id == CveMetadata.id)
& (CveAccountData.rh_account_id == RHAccount.id))).
join(BusinessRisk,
JOIN.LEFT_OUTER,
on=(CveAccountData.business_risk_id == BusinessRisk.id)).join(
InsightsRule,
JOIN.LEFT_OUTER,
on=(InsightsRule.id == SystemVulnerabilities.rule_id)).
where((SystemVulnerabilities.when_mitigated.is_null(True))
| (InsightsRule.active == True)).where(
RHAccount.name == query_args['rh_account_number']).where(
SystemPlatform.inventory_id ==
query_args['inventory_id']).where(
SystemPlatform.opt_out == False) # noqa: E712
)
if 'cvss_from' in filter_args and filter_args['cvss_from']:
query = query.where(
fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score)
>= filter_args['cvss_from'])
if 'cvss_to' in filter_args and filter_args['cvss_to']:
query = query.where(
fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score)
<= filter_args['cvss_to'])
if 'public_from' in filter_args and filter_args['public_from']:
query = query.where(
CveMetadata.public_date >= filter_args['public_from'])
if 'public_to' in filter_args and filter_args['public_to']:
query = query.where(
CveMetadata.public_date <= filter_args['public_to'])
if 'status_id' in filter_args and filter_args['status_id']:
query = query.where(Status.id << filter_args['status_id'])
if 'impact' in filter_args and filter_args['impact']:
query = query.where(CveMetadata.impact_id << filter_args['impact'])
if 'business_risk_id' in filter_args and filter_args[
'business_risk_id']:
query = query.where(
fn.COALESCE(CveAccountData.business_risk_id, 0) <<
filter_args['business_risk_id'])
if 'stale' in filter_args and filter_args['stale']:
query = query.where(SystemPlatform.stale == filter_args['stale'])
else:
query = query.where(SystemPlatform.stale == False) # noqa: E712
if 'security_rule' in filter_args and filter_args[
'security_rule'] is not None:
if filter_args['security_rule']:
query = query.where(InsightsRule.active == True) # noqa: E712
else:
query = query.where(
(InsightsRule.active == False) |
(InsightsRule.active == None)) # noqa: E712
query = query.dicts()
sortable_columns = {
'id': CveMetadata.id,
'cve': CVE_SYNOPSIS_SORT,
'synopsis': CVE_SYNOPSIS_SORT,
'public_date': CveMetadata.public_date,
# This assumes we only show one score, and that cvss3 wins over cvss2
'cvss_score': Case(None, ((CveMetadata.cvss3_score.is_null(True), CveMetadata.cvss2_score),), \
CveMetadata.cvss3_score),
"cvss3_score": CveMetadata.cvss3_score,
"cvss2_score": CveMetadata.cvss2_score,
'impact_id': CveMetadata.impact_id,
'impact': CveMetadata.impact_id,
'status_id': Status.id,
'status': Status.id,
'business_risk_id': SQL('business_risk_id'),
'business_risk': SQL('business_risk_id'),
}
default_sort_columns = ['id']
filterable_columns = {
'cve': CveMetadata.cve,
'description': CveMetadata.description,
'status': Status.name
}
super(SystemCvesView,
self).__init__(query, sortable_columns, default_sort_columns,
filterable_columns, list_args, parsed_args, uri)
def handle_get(cls, **kwargs):
# pylint: disable=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 user_summary(): # noqa: D103
form = DateRangeForm(request.args)
sort = request.args.get("sort")
if sort:
try:
sort = int(sort)
except:
sort = None
desc = request.args.get("desc")
if desc:
try:
desc = int(desc)
except:
desc = None
if not (form.from_date.data and form.to_date.data):
date_range = User.select(
fn.MIN(User.created_at).alias('from_date'),
fn.MAX(User.created_at).alias('to_date')).first()
return redirect(
url_for(
"user_summary",
from_date=date_range.from_date.date().isoformat(),
to_date=date_range.to_date.date().isoformat(),
sort=sort, desc=desc))
user_counts = (User.select(
User.organisation.alias("org_id"),
fn.COUNT(fn.DISTINCT(User.id)).alias("user_count")).where(
User.created_at.between(form.from_date.data, form.to_date.data)).join(
UserOrg, JOIN.LEFT_OUTER, on=(UserOrg.org_id == User.id)).group_by(
User.organisation)).alias("user_counts")
linked_counts = (OrcidToken.select(
OrcidToken.org.alias("org_id"),
fn.COUNT(fn.DISTINCT(OrcidToken.user)).alias("linked_user_count")).where(
OrcidToken.created_at.between(form.from_date.data, form.to_date.data)).group_by(
OrcidToken.org).alias("linked_counts"))
query = (Organisation.select(
Organisation.name,
fn.COALESCE(user_counts.c.user_count, 0).alias("user_count"),
fn.COALESCE(linked_counts.c.linked_user_count, 0).alias("linked_user_count")).join(
user_counts, on=(Organisation.id == user_counts.c.org_id)).join(
linked_counts, JOIN.LEFT_OUTER,
on=(Organisation.id == linked_counts.c.org_id)))
if sort == 1:
order_fields = [SQL("user_count"), SQL("linked_user_count"), ]
else:
order_fields = [Organisation.name, ]
if desc:
order_fields = [f.desc() for f in order_fields]
query = query.order_by(*order_fields)
total_user_count = sum(r.user_count for r in query if r.user_count)
total_linked_user_count = sum(r.linked_user_count for r in query if r.linked_user_count)
headers = [(h,
url_for(
"user_summary",
from_date=form.from_date.data,
to_date=form.to_date.data,
sort=i,
desc=1 if sort == i and not desc else 0))
for i, h in enumerate(["Name", "Linked User Count / User Count (%)"])]
return render_template(
"user_summary.html",
form=form,
query=query,
total_user_count=total_user_count,
total_linked_user_count=total_linked_user_count,
sort=sort, desc=desc,
headers=headers)
def __init__(self, synopsis, list_args, query_args, filter_args, parsed_args, uri):
# pylint: disable=singleton-comparison
query = (
SystemVulnerabilities
.select(fn.COALESCE(CveAccountData.status_id, 0).alias('cve_status_id'),
SystemPlatform.inventory_id,
SystemPlatform.display_name,
SystemPlatform.last_evaluation,
SystemPlatform.advisor_evaluated.alias('rules_evaluation'),
Status.id.alias('status_id'),
Status.name.alias('status_name'),
SystemVulnerabilities.status_text.alias('status_text'),
SystemVulnerabilities.rule_hit_details,
InsightsRule.name.alias('rule_id'),
InsightsRule.active.alias('rule_active'),
InsightsRule.description_text,
InsightsRule.reason_text,
InsightsRule.resolution_text,
InsightsRule.kbase_node_id,
InsightsRule.more_info_text)
.join(SystemPlatform, on=(SystemVulnerabilities.system_id == SystemPlatform.id))
.join(Status, on=(SystemVulnerabilities.status_id == Status.id))
.join(CveMetadata, on=(SystemVulnerabilities.cve_id == CveMetadata.id))
.join(RHAccount, on=(SystemPlatform.rh_account_id == RHAccount.id))
.join(CveAccountData, JOIN.LEFT_OUTER, on=((RHAccount.id == CveAccountData.rh_account_id)
& (CveMetadata.id == CveAccountData.cve_id)))
.join(InsightsRule, JOIN.LEFT_OUTER, on=(InsightsRule.id == SystemVulnerabilities.rule_id))
.where(CveMetadata.cve == synopsis)
.where(RHAccount.name == query_args['rh_account_number'])
.where((SystemVulnerabilities.when_mitigated.is_null(True)) | (InsightsRule.active == True))
.where((SystemPlatform.opt_out == False) & (SystemPlatform.stale == False)) # noqa: E712
)
if 'status_id' in filter_args and filter_args['status_id']:
query = query.where(Status.id << filter_args['status_id'])
if 'security_rule' in filter_args and filter_args['security_rule'] is not None:
if isinstance(filter_args['security_rule'], bool):
if filter_args['security_rule']:
query = query.where(InsightsRule.active == True) # noqa: E712
else:
query = query.where((InsightsRule.active == False) | (InsightsRule.active == None)) # noqa: E712
else:
query = query.where(InsightsRule.name % '%{}%'.format(filter_args['security_rule']))
query = query.dicts()
sortable_columns = {
'id': SystemPlatform.id,
'inventory_id': SystemPlatform.inventory_id,
'last_evaluation': SystemPlatform.last_evaluation,
'last_upload': SystemPlatform.last_upload,
'rules_evaluation': SystemPlatform.advisor_evaluated,
'status_id': Status.id,
'status': Status.id
}
default_sort_columns = ['-last_upload', 'id']
filterable_columns = {
'inventory_id': SystemPlatform.inventory_id,
'display_name': SystemPlatform.display_name
}
super(AffectedSystemsView, self).__init__(query, sortable_columns, default_sort_columns,
filterable_columns, list_args, parsed_args, uri)
