diff options
Diffstat (limited to 'lib/srtgui/reports.py')
| -rw-r--r-- | lib/srtgui/reports.py | 407 |
1 files changed, 367 insertions, 40 deletions
diff --git a/lib/srtgui/reports.py b/lib/srtgui/reports.py index 715c5606..3a7414c6 100644 --- a/lib/srtgui/reports.py +++ b/lib/srtgui/reports.py @@ -22,6 +22,10 @@ import os import logging from datetime import datetime, timedelta import csv +from openpyxl import Workbook +from openpyxl import load_workbook +from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment +from openpyxl.utils import get_column_letter from orm.models import Cve, CveSource, Vulnerability, Investigation, Defect, Product from orm.models import Package @@ -34,7 +38,7 @@ from django.db.models import Q logger = logging.getLogger("srt") -SRT_BASE_DIR = os.environ['SRT_BASE_DIR'] +SRT_BASE_DIR = os.environ.get('SRT_BASE_DIR', '.') SRT_REPORT_DIR = '%s/reports' % SRT_BASE_DIR # quick development/debugging support @@ -52,6 +56,54 @@ def _log_args(msg, *args, **kwargs): s += ')' _log(s) +############################################################################### +# Excel/openpyxl common look and feel formatting objects +# + +#pyxl_border_all = Border(left=thin, right=thin, top=thin, bottom=thin) # , outline=True) +pyxl_thin = Side(border_style="thin") +pyxl_double = Side(border_style="double") +pyxl_border_left = Border(left=pyxl_thin) +pyxl_border_bottom = Border(bottom=pyxl_thin) +pyxl_border_bottom_left = Border(bottom=pyxl_thin, left=pyxl_thin) +pyxl_alignment_left = Alignment(horizontal='left') +pyxl_alignment_right = Alignment(horizontal='right') +pyxl_alignment_wrap = Alignment(wrap_text=True) +pyxl_font_bold = Font(bold=True) +pyxl_font_red = Font(color="A00000",bold=True,size = "13") +pyxl_font_grn = Font(color="00A000",bold=True,size = "13") +pyxl_font_blu = Font(color="0000A0",bold=True,size = "13") +pyxl_font_orn = Font(color="FF6600",bold=True,size = "13") +pyxl_fill_green = PatternFill(start_color="E0FFF0", end_color="E0FFF0", fill_type = "solid") +# Warning: the form "PatternFill(bgColor="xxxxxx", fill_type = "solid")" returns black cells +pyxl_backcolor_red = PatternFill(start_color='FCCDBA', end_color='FCCDBA', fill_type = "solid") +pyxl_backcolor_orn = PatternFill(start_color='FBEAAB', end_color='FBEAAB', fill_type = "solid") +pyxl_backcolor_yel = PatternFill(start_color='FCFDC7', end_color='FCFDC7', fill_type = "solid") +pyxl_backcolor_blu = PatternFill(start_color='C5E2FF', end_color='C5E2FF', fill_type = "solid") +pyxl_backcolor_grn = PatternFill(start_color='D6EDBD', end_color='D6EDBD', fill_type = "solid") +pyxl_cve_fills = [pyxl_backcolor_red,pyxl_backcolor_orn,pyxl_backcolor_yel,pyxl_backcolor_blu,None,None,None] + +def pyxl_write_cell(ws,row_num,column_num,value,border=None,font=None,fill=None,alignment=None): + cell = ws.cell(row=row_num, column=column_num) + try: + cell.value = value + if fill: + cell.fill = fill + if alignment: + cell.alignment = alignment + if border: + cell.border = border + if font: + cell.font = font + except Exception as e: + print("ERROR:(%d,%d):%s" % (row_num,column_num,e)) + # Optional next column return value + return(column_num+1) + +############################################################################### +# Core report support +# + class Report(): def __init__(self, parent_page, *args, **kwargs): self.parent_page = parent_page @@ -679,6 +731,8 @@ class CvesReport(Report): context['report_type_list'] = '\ <option value="summary">CVEs Table</option> \ + <option value="year_pub_summary">CVE by Year Prefix Summary</option> \ + <option value="year_summary">CVE by Publish Date Summary</option> \ <option value="cve_defects">CVE to Defects Table</option> \ ' context['report_get_title'] = '' @@ -692,7 +746,14 @@ class CvesReport(Report): ' context['report_format_list'] = '\ <input type="radio" name="format" value="txt" checked> Text (comma delimited)<br> \ - <input type="radio" name="format" value="csv"> CSV (tab delimited)<br> \ + <input type="radio" name="format" value="csv"> CSV \ + (Separator: \ + <select name="csv_separator"> \ + <option value="comma" checked>Comma</option> \ + <option value="semi">Semi-colon</option> \ + <option value="tab">Tab</option> \ + <br> \ + </select>) \ ' context['report_custom_list'] = '\ CVE name filter = <input type="text" placeholder="e.g. CVE-2018" name="name_filter" size="40"> <br>\ @@ -877,17 +938,20 @@ class CvesReport(Report): request_POST = self.request.POST - range = request_POST.get('range', '') + range_rec = request_POST.get('range', '') columns = request_POST.get('columns', '') format = request_POST.get('format', '') title = request_POST.get('title', '') report_type = request_POST.get('report_type', '') record_list = request_POST.get('record_list', '') name_filter = request_POST.get('name_filter', '').upper() + csv_separator = request_POST.get('csv_separator', 'semi') report_name = '%s/cves_%s_%s.%s' % (SRT_REPORT_DIR,report_type,datetime.today().strftime('%Y%m%d_%H%M'),format) if 'csv' == format: - delimiter = '\t' + delimiter = ';' + if csv_separator == 'comma': delimiter = ',' + if csv_separator == 'tab': delimiter = '\t' else: delimiter = ',' @@ -896,14 +960,14 @@ class CvesReport(Report): quotechar='"', quoting=csv.QUOTE_MINIMAL) if ('summary' == report_type): self.print_row_summary(writer,True,"all" == columns,None) - if 'displayed' == range: + if 'displayed' == range_rec: for id in record_list.split(','): if not id: continue cve = Cve.objects.get(id=id) if not name_filter or (name_filter in cve.name): self.print_row_summary(writer,False,"all" == columns,cve) - elif 'all' == range: + elif 'all' == range_rec: if name_filter: query = Cve.objects.filter(name__contains=name_filter).order_by('name') else: @@ -913,14 +977,14 @@ class CvesReport(Report): if ('cve_defects' == report_type): self.print_row_cve_defects(writer,'header',"all" == columns,None,None,None,None) - if 'displayed' == range: + if 'displayed' == range_rec: for id in record_list.split(','): if not id: continue cve = Cve.objects.get(id=id) if not name_filter or (name_filter in cve.name): self.print_row_cve_defects(writer,'cve',"all" == columns,cve,None,None,None) - elif 'all' == range: + elif 'all' == range_rec: if name_filter: query = Cve.objects.filter(name__contains=name_filter).order_by('name') else: @@ -928,6 +992,114 @@ class CvesReport(Report): for cve in query: self.print_row_cve_defects(writer,'line',"all" == columns,cve,None,None,None) + if report_type in ['year_summary','year_pub_summary']: + columns = ["Year", "CVE_Total", "CVE_HIST", "CVE_NEW", "CVE_RES", "CVE_INV", "CVE_VUL", "CVE_NVUL", "Defect_Total", "DEFECT_HIST", "DEFECT_NEW", "DEFECT_RES", "DEFECT_INV", "DEFECT_VUL", "DEFECT_NVUL","BY_PUBLISH"] + for i,column in enumerate(columns): + csvfile.write("%s%s" % (columns[i],delimiter)) + csvfile.write("\n") + + summary = {} + YEAR_START = 1999 + YEAR_STOP = 2020 + for the_year in range(YEAR_START,YEAR_STOP+1): + summary[the_year] = { + 'CVE_TOTAL':0, + 'CVE_HISTORICAL':0, + 'CVE_NEW':0, + 'CVE_NEW_RESERVED':0, + 'CVE_INVESTIGATE':0, + 'CVE_VULNERABLE':0, + 'CVE_NOT_VULNERABLE':0, + 'DEFECT_TOTAL':0, + 'DEFECT_HISTORICAL':0, + 'DEFECT_NEW':0, + 'DEFECT_NEW_RESERVED':0, + 'DEFECT_INVESTIGATE':0, + 'DEFECT_VULNERABLE':0, + 'DEFECT_NOT_VULNERABLE':0, + 'PUBLISH_DATE':0, + } + + # Gather historgram on CVE status + error_count = 0 + for cve in Cve.objects.all(): + # Extract the year created + if (report_type == 'year_pub_summary') and (not cve.status in [SRTool.HISTORICAL]) and cve.publishedDate: + the_year = cve.publishedDate.split('-')[0] + summary[the_year]['PUBLISH_DATE'] += 1 + else: + the_year = cve.name.split('-')[1] + + if (not the_year[0].isdigit()) or (the_year < '1999') or (the_year > '2020'): + if 10 > error_count: + _log('FOO_CVE_YEARLY:%s,%s' % (cve.name, cve.publishedDate)) + error_count += 1 + continue + the_year = int(the_year) + + # Register the CVE status + summary[the_year]['CVE_TOTAL'] += 1 + if cve.status in [SRTool.HISTORICAL]: + summary[the_year]['CVE_HISTORICAL'] += 1 + if cve.status in [SRTool.NEW,SRTool.NEW_INACTIVE]: + summary[the_year]['CVE_NEW'] += 1 + if cve.status in [SRTool.NEW_RESERVED]: + summary[the_year]['CVE_NEW_RESERVED'] += 1 + if cve.status in [SRTool.INVESTIGATE,SRTool.INVESTIGATE_INACTIVE]: + summary[the_year]['CVE_INVESTIGATE'] += 1 + if cve.status in [SRTool.VULNERABLE,SRTool.VULNERABLE_INACTIVE]: + summary[the_year]['CVE_VULNERABLE'] += 1 + if cve.status in [SRTool.NOT_VULNERABLE,SRTool.NOT_VULNERABLE_INACTIVE]: + summary[the_year]['CVE_NOT_VULNERABLE'] += 1 + + # Register the releated defects status + for cv in cve.cve_to_vulnerability.all(): + for investigation in cv.vulnerability.vulnerability_investigation.all(): + for id in investigation.investigation_to_defect.all(): + + # Only check defects for current and previously active products + if not id.product.get_product_tag('mode') in ['support','develop','eol']: + continue + + # Register the defect status + summary[the_year]['DEFECT_TOTAL'] += 1 + if id.defect.srt_status in [SRTool.HISTORICAL]: + summary[the_year]['DEFECT_HISTORICAL'] += 1 + if id.defect.srt_status in [SRTool.NEW,SRTool.NEW_INACTIVE]: + summary[the_year]['DEFECT_NEW'] += 1 + if id.defect.srt_status in [SRTool.NEW_RESERVED]: + summary[the_year]['DEFECT_NEW_RESERVED'] += 1 + if id.defect.srt_status in [SRTool.INVESTIGATE,SRTool.INVESTIGATE_INACTIVE]: + summary[the_year]['DEFECT_INVESTIGATE'] += 1 + if id.defect.srt_status in [SRTool.VULNERABLE,SRTool.VULNERABLE_INACTIVE]: + summary[the_year]['DEFECT_VULNERABLE'] += 1 + if id.defect.srt_status in [SRTool.NOT_VULNERABLE,SRTool.NOT_VULNERABLE_INACTIVE]: + summary[the_year]['DEFECT_NOT_VULNERABLE'] += 1 + + # Print historgram + for the_year in range(YEAR_START,YEAR_STOP+1): + csvfile.write("%s%s" % (the_year,delimiter)) + + csvfile.write("%s%s" % (summary[the_year]['CVE_TOTAL'],delimiter)) + csvfile.write("%s%s" % (summary[the_year]['CVE_HISTORICAL'],delimiter)) + csvfile.write("%s%s" % (summary[the_year]['CVE_NEW'],delimiter)) + csvfile.write("%s%s" % (summary[the_year]['CVE_NEW_RESERVED'],delimiter)) + csvfile.write("%s%s" % (summary[the_year]['CVE_INVESTIGATE'],delimiter)) + csvfile.write("%s%s" % (summary[the_year]['CVE_VULNERABLE'],delimiter)) + csvfile.write("%s%s" % (summary[the_year]['CVE_NOT_VULNERABLE'],delimiter)) + + csvfile.write("%s%s" % (summary[the_year]['DEFECT_TOTAL'],delimiter)) + csvfile.write("%s%s" % (summary[the_year]['DEFECT_HISTORICAL'],delimiter)) + csvfile.write("%s%s" % (summary[the_year]['DEFECT_NEW'],delimiter)) + csvfile.write("%s%s" % (summary[the_year]['DEFECT_NEW_RESERVED'],delimiter)) + csvfile.write("%s%s" % (summary[the_year]['DEFECT_INVESTIGATE'],delimiter)) + csvfile.write("%s%s" % (summary[the_year]['DEFECT_VULNERABLE'],delimiter)) + csvfile.write("%s%s" % (summary[the_year]['DEFECT_NOT_VULNERABLE'],delimiter)) + + csvfile.write("%s%s" % (summary[the_year]['PUBLISH_DATE'],delimiter)) + csvfile.write("\n") + + return report_name,os.path.basename(report_name) class SelectCvesReport(Report): @@ -1002,6 +1174,8 @@ class SelectCvesReport(Report): file.write("%s%s" % (cve.description,tab)) file.write("\n") + + return report_name,os.path.basename(report_name) class VulnerabilitiesReport(Report): @@ -1458,8 +1632,72 @@ class DefectsReport(Report): return report_name,os.path.basename(report_name) +# +# Products Reports +# + +product_summary = {} + +def scan_product_jira(product): + global product_summary + + # Totals + critical_count = 0 + high_count = 0 + medium_count = 0 + low_count = 0 + p1_count = 0 + p2_count = 0 + p3_count = 0 + p4_count = 0 + px_count = 0 + unresolved_count = 0 + resolved_count = 0 + fixed_count = 0 + wontfix_count = 0 + withdrawn_count = 0 + rejected_count = 0 + + # Scan the registered defects + queryset = product.product_defect.all() + for defect in queryset: + if Defect.CRITICAL == defect.srt_priority: critical_count += 1 + elif Defect.HIGH == defect.srt_priority: high_count += 1 + elif Defect.MEDIUM == defect.srt_priority: medium_count += 1 + elif Defect.LOW == defect.srt_priority: low_count += 1 + if Defect.DEFECT_CRITICAL == defect.priority: p1_count += 1 + elif Defect.DEFECT_HIGH == defect.priority: p2_count += 1 + elif Defect.DEFECT_MEDIUM == defect.priority: p3_count += 1 + elif Defect.DEFECT_LOW == defect.priority: p4_count += 1 + if Defect.DEFECT_UNRESOLVED == defect.resolution: unresolved_count += 1 + elif Defect.DEFECT_RESOLVED == defect.resolution: resolved_count += 1 + elif Defect.DEFECT_FIXED == defect.resolution: fixed_count += 1 + elif Defect.DEFECT_WILL_NOT_FIX == defect.resolution: wontfix_count += 1 + elif Defect.DEFECT_WITHDRAWN == defect.resolution: withdrawn_count += 1 + elif Defect.DEFECT_REJECTED == defect.resolution: rejected_count += 1 + + # Add this specific entry + product_summary[product.long_name] = [ + critical_count, + high_count, + medium_count, + low_count, + p1_count, + p2_count, + p3_count, + p4_count, + px_count, + unresolved_count, + resolved_count, + fixed_count, + wontfix_count, + withdrawn_count, + rejected_count, + ] + class ProductsReport(Report): """Report for the Products Page""" + global product_summary def __init__(self, parent_page, *args, **kwargs): _log_args("REPORT_PRODUCTS_INIT(%s)" % parent_page, *args, **kwargs) @@ -1471,6 +1709,7 @@ class ProductsReport(Report): context['report_type_list'] = '\ <option value="summary">Products Table</option> \ + <option value="status_jira">Product Jira Status</option> \ ' context['report_get_title'] = '1' context['report_recordrange_list'] = '\ @@ -1480,6 +1719,7 @@ class ProductsReport(Report): context['report_format_list'] = '\ <input type="radio" name="format" value="txt" checked> Text<br> \ <input type="radio" name="format" value="csv"> CSV<br> \ + <input type="radio" name="excel" value="excel"> Excel<br> \ ' return context @@ -1495,44 +1735,131 @@ class ProductsReport(Report): report_type = request_POST.get('report_type', '') record_list = request_POST.get('record_list', '') - report_name = '%s/products_%s_%s.%s' % (SRT_REPORT_DIR,report_type,datetime.today().strftime('%Y%m%d_%H%M'),format) - with open(report_name, 'w') as file: + if 'summary' == report_type: + report_name = '%s/products_%s_%s.%s' % (SRT_REPORT_DIR,report_type,datetime.today().strftime('%Y%m%d_%H%M'),format) + with open(report_name, 'w') as file: - if 'csv' == format: - tab = "\t" - else: - tab = "," - - if ('summary' == report_type): if 'csv' == format: - file.write("Name\tVersion\tProfile\tCPE\tSRT SPE\tInvestigations\tDefects\n") - if 'txt' == format: - file.write("Report : Products Table\n") - file.write("\n") - file.write("Name,Version,Profile,CPE,SRT SPE,Investigations,Defects\n") - - for product in Product.objects.all(): - file.write("%s%s" % (product.name,tab)) - file.write("%s%s" % (product.version,tab)) - file.write("%s%s" % (product.profile,tab)) - file.write("%s%s" % (product.cpe,tab)) - file.write("%s%s" % (product.defect_tags,tab)) - file.write("%s%s" % (product.product_tags,tab)) + tab = "\t" + else: + tab = "," - for i,pi in enumerate(product.product_investigation.all()): - if i > 0: - file.write(" ") - file.write("%s" % (pi.name)) - file.write("%s" % tab) - for i,pd in enumerate(product.product_defect.all()): - if i > 0: - file.write(" ") - file.write("%s" % (pd.name)) - #file.write("%s" % tab) - file.write("\n") + if ('summary' == report_type): + if 'csv' == format: + file.write("Name\tVersion\tProfile\tCPE\tSRT SPE\tInvestigations\tDefects\n") + if 'txt' == format: + file.write("Report : Products Table\n") + file.write("\n") + file.write("Name,Version,Profile,CPE,SRT SPE,Investigations,Defects\n") + + for product in Product.objects.all(): + file.write("%s%s" % (product.name,tab)) + file.write("%s%s" % (product.version,tab)) + file.write("%s%s" % (product.profile,tab)) + file.write("%s%s" % (product.cpe,tab)) + file.write("%s%s" % (product.defect_tags,tab)) + file.write("%s%s" % (product.product_tags,tab)) + + if False: + for i,pi in enumerate(product.product_investigation.all()): + if i > 0: + file.write(" ") + file.write("%s" % (pi.name)) + file.write("%s" % tab) + for i,pd in enumerate(product.product_defect.all()): + if i > 0: + file.write(" ") + file.write("%s" % (pd.name)) + #file.write("%s" % tab) + file.write("\n") + elif 'status_jira' == report_type: + def resolution_color(i): + if 0 == i: fill = pyxl_backcolor_orn + elif 1 == i: fill = pyxl_backcolor_grn + elif 2 == i: fill = pyxl_backcolor_grn + elif 3 == i: fill = pyxl_backcolor_yel + elif 4 == i: fill = pyxl_backcolor_blu + elif 5 == i: fill = pyxl_backcolor_blu + else: fill = None + return(fill) + + for product in Product.objects.all(): + scan_product_jira(product) + + format = "xlsx" + report_name = '%s/products_jira_%s_%s.%s' % (SRT_REPORT_DIR,report_type,datetime.today().strftime('%Y%m%d_%H%M'),format) + wb = Workbook() + ws = wb.active + ws.title = "Product Jira Summary" + ws.column_dimensions[get_column_letter(1)].width = 30 + + row = 1 + first_row = 2 + + col = 1 + for header in ('Product','Critical','High','Medium','Low','P1','P2','P3','P4','Unresolved','Resolved','Fixed',"Won't Fix",'Withdrawn','Rejected'): + border = pyxl_border_bottom_left if (col in (2,6,10)) else pyxl_border_bottom + pyxl_write_cell(ws,row,col,header,border=border) + col += 1 + row += 1 + + for product in Product.objects.order_by("order"): + key = product.long_name + scan_product_jira(product) + pyxl_write_cell(ws,row,1,key) + # CVE Severity + col_excel = 2 + col_summary = 1 + for i in range(0,4): + border = pyxl_border_left if (i==0) else None + value = product_summary[key][col_summary+i] + pyxl_write_cell(ws,row,col_excel+i,value,border=border,fill=pyxl_cve_fills[i] if value else None) + + # Jira Priority + col_excel = 6 + col_summary = 5 + for i in range(0,4): + border = pyxl_border_left if (i==0) else None + value = product_summary[key][col_summary+i] + pyxl_write_cell(ws,row,col_excel+i,value,border=border,fill=pyxl_cve_fills[i] if value else None) + # Jira Resolution + col_excel = 10 + col_summary = 9 + for i in range(0,6): + border = pyxl_border_left if (i==0) else None + value = product_summary[key][col_summary+i] + pyxl_write_cell(ws,row,col_excel+i,value,border=border,fill=resolution_color(i) if value else None) + row += 1 + + # Sums + row -= 1 + for i in range(1,16): + border = pyxl_border_bottom_left if (i in (2,6,10)) else pyxl_border_bottom + ws.cell(row=row,column=i).border=border + row += 1 + letters = (' ','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q') + for col_excel in range(2,16): + # CVE Severity + col_excel = 2 + for i in range(0,4): + pyxl_write_cell(ws,row,col_excel+i,'=SUM(%s%d:%s%d)' % (letters[col_excel+i],first_row,letters[col_excel+i],row-1),fill=pyxl_cve_fills[i]) + # Jira Priority + col_excel = 6 + for i in range(0,4): + pyxl_write_cell(ws,row,col_excel+i,'=SUM(%s%d:%s%d)' % (letters[col_excel+i],first_row,letters[col_excel+i],row-1),fill=pyxl_cve_fills[i]) + # Jira Resolution + col_excel = 10 + for i in range(0,6): + pyxl_write_cell(ws,row,col_excel+i,'=SUM(%s%d:%s%d)' % (letters[col_excel+i],first_row,letters[col_excel+i],row-1),fill=resolution_color(i)) + + wb.save(report_name) return report_name,os.path.basename(report_name) +# +# CVE Reports +# + class PublishCveReport(Report): """Report for the Publish Cve Page""" |