Trying to build a query to list out exceptions per asset. Need to also include CVSSv3 score and Owner tags field. I built the below query but something is limiting the output. When I run it for my entire environment I only get ~4k results. We have over 9K assets and Every asset should have at least 1 exception applied to it if not more.
with owner_tags AS (
SELECT
dta.asset_id,
string_agg(dt.tag_name, ‘, ‘) as owner_tags
FROM
dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id = dta.tag_id
WHERE
dt.tag_type = ‘OWNER’
GROUP BY
dta.asset_id
)
SELECT
ot.owner_tags,
da.host_name,
da.ip_address,
dv.title,
(dv.riskscore::Integer),
(dv.cvss_v3_score::Integer),
dve.asset_id,
dve.site_id,
dve.vulnerability_id,
dve.vulnerability_exception_id,
dve.reason_id,
CASE
WHEN dve.scope_id = ‘G’ THEN ‘All instances across all assets’
WHEN dve.scope_id = ‘D’ THEN ‘All instances on asset on asset "’ || COALESCE(da.host_name, da.ip_address) || ’ "’
WHEN dve.scope_id = ‘I’ THEN ‘Specific instance on asset "’ || da.host_name || ‘or’ || da.ip_address || ’ "’
WHEN dve.scope_id = ‘S’ THEN ‘All instances on this site "’ || ds.name || ’ "’
END AS exceptionscope, COALESCE(dve.additional_comments,‘’) as additional_comments, dve.expiration_date, des.description as status, der.description as reason, dve.vulnerability_exception_id, dve.reason_id, des.status_id
FROM dim_vulnerability_exception dve
LEFT OUTER JOIN dim_asset da USING (asset_id)
LEFT OUTER JOIN dim_site ds USING (site_id)
LEFT OUTER JOIN owner_tags ot using (asset_id)
LEFT OUTER JOIN dim_tag_asset dta USING (asset_id)
LEFT OUTER JOIN dim_tag dt USING (tag_id)
LEFT OUTER JOIN dim_exception_status des on des.status_id = dve.status_id
LEFT OUTER JOIN dim_exception_reason der on der.reason_id = dve.reason_id
LEFT OUTER JOIN dim_exception_scope descope on descope.scope_id = dve.scope_id
LEFT OUTER JOIN dim_vulnerability dv on dv.vulnerability_id = dve.vulnerability_id
where des.description = ‘Approved’
Am I using the wrong table perhaps? Or can anyone see what in this query could be restricting the results?