Knowledge Base

Ask A Question



Reporting Expiring Certs.. issues with JOIN

To start I'd like to thank Jasey DePriest for getting me this far into the Rabbit hole from the 5a0565fd82cf83001c169616 discussion post. I have the below query and the problem I am dealing with is if there is an owner tag I am getting duplicate entries the second being a null. The desire is to wind up with all assets with expiring certificates and for those with a tag like Owner to show that tag otherwise null. I built an API with powershell (ironically) that pulls from our inventory system and updates tags on known devices, and having this in the report will help identify what teams need to be engaged. Also as I am no database wizard much less with PostgreSQL.. this is a query cobbled together from other queries JOIN'd to the the one Jasey posted which got me all the broken down SSL cert info pulled from one column. So any optimizations or improvements are more then welcome ~Will ------------------------------------------------------------------------------------------------------------------ WITH owner_asset_tags AS ( SELECT DISTINCT asset_group_id, name FROM dim_asset_group WHERE (lower( LIKE 'owner - %') ) 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", dos.description AS "Operating System", fa.scan_started AS "Last Scan Date", AS "Asset Group", 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->>'' AS "Algorithm", json_certs.cert->>'' 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)" 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) JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id JOIN fact_asset fa ON fa.asset_id = da.asset_id JOIN dim_asset_group_asset daga ON daga.asset_id = da.asset_id LEFT OUTER JOIN owner_asset_tags oat ON oat.asset_group_id = daga.asset_group_id WHERE (cast(json_certs.cert->>'ssl.cert.not.valid.after' AS DATE) - CURRENT_TIMESTAMP <= INTERVAL '90 days')

Posted by William Pfeifer 4 months ago