SQL Query for IP, Hostname, Region, Land and Path to Vulnerability based on CVE

Hey all,

I’m currently trying to get all the Fields Mentioned in the Titel for a specific CVE on all Assets can you please help me?

Thanks in Advance

what are you referring to as Region and Land?

This is essentially what you’re looking for though. We just need to clarify what exactly you’re looking for on the region and land (are these tags maybe?) and then joining the proper table

SELECT
da.ip_address,
da.host_name,
region?,
land?,
favi.proof

FROM fact_asset_vulnerability_instance favi

JOIN dim_asset da ON favi.asset_id=da.asset_id
JOIN dim_vulnerability dv ON favi.vulnerability_id=dv.vulnerability_id
JOIN some_other_table sot ON something=something 

WHERE dv.nexpose_id = "Your CVE here"

@john_hartman Thank you!

you guessed right, Region and Land are Tags
however when I run your statement testwise it returns nothing (should be something about 100 based on the Webgui - Remediation Projects)

So for your “Region” and “Land” tags. Are they location based tags or were they created as Custom Tags?

Also how many Regions and how many Lands are there? You would essentially need to make CASE statements or some temporary tables to pull only those tags. If the tags were created as Location based tags and Land and Region are the only types of tags in there it may make it easier because you can start by filtering down on the Location based tags.

The query below should work a lot better but you’ll need to edit my two temporary tables for your region and land values. For example I have “home” and “work” as my two regions and then in my land tags I’m just saying anything that isn’t a region is a land tag.

You would of course need to specify a vulnerability as well. Keep in mind that the nexpose_id is not the exact CVE so I’m using the LIKE operator and prefixing the CVE with a % symbol to wildcard the beginning. So just paste your CVE after the % symbol

WITH 
region_tags AS (
SELECT dta.asset_id, dt.tag_name 
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id=dta.tag_id
WHERE dt.tag_type = 'LOCATION'
AND (dt.tag_name = 'work' OR dt.tag_name = 'Home')
),
land_tags AS (
SELECT dta.asset_id, dt.tag_name 
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id=dta.tag_id
WHERE dt.tag_type = 'LOCATION'
AND (dt.tag_name != 'work' AND dt.tag_name != 'Home')
)

SELECT
da.ip_address,
da.host_name,
rt.tag_name,
lt.tag_name,
htmltotext(favi.proof)

FROM fact_asset_vulnerability_instance favi

JOIN dim_asset da ON favi.asset_id=da.asset_id
JOIN dim_vulnerability dv ON favi.vulnerability_id=dv.vulnerability_id
JOIN region_tags rt ON favi.asset_id=rt.asset_id AND da.asset_id=rt.asset_id
JOIN land_tags lt ON favi.asset_id=lt.asset_id AND da.asset_id=lt.asset_id AND lt.asset_id=rt.asset_id

WHERE dv.nexpose_id LIKE '%Your CVE here'

ORDER BY da.ip_address DESC

Works like a charm thank you very much!