module Sourcefire::Queries
Public Class Methods
sf_host_vuln_info(options = {})
click to toggle source
# 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