Report on SSL Certificate Usage in Mirth Connect

mirth-cert-report.sql

with channel_xml as (
    select
        name,
        xmlparse(document channel) as channel_xml
    from channel c
)
, destination_connector_xml as (
    SELECT
        name as channel_name,
        unnest(xpath('//destinationConnectors/connector/name/text()', channel_xml))::TEXT as connector_name,
        unnest(xpath('//destinationConnectors/connector', channel_xml)) as connector_xml
    FROM
        channel_xml
)
, source_connector_xml as (
    SELECT
        name as channel_name,
        'Source' as connector_name,
        unnest(xpath('//sourceConnector', channel_xml)) as connector_xml
    FROM
        channel_xml
)
, all_connector_xml AS (
    SELECT * FROM source_connector_xml
     UNION ALL
     SELECT * FROM destination_connector_xml
)
, rows as ( select
                 unnest(xpath('//trustedCertificates/trustCACerts/text()', connector_xml))::TEXT as trust_ca_certs,
                 unnest(xpath('//trustedCertificates/trustedCertificateAliases/string/text()', connector_xml))::TEXT as trusted_cert_alias,
                 unnest(xpath('//localCertificateAlias/text()', connector_xml))::TEXT as private_alias,
                 unnest(xpath('//keyAlias/text()', connector_xml))::TEXT as interop_alias
                 , channel_name::TEXT as channel_name
                , connector_name::TEXT as connector_name
             from all_connector_xml
)
SELECT * FROM "rows"
ORDER BY channel_name ASC, connector_name ASC;

Written for postgres. Be aware of the XPATH parsing changes around PG version 12

Adding WHERE trusted_cert_alias IS NOT NULL OR private_alias IS NOT NULL OR interop_alias IS NOT NULL reduces the report to only channels and connectors that are using soemthing other than the default trusted certs