Coolify - Pull IP's and their associated domains.

getDNS.sh

#!/bin/bash

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 -n --slurpfile domains /dev/stdin \
    '$domains[0] | reduce .[] as $item ({};
        if $item.domain != null then
            .[$item.ip] += [($item.domain | split(",")[] | sub("^https?://|`$";""))]
        else
            .[$item.ip] += [($item.docker_compose | capture("(?i)Host\\((?<host>.*?)\\)") | .host | sub("^`|`$|^https?://";"")), ($item.docker_compose | capture("(?i)Host\\(www\\.(?<host>.*?)\\)") | "www.\(.host)" | select(. != null) | sub("^`|`$|^https?://";""))]
        end
    ) | map_values(map_values(if type == "string" then sub("`$";"") else . end))' \
> DNS_Entries.json