Coolify DNS entries lookup

command.txt

docker exec -i coolify-db psql -U coolify -t -c "\
    SELECT
        json_agg(json_build_object(
            'uuid', uuid,
            'destination_id', destination_id,
            'name', name,
            'domain', domain,
            'ip', ip,
            'source_table', source_table,
            'docker_compose', docker_compose
        )) AS result
    FROM (
        SELECT
            a.uuid,
            a.destination_id,
            a.name,
            a.fqdn AS domain,
            s.ip,
            'applications' AS source_table,
            '' AS docker_compose -- Placeholder for applications table
        FROM
            applications a
        JOIN
            servers s ON a.destination_id = s.id
        UNION ALL
        SELECT
            se.uuid,
            se.destination_id,
            se.name,
            NULL AS domain,
            s.ip,
            'services' AS source_table,
            se.docker_compose
        FROM
            services se
        JOIN
            servers s ON se.destination_id = s.id
    ) subquery;"  | jq  'map(select(.domain == null) | {ip: .ip, hosts: [.docker_compose | capture("(?i)Host\\((?<host>.*?)\\)")]})'