Hello wonderful support people. I’m wanting to modify the awesome existing report query below to only return entries for a specific CA - "Issuer LIKE “cert authority” etc.
Is this possible?
Query:
SELECT
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)”
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)
This works great. Also added expiration variable at the end.
SELECT
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)”
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)
WHERE (json_certs.cert->>‘ssl.cert.issuer.dn’ LIKE ‘CN=contoso-cert01-CA, DC=example, DC=com’ or json_certs.cert->>‘ssl.cert.issuer.dn’ LIKE ‘CN=contoso-cert02-CA, DC=example, DC=com’)
and (CAST(json_certs.cert->>‘ssl.cert.not.valid.after’ AS DATE) - CURRENT_DATE) < 90
new to this form of reporting, where are you using this query? when I got to Report and choose SQL Query template and use this language, it says it is invalid at character 0.
if you copied and pasted FROM the discussion forums it’s probably failing because of the quotes. For some reason or another, the quotes are different ASCII characters when in the code pop out instead of the actual text fields.
exactly the issue, still didn’t get any results, so not sure where I may be going wrong, but it did validate the query this time. It also italicized the apostrophes.
I’ve got a public Entrust cert, so I’m basically looking for any certificate issued by “Entrust Root Certification Authority - G2” across my environment to find everywhere that this wildcard cert has been installed. I changed the -
WHERE (json_certs.cert->>‘ssl.cert.issuer.dn’ LIKE ‘CN=contoso-cert01-CA, DC=example, DC=com’ or json_certs.cert->>‘ssl.cert.issuer.dn’ LIKE ‘CN=contoso-cert02-CA, DC=example, DC=com’)
to
WHERE (json_certs.cert->>‘ssl.cert.issuer.dn’ LIKE ‘CN = Entrust Root Certification Authority - G2’ or json_certs.cert->>‘ssl.cert.issuer.dn’ LIKE ‘CN = Entrust Root Certification Authority - G2’)
Does this function like other “LIKE” statements where you’re kind of wildcarding or do I need to have a full path specified?
Hmm… not sure why it isn’t working. I know the cert is on many servers in my environment and the standard card “Assets with Expiring SSL certificates” is populated, so I know InsightVM is auditing them, but I’m still not getting anything from this query -
SELECT
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)”
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)
WHERE json_certs.cert->>‘ssl.cert.issuer.dn’ LIKE ‘%Entrust%’
and (CAST(json_certs.cert->>‘ssl.cert.not.valid.after’ AS DATE) - CURRENT_DATE) < 90
I was able to run the report with the original query which returns all issuers, then I grabbed the CN name from there and inserted it into the new query. You might have luck doing that.
Well this gives me nothing either, so I guess maybe my stuff just isn’t in the database as this. Either that, or there may just be an issue with my db. I just tried one of the sample inventory queries and it returned no rows in the preview, also, so maybe a support ticket is needed.
SELECT
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)”
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)
Ah, disregard, success! Thanks for tolerating my n00bness
My issue was what site I was pointing it at. I pointed it at my scanning site originally that is named Windows, but I changed the target to my “Rubrik Agents” site and viola!
