Sample SQL Querys

InsightVM SQL Queries
1

Hello,
I’m new to the platform and I was wondering if anyone has tweaked the sample query’s given in the R7 Knowledge base? I’m looking for a query that breaks down my assets by groups and shows the criticality percentage from low medium high and critical. I’m trying to build out a weekly report for our Infrastructure team and zone in on our discovery and remediation process.

1 Like
2

I have an example query for Solutions by IP and CVSS here that might be beneficial to you

SELECT
STRING_AGG(DISTINCT(tul.ip_address), ', ' Order by tul.ip_address) AS ip_address,
STRING_AGG(DISTINCT(vuln.title), ', ' Order by vuln.title) AS vulnerability_description,
CAST(vuln.cvss_score AS DECIMAL(10,1)) AS cvssv2_score,
CASE WHEN vuln.cvss_score = 10 THEN 'Critical'
  WHEN vuln.cvss_score BETWEEN 7 AND 9.9 THEN 'High'
  WHEN vuln.cvss_score BETWEEN 4.0 AND 6.9 THEN 'Medium'
  WHEN vuln.cvss_score BETWEEN 0.1 and 3.9 THEN 'Low'
ELSE 'Info'
END AS severity, 
htmltotext(sol.fix) AS solution,
STRING_AGG(DISTINCT(sol.url), ', ' Order by sol.url) as solution_url


FROM fact_asset_vulnerability_finding AS avf

JOIN dim_asset AS tul ON avf.asset_id = tul.asset_id
JOIN dim_vulnerability AS vuln ON avf.vulnerability_id = vuln.vulnerability_id
JOIN dim_vulnerability_solution AS vs ON avf.vulnerability_id = vs.vulnerability_id
JOIN dim_solution AS sol ON vs.solution_id = sol.solution_id

GROUP by vuln.title, sol.fix, tul.ip_address, vuln.cvss_score, sol.url

ORDER by vuln.cvss_score DESC
1 Like
3

Thank you so much John! You freaking rock! :+1:t4:

4

:rock:

1 Like
5

For anyone interested, here is a version that uses dim_asset_vulnerability_finding_rollup_solution for the “best” solution and reduces the dataset size. EDIT: Just learned how to use the preformatted text. smh.

SELECT
STRING_AGG(DISTINCT(tul.ip_address::text), ', ' Order by tul.ip_address::text) AS ip_address,
STRING_AGG(DISTINCT(vuln.title), ', ' Order by vuln.title) AS vulnerability_description,
CAST(vuln.cvss_score AS DECIMAL(10,1)) AS cvssv2_score,
CASE WHEN vuln.cvss_score = 10 THEN 'Critical'
  WHEN vuln.cvss_score BETWEEN 7 AND 9.9 THEN 'High'
  WHEN vuln.cvss_score BETWEEN 4.0 AND 6.9 THEN 'Medium'
  WHEN vuln.cvss_score BETWEEN 0.1 and 3.9 THEN 'Low'
ELSE 'Info'
END AS severity, 
htmltotext(sol.fix) AS solution,
STRING_AGG(DISTINCT(sol.url), ', ' Order by sol.url) as solution_url


FROM fact_asset_vulnerability_finding AS avf

JOIN dim_asset AS tul ON avf.asset_id = tul.asset_id
JOIN dim_vulnerability AS vuln ON avf.vulnerability_id = vuln.vulnerability_id
JOIN dim_asset_vulnerability_finding_rollup_solution AS davfrs ON vuln.vulnerability_id = davfrs.vulnerability_id AND tul.asset_id = davfrs.asset_id
JOIN dim_solution AS sol ON davfrs.solution_id = sol.solution_id


GROUP by vuln.title, sol.fix, tul.ip_address, vuln.cvss_score, sol.url

ORDER by vuln.cvss_score DESC
3 Likes