AnsweredAssumed Answered

issues with SQL query for expiring certs

Question asked by Johnny Smith on Nov 15, 2016
Latest reply on Nov 16, 2016 by Johnny Smith

I'm having some issues with a SQL query report where I'm trying to get the certificates expiring in 90 days or less but I also need to get the service port number because they don't all run on 443. That way if you wanted to validate something you know where to look for the certificate.

 

I'm using:

WITH
   cert_expiration_dates AS (
      SELECT DISTINCT asset_id, service_id, name,value
      FROM dim_asset_service_configuration
      WHERE lower(name) LIKE '%ssl.cert.not.valid.after'
   )
SELECT DISTINCT ip_address, host_name AS "Hostname", fasvi.port AS "Port",ced.value AS "Expiration Date"
FROM dim_asset, fact_asset_scan_vulnerability_instance fasvi
   JOIN cert_expiration_dates AS ced USING (asset_id)
WHERE (cast(ced.value AS DATE) - CURRENT_TIMESTAMP  <= INTERVAL '90 days') AND (cast(ced.value AS DATE) - CURRENT_TIMESTAMP  > INTERVAL '0 days')

 

However, this does not select distinct ip addresses as I wanted, probably due to the fact it has all these fictitious entries in there for expiry dates that don't actually exist. I generated a test report and then checked on the expiry date for the certificate and it wasn't even in the report.

 

Does anyone know how I can adjust this to get a warning about certificates that will expire within 90 days (or have expired), but then also generate what port the cert was on?

Outcomes