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