3
4
5
6
7
8
9
10
11
12
13
14
15
|
# File 'lib/sourcefire/queries.rb', line 3
def self.sf_host_vuln_info(options = {})
"SELECT favi.asset_id as asset_id, da.ip_address, favi.port, favi.protocol_id, dv.title, dv.vulnerability_id, dv.nexpose_id, string_agg(DISTINCT '<' || dvr.source || ':' || dvr.reference,'>') || '>' as references, dv.severity_score, dv.pci_severity_score, round((dv.cvss_score)::numeric,1) as cvss_score, dv.cvss_vector,
os.vendor, os.name, os.version
FROM fact_vulnerability fa
JOIN dim_vulnerability dv USING (vulnerability_id)
LEFT OUTER JOIN dim_vulnerability_reference dvr USING (vulnerability_id)
LEFT OUTER JOIN fact_asset_vulnerability_instance favi USING (vulnerability_id)
LEFT OUTER JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system os ON da.operating_system_id=os.operating_system_id
WHERE affected_assets > 0
GROUP BY da.ip_address, dv.title, favi.port, dv.vulnerability_id, dv.severity, dv.pci_severity_score, dv.cvss_score, dv.cvss_vector, favi.asset_id, favi.protocol_id, dv.nexpose_id, dv.severity_score, os.vendor, os.name, os.version
ORDER BY da.ip_address ASC"
end
|