How to Join dim asset to dim_vulnerability

If you only run that section do you get results?

Modifed your join to include the DTA

image

No return. Thinking about what you said earlier about Case, Character Sensitive, I did see the N2 tag includes a space between -. Modified the run now to look like

image

That returned data. So I’m going to try the original one wrote yesterday matching the character requirement

Alright good look, let me know how it turns out!

1 Like

We are Solid Gold!

I didn’t know you can nest queries together in the Console (Or just in general :P)

I’m pretty dang happy with the output

Finally Query is

WITH n2_tags AS (
SELECT dt.tag_name, dta.asset_id
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id=dta.tag_id
WHERE dt.tag_name LIKE ‘%N2 - %’
)

SELECT
da.host_name,
da.ip_address,
STRING_AGG(nt.tag_name, ', ') Tags,
dv.nexpose_id

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 n2_tags nt ON favi.asset_id=nt.asset_id

GROUP BY
da.host_name,
da.ip_address,
dv.nexpose_id,
da.last_assessed_for_vulnerabilities

ORDER BY da.last_assessed_for_vulnerabilities


So in the Scope Filter

image

If you drill down

image

CISA.KEV Exist

So I used that instead of enumerating all the CVE’s individually out.

Thanks John for the Assist on this!

Oh good deal. I’ve never drilled that far down into the GUI scopes for vulnerabilities. That is super helpful. We both learned something. :smiley:

1 Like