SQL query - Assets with Exploitable vulnerabilities and its solutions

InsightVM SQL Queries
1

Hello,

I’m attempting to extract all my current assets with exploitable vulnerabilities where CVSSv3 score is higher than 7 and skill level is novice or intermediate.

Here is what I have for now. Its not brining up results after running for a long time.
Any idea what is wrong with this query ?

select
da.ip_address,
da.host_name,
dv.title as vulnerability_name,
dv.description,
dv.severity,
dve.title,
dve.description,
dve.skill_level,
dve.source,
dv.cvss_v3_score,
sol.solution_type,
sol.fix,
sol.summary,
sol.url
from
fact_asset_vulnerability_finding favf
JOIN dim_asset da using (asset_id)
JOIN dim_vulnerability_exploit dve USING (vulnerability_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_vulnerability_solution ds USING (vulnerability_id)
JOIN dim_solution sol USING (solution_id)
where
dv.cvss_v3_score >= 7
AND (dve.skill_level like ‘Novice’ or dve.skill_level like ‘Intermediate’)

2

It works for me (completes in under a minute). How many assets do you have?

BTW, you don’t really need “like” in your query.

select
da.ip_address,
da.host_name,
dv.title as vulnerability_name,
dv.description,
dv.severity,
dve.title,
dve.description,
dve.skill_level,
dve.source,
dv.cvss_v3_score,
sol.solution_type,
sol.fix,
sol.summary,
sol.url
from
fact_asset_vulnerability_finding favf
JOIN dim_asset da using (asset_id)
JOIN dim_vulnerability_exploit dve USING (vulnerability_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_vulnerability_solution ds USING (vulnerability_id)
JOIN dim_solution sol USING (solution_id)
where
dv.cvss_v3_score >= 7
AND (dve.skill_level = 'Novice' or dve.skill_level = 'Intermediate')
3

Yeah, since it is just the whole value, you can use = operator instead of LIKE since you don’t need to match anything. But if you want to use LIKE, you would need to add % before and after the value in order to get results. So, the last line would become

AND (dve.skill_level like ‘%Novice%’ or dve.skill_level like ‘%Intermediate%’)