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,
CASE
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,
CASE
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,
fava.age_in_days,
fava.first_discovered,
fava.most_recently_discovered,
case
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,
CASE
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,
CASE
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)(dv.malware_kits100+dv.exploits10+1)3)
WHEN skill.skill_level ILIKE ‘%Expert%’ then (round(dv.riskscore)(dv.malware_kits100+dv.exploits10+1)2)
ELSE (round(dv.riskscore)(dv.malware_kits100+dv.exploits*10+1)*1)
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.
