def finding_querys(prod_type, request):
filters = dict()
findings_query = Finding.objects.filter(
verified=True,
severity__in=('Critical', 'High', 'Medium', 'Low', 'Info')
).prefetch_related(
'test__engagement__product', 'test__engagement__product__prod_type',
'test__engagement__risk_acceptance', 'risk_acceptance_set', 'reporter'
).extra(select={
'ra_count':
'SELECT COUNT(*) FROM dojo_risk_acceptance INNER JOIN '
'dojo_risk_acceptance_accepted_findings ON '
'( dojo_risk_acceptance.id = dojo_risk_acceptance_accepted_findings.risk_acceptance_id ) '
'WHERE dojo_risk_acceptance_accepted_findings.finding_id = dojo_finding.id',
}, )
active_findings_query = Finding.objects.filter(
verified=True,
active=True,
severity__in=('Critical', 'High', 'Medium', 'Low', 'Info')
).prefetch_related(
'test__engagement__product', 'test__engagement__product__prod_type',
'test__engagement__risk_acceptance', 'risk_acceptance_set', 'reporter'
).extra(select={
'ra_count':
'SELECT COUNT(*) FROM dojo_risk_acceptance INNER JOIN '
'dojo_risk_acceptance_accepted_findings ON '
'( dojo_risk_acceptance.id = dojo_risk_acceptance_accepted_findings.risk_acceptance_id ) '
'WHERE dojo_risk_acceptance_accepted_findings.finding_id = dojo_finding.id',
}, )
findings = MetricsFindingFilter(request.GET, queryset=findings_query)
active_findings = MetricsFindingFilter(request.GET,
queryset=active_findings_query)
findings_qs = queryset_check(findings)
active_findings_qs = queryset_check(active_findings)
if not findings_qs:
findings = findings_query
active_findings = active_findings_query
findings_qs = findings if isinstance(findings,
QuerySet) else findings.qs
active_findings_qs = active_findings if isinstance(
active_findings, QuerySet) else active_findings.qs
messages.add_message(
request,
messages.ERROR,
'All objects have been filtered away. Displaying all objects',
extra_tags='alert-danger')
start_date = findings_qs.earliest('date').date
start_date = datetime(start_date.year,
start_date.month,
start_date.day,
tzinfo=timezone.get_current_timezone())
end_date = findings_qs.latest('date').date
end_date = datetime(end_date.year,
end_date.month,
end_date.day,
tzinfo=timezone.get_current_timezone())
if len(prod_type) > 0:
findings_closed = Finding.objects.filter(
mitigated__date__range=[start_date, end_date],
test__engagement__product__prod_type__in=prod_type
).prefetch_related('test__engagement__product')
# capture the accepted findings in period
accepted_findings = Finding.objects.filter(risk_acceptance__created__date__range=[start_date, end_date],
test__engagement__product__prod_type__in=prod_type). \
prefetch_related('test__engagement__product')
accepted_findings_counts = Finding.objects.filter(risk_acceptance__created__date__range=[start_date, end_date],
test__engagement__product__prod_type__in=prod_type). \
prefetch_related('test__engagement__product')
accepted_findings_counts = severity_count(accepted_findings_counts,
'aggregate', 'severity')
else:
findings_closed = Finding.objects.filter(
mitigated__date__range=[start_date, end_date]).prefetch_related(
'test__engagement__product')
accepted_findings = Finding.objects.filter(risk_acceptance__created__date__range=[start_date, end_date]). \
prefetch_related('test__engagement__product')
accepted_findings_counts = Finding.objects.filter(risk_acceptance__created__date__range=[start_date, end_date]). \
prefetch_related('test__engagement__product')
accepted_findings_counts = severity_count(accepted_findings_counts,
'aggregate', 'severity')
r = relativedelta(end_date, start_date)
months_between = (r.years * 12) + r.months
# include current month
months_between += 1
weeks_between = int(
ceil((((r.years * 12) + r.months) * 4.33) + (r.days / 7)))
if weeks_between <= 0:
weeks_between += 2
monthly_counts = get_period_counts(active_findings_qs,
findings_qs,
findings_closed,
accepted_findings,
months_between,
start_date,
relative_delta='months')
weekly_counts = get_period_counts(active_findings_qs,
findings_qs,
findings_closed,
accepted_findings,
weeks_between,
start_date,
relative_delta='weeks')
top_ten = Product.objects.filter(
engagement__test__finding__verified=True,
engagement__test__finding__false_p=False,
engagement__test__finding__duplicate=False,
engagement__test__finding__out_of_scope=False,
engagement__test__finding__mitigated__isnull=True,
engagement__test__finding__severity__in=('Critical', 'High', 'Medium',
'Low'),
prod_type__in=prod_type)
top_ten = severity_count(top_ten, 'annotate',
'engagement__test__finding__severity').order_by(
'-critical', '-high', '-medium', '-low')[:10]
filters['all'] = findings
filters['closed'] = findings_closed
filters['accepted'] = accepted_findings
filters['accepted_count'] = accepted_findings_counts
filters['top_ten'] = top_ten
filters['monthly_counts'] = monthly_counts
filters['weekly_counts'] = weekly_counts
filters['weeks_between'] = weeks_between
filters['start_date'] = start_date
filters['end_date'] = end_date
return filters
def metrics(request, mtype):
template = 'dojo/metrics.html'
page_name = 'Product Type Metrics'
show_pt_filter = True
sql_age_query = ""
if "postgresql" in settings.DATABASES["default"]["ENGINE"]:
sql_age_query = """SELECT (CASE WHEN (dojo_finding.mitigated IS NULL)
THEN DATE_PART(\'day\', date::timestamp - dojo_finding.date::timestamp)
ELSE DATE_PART(\'day\', dojo_finding.mitigated::timestamp - dojo_finding.date::timestamp) END)"""
else:
sql_age_query = """SELECT IF(dojo_finding.mitigated IS NULL, DATEDIFF(CURDATE(), dojo_finding.date),
DATEDIFF(dojo_finding.mitigated, dojo_finding.date))"""
findings = Finding.objects.filter(
verified=True,
severity__in=('Critical', 'High', 'Medium', 'Low', 'Info')
).prefetch_related(
'test__engagement__product', 'test__engagement__product__prod_type',
'test__engagement__risk_acceptance', 'risk_acceptance_set', 'reporter'
).extra(select={
'ra_count':
'SELECT COUNT(*) FROM dojo_risk_acceptance INNER JOIN '
'dojo_risk_acceptance_accepted_findings ON '
'( dojo_risk_acceptance.id = dojo_risk_acceptance_accepted_findings.risk_acceptance_id ) '
'WHERE dojo_risk_acceptance_accepted_findings.finding_id = dojo_finding.id',
"sql_age":
sql_age_query
}, )
active_findings = Finding.objects.filter(
verified=True,
active=True,
severity__in=('Critical', 'High', 'Medium', 'Low', 'Info')
).prefetch_related(
'test__engagement__product', 'test__engagement__product__prod_type',
'test__engagement__risk_acceptance', 'risk_acceptance_set', 'reporter'
).extra(select={
'ra_count':
'SELECT COUNT(*) FROM dojo_risk_acceptance INNER JOIN '
'dojo_risk_acceptance_accepted_findings ON '
'( dojo_risk_acceptance.id = dojo_risk_acceptance_accepted_findings.risk_acceptance_id ) '
'WHERE dojo_risk_acceptance_accepted_findings.finding_id = dojo_finding.id',
"sql_age":
sql_age_query
}, )
if mtype != 'All':
pt = Product_Type.objects.filter(id=mtype)
request.GET._mutable = True
request.GET.appendlist('test__engagement__product__prod_type', mtype)
request.GET._mutable = False
mtype = pt[0].name
show_pt_filter = False
page_name = '%s Metrics' % mtype
prod_type = pt
elif 'test__engagement__product__prod_type' in request.GET:
prod_type = Product_Type.objects.filter(id__in=request.GET.getlist(
'test__engagement__product__prod_type', []))
else:
prod_type = Product_Type.objects.all()
findings = MetricsFindingFilter(request.GET, queryset=findings)
active_findings = MetricsFindingFilter(request.GET,
queryset=active_findings)
findings.qs # this is needed to load details from filter since it is lazy
active_findings.qs # this is needed to load details from filter since it is lazy
start_date = findings.filters['date'].start_date
start_date = datetime(start_date.year,
start_date.month,
start_date.day,
tzinfo=timezone.get_current_timezone())
end_date = findings.filters['date'].end_date
end_date = datetime(end_date.year,
end_date.month,
end_date.day,
tzinfo=timezone.get_current_timezone())
if len(prod_type) > 0:
findings_closed = Finding.objects.filter(
mitigated__range=[start_date, end_date],
test__engagement__product__prod_type__in=prod_type
).prefetch_related('test__engagement__product')
# capture the accepted findings in period
accepted_findings = Finding.objects.filter(risk_acceptance__created__range=[start_date, end_date],
test__engagement__product__prod_type__in=prod_type). \
prefetch_related('test__engagement__product')
accepted_findings_counts = Finding.objects.filter(risk_acceptance__created__range=[start_date, end_date],
test__engagement__product__prod_type__in=prod_type). \
prefetch_related('test__engagement__product').aggregate(
total=Sum(
Case(When(severity__in=('Critical', 'High', 'Medium', 'Low'),
then=Value(1)),
output_field=IntegerField())),
critical=Sum(
Case(When(severity='Critical',
then=Value(1)),
output_field=IntegerField())),
high=Sum(
Case(When(severity='High',
then=Value(1)),
output_field=IntegerField())),
medium=Sum(
Case(When(severity='Medium',
then=Value(1)),
output_field=IntegerField())),
low=Sum(
Case(When(severity='Low',
then=Value(1)),
output_field=IntegerField())),
info=Sum(
Case(When(severity='Info',
then=Value(1)),
output_field=IntegerField())),
)
else:
findings_closed = Finding.objects.filter(
mitigated__range=[start_date, end_date]).prefetch_related(
'test__engagement__product')
accepted_findings = Finding.objects.filter(risk_acceptance__created__range=[start_date, end_date]). \
prefetch_related('test__engagement__product')
accepted_findings_counts = Finding.objects.filter(risk_acceptance__created__range=[start_date, end_date]). \
prefetch_related('test__engagement__product').aggregate(
total=Sum(
Case(When(severity__in=('Critical', 'High', 'Medium', 'Low'),
then=Value(1)),
output_field=IntegerField())),
critical=Sum(
Case(When(severity='Critical',
then=Value(1)),
output_field=IntegerField())),
high=Sum(
Case(When(severity='High',
then=Value(1)),
output_field=IntegerField())),
medium=Sum(
Case(When(severity='Medium',
then=Value(1)),
output_field=IntegerField())),
low=Sum(
Case(When(severity='Low',
then=Value(1)),
output_field=IntegerField())),
info=Sum(
Case(When(severity='Info',
then=Value(1)),
output_field=IntegerField())),
)
r = relativedelta(end_date, start_date)
months_between = (r.years * 12) + r.months
# include current month
months_between += 1
weeks_between = int(
ceil((((r.years * 12) + r.months) * 4.33) + (r.days / 7)))
if weeks_between <= 0:
weeks_between += 2
monthly_counts = get_period_counts(active_findings.qs,
findings.qs,
findings_closed,
accepted_findings,
months_between,
start_date,
relative_delta='months')
weekly_counts = get_period_counts(active_findings.qs,
findings.qs,
findings_closed,
accepted_findings,
weeks_between,
start_date,
relative_delta='weeks')
top_ten = Product.objects.filter(
engagement__test__finding__verified=True,
engagement__test__finding__false_p=False,
engagement__test__finding__duplicate=False,
engagement__test__finding__out_of_scope=False,
engagement__test__finding__mitigated__isnull=True,
engagement__test__finding__severity__in=('Critical', 'High', 'Medium',
'Low'),
prod_type__in=prod_type).annotate(
critical=Sum(
Case(When(engagement__test__finding__severity='Critical',
then=Value(1)),
output_field=IntegerField())),
high=Sum(
Case(When(engagement__test__finding__severity='High',
then=Value(1)),
output_field=IntegerField())),
medium=Sum(
Case(When(engagement__test__finding__severity='Medium',
then=Value(1)),
output_field=IntegerField())),
low=Sum(
Case(When(engagement__test__finding__severity='Low',
then=Value(1)),
output_field=IntegerField())),
total=Sum(
Case(When(engagement__test__finding__severity__in=('Critical',
'High',
'Medium',
'Low'),
then=Value(1)),
output_field=IntegerField()))).order_by(
'-critical', '-high', '-medium', '-low')[:10]
age_detail = [0, 0, 0, 0]
in_period_counts = {
"Critical": 0,
"High": 0,
"Medium": 0,
"Low": 0,
"Info": 0,
"Total": 0
}
in_period_details = {}
closed_in_period_counts = {
"Critical": 0,
"High": 0,
"Medium": 0,
"Low": 0,
"Info": 0,
"Total": 0
}
closed_in_period_details = {}
accepted_in_period_details = {}
for finding in findings.qs:
if 0 <= finding.sql_age <= 30:
age_detail[0] += 1
elif 30 < finding.sql_age <= 60:
age_detail[1] += 1
elif 60 < finding.sql_age <= 90:
age_detail[2] += 1
elif finding.sql_age > 90:
age_detail[3] += 1
in_period_counts[finding.severity] += 1
in_period_counts['Total'] += 1
if finding.test.engagement.product.name not in in_period_details:
in_period_details[finding.test.engagement.product.name] = {
'path':
reverse('view_product_findings',
args=(finding.test.engagement.product.id, )),
'Critical':
0,
'High':
0,
'Medium':
0,
'Low':
0,
'Info':
0,
'Total':
0
}
in_period_details[finding.test.engagement.product.name][
finding.severity] += 1
in_period_details[finding.test.engagement.product.name]['Total'] += 1
for finding in accepted_findings:
if finding.test.engagement.product.name not in accepted_in_period_details:
accepted_in_period_details[
finding.test.engagement.product.name] = {
'path':
reverse('accepted_findings') +
'?test__engagement__product=' +
str(finding.test.engagement.product.id),
'Critical':
0,
'High':
0,
'Medium':
0,
'Low':
0,
'Info':
0,
'Total':
0
}
accepted_in_period_details[finding.test.engagement.product.name][
finding.severity] += 1
accepted_in_period_details[
finding.test.engagement.product.name]['Total'] += 1
for f in findings_closed:
closed_in_period_counts[f.severity] += 1
closed_in_period_counts['Total'] += 1
if f.test.engagement.product.name not in closed_in_period_details:
closed_in_period_details[f.test.engagement.product.name] = {
'path':
reverse('closed_findings') + '?test__engagement__product=' +
str(f.test.engagement.product.id),
'Critical':
0,
'High':
0,
'Medium':
0,
'Low':
0,
'Info':
0,
'Total':
0
}
closed_in_period_details[f.test.engagement.product.name][
f.severity] += 1
closed_in_period_details[f.test.engagement.product.name]['Total'] += 1
punchcard = list()
ticks = list()
highest_count = 0
if 'view' in request.GET and 'dashboard' == request.GET['view']:
punchcard, ticks, highest_count = get_punchcard_data(
findings.qs, weeks_between, start_date)
page_name = (get_system_setting('team_name')) + " Metrics"
template = 'dojo/dashboard-metrics.html'
add_breadcrumb(title=page_name,
top_level=not len(request.GET),
request=request)
return render(
request, template, {
'name': page_name,
'start_date': start_date,
'end_date': end_date,
'findings': findings,
'opened_per_month': monthly_counts['opened_per_period'],
'active_per_month': monthly_counts['active_per_period'],
'opened_per_week': weekly_counts['opened_per_period'],
'accepted_per_month': monthly_counts['accepted_per_period'],
'accepted_per_week': weekly_counts['accepted_per_period'],
'top_ten_products': top_ten,
'age_detail': age_detail,
'in_period_counts': in_period_counts,
'in_period_details': in_period_details,
'accepted_in_period_counts': accepted_findings_counts,
'accepted_in_period_details': accepted_in_period_details,
'closed_in_period_counts': closed_in_period_counts,
'closed_in_period_details': closed_in_period_details,
'punchcard': punchcard,
'ticks': ticks,
'highest_count': highest_count,
'show_pt_filter': show_pt_filter,
})
def finding_querys(prod_type, request):
findings_query = Finding.objects.filter(
verified=True,
severity__in=('Critical', 'High', 'Medium', 'Low', 'Info')
).select_related(
'reporter',
'test',
'test__engagement__product',
'test__engagement__product__prod_type',
).prefetch_related(
'risk_acceptance_set',
'test__engagement__risk_acceptance',
'test__test_type',
)
findings_query = get_authorized_findings(Permissions.Finding_View, findings_query, request.user)
active_findings_query = Finding.objects.filter(
verified=True,
active=True,
severity__in=('Critical', 'High', 'Medium', 'Low', 'Info')
).select_related(
'reporter',
'test',
'test__engagement__product',
'test__engagement__product__prod_type',
).prefetch_related(
'risk_acceptance_set',
'test__engagement__risk_acceptance',
'test__test_type',
)
active_findings_query = get_authorized_findings(Permissions.Finding_View, active_findings_query, request.user)
findings = MetricsFindingFilter(request.GET, queryset=findings_query)
active_findings = MetricsFindingFilter(request.GET, queryset=active_findings_query)
findings_qs = queryset_check(findings)
active_findings_qs = queryset_check(active_findings)
if not findings_qs and not findings_query:
findings = findings_query
active_findings = active_findings_query
findings_qs = findings if isinstance(findings, QuerySet) else findings.qs
active_findings_qs = active_findings if isinstance(active_findings, QuerySet) else active_findings.qs
messages.add_message(request,
messages.ERROR,
'All objects have been filtered away. Displaying all objects',
extra_tags='alert-danger')
try:
start_date = findings_qs.earliest('date').date
start_date = datetime(start_date.year,
start_date.month, start_date.day,
tzinfo=timezone.get_current_timezone())
end_date = findings_qs.latest('date').date
end_date = datetime(end_date.year,
end_date.month, end_date.day,
tzinfo=timezone.get_current_timezone())
except:
start_date = timezone.now()
end_date = timezone.now()
if len(prod_type) > 0:
findings_closed = Finding.objects.filter(mitigated__date__range=[start_date, end_date],
test__engagement__product__prod_type__in=prod_type).prefetch_related(
'test__engagement__product')
# capture the accepted findings in period
accepted_findings = Finding.objects.filter(risk_accepted=True, date__range=[start_date, end_date],
test__engagement__product__prod_type__in=prod_type). \
prefetch_related('test__engagement__product')
accepted_findings_counts = Finding.objects.filter(risk_accepted=True, date__range=[start_date, end_date],
test__engagement__product__prod_type__in=prod_type). \
prefetch_related('test__engagement__product')
else:
findings_closed = Finding.objects.filter(mitigated__date__range=[start_date, end_date]).prefetch_related(
'test__engagement__product')
accepted_findings = Finding.objects.filter(risk_accepted=True, date__range=[start_date, end_date]). \
prefetch_related('test__engagement__product')
accepted_findings_counts = Finding.objects.filter(risk_accepted=True, date__range=[start_date, end_date]). \
prefetch_related('test__engagement__product')
findings_closed = get_authorized_findings(Permissions.Finding_View, findings_closed, request.user)
accepted_findings = get_authorized_findings(Permissions.Finding_View, accepted_findings, request.user)
accepted_findings_counts = get_authorized_findings(Permissions.Finding_View, accepted_findings_counts, request.user)
accepted_findings_counts = severity_count(accepted_findings_counts, 'aggregate', 'severity')
r = relativedelta(end_date, start_date)
months_between = (r.years * 12) + r.months
# include current month
months_between += 1
weeks_between = int(ceil((((r.years * 12) + r.months) * 4.33) + (r.days / 7)))
if weeks_between <= 0:
weeks_between += 2
monthly_counts = get_period_counts(active_findings_qs, findings_qs, findings_closed, accepted_findings, months_between, start_date,
relative_delta='months')
weekly_counts = get_period_counts(active_findings_qs, findings_qs, findings_closed, accepted_findings, weeks_between, start_date,
relative_delta='weeks')
top_ten = get_authorized_products(Permissions.Product_View)
top_ten = top_ten.filter(engagement__test__finding__verified=True,
engagement__test__finding__false_p=False,
engagement__test__finding__duplicate=False,
engagement__test__finding__out_of_scope=False,
engagement__test__finding__mitigated__isnull=True,
engagement__test__finding__severity__in=(
'Critical', 'High', 'Medium', 'Low'),
prod_type__in=prod_type)
top_ten = severity_count(top_ten, 'annotate', 'engagement__test__finding__severity').order_by('-critical', '-high', '-medium', '-low')[:10]
return {
'all': findings,
'closed': findings_closed,
'accepted': accepted_findings,
'accepted_count': accepted_findings_counts,
'top_ten': top_ten,
'monthly_counts': monthly_counts,
'weekly_counts': weekly_counts,
'weeks_between': weeks_between,
'start_date': start_date,
'end_date': end_date,
}