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