Trouble with old sql query

InsightVM SQL Queries
1

half the time this query doesnt work sometimes it does is there any way to revamp this or does anyone else have another version.

Most of the time my reports are blank.

WITH site_info AS (
SELECT site_id, ds.name, asset_id, riskscore, rank() over (partition BY site_id ORDER BY riskscore DESC) AS risk_rank
FROM dim_site_asset
JOIN dim_site ds USING (site_id)
JOIN fact_asset da USING (asset_id)
), riskiest_assets AS (
SELECT site_id, name, asset_id, riskscore
FROM site_info
GROUP BY site_id, name, asset_id, riskscore
Order BY site_id, riskscore DESC
), vulnerability_solutions AS (
SELECT asset_id, avi.vulnerability_id, csv(htmlToText(proof, false)) AS proofs, csv(htmlToText(ds.fix, false)) AS solutions
FROM riskiest_assets
JOIN fact_asset_vulnerability_instance avi USING (asset_id)
JOIN dim_asset_vulnerability_solution avs USING (asset_id, vulnerability_id)
left JOIN dim_solution_highest_supercedence dshs USING (solution_id)
left JOIN dim_solution ds ON ds.solution_id = dshs.superceding_solution_id
GROUP BY asset_id, avi.vulnerability_id
), asset_os_info AS (
SELECT DISTINCT ON (asset_id, operating_system_id) asset_id, operating_system_id, certainty, site_id, ra.name, riskscore,
da.ip_address, da.host_name, dos.description, dos.version
FROM riskiest_assets ra
JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN fact_asset_scan_operating_system aos USING (asset_id, operating_system_id)
ORDER BY asset_id, operating_system_id, certainty DESC
),
owner_tags AS (
SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS owner_tags
FROM dim_tag
JOIN dim_scope_tag USING(tag_id)
JOIN dim_tag_asset USING (tag_id)
WHERE tag_type = 'OWNER'
GROUP BY asset_id
)
SELECT
aoi.name AS "Site Name", aoi.ip_address AS "IP Address", aoi.host_name AS "Hostname", aoi.description AS "OS",
dv.title AS "Vulnerability Title", dv.severity AS "Vulnerability Severity", csv(dvc.category_name) AS "Category Names",
round(dv.riskscore) AS "Vulnerability Risk Score", round(dv.cvss_score::numeric, 1) AS "CVSS", htmlToText(dv.description) AS "Vulnerability Description",
proofs AS "Proof", solutions AS "Solution", ot.owner_tags AS "Owner"
FROM asset_os_info aoi
JOIN vulnerability_solutions vs USING (asset_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_vulnerability_category dvc USING (vulnerability_id)
LEFT OUTER JOIN owner_tags ot USING (asset_id)
WHERE dv.cvss_score > 8
GROUP BY aoi.name, aoi.ip_address, aoi.host_name, aoi.description, aoi.version, aoi.certainty, aoi.riskscore, vulnerability_id,
dv.title, dv.severity, dv.riskscore, dv.cvss_score, dv.description, vs.proofs, ot.owner_tags, vs.solutions
ORDER BY aoi.name, aoi.ip_address, aoi.host_name, aoi.description, aoi.version, aoi.certainty, aoi.riskscore, vulnerability_id

2

try this on for size, there was a asset_os_info CTE does INNER JOIN fact_asset_scan_operating_system, any asset missing a row in fact_asset_scan_operating_system. and a few inner join issues and few other items.

WITH asset_site_info AS (
  SELECT 
    dsa.site_id, 
    ds.name AS site_name, 
    dsa.asset_id, 
    fa.riskscore
  FROM dim_site_asset dsa
  JOIN dim_site ds USING (site_id)
  JOIN fact_asset fa USING (asset_id)
),
high_vuln_solutions AS (
  SELECT 
    avi.asset_id, 
    avi.vulnerability_id, 
    csv(htmlToText(avi.proof, false)) AS proofs,
    csv(htmlToText(ds.fix, false)) AS solutions
  FROM asset_site_info asi
  JOIN fact_asset_vulnerability_instance avi USING (asset_id)
  JOIN dim_asset_vulnerability_solution avs USING (asset_id, vulnerability_id)
  LEFT JOIN dim_solution_highest_supercedence dshs USING (solution_id)
  LEFT JOIN dim_solution ds ON ds.solution_id = dshs.superceding_solution_id
  GROUP BY avi.asset_id, avi.vulnerability_id
),
asset_os_info AS (
  SELECT DISTINCT ON (aos.asset_id)
    aos.asset_id,
    dos.description AS os_description,
    dos.version AS os_version,
    aos.certainty
  FROM asset_site_info asi
  JOIN fact_asset_scan_operating_system aos USING (asset_id)
  JOIN dim_operating_system dos USING (operating_system_id)
  ORDER BY aos.asset_id, aos.certainty DESC
),
owner_tags AS (
  SELECT 
    dta.asset_id, 
    csv(dt.tag_name ORDER BY dt.tag_name) AS owner_tags
  FROM dim_tag dt
  JOIN dim_scope_tag USING (tag_id)
  JOIN dim_tag_asset dta USING (tag_id)
  WHERE dt.tag_type = 'OWNER'
  GROUP BY dta.asset_id
)
SELECT
  asi.site_name AS "Site Name",
  da.ip_address AS "IP Address",
  da.host_name AS "Hostname",
  aoi.os_description AS "OS",
  dv.title AS "Vulnerability Title",
  dv.severity AS "Vulnerability Severity",
  csv(dvc.category_name) AS "Category Names",
  round(dv.riskscore) AS "Vulnerability Risk Score",
  round(dv.cvss_score::numeric, 1) AS "CVSS",
  htmlToText(dv.description) AS "Vulnerability Description",
  hvs.proofs AS "Proof",
  hvs.solutions AS "Solution",
  ot.owner_tags AS "Owner",
  round(asi.riskscore) AS "Asset Risk Score"
FROM asset_site_info asi
JOIN dim_asset da USING (asset_id)
JOIN high_vuln_solutions hvs USING (asset_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
LEFT JOIN dim_vulnerability_category dvc USING (vulnerability_id)
LEFT JOIN asset_os_info aoi USING (asset_id)
LEFT JOIN owner_tags ot USING (asset_id)
WHERE dv.cvss_score > 8
GROUP BY 
  asi.site_name, 
  da.ip_address, 
  da.host_name, 
  aoi.os_description, 
  dv.title, 
  dv.severity, 
  dv.riskscore, 
  dv.cvss_score, 
  dv.description, 
  hvs.proofs, 
  hvs.solutions, 
  ot.owner_tags,
  asi.riskscore
ORDER BY asi.site_name, da.ip_address, da.host_name, dv.title