def spreadsheet():
"""
Generate a Excel xlsx file of vulnerability and password statistics and charts
"""
rows = db(db.t_hosts).select(db.t_hosts.f_asset_group, distinct=True)
ags = [ag.f_asset_group for ag in rows]
form = SQLFORM.factory(
Field('ag_per_tab',
'boolean',
default=False,
label=T('Tab per Asset Group')),
Field('asset_group',
'select',
default=False,
requires=IS_EMPTY_OR(IS_IN_SET(ags)),
label=T('Specific Asset Group')),
)
response.title = "%s :: Excel Spreadsheet Generator" % (settings.title)
if form.errors:
response.flash = 'Error in form'
elif form.process().accepted:
if form.vars.asset_group:
ags = [form.vars.asset_group]
elif form.vars.ag_per_tab:
rows = db(db.t_hosts).select(db.t_hosts.f_asset_group,
distinct=True)
ags = [ag.f_asset_group for ag in rows]
else:
ags = ['%']
from skaldship.statistics import vulnlist, graphs_index
from skaldship.general import vulntype_mapping
import os
from datetime import datetime
from xlsxwriter.workbook import Workbook
tmpfile = os.path.join(
request.folder, 'data/stats/kvasir-stats-%s.xlsx' %
datetime.now().strftime("%m%d%y-%H%M%S"))
workbook = Workbook(tmpfile)
bold = workbook.add_format({'bold': 1})
# Create main statistics page / charts
graphs = graphs_index()
stat_worksheet = workbook.add_worksheet('Main Statistics')
# Top Host Severity statistics / chart
stat_worksheet.write('A1', 'Vuln Severity', bold)
stat_worksheet.write('B1', 'Host Count', bold)
row_num = 1
col_num = 0
for sev_cnt in graphs['top_host_sev_count_raw']:
stat_worksheet.write_number(row_num, col_num, row_num)
stat_worksheet.write_number(row_num, col_num + 1, int(sev_cnt))
row_num += 1
stat_chart_host = workbook.add_chart({'type': 'column'})
stat_chart_host.add_series({
'categories': ["'Main Statistics'", 1, 0, row_num - 1, 0],
'values': ["'Main Statistics'", 1, 1, row_num - 1, 1],
'name':
'Host Count',
})
stat_chart_host.set_title({'name': 'Top Host Severities'})
stat_chart_host.set_table({'show_keys': True})
stat_chart_host.set_legend({'position': 'none'})
stat_chart_host.set_x_axis({
'min': 1,
'max': 10,
'name_font': {
'bold': True
},
})
stat_chart_host.set_size({'width': 768, 'height': 576})
stat_worksheet.insert_chart('A13', stat_chart_host)
# Vulnerability Severity statistics / chart
stat_worksheet.write('D1', 'Vuln Severity', bold)
stat_worksheet.write('E1', 'Vuln Count', bold)
row_num = 1
col_num = 3
for sev_cnt in graphs['vuln_by_sev_count_raw']:
stat_worksheet.write_number(row_num, col_num, row_num)
stat_worksheet.write_number(row_num, col_num + 1, int(sev_cnt))
row_num += 1
stat_chart_vulns = workbook.add_chart({'type': 'column'})
stat_chart_vulns.add_series({
'categories': ["'Main Statistics'", 1, 3, row_num - 1, 3],
'values': ["'Main Statistics'", 1, 4, row_num - 1, 4],
'name':
'Vulnerability Count',
})
stat_chart_vulns.set_title({'name': 'Top Vulnerability Severities'})
stat_chart_vulns.set_table({'show_keys': True})
stat_chart_vulns.set_legend({'position': 'none'})
stat_chart_vulns.set_x_axis({
'min': 1,
'max': 10,
'name_font': {
'bold': True
},
})
stat_chart_vulns.set_size({'width': 768, 'height': 576})
stat_worksheet.insert_chart('G13', stat_chart_vulns)
# Create tab(s) for vulnerability listings and charts
for ag in ags:
if ag == "%":
ag = "Vulnlist"
hostfilter = None
else:
hostfilter = {
'filtertype': 'assetgroup',
'content': ag,
}
vl_worksheet = workbook.add_worksheet(ag)
vl_worksheet.write('A1', 'Vulnerability ID', bold)
vl_worksheet.set_column(1, 0, 45)
vl_worksheet.write('B1', 'Status', bold)
vl_worksheet.set_column(1, 1, 20)
vl_worksheet.write('C1', 'Count', bold)
vl_worksheet.write('D1', 'Severity', bold)
vl_worksheet.write('E1', 'CVSS Score', bold)
# { 'vulnerability id': [ status, count, severity, cvss ] }
vlist = vulnlist(hostfilter=hostfilter)
vuln_count = 1
vl_stats = {}
for k, v in vlist.iteritems():
col_num = 0
for row in v:
(status, count, severity, cvss) = row
vl_worksheet.write_string(vuln_count, col_num, k)
vl_worksheet.write_string(vuln_count, col_num + 1, status)
vl_worksheet.write_number(vuln_count, col_num + 2,
int(count))
vl_worksheet.write_number(vuln_count, col_num + 3,
int(severity))
if cvss:
vl_worksheet.write_number(vuln_count, col_num + 4,
float(cvss))
vuln_count += 1
# make vl_stats dictionary:
# { 'status': { 1: count, 2:count ... }}
vl_tmpstatus = vl_stats.setdefault(
status, {
1: 0,
2: 0,
3: 0,
4: 0,
5: 0,
6: 0,
7: 0,
8: 0,
9: 0,
10: 0
})
status_tmp = vl_tmpstatus.setdefault(severity, 0)
vl_tmpstatus[severity] = status_tmp + count
vl_stats[status] = vl_tmpstatus
# create vulnerability severity distribution chart
vl_chart_ws_name = "%s VulnChart" % (ag)
vl_chart_worksheet = workbook.add_worksheet(vl_chart_ws_name)
vl_chart_worksheet.write('A1', 'Severity', bold)
vl_chart = workbook.add_chart({'type': 'column'})
for k, v in vl_stats.iteritems():
vl_chart_worksheet.write(0, col_num, k, bold)
row_num = 1
for k2, v2 in v.iteritems():
vl_chart_worksheet.write(row_num, 0, k2)
vl_chart_worksheet.write(row_num, 1, v2)
row_num += 1
vl_chart.add_series({
'categories':
["'%s'" % (vl_chart_ws_name), 1, 0, row_num - 1, 0],
'values': [
"'%s'" % (vl_chart_ws_name), 1, col_num, row_num - 1,
col_num
],
'name':
k,
'color':
vulntype_mapping(k)
})
col_num += 1
# if multiple account groups, change title accordingly
if ag == "Vulnlist":
vl_chart.set_title(
{'name': 'Vulnerability Severity Distribution'})
else:
vl_chart.set_title(
{'name': 'Vulnerability Severity Distribution: %s' % (ag)})
vl_chart.set_table({'show_keys': True})
vl_chart.set_legend({'position': 'none'})
vl_chart.set_x_axis({
'min': 1,
'max': 10,
'name_font': {
'bold': True
},
})
vl_chart.set_size({'width': 960, 'height': 576})
vl_chart_worksheet.insert_chart('A13', vl_chart)
# Top 15 passwords and UNIX / Windows distribution and compromised pie charts
password_worksheet = workbook.add_worksheet('Passwords')
pw_cnt = db.t_accounts.f_password.count()
top15 = db(db.t_accounts.f_password != None).select(
db.t_accounts.f_password,
pw_cnt,
groupby=db.t_accounts.f_password,
orderby=~pw_cnt,
limitby=(0, 15))
password_worksheet.write('A1', 'Password', bold)
password_worksheet.write('B1', 'Count', bold)
row_num = 1
for row in top15:
password_worksheet.write(row_num, 0, row.t_accounts.f_password)
password_worksheet.write(
row_num, 1, row._extra['COUNT(t_accounts.f_password)'])
row_num += 1
# all done!
workbook.close()
redirect(URL('default', 'data_dir/stats'))
return dict(form=form)
def spreadsheet():
"""
Generate a Excel xlsx file of vulnerability and password statistics and charts
"""
rows = db(db.t_hosts).select(db.t_hosts.f_asset_group, distinct=True)
ags = [ag.f_asset_group for ag in rows]
form=SQLFORM.factory(
Field('ag_per_tab', 'boolean', default=False, label=T('Tab per Asset Group')),
Field('asset_group', 'select', default=False, requires=IS_EMPTY_OR(IS_IN_SET(ags)), label=T('Specific Asset Group')),
)
response.title = "%s :: Excel Spreadsheet Generator" % (settings.title)
if form.errors:
response.flash = 'Error in form'
elif form.process().accepted:
if form.vars.asset_group:
ags = [form.vars.asset_group]
elif form.vars.ag_per_tab:
rows = db(db.t_hosts).select(db.t_hosts.f_asset_group, distinct=True)
ags = [ag.f_asset_group for ag in rows]
else:
ags = ['%']
from skaldship.statistics import vulnlist, graphs_index
from skaldship.general import vulntype_mapping
import os
from datetime import datetime
from xlsxwriter.workbook import Workbook
tmpfile = os.path.join(request.folder, 'data/stats/kvasir-stats-%s.xlsx' % datetime.now().strftime("%m%d%y-%H%M%S"))
workbook = Workbook(tmpfile)
bold = workbook.add_format({'bold': 1})
# Create main statistics page / charts
graphs = graphs_index()
stat_worksheet = workbook.add_worksheet('Main Statistics')
# Top Host Severity statistics / chart
stat_worksheet.write('A1', 'Vuln Severity', bold)
stat_worksheet.write('B1', 'Host Count', bold)
row_num = 1
col_num = 0
for sev_cnt in graphs['top_host_sev_count_raw']:
stat_worksheet.write_number(row_num, col_num, row_num)
stat_worksheet.write_number(row_num, col_num+1, int(sev_cnt))
row_num += 1
stat_chart_host = workbook.add_chart({'type': 'column'})
stat_chart_host.add_series({
'categories': ["'Main Statistics'", 1, 0, row_num-1, 0],
'values': ["'Main Statistics'", 1, 1, row_num-1, 1],
'name': 'Host Count',
})
stat_chart_host.set_title({'name': 'Top Host Severities'})
stat_chart_host.set_table({'show_keys': True})
stat_chart_host.set_legend({'position': 'none'})
stat_chart_host.set_x_axis({
'min': 1,
'max': 10,
'name_font': {'bold': True},
})
stat_chart_host.set_size({'width': 768, 'height': 576})
stat_worksheet.insert_chart('A13', stat_chart_host)
# Vulnerability Severity statistics / chart
stat_worksheet.write('D1', 'Vuln Severity', bold)
stat_worksheet.write('E1', 'Vuln Count', bold)
row_num = 1
col_num = 3
for sev_cnt in graphs['vuln_by_sev_count_raw']:
stat_worksheet.write_number(row_num, col_num, row_num)
stat_worksheet.write_number(row_num, col_num+1, int(sev_cnt))
row_num += 1
stat_chart_vulns = workbook.add_chart({'type': 'column'})
stat_chart_vulns.add_series({
'categories': ["'Main Statistics'", 1, 3, row_num-1, 3],
'values': ["'Main Statistics'", 1, 4, row_num-1, 4],
'name': 'Vulnerability Count',
})
stat_chart_vulns.set_title({'name': 'Top Vulnerability Severities'})
stat_chart_vulns.set_table({'show_keys': True})
stat_chart_vulns.set_legend({'position': 'none'})
stat_chart_vulns.set_x_axis({
'min': 1,
'max': 10,
'name_font': {'bold': True},
})
stat_chart_vulns.set_size({'width': 768, 'height': 576})
stat_worksheet.insert_chart('G13', stat_chart_vulns)
# Create tab(s) for vulnerability listings and charts
for ag in ags:
if ag == "%":
ag = "Vulnlist"
hostfilter = [(None, None), False]
else:
hostfilter = [('assetgroup', ag), False]
vl_worksheet = workbook.add_worksheet(ag)
vl_worksheet.write('A1', 'Vulnerability ID', bold)
vl_worksheet.set_column(1, 0, 45)
vl_worksheet.write('B1', 'Status', bold)
vl_worksheet.set_column(1, 1, 20)
vl_worksheet.write('C1', 'Count', bold)
vl_worksheet.write('D1', 'Severity', bold)
vl_worksheet.write('E1', 'CVSS Score', bold)
# { 'vulnerability id': [ status, count, severity, cvss ] }
vlist = vulnlist(hostfilter)
vuln_count = 1
vl_stats = {}
for k, v in vlist.iteritems():
col_num = 0
for row in v:
(status, count, severity, cvss) = row
vl_worksheet.write_string(vuln_count, col_num, k)
vl_worksheet.write_string(vuln_count, col_num + 1, status)
vl_worksheet.write_number(vuln_count, col_num + 2, int(count))
vl_worksheet.write_number(vuln_count, col_num + 3, int(severity))
if cvss:
vl_worksheet.write_number(vuln_count, col_num + 4, float(cvss))
vuln_count += 1
# make vl_stats dictionary:
# { 'status': { 1: count, 2:count ... }}
vl_tmpstatus = vl_stats.setdefault(status, {
1: 0, 2: 0, 3: 0, 4: 0, 5: 0, 6: 0, 7: 0, 8: 0, 9: 0, 10: 0
})
status_tmp = vl_tmpstatus.setdefault(severity, 0)
vl_tmpstatus[severity] = status_tmp + count
vl_stats[status] = vl_tmpstatus
# create vulnerability severity distribution chart
vl_chart_ws_name = "%s VulnChart" % (ag)
vl_chart_worksheet = workbook.add_worksheet(vl_chart_ws_name)
vl_chart_worksheet.write('A1', 'Severity', bold)
vl_chart = workbook.add_chart({'type': 'column'})
for k,v in vl_stats.iteritems():
vl_chart_worksheet.write(0, col_num, k, bold)
row_num = 1
for k2,v2 in v.iteritems():
vl_chart_worksheet.write(row_num, 0, k2)
vl_chart_worksheet.write(row_num, 1, v2)
row_num += 1
vl_chart.add_series({
'categories': ["'%s'" % (vl_chart_ws_name), 1, 0, row_num-1, 0],
'values': ["'%s'" % (vl_chart_ws_name), 1, col_num, row_num-1, col_num],
'name': k,
'color': vulntype_mapping(k)
})
col_num += 1
# if multiple account groups, change title accordingly
if ag == "Vulnlist":
vl_chart.set_title({'name': 'Vulnerability Severity Distribution'})
else:
vl_chart.set_title({'name': 'Vulnerability Severity Distribution: %s' % (ag)})
vl_chart.set_table({'show_keys': True})
vl_chart.set_legend({'position': 'none'})
vl_chart.set_x_axis({
'min': 1,
'max': 10,
'name_font': {'bold': True},
})
vl_chart.set_size({'width': 960, 'height': 576})
vl_chart_worksheet.insert_chart('A13', vl_chart)
# Top 15 passwords and UNIX / Windows distribution and compromised pie charts
password_worksheet = workbook.add_worksheet('Passwords')
pw_cnt = db.t_accounts.f_password.count()
top15 = db(db.t_accounts.f_password != None).select(db.t_accounts.f_password, pw_cnt, groupby=db.t_accounts.f_password, orderby=~pw_cnt, limitby=(0,15))
password_worksheet.write('A1', 'Password', bold)
password_worksheet.write('B1', 'Count', bold)
row_num = 1
for row in top15:
password_worksheet.write(row_num, 0, row.t_accounts.f_password)
password_worksheet.write(row_num, 1, row._extra['COUNT(t_accounts.f_password)'])
row_num += 1
# all done!
workbook.close()
redirect(URL('default', 'data_dir/stats'))
return dict(form=form)