Vulnerability Tags

I’m trying to add the vulnerability category to an existing report. This is what I currently have:

SELECT
da.asset_id AS “asset_id”,
da.host_name AS “Hostname”,
da.ip_address AS “IP Address”,
da.sites AS “Site Name”,
dos.description AS “Operating System”,
string_agg(dt.tag_name, ', ') AS “Custom Tag”,
dv.vulnerability_id AS “vulnerability_id”,
dv.exploits AS “Exploit Count”,
dv.malware_kits AS “Malware Count”,
dv.severity AS “Vulnerability Severity Level”,
dv.riskscore AS “Risk Score”,
dv.title AS “Vulnerability Title”,
htmltotext(favi.proof) AS “Vulnerability Proof”,
ds.summary AS “Vulnerability Solution”,
ds.solution_type AS “Solution Type”,
favi.date AS “Last Scan”,
dv.cvss_v3_score AS “CVSS v3 Score”,
dv.cvss_score AS “CVSS v2 Score”,
fava.age_in_days AS “Age”,
dvc.category_name AS “Vulnerability Category”
FROM
fact_asset fa
JOIN dim_asset da ON fa.asset_id = da.asset_id
JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id
JOIN dim_vulnerability dv ON favi.vulnerability_id = dv.vulnerability_id
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_tag_asset dta ON da.asset_id = dta.asset_id
JOIN dim_vulnerability_category dvc ON dvc.vulnerability_id = dv.vulnerability_id
LEFT JOIN dim_tag dt ON dta.tag_id = dt.tag_id
LEFT JOIN dim_vulnerability_exploit dve ON dve.vulnerability_id = dv.vulnerability_id
LEFT JOIN dim_vulnerability_malware_kit dvm ON dvm.vulnerability_id = dv.vulnerability_id
LEFT JOIN dim_asset_vulnerability_best_solution davbs
ON da.asset_id = davbs.asset_id AND dv.vulnerability_id = davbs.vulnerability_id
LEFT JOIN dim_solution ds ON davbs.solution_id = ds.solution_id
LEFT JOIN fact_asset_vulnerability_age fava
ON da.asset_id = fava.asset_id AND dv.vulnerability_id = fava.vulnerability_id
Group By
da.asset_id,
da.host_name,
da.ip_address,
da.sites,
dos.description,
dv.vulnerability_id,
dv.exploits,
dv.malware_kits,
dv.severity,
dv.riskscore,
dv.title,
favi.proof,
ds.summary,
ds.solution_type,
favi.date,
dv.cvss_v3_score,
dv.cvss_score,
fava.age_in_days,
dvc.category_name

The output does include the categories, however it creates new lines for each category. I would like all of them to be concatenated into a single field per vulnerability.

Thanks in advance for any help!
Cory

Check out STRING_AGG()

Thanks bobsledtedd. I forgot about that one. I tried it, but there is still an issue, now I’m seeing repeats of the same category - Oracle, Oracle, Oracle, Oracle vs different tags - Oracle, Oracle Java.

Thanks!

@Cory - If you haven’t already, try using it with a DISTINCT and remove dvc.category_name from the GROUP BY.

STRING_AGG(DISTINCT dvc.category_name,', ') AS vulnerability_category