def get_compliance(self, aid):
c = self._conn.cursor()
c.execute('''SELECT assets.id, compliance.id AS cid,
assets.ip, assets.hostname, assets.mac,
compliance.lastupdated, compliance.failed,
vulns.nxvid, vulns.title, vulns.cvss,
assetvulns.age, assetvulns.autogroup
FROM assets
JOIN compliance ON assets.id = compliance.aid
JOIN assetvulns ON (compliance.failingvid = assetvulns.id
AND assets.id = assetvulns.aid)
JOIN vulns ON (assetvulns.vid = vulns.id)
WHERE assets.id = ?''', (aid,))
rows = c.fetchall()
if len(rows) == 0:
return None
i = rows[0]
ce = vuln.ComplianceElement()
ce.compliance_id = i['cid']
if i['failed'] == 1:
ce.failed = True
else:
ce.failed = False
ce.lasthandled = i['lastupdated']
v = vuln.vulnerability()
v.assetid = aid
v.ipaddr = i['ip'].encode('ascii', 'ignore')
v.macaddr = i['mac'].encode('ascii', 'ignore')
v.hostname = i['hostname'].encode('ascii', 'ignore')
v.vid = i['nxvid']
v.age_days = i['age']
v.title = i['title'].encode('ascii', 'ignore')
v.cvss = i['cvss']
v.autogroup = i['autogroup']
ce.failvuln = v
return ce
def get_workflow(self, aid):
c = self._conn.cursor()
c.execute('''SELECT assets.id, workflow.id AS wid,
assets.ip, assets.hostname, vulns.id AS vid,
assets.mac, vulns.nxvid, vulns.title, vulns.cvss,
vulns.known_exploits, vulns.known_malware,
assetvulns.detected, assetvulns.age,
workflow.lasthandled, workflow.contact, workflow.status,
assetvulns.autogroup
FROM assetvulns
JOIN assets ON assets.id = assetvulns.aid
JOIN vulns ON vulns.id = assetvulns.vid
JOIN workflow ON assetvulns.id = workflow.vid
WHERE assets.id = %d''' % aid)
rows = c.fetchall()
ret = []
for i in rows:
wfe = vuln.WorkflowElement()
wfe.lasthandled = i['lasthandled']
wfe.contact = i['contact']
wfe.workflow_id = i['wid']
wfe.status = i['status']
v = vuln.vulnerability()
v.assetid = aid
v.ipaddr = i['ip'].encode('ascii', 'ignore')
v.macaddr = i['mac'].encode('ascii', 'ignore')
v.hostname = i['hostname'].encode('ascii', 'ignore')
v.vid = i['nxvid']
v.autogroup = i['autogroup']
# All that is stored right now is Nexpose vulnerabilities, so
# create a classification value including that
v.vid_classified = 'nexpose:%d' % v.vid
rowvid = i['vid']
v.discovered_date_unix = i['detected']
v.title = i['title'].encode('ascii', 'ignore')
v.cvss = i['cvss']
v.known_malware = False
v.known_exploits = False
if i['known_malware'] != 0:
v.known_malware = True
if i['known_exploits'] != 0:
v.known_exploits = True
v.age_days = i['age']
# Supplement the element with associated CVEs
c.execute('''SELECT cve FROM cves
WHERE vid = %d''' % rowvid)
rows2 = c.fetchall()
v.cves = []
for j in rows2:
v.cves.append(j[0].encode('ascii', 'ignore'))
wfe.vulnerability = v
ret.append(wfe)
return ret
def get_workflow(self, aid):
c = self._conn.cursor()
c.execute('''SELECT assets.id, workflow.id AS wid,
assets.ip, assets.hostname, vulns.id AS vid,
assets.mac, vulns.nxvid, vulns.title, vulns.cvss,
vulns.known_exploits, vulns.known_malware,
assetvulns.detected, assetvulns.age,
workflow.lasthandled, workflow.contact, workflow.status,
assetvulns.autogroup, vulns.description, vulns.cvss_vector,
assets.nxaid, assetvulns.proof
FROM assetvulns
JOIN assets ON assets.id = assetvulns.aid
JOIN vulns ON vulns.id = assetvulns.vid
JOIN workflow ON assetvulns.id = workflow.vid
WHERE assets.id = ?''', (aid,))
rows = c.fetchall()
ret = []
for i in rows:
wfe = vuln.WorkflowElement()
wfe.lasthandled = i['lasthandled']
wfe.contact = i['contact']
wfe.workflow_id = i['wid']
wfe.status = i['status']
wfe.assetid_site = i['nxaid']
v = vuln.vulnerability()
v.assetid = aid
v.ipaddr = i['ip'].encode('ascii', 'ignore')
v.macaddr = i['mac'].encode('ascii', 'ignore')
v.hostname = i['hostname'].encode('ascii', 'ignore')
v.vid = i['nxvid']
v.autogroup = i['autogroup']
v.proof = i['proof']
# All that is stored right now is Nexpose vulnerabilities, so
# create a classification value including that
v.vid_classified = 'nexpose:%d' % v.vid
rowvid = i['vid']
v.discovered_date_unix = i['detected']
v.title = i['title'].encode('ascii', 'ignore')
v.description = i['description'].encode('ascii', 'ignore')
v.cvss = i['cvss']
v.cvss_vector = i['cvss_vector']
v.impact_label = vuln.cvss_to_label(v.cvss)
v.known_malware = False
v.known_exploits = False
if i['known_malware'] != 0:
v.known_malware = True
if i['known_exploits'] != 0:
v.known_exploits = True
v.age_days = i['age']
# Based on the score of the vulnerability, include the expected
# patch time (based on initial detection)
v.patch_in = vuln.cvss_to_patch_in(v.cvss)
# Supplement the element with associated CVEs
c.execute('''SELECT cve FROM cves
WHERE vid = %d''' % rowvid)
rows2 = c.fetchall()
v.cves = []
for j in rows2:
v.cves.append(j[0].encode('ascii', 'ignore'))
wfe.vulnerability = v
ret.append(wfe)
return ret
def vuln_extraction(scanner, vulnquery_where, writefile=None, readfile=None,
targetcve=None):
squery = '''
WITH
vuln_references AS (
SELECT vulnerability_id, array_to_string(array_agg(reference), ', ')
AS references
FROM dim_vulnerability
JOIN dim_vulnerability_reference USING (vulnerability_id)
GROUP BY vulnerability_id
)
SELECT ds.name AS site, da.asset_id, da.ip_address,
da.host_name, da.mac_address,
dv.title AS vulnerability, dvs.description AS status, favi.date
AS discovered_date,
CASE WHEN favi.port = -1 THEN NULL ELSE favi.port END AS port,
dp.name AS protocol, dsvc.name AS service,
round(dv.cvss_score::numeric, 2) AS cvss_score, vr.references, dv.exploits,
dv.malware_kits, dv.vulnerability_id
FROM fact_asset_vulnerability_instance favi
JOIN dim_asset da USING (asset_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)
JOIN dim_vulnerability_status dvs USING (status_id)
JOIN dim_protocol dp USING (protocol_id)
JOIN dim_service dsvc USING (service_id)
JOIN vuln_references vr USING (vulnerability_id)
%s
ORDER BY ds.name, da.ip_address
''' % vulnquery_where
debug.printd('requesting vulnerability details')
sites = scanner.sitelist.keys()
if len(sites) == 0:
return
agedata = vuln_get_age_data(scanner)
if readfile != None:
debug.printd('reading vulnerability data from %s' % readfile)
fd = open(readfile, 'r')
vulndata = fd.read()
fd.close()
else:
vulndata = scanner.conn.adhoc_report(squery, sites,
api_version='1.3.2')
if writefile != None:
fd = open(writefile, 'w')
fd.write(vulndata)
fd.close()
sys.exit(0)
reader = csv.reader(StringIO.StringIO(vulndata))
nvulns = 0
linked = 0
for i in reader:
if len(i) == 0:
continue
if i[0] == 'site':
continue
nvulns += 1
v = vuln.vulnerability()
v.sitename = i[0]
v.assetid = int(i[1])
v.ipaddr = i[2]
v.hostname = i[3]
v.macaddr = i[4]
v.title = i[5]
v.known_exploits = False
v.known_malware = False
if int(i[13]) > 0:
v.known_exploits = True
if int(i[14]) > 0:
v.known_malware = True
v.vid = i[15]
idx = i[7].find('.')
if idx > 0:
dstr = i[7][:idx]
else:
dstr = i[7]
v.age_days = vuln_age_days(v, agedata)
dt = datetime.datetime.strptime(dstr, '%Y-%m-%d %H:%M:%S')
dt = dt.replace(tzinfo=pytz.UTC)
v.discovered_date = dt
v.discovered_date_unix = int((v.discovered_date - \
datetime.datetime(1970, 1, 1, tzinfo=pytz.utc)).total_seconds())
v.cvss = float(i[11])
for i in i[12].split(','):
buf = i.strip()
if 'CVE-' in buf:
if v.cves == None:
v.cves = [buf,]
else:
v.cves.append(buf)
if 'RHSA-' in buf:
if v.rhsa == None:
v.rhsa = [buf,]
else:
v.rhsa.append(buf)
linked += vuln_instance_link(v, scanner)
debug.printd('%d vulnerabilities loaded' % nvulns)
debug.printd('%d vulnerabilities linked' % linked)
vuln.vuln_reset_uid_cache()
for s in scanner.sitelist.keys():
for a in scanner.sitelist[s]['assets']:
if len(a['vulns']) == 0:
continue
# If in target CVE report mode, just report on the CVE but
# don't actually process the vulnerability
if targetcve != None:
vuln.vuln_cvereport(a, targetcve)
continue
vuln.vuln_proc_pipeline(a['vulns'],
a['id'], a['address'], a['macaddress'],
a['hostname'])
if targetcve != None:
return
vuln.expire_hosts()
def vulns_over_time(scanner, gid, start, end):
squery = '''
WITH applicable_assets AS (
SELECT asset_id FROM dim_asset_group_asset
WHERE asset_group_id = %s
),
applicable_scans AS (
SELECT asset_id, scan_id
FROM fact_asset_scan
WHERE (scan_finished >= '%s') AND
(scan_finished <= '%s') AND
asset_id IN (SELECT asset_id FROM applicable_assets)
),
all_findings AS (
SELECT fasvf.asset_id, da.ip_address, da.host_name,
MIN(fasvf.date) as first_seen,
MAX(fasvf.date) as last_seen, fasvf.vulnerability_id,
dv.title AS vulnerability,
round(dv.cvss_score::numeric, 2) AS cvss_score
FROM fact_asset_scan_vulnerability_finding fasvf
JOIN dim_asset da USING (asset_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN applicable_scans USING (asset_id, scan_id)
GROUP BY asset_id, ip_address, host_name, vulnerability_id,
vulnerability, cvss_score
)
SELECT * FROM all_findings
''' % (gid, start, end)
ret = nexadhoc.nexpose_adhoc(scanner, squery, [], api_version='1.3.2',
device_ids=device_filter)
reader = csv.reader(StringIO.StringIO(ret))
vulnret = {}
cnt = 0
for i in reader:
if i == None or len(i) == 0:
continue
if i[0] == 'asset_id':
continue
newvuln = vuln.vulnerability()
newvuln.assetid = int(i[0])
newvuln.ipaddr = i[1]
newvuln.hostname = i[2]
newvuln.vid = i[5]
newvuln.title = i[6]
newvuln.cvss = float(i[7])
idx = i[3].find('.')
if idx > 0:
dstr = i[3][:idx]
else:
dstr = i[3]
dt = datetime.datetime.strptime(dstr, '%Y-%m-%d %H:%M:%S')
dt = dt.replace(tzinfo=pytz.UTC)
first_date = dt
idx = i[4].find('.')
if idx > 0:
dstr = i[4][:idx]
else:
dstr = i[4]
dt = datetime.datetime.strptime(dstr, '%Y-%m-%d %H:%M:%S')
dt = dt.replace(tzinfo=pytz.UTC)
last_date = dt
if newvuln.assetid not in vulnret:
vulnret[newvuln.assetid] = {}
newfinding = {}
newfinding['vulnerability'] = newvuln
newfinding['first_date'] = first_date
newfinding['last_date'] = last_date
vulnret[newvuln.assetid][newvuln.vid] = newfinding
cnt += 1
debug.printd('vulns_over_time: returning %d issues from %s to %s' % \
(cnt, start, end))
return vulnret
def vulns_at_time(scanner, gid, timestamp):
squery = '''
WITH applicable_assets AS (
SELECT asset_id FROM dim_asset_group_asset
WHERE asset_group_id = %s
),
asset_scan_map AS (
SELECT asset_id, scanAsOf(asset_id, '%s') as scan_id
FROM dim_asset
WHERE asset_id IN (SELECT asset_id FROM applicable_assets)
),
current_state_snapshot AS (
SELECT
fasvf.asset_id, da.ip_address, da.host_name,
fasvf.date AS discovered_date,
fasvf.vulnerability_id,
dv.title AS vulnerability,
round(dv.cvss_score::numeric, 2) AS cvss_score,
dv.cvss_vector AS cvss_vector
FROM fact_asset_scan_vulnerability_finding fasvf
JOIN dim_asset da USING (asset_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN asset_scan_map USING (asset_id, scan_id)
WHERE fasvf.asset_id IN (SELECT asset_id FROM applicable_assets)
),
issue_age AS (
SELECT
fasvf.asset_id, fasvf.vulnerability_id,
MIN(fasvf.date) as earliest
FROM fact_asset_scan_vulnerability_finding fasvf
JOIN current_state_snapshot css USING (asset_id, vulnerability_id)
GROUP BY asset_id, vulnerability_id
)
SELECT asset_id, ip_address, host_name, discovered_date,
vulnerability_id, vulnerability, cvss_score, cvss_vector,
iage.earliest,
EXTRACT(EPOCH FROM (discovered_date - iage.earliest))
FROM current_state_snapshot
JOIN issue_age iage USING (asset_id, vulnerability_id)
''' % (gid, timestamp)
ret = nexadhoc.nexpose_adhoc(scanner, squery, [], api_version='1.3.2',
device_ids=device_filter)
reader = csv.reader(StringIO.StringIO(ret))
vulnret = {}
cnt = 0
duprem = 0
for i in reader:
if i == None or len(i) == 0:
continue
if i[0] == 'asset_id':
continue
newvuln = vuln.vulnerability()
newvuln.assetid = int(i[0])
newvuln.ipaddr = i[1]
newvuln.hostname = i[2]
if filter_dupip and \
duplicate_test(vulnret, newvuln.assetid, newvuln.ipaddr):
duprem += 1
continue
idx = i[3].find('.')
if idx > 0:
dstr = i[3][:idx]
else:
dstr = i[3]
dt = datetime.datetime.strptime(dstr, '%Y-%m-%d %H:%M:%S')
dt = dt.replace(tzinfo=pytz.UTC)
newvuln.discovered_date = dt
newvuln.vid = i[4]
newvuln.title = i[5]
newvuln.cvss = float(i[6])
newvuln.cvss_vector = i[7]
newvuln.age_days = float(i[9]) / 60 / 60 / 24
if newvuln.assetid not in vulnret:
vulnret[newvuln.assetid] = []
vulnret[newvuln.assetid].append(newvuln)
cnt += 1
debug.printd('vulns_at_time: %s: returning %d issues for %d assets' % \
(timestamp, cnt, len(vulnret.keys())))
if duprem > 0:
debug.printd('vulns_at_time: %d duplicate items were removed' % duprem)
return vulnret