Adding additional Fields in my SQL existing Insight VM report

Hello Everyone,

We like to add Solution Summary, Asset OS Name and Vulnerability Description in the following SQL query.

WITH sol AS (
SELECT DISTINCT vulnerability_id,solution_type
from dim_asset_vulnerability_solution avs
JOIN dim_solution_highest_supercedence dshs USING (solution_id)
JOIN dim_solution USING (solution_id)

vul_skill AS (
SELECT dve.vulnerability_id, string_agg(skill_level, ‘,’ order by skill_level) as skill_level
FROM dim_vulnerability_exploit as dve
group by dve.vulnerability_id
SELECT da.host_name,da.ip_address,da.mac_address,dv.vulnerability_id,dv.title,dv.severity, round(dv.riskscore) as riskscore,dv.exploits,dv.malware_kits,favf.vulnerability_instances,
WHEN dv.cvss_access_vector_id = ‘L’ then ‘Local’
WHEN dv.cvss_access_vector_id = ‘A’ then ‘Adjacent Network’
WHEN dv.cvss_access_vector_id = ‘N’ then ‘Network’
END as access_vector,
WHEN skill.skill_level ILIKE ‘%Novice%’ then ‘Novice’
WHEN skill.skill_level ILIKE ‘%Intermediate%’ then ‘Intermediate’
WHEN skill.skill_level ILIKE ‘%Expert%’ then ‘Expert’
ELSE ‘Unknown’
END as skill_level,
when fava.age_in_days > 90 then ‘More than 90 days’
when fava.age_in_days > 60 then ‘61 to 90 days’
when fava.age_in_days > 30 then ‘31 to 60 days’
else ‘30 Days or less’
End as age_band,
when dv.title ILIKE ‘%security update for%’ then ‘PATCH’
when dv.title ILIKE ‘%Security updates available%’ then ‘PATCH’
when dv.title ILIKE ‘%Security update available for%’ Then ‘PATCH’
when dv.title ILIKE ‘%Obsolete Version%’ Then ‘PATCH’
when dv.title ILIKE ‘Google Chrome Vulnerability%’ Then ‘PATCH’
when dv.title ILIKE ‘MFSA____-__ Firefox:%’ Then ‘PATCH’
when dv.title ILIKE ‘MFSA____-___ Firefox:%’ Then ‘PATCH’
when dv.title ILIKE ‘HP System Management Homepage%’ Then ‘PATCH’
when solution_type = ‘ROLLUP’ then ‘PATCH’
when solution_type = ‘WORKAROUND’ then ‘CONFIG CHNG’
else solution_type
End as solution_type,
WHEN skill.skill_level ILIKE ‘%Novice%’ then (round(dv.riskscore)(dv.malware_kits100+dv.exploits10+1)4)
WHEN skill.skill_level ILIKE ‘%Intermediate%’ then (round(dv.riskscore)
WHEN skill.skill_level ILIKE ‘%Expert%’ then (round(dv.riskscore)
ELSE (round(dv.riskscore)
END as priority

FROM dim_asset as da
INNER JOIN fact_asset_vulnerability_finding as favf USING (asset_id)
INNER JOIN dim_vulnerability as dv USING (vulnerability_id)
INNER JOIN fact_asset_vulnerability_age as fava USING (asset_id,vulnerability_id)
INNER JOIN sol as S using (vulnerability_id)
LEFT JOIN vul_skill as skill USING (vulnerability_id)
order by host_name,priority desc,dv.title

Please help me perform the right changes.


There is a dim_asset_vulnerability_best_solution table that may be a better fit for your use case, if that would work you should be able to replace your sol subquery with a join on that table. As for the solution details, the dim_solution table has those details. You could join on that via the solution_id to access any of the columns there. Finally, on dim_asset there is an operating_system_id column. The dim_operating_system table could be joined on to get the OS name, I’d recommend the dim_operating_system.description field since it contains the full name of the OS.

Hope that helps!


Thanks I am able to fetch the OS description and Summary from the tables, however I am still not sure where can I find vulnerability description.

Since you have the dim_vulnerability table joined already, you just need to add dv.description to your SELECT. :slight_smile:


Thanks so much. I think I got what I needed.