I’m working on an SQL query below but the export shows duplicate assets. I only selected the R7 Agent site as the target. Anyone can help me out why I’m getting duplicate assets in a csv file?
SELECT
DISTINCT da.last_assessed_for_vulnerabilities AS “Last Scan Date”,
da.ip_address AS “Host IP Address”,
da.host_name AS “Hostname”,
da.mac_address AS “MAC Address”,
json_certs.port AS “Port”,
json_certs.cert ->> ‘ssl.cert.issuer.dn’ AS “Issuer”,
json_certs.cert ->> ‘ssl.cert.subject.dn’ AS “Subject”,
json_certs.cert ->> ‘ssl.cert.key.alg.name’ AS “Algorithm”,
json_certs.cert ->> ‘ssl.cert.sig.alg.name’ AS “Algorithm Signature”,
json_certs.cert ->> ‘ssl.cert.key.rsa.modulusBits’ AS “Key Size”,
json_certs.cert ->> ‘ssl.cert.not.valid.before’ AS “Invalid Before”,
json_certs.cert ->> ‘ssl.cert.not.valid.after’ AS “Invalid After”,
(
CAST(
json_certs.cert ->> ‘ssl.cert.not.valid.after’ AS DATE
) - CURRENT_DATE
) AS “Expires In (days)”,
dt.tag_name AS “Asset Owner”
FROM
(
SELECT
asset_id,
service_id,
port,
json_object_agg(name, replace(value :: text, ‘"’, ‘’)) as cert
FROM
dim_asset_service_configuration
WHERE
lower(name) like ‘ssl.cert.%’
GROUP BY
1,
2,
3
) as json_certs
JOIN dim_asset AS da USING (asset_id)
LEFT JOIN dim_tag_asset AS dta ON da.asset_id = dta.asset_id
LEFT JOIN dim_tag AS dt ON dta.tag_id = dt.tag_id AND dt.tag_type = ‘OWNER’
The root cause of duplicate assets in your CSV export is most likely due to multiple service configurations like certs per asset, probally because of :
One asset has multiple services i.e. multiple SSL ports like 443, 8443, 9443 etc.
Each service might expose a different certificate producing multiple rows for the same asset.
Even when DISTINCT is used, it’s applied to the entire row, not just the asset so theres some slight variations (like port or cert subject) result in unique rows.
You’re aggregating by asset_id, service_id, port → so every distinct cert+port is a separate row. If the same asset_id appears across multiple ports/services, it repeats in the final output. Since your outer SELECT includes cert details and ports, DISTINCT will not collapse rows unless everything is identical.
you can try these instead based on your use case and see if thats what you are wanting…
If you just want one row per asset, try this structure to only pull the earliest-expiring cert per asset
WITH json_certs AS (
SELECT
asset_id,
port,
json_object_agg(name, REPLACE(value::text, '"', '')) AS cert
FROM dim_asset_service_configuration
WHERE lower(name) LIKE 'ssl.cert.%'
GROUP BY asset_id, port
),
ranked_certs AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY asset_id ORDER BY (cert ->> 'ssl.cert.not.valid.after')::DATE ASC) AS rn
FROM json_certs
)
SELECT
da.last_assessed_for_vulnerabilities AS "Last Scan Date",
da.ip_address AS "Host IP Address",
da.host_name AS "Hostname",
da.mac_address AS "MAC Address",
rc.port AS "Port",
rc.cert ->> 'ssl.cert.issuer.dn' AS "Issuer",
rc.cert ->> 'ssl.cert.subject.dn' AS "Subject",
rc.cert ->> 'ssl.cert.key.alg.name' AS "Algorithm",
rc.cert ->> 'ssl.cert.sig.alg.name' AS "Algorithm Signature",
rc.cert ->> 'ssl.cert.key.rsa.modulusBits' AS "Key Size",
rc.cert ->> 'ssl.cert.not.valid.before' AS "Invalid Before",
rc.cert ->> 'ssl.cert.not.valid.after' AS "Invalid After",
(
CAST(rc.cert ->> 'ssl.cert.not.valid.after' AS DATE) - CURRENT_DATE
) AS "Expires In (days)",
dt.tag_name AS "Asset Owner"
FROM ranked_certs rc
JOIN dim_asset da ON da.asset_id = rc.asset_id
LEFT JOIN dim_tag_asset dta ON da.asset_id = dta.asset_id
LEFT JOIN dim_tag dt ON dta.tag_id = dt.tag_id AND dt.tag_type = 'OWNER'
WHERE rc.rn = 1
If you do want multiple certs, but want only one line per asset, you could consider using string_agg() to group them up with something like this.
SELECT
da.last_assessed_for_vulnerabilities AS "Last Scan Date",
da.ip_address AS "Host IP Address",
da.host_name AS "Hostname",
da.mac_address AS "MAC Address",
string_agg(json_certs.cert ->> 'ssl.cert.subject.dn', ', ') AS "Subjects",
string_agg(json_certs.cert ->> 'ssl.cert.not.valid.after', ', ') AS "Expires",
dt.tag_name AS "Asset Owner"
FROM (
SELECT asset_id, port,
json_object_agg(name, REPLACE(value::text, '"', '')) AS cert
FROM dim_asset_service_configuration
WHERE lower(name) LIKE 'ssl.cert.%'
GROUP BY asset_id, port
) AS json_certs
JOIN dim_asset da USING (asset_id)
LEFT JOIN dim_tag_asset dta ON da.asset_id = dta.asset_id
LEFT JOIN dim_tag dt ON dta.tag_id = dt.tag_id AND dt.tag_type = 'OWNER'
GROUP BY
da.asset_id,
da.ip_address,
da.host_name,
da.mac_address,
da.last_assessed_for_vulnerabilities,
dt.tag_name