Goal #1: “count the number of unexpired pre-certificates known to CT that contain a CA/Browser Forum Certificate Policy OID (i.e., DV/OV/EV) and the TLS feature extension”

SELECT COUNT(DISTINCT fingerprint_sha256)

FROM `censys-io.certificates_public.certificates`, unnest (parsed.extensions.certificate_policies) as cert_policies, unnest (parsed.unknown_extensions) as unknown_ext

WHERE parsed.validity.end >= TIMESTAMP("2023-01-31")

AND validation.google_ct_primary.valid = TRUE

AND parsed.extensions.ct_poison is TRUE

AND parsed.extensions.basic_constraints.is_ca IS NOT TRUE

AND (cert_policies.id = "2.23.140.1.2.1" OR cert_policies.id = "2.23.140.1.2.2" OR cert_policies.id = "2.23.140.1.1")

AND unknown_ext.id = "1.3.6.1.5.5.7.1.24"

The query returned 301,300 entries.

Goal #2: “count the number of unexpired pre-certificates known to CT that contain a CA/Browser Forum Certificate Policy OID (i.e., DV/OV/EV)”

SELECT COUNT(DISTINCT fingerprint_sha256)

FROM `censys-io.certificates_public.certificates`, unnest (parsed.extensions.certificate_policies) as cert_policies

WHERE parsed.validity.end >= TIMESTAMP("2023-01-31")

AND validation.google_ct_primary.valid = TRUE

AND parsed.extensions.ct_poison is TRUE

AND parsed.extensions.basic_constraints.is_ca IS NOT TRUE

AND (cert_policies.id = "2.23.140.1.2.1" OR cert_policies.id = "2.23.140.1.2.2" OR cert_policies.id = "2.23.140.1.1")

The query returned 484,341,565 entries.

Percent of Population with “MUST-STAPLE”:  301,300 / 484,341,565 = .0622%