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)))).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 _build_update_condition(systems, status_cve_list):
# pylint: disable=singleton-comparison
return ((SystemVulnerabilities.system_id << systems) &
(SystemVulnerabilities.cve_id << (CveMetadata.select(
CveMetadata.id).where(CveMetadata.cve << status_cve_list)))
& ((SystemVulnerabilities.mitigation_reason.is_null(True)) |
(SystemVulnerabilities.rule_id << (InsightsRule.select(
InsightsRule.id).where(InsightsRule.active == True)))) &
((SystemVulnerabilities.when_mitigated.is_null(True)) |
(SystemVulnerabilities.rule_id <<
(InsightsRule.select(InsightsRule.id).where(
InsightsRule.active == True))) # noqa: E712
))
def handle_get(cls, **kwargs):
try:
rule_id = InsightsRule.select(InsightsRule.id).where(
InsightsRule.name == kwargs['rule_id']).get()
except DoesNotExist:
return cls.format_exception(
'Rule %s does not exist' % kwargs['rule_id'], 404)
data = Playbook.select(
Playbook.description, Playbook.play,
Playbook.version).where(Playbook.rule_id == rule_id).dicts()
return {'data': [playbook for playbook in data]} # pylint: disable=bad-option-value,unnecessary-comprehension
def handle_get(cls, **kwargs):
# pylint: disable=singleton-comparison, too-many-branches, too-many-statements
retval = {
'system_count': 0,
'cves_total': 0,
'cves_by_severity': {
'0to3.9': {
'percentage': 0,
'count': 0,
'known_exploit_count': 0
},
'4to7.9': {
'percentage': 0,
'count': 0,
'known_exploit_count': 0
},
'8to10': {
'percentage': 0,
'count': 0,
'known_exploit_count': 0
},
'na': {
'percentage': 0,
'count': 0,
'known_exploit_count': 0
}
},
'recent_cves': {
'last7days': 0,
'last30days': 0,
'last90days': 0
},
'rules_total': 0,
'rules_by_severity': {
1: {
'rule_count': 0,
'systems_affected': 0
},
2: {
'rule_count': 0,
'systems_affected': 0
},
3: {
'rule_count': 0,
'systems_affected': 0
},
4: {
'rule_count': 0,
'systems_affected': 0
},
},
'top_cves': [],
'top_rules': [],
}
rh_account, cve_cache_from, cve_cache_keepalive = get_account_data(
connexion.context['user'])
if rh_account is None:
return retval
retval['system_count'] = get_system_count(rh_account)
if retval['system_count'] == 0:
return retval
# API using cache, set keepalive for account to enable maintaining cache
update_cve_cache_keepalive(rh_account, cve_cache_keepalive)
# Use cache if not disabled + cache exists
if not DISABLE_ACCOUNT_CACHE and cve_cache_from:
count_query = (CveAccountCache.select(
CveAccountCache.cve_id.alias("cve_id_"),
CveAccountCache.systems_affected.alias("systems_affected_")).
where(CveAccountCache.rh_account_id == rh_account))
else:
count_query = (
SystemVulnerabilities.select(
SystemVulnerabilities.cve_id.alias("cve_id_"),
fn.Count(
SystemVulnerabilities.id).alias("systems_affected_")).
join(SystemPlatform,
on=(SystemVulnerabilities.system_id == SystemPlatform.id)
& (SystemPlatform.rh_account_id == rh_account) &
(SystemPlatform.opt_out == False) &
(SystemPlatform.stale == False) &
(SystemPlatform.when_deleted.is_null(True))).where(
SystemVulnerabilities.rh_account_id == rh_account).
where((SystemVulnerabilities.mitigation_reason.is_null(True))
| (SystemVulnerabilities.rule_id << InsightsRule.select(
InsightsRule.id).where((InsightsRule.active == False)
& (~InsightsRule.rule_only))))
.where((SystemVulnerabilities.when_mitigated.is_null(True))
| (SystemVulnerabilities.rule_id << InsightsRule.select(
InsightsRule.id).where(
(InsightsRule.active == True) &
(~InsightsRule.rule_only)))).group_by(
SystemVulnerabilities.cve_id))
count_query = cyndi_join(count_query)
cve_query = (CveMetadata.select(
CveMetadata.id.alias("cve_id"),
fn.COALESCE(CveMetadata.cvss3_score,
CveMetadata.cvss2_score).alias('cvss_score'),
CveMetadata.public_date, CveMetadata.exploits).join(
count_query,
JOIN.INNER,
on=(CveMetadata.id == count_query.c.cve_id_)).dicts())
cve_data = [(cve["cvss_score"], cve["public_date"], cve["exploits"])
for cve in cve_query]
cves_total = len(cve_data)
if cves_total == 0:
return retval
retval['cves_total'] = cves_total
today = datetime.now(timezone.utc).replace(
hour=0, minute=0, second=0, microsecond=0) # offset-aware
last7 = today - timedelta(days=7)
last30 = today - timedelta(days=30)
last90 = today - timedelta(days=90)
for cvss_score, public_date, exploits in cve_data:
if cvss_score is None:
retval["cves_by_severity"]["na"]["count"] += 1
if exploits:
retval["cves_by_severity"]["na"][
"known_exploit_count"] += 1
elif cvss_score < 4:
retval["cves_by_severity"]["0to3.9"]["count"] += 1
if exploits:
retval["cves_by_severity"]["0to3.9"][
"known_exploit_count"] += 1
elif 4 <= cvss_score < 8:
retval["cves_by_severity"]["4to7.9"]["count"] += 1
if exploits:
retval["cves_by_severity"]["4to7.9"][
"known_exploit_count"] += 1
elif cvss_score >= 8:
retval["cves_by_severity"]["8to10"]["count"] += 1
if exploits:
retval["cves_by_severity"]["8to10"][
"known_exploit_count"] += 1
if public_date is not None:
if public_date >= last7:
retval["recent_cves"]["last7days"] += 1
if public_date >= last30:
retval["recent_cves"]["last30days"] += 1
if public_date >= last90:
retval["recent_cves"]["last90days"] += 1
cve_count_by_severity = [
v['count'] for v in retval['cves_by_severity'].values()
]
rounded_percentage = round_to_100_percent(cve_count_by_severity)
for indx, keys in enumerate(retval['cves_by_severity']):
retval['cves_by_severity'][keys][
'percentage'] = rounded_percentage[indx]
# The algorithm searches for CVEs with cvss score between 8 and 10, and then sort by a number of affected
# systems if there are not 3 CVE in the 8 to 10 range, then it looks for CVEs in 4 to 8 range, sorted by a
# number of systems affected. The high-end range check is exclusive that is why 11 here.
cves_limit = 3
top_cves = cls._get_top_cves_by_cvss(8.0,
11,
count_query,
limit=cves_limit)
cls._build_top_cves(top_cves, retval)
cves_count = top_cves.count()
if cves_count < cves_limit:
next_tier_top = cls._get_top_cves_by_cvss(4.0,
8.0,
count_query,
limit=cves_limit -
cves_count)
cls._build_top_cves(next_tier_top, retval)
next_cves_count = next_tier_top.count()
if next_cves_count < (cves_limit - cves_count):
last_tier_top = cls._get_top_cves_by_cvss(
0.0,
4.0,
count_query,
limit=cves_limit - (cves_count + next_cves_count))
cls._build_top_cves(last_tier_top, retval)
rules_breakdown = (SystemVulnerabilities.select(
fn.COUNT(fn.Distinct(InsightsRule.id)).alias('rule_count'),
InsightsRule.rule_impact.alias('severity'),
fn.COUNT(fn.Distinct(
SystemVulnerabilities.system_id)).alias('systems_affected')
).join(
InsightsRule,
on=(SystemVulnerabilities.rule_id == InsightsRule.id)
).join(
SystemPlatform,
on=(SystemVulnerabilities.system_id == SystemPlatform.id) &
(SystemPlatform.rh_account_id == rh_account) &
(SystemPlatform.opt_out == False) & (SystemPlatform.stale == False)
& (SystemPlatform.when_deleted.is_null(True)) &
(SystemPlatform.last_evaluation.is_null(False)
| SystemPlatform.advisor_evaluated.is_null(False))).where(
SystemVulnerabilities.rh_account_id == rh_account).where(
(SystemVulnerabilities.mitigation_reason.is_null(True))
& (SystemVulnerabilities.rule_id << InsightsRule.select(
InsightsRule.id).where(
(InsightsRule.active == True)
& (~InsightsRule.rule_only)))).group_by(
InsightsRule.rule_impact).dicts())
rules_breakdown = cyndi_join(rules_breakdown)
for section in rules_breakdown:
retval['rules_by_severity'][
section['severity']]['rule_count'] = section['rule_count']
retval['rules_by_severity'][section['severity']][
'systems_affected'] = section['systems_affected']
retval['rules_total'] = sum(
[item['rule_count'] for item in rules_breakdown])
top_rules = (SystemVulnerabilities.select(
InsightsRule.name.alias('rule_id'),
InsightsRule.description_text.alias('name'),
InsightsRule.rule_impact.alias('severity'),
InsightsRule.summary_text.alias('description'),
fn.COUNT(fn.Distinct(
SystemVulnerabilities.system_id)).alias('systems_affected'),
fn.ARRAY_AGG(fn.Distinct(CveMetadata.cve)).alias('associated_cves')
).join(
InsightsRule,
on=(SystemVulnerabilities.rule_id == InsightsRule.id)).join(
CveRuleMapping,
on=(InsightsRule.id == CveRuleMapping.rule_id)).join(
CveMetadata, on=(CveRuleMapping.cve_id == CveMetadata.id)
).join(
SystemPlatform,
on=(SystemVulnerabilities.system_id == SystemPlatform.id) &
(SystemPlatform.rh_account_id == rh_account) &
(SystemPlatform.opt_out == False) &
(SystemPlatform.stale == False) &
(SystemPlatform.when_deleted.is_null(True)) &
(SystemPlatform.last_evaluation.is_null(False)
| SystemPlatform.advisor_evaluated.is_null(False))).where(
SystemVulnerabilities.rh_account_id == rh_account).
where((
SystemVulnerabilities.mitigation_reason.is_null(True))
& (SystemVulnerabilities.rule_id <<
InsightsRule.select(InsightsRule.id).where(
(InsightsRule.active == True)
& (~InsightsRule.rule_only)))).group_by(
InsightsRule.name,
InsightsRule.description_text,
InsightsRule.rule_impact,
InsightsRule.summary_text).order_by(
InsightsRule.rule_impact.desc(),
SQL('systems_affected desc'),
InsightsRule.description_text,
InsightsRule.name).limit(3).dicts())
top_rules = cyndi_join(top_rules)
for top_rule in top_rules:
retval['top_rules'].append(top_rule)
return retval
def handle_get(cls, **kwargs):
# pylint: disable=singleton-comparison, too-many-branches, too-many-statements
retval = {
'cves_total': 0,
'cves_by_severity': {
'0to3.9': {
'percentage': 0,
'count': 0,
'known_exploits': 0
},
'4to7.9': {
'percentage': 0,
'count': 0,
'known_exploits': 0
},
'8to10': {
'percentage': 0,
'count': 0,
'known_exploits': 0
},
'na': {
'percentage': 0,
'count': 0,
'known_exploits': 0
}
},
'exploited_cves_count': 0,
'system_count': 0,
'recent_cves': {
'last7days': 0,
'last30days': 0,
'last90days': 0
},
'recent_rules': [],
'rules_cves_total': 0,
}
args_desc = [
{'arg_name': 'tags', 'convert_func': parse_tags},
{'arg_name': 'sap_system', 'convert_func': None},
{'arg_name': 'sap_sids', 'convert_func': None}
]
args = cls._parse_arguments(kwargs, args_desc)
cyndi_request = is_cyndi_request(args)
rh_account, cve_cache_from, cve_cache_keepalive = get_account_data(connexion.context['user'])
retval['system_count'] = get_system_count(rh_account, True, [filter_types.SYSTEM_TAGS, filter_types.SYSTEM_SAP, filter_types.SYSTEM_SAP_SIDS], args)
# API using cache, set keepalive for account to enable maintaining cache
update_cve_cache_keepalive(rh_account, cve_cache_keepalive)
# Use cache if not disabled + systems are not filtered + cache exists
if not DISABLE_ACCOUNT_CACHE and not cyndi_request and cve_cache_from:
active_cves_subquery = (CveAccountCache
.select(CveAccountCache.cve_id.alias("cve_id_"))
.where(CveAccountCache.rh_account_id == rh_account))
else:
active_cves_subquery = (SystemVulnerabilities
.select(fn.Distinct(SystemVulnerabilities.cve_id).alias("cve_id_"))
.join(SystemPlatform, on=((SystemVulnerabilities.system_id == SystemPlatform.id) &
(SystemPlatform.rh_account_id == rh_account) &
(SystemPlatform.opt_out == False) & # noqa: E712
(SystemPlatform.stale == False) & # noqa: E712
(SystemPlatform.when_deleted.is_null(True)) &
(fn.COALESCE(SystemPlatform.host_type, 'null') != HostType.EDGE)))
.where(SystemVulnerabilities.rh_account_id == rh_account)
.where((SystemVulnerabilities.mitigation_reason.is_null(True)) |
(SystemVulnerabilities.rule_id << InsightsRule.select(InsightsRule.id)
.where((InsightsRule.active == False) & (~InsightsRule.rule_only))))
.where((SystemVulnerabilities.when_mitigated.is_null(True)) |
(SystemVulnerabilities.rule_id << InsightsRule.select(InsightsRule.id)
.where((InsightsRule.active == True) & (~InsightsRule.rule_only)))))
if cyndi_request:
active_cves_subquery = cyndi_join(active_cves_subquery)
active_cves_subquery = apply_filters(active_cves_subquery, args,
[filter_types.SYSTEM_TAGS, filter_types.SYSTEM_SAP, filter_types.SYSTEM_SAP_SIDS], {})
query = (CveMetadata
.select(CveMetadata.cve,
fn.COALESCE(CveMetadata.cvss3_score, CveMetadata.cvss2_score).alias('cvss_score'),
CveMetadata.public_date,
CveMetadata.id,
CveMetadata.exploits)
.join(active_cves_subquery, on=(CveMetadata.id == active_cves_subquery.c.cve_id_))
.dicts())
cve_data = [(cve["cvss_score"], cve["public_date"], cve["exploits"]) for cve in query]
retval["cves_total"] = len(cve_data)
retval["exploited_cves_count"] = len([row[2] for row in cve_data if row[2] is True])
today = datetime.now(timezone.utc).replace(hour=0, minute=0, second=0, microsecond=0) # offset-aware
last7 = today - timedelta(days=7)
last30 = today - timedelta(days=30)
last90 = today - timedelta(days=90)
rules_date = today - timedelta(days=CFG.dashboard_rules_age)
for cvss_score, public_date, exploit in cve_data:
if cvss_score is None:
retval["cves_by_severity"]["na"]["count"] += 1
if exploit:
retval["cves_by_severity"]["na"]["known_exploits"] += 1
elif cvss_score < 4:
retval["cves_by_severity"]["0to3.9"]["count"] += 1
if exploit:
retval["cves_by_severity"]["0to3.9"]["known_exploits"] += 1
elif 4 <= cvss_score < 8:
retval["cves_by_severity"]["4to7.9"]["count"] += 1
if exploit:
retval["cves_by_severity"]["4to7.9"]["known_exploits"] += 1
elif cvss_score >= 8:
retval["cves_by_severity"]["8to10"]["count"] += 1
if exploit:
retval["cves_by_severity"]["8to10"]["known_exploits"] += 1
if public_date is not None:
if public_date >= last7:
retval["recent_cves"]["last7days"] += 1
if public_date >= last30:
retval["recent_cves"]["last30days"] += 1
if public_date >= last90:
retval["recent_cves"]["last90days"] += 1
rounded_percentage = round_to_100_percent([v['count'] for v in retval['cves_by_severity'].values()])
for indx, keys in enumerate(retval['cves_by_severity']):
retval['cves_by_severity'][keys]['percentage'] = rounded_percentage[indx]
if not DISABLE_ACCOUNT_CACHE and not cyndi_request and cve_cache_from:
rules_breakdown = (CveAccountCache.select(fn.COUNT(fn.Distinct(CveAccountCache.cve_id)).alias("rules_cves_count"))
.join(CveRuleMapping, on=(CveAccountCache.cve_id == CveRuleMapping.cve_id))
.join(InsightsRule, on=((CveRuleMapping.rule_id == InsightsRule.id) & (InsightsRule.active == True) & (~InsightsRule.rule_only)))
.where(CveAccountCache.rh_account_id == rh_account))
else:
rules_breakdown = (SystemVulnerabilities.select(fn.COUNT(fn.Distinct(SystemVulnerabilities.cve_id)).alias('rules_cves_count'))
.join(CveRuleMapping, on=(SystemVulnerabilities.cve_id == CveRuleMapping.cve_id))
.join(InsightsRule, on=((CveRuleMapping.rule_id == InsightsRule.id) & (InsightsRule.active == True) & (~InsightsRule.rule_only)))
.join(SystemPlatform, on=((SystemVulnerabilities.system_id == SystemPlatform.id) & (SystemPlatform.rh_account_id == rh_account) &
(SystemPlatform.when_deleted.is_null(True)) &
(SystemPlatform.stale == False) & (SystemPlatform.opt_out == False) & # noqa: E712
(SystemPlatform.last_evaluation.is_null(False) |
SystemPlatform.advisor_evaluated.is_null(False)) &
(fn.COALESCE(SystemPlatform.host_type, 'null') != HostType.EDGE)))
.where(SystemVulnerabilities.rh_account_id == rh_account)
.where(SystemVulnerabilities.mitigation_reason.is_null(True))
)
if cyndi_request:
rules_breakdown = cyndi_join(rules_breakdown)
rules_breakdown = apply_filters(rules_breakdown, args, [filter_types.SYSTEM_TAGS, filter_types.SYSTEM_SAP, filter_types.SYSTEM_SAP_SIDS], {})
rules_breakdown = rules_breakdown.first()
retval['rules_cves_total'] = rules_breakdown.rules_cves_count
if not DISABLE_ACCOUNT_CACHE and not cyndi_request and cve_cache_from:
counts_query = (RuleAccountCache.select(RuleAccountCache.rule_id.alias("rule_id_"),
RuleAccountCache.systems_affected.alias("systems_affected_"))
.where(RuleAccountCache.rh_account_id == rh_account))
else:
counts_query = (SystemVulnerabilities
.select(SystemVulnerabilities.rule_id.alias("rule_id_"),
fn.Count(fn.Distinct(SystemVulnerabilities.system_id)).alias("systems_affected_"))
.join(SystemPlatform, on=((SystemVulnerabilities.system_id == SystemPlatform.id) &
(SystemPlatform.rh_account_id == rh_account) &
(SystemPlatform.opt_out == False) &
(SystemPlatform.stale == False) &
(SystemPlatform.when_deleted.is_null(True)) &
(fn.COALESCE(SystemPlatform.host_type, 'null') != HostType.EDGE)))
.where(SystemVulnerabilities.rh_account_id == rh_account)
.where((SystemVulnerabilities.rule_id << InsightsRule.select(InsightsRule.id).where((InsightsRule.active == True)
& (~InsightsRule.rule_only)))
& (SystemVulnerabilities.mitigation_reason.is_null(True)))
.group_by(SystemVulnerabilities.rule_id))
if cyndi_request:
counts_query = cyndi_join(counts_query)
counts_query = apply_filters(counts_query, args, [filter_types.SYSTEM_TAGS, filter_types.SYSTEM_SAP, filter_types.SYSTEM_SAP_SIDS], {})
recent_rules = (InsightsRule.select(InsightsRule.description_text.alias('name'),
InsightsRule.summary_text.alias('description'),
counts_query.c.systems_affected_.alias('systems_affected'),
InsightsRule.rule_impact.alias('severity'),
InsightsRule.kbase_node_id.alias('node_id'),
fn.ARRAY_AGG(fn.Distinct(CveMetadata.cve)).alias('associated_cves'),
InsightsRule.name.alias('id'),
InsightsRule.publish_date.alias('public_date'))
.join(CveRuleMapping, on=(InsightsRule.id == CveRuleMapping.rule_id))
.join(counts_query, on=(InsightsRule.id == counts_query.c.rule_id_))
.join(CveMetadata, on=(CveRuleMapping.cve_id == CveMetadata.id))
.where((InsightsRule.publish_date >= rules_date) & (InsightsRule.active == True) & (~InsightsRule.rule_only)) # noqa: E712
.group_by(InsightsRule.description_text, InsightsRule.publish_date, InsightsRule.rule_impact, InsightsRule.kbase_node_id,
SQL('systems_affected'), InsightsRule.name, InsightsRule.publish_date, InsightsRule.summary_text)
.order_by(InsightsRule.publish_date.desc(), InsightsRule.rule_impact, InsightsRule.description_text)
.dicts())
recent_rules = apply_filters(recent_rules, args, [], {"count_subquery": counts_query})
for rule in recent_rules:
retval['recent_rules'].append(rule)
return retval