Skip to content

Instantly share code, notes, and snippets.

@jfrantz1-r7

jfrantz1-r7/expired_cert.sql

Created April 17, 2019 17:55
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save jfrantz1-r7/7d2d51e9ff4897369e5528a2bfd6eef9 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save jfrantz1-r7/7d2d51e9ff4897369e5528a2bfd6eef9 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
expired_cert.sql
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 ip_address, host_name, mac_address, date(ced.value)
FROM dim_asset
JOIN cert_expiration_dates AS ced USING (asset_id)
WHERE CURRENT_TIMESTAMP - cast(ced.value AS DATE) <= INTERVAL '90 days'
ORDER BY date
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment