I’m having trouble adding certainty level to these partial, full, and unknown authentication the port it’s failing on would be great to but not necessary. On this SQL can anyone help?
WITH owner_tags AS (
SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS owner_tags
FROM dim_tag
JOIN dim_tag_asset USING (tag_id)
WHERE tag_type = ‘OWNER’
GROUP BY asset_id
),
custom_tags AS (
SELECT asset_id, CSV(tag_name ORDER BY tag_name) AS custom_tags
FROM dim_tag
JOIN dim_tag_asset USING (tag_id)
WHERE tag_type = 'CUSTOM'
GROUP BY asset_id
)
SELECT ot.owner_tags AS “Owner”,
ct.custom_tags AS “Custom Tags”,
dos.asset_type AS “Asset Type”,
dos.description AS “Asset Description”,
dos.name AS “OS Name”,
da.host_name AS “Host Name”,
da.ip_address AS “IP Address”,
CASE
WHEN fa.aggregated_credential_status_id = 1 THEN ‘No credentials supplied’
WHEN fa.aggregated_credential_status_id = 2 THEN ‘All credentials failed’
WHEN fa.aggregated_credential_status_id = 3 THEN ‘Credentials partially successful’
WHEN fa.aggregated_credential_status_id = 4 THEN ‘All credentials successful’
WHEN fa.aggregated_credential_status_id = -1 THEN ‘None of the applicable services were discovered in the scan’
END AS “Credential Status”,
to_char (fa.scan_finished, ‘mm/dd/yyyy’) AS “Date Scan Completed”
FROM fact_asset fa
LEFT JOIN dim_asset da USING (asset_id)
LEFT JOIN custom_tags ct USING (asset_id)
LEFT JOIN owner_tags ot USING (asset_id)
LEFT JOIN dim_operating_system dos USING (operating_system_id)
ORDER BY fa.aggregated_credential_status_id DESC, ot.owner_tags DESC