tem_openssl

CREATE VIEW tem_openssl AS
WITH parsed AS (
  SELECT
    uniform_resource_id,
    tenant_id,
    tenant_name,
    ur_ingest_session_id,
    cert_index,
    metadata,

    -- full subject line starting at the first "s:" up to the newline
    CASE
      WHEN instr(metadata, 's:') = 0 THEN ''
      ELSE trim(
        substr(
          metadata,
          instr(metadata, 's:'),
          CASE
            WHEN instr(substr(metadata, instr(metadata, 's:')), char(10)) > 0
              THEN instr(substr(metadata, instr(metadata, 's:')), char(10)) - 1
            ELSE length(metadata) - instr(metadata, 's:') + 1
          END
        )
      )
    END AS subject_line,

    -- full issuer line starting at the first "i:" up to the newline
    CASE
      WHEN instr(metadata, 'i:') = 0 THEN ''
      ELSE trim(
        substr(
          metadata,
          instr(metadata, 'i:'),
          CASE
            WHEN instr(substr(metadata, instr(metadata, 'i:')), char(10)) > 0
              THEN instr(substr(metadata, instr(metadata, 'i:')), char(10)) - 1
            ELSE length(metadata) - instr(metadata, 'i:') + 1
          END
        )
      )
    END AS issuer_line

  FROM tem_openssl_original_txt
)

SELECT
  uniform_resource_id,
  tenant_id,
  tenant_name,
  ur_ingest_session_id,
  cert_index,

  /* common_name (from subject_line -> CN=...) */
  CASE
    WHEN instr(subject_line, 'CN=') = 0 THEN ''
    ELSE trim(
      substr(
        subject_line,
        instr(subject_line, 'CN=') + 3,
        CASE
          WHEN instr(substr(subject_line, instr(subject_line, 'CN=') + 3), ',') > 0
            THEN instr(substr(subject_line, instr(subject_line, 'CN=') + 3), ',') - 1
          ELSE length(subject_line) - (instr(subject_line, 'CN=') + 3) + 1
        END
      )
    )
  END AS common_name,

  /* subject_organization (from subject_line -> O=...) */
  CASE
    WHEN instr(subject_line, 'O=') = 0 THEN ''
    ELSE trim(
      substr(
        subject_line,
        instr(subject_line, 'O=') + 2,
        CASE
          WHEN instr(substr(subject_line, instr(subject_line, 'O=') + 2), ',') > 0
            THEN instr(substr(subject_line, instr(subject_line, 'O=') + 2), ',') - 1
          ELSE length(subject_line) - (instr(subject_line, 'O=') + 2) + 1
        END
      )
    )
  END AS subject_organization,

  /* issuer_country (from issuer_line -> C=...) */
  CASE
    WHEN instr(issuer_line, 'C=') = 0 THEN ''
    ELSE trim(
      substr(
        issuer_line,
        instr(issuer_line, 'C=') + 2,
        CASE
          WHEN instr(substr(issuer_line, instr(issuer_line, 'C=') + 2), ',') > 0
            THEN instr(substr(issuer_line, instr(issuer_line, 'C=') + 2), ',') - 1
          ELSE length(issuer_line) - (instr(issuer_line, 'C=') + 2) + 1
        END
      )
    )
  END AS issuer_country,

  /* issuer_common_name (from issuer_line -> CN=...) */
  CASE
    WHEN instr(issuer_line, 'CN=') = 0 THEN ''
    ELSE trim(
      substr(
        issuer_line,
        instr(issuer_line, 'CN=') + 3,
        CASE
          WHEN instr(substr(issuer_line, instr(issuer_line, 'CN=') + 3), ',') > 0
            THEN instr(substr(issuer_line, instr(issuer_line, 'CN=') + 3), ',') - 1
          ELSE length(issuer_line) - (instr(issuer_line, 'CN=') + 3) + 1
        END
      )
    )
  END AS issuer_common_name,

  /* issuer_organization (from issuer_line -> O=...) */
  CASE
    WHEN instr(issuer_line, 'O=') = 0 THEN ''
    ELSE trim(
      substr(
        issuer_line,
        instr(issuer_line, 'O=') + 2,
        CASE
          WHEN instr(substr(issuer_line, instr(issuer_line, 'O=') + 2), ',') > 0
            THEN instr(substr(issuer_line, instr(issuer_line, 'O=') + 2), ',') - 1
          ELSE length(issuer_line) - (instr(issuer_line, 'O=') + 2) + 1
        END
      )
    )
  END AS issuer_organization,

  /* issued_date (NotBefore:) */
  CASE
    WHEN instr(metadata, 'NotBefore:') = 0 THEN ''
    ELSE trim(
      substr(
        metadata,
        instr(metadata, 'NotBefore:') + length('NotBefore:'),
        CASE
          WHEN instr(substr(metadata, instr(metadata, 'NotBefore:') + length('NotBefore:')), ';') > 0
            THEN instr(substr(metadata, instr(metadata, 'NotBefore:') + length('NotBefore:')), ';') - 1
          WHEN instr(substr(metadata, instr(metadata, 'NotBefore:') + length('NotBefore:')), char(10)) > 0
            THEN instr(substr(metadata, instr(metadata, 'NotBefore:') + length('NotBefore:')), char(10)) - 1
          ELSE length(metadata) - (instr(metadata, 'NotBefore:') + length('NotBefore:')) + 1
        END
      )
    )
  END AS issued_date,

  /* expires_date (NotAfter:) */
  CASE
    WHEN instr(metadata, 'NotAfter:') = 0 THEN ''
    ELSE trim(
      substr(
        metadata,
        instr(metadata, 'NotAfter:') + length('NotAfter:'),
        CASE
          WHEN instr(substr(metadata, instr(metadata, 'NotAfter:') + length('NotAfter:')), char(10)) > 0
            THEN instr(substr(metadata, instr(metadata, 'NotAfter:') + length('NotAfter:')), char(10)) - 1
          WHEN instr(substr(metadata, instr(metadata, 'NotAfter:') + length('NotAfter:')), ';') > 0
            THEN instr(substr(metadata, instr(metadata, 'NotAfter:') + length('NotAfter:')), ';') - 1
          ELSE length(metadata) - (instr(metadata, 'NotAfter:') + length('NotAfter:')) + 1
        END
      )
    )
  END AS expires_date

FROM parsed
WHERE instr(subject_line, 'CN=') > 0