Find orphaned DID's

Status
Not open for further replies.

KonradSC

Active Member
Mar 10, 2017
166
98
28
At this point we have almost 8,000 Destinations on our platform. It's easy to setup a destination that routes to an extension and then later delete the extension without updating the destination. The destination is now set to transfer to an invalid number. I wanted a way to audit our system to find these orphaned DID's. The below query was my solution. It's version 1.1, so I'm sure it can be improved. Right now it only checks for "transfer" actions. It does not search for valid recordings.

Enjoy...

Code:
WITH valid_extensions AS (
    WITH destinations_table AS (
            SELECT v_destinations.destination_number,
                array_to_string((SELECT regexp_matches(v_destinations.destination_data, '\d+')),',') as transfer_extension,
                v_destinations.domain_uuid
            FROM v_destinations, v_domains
            WHERE destination_app = 'transfer'
            AND v_destinations.domain_uuid = v_domains.domain_uuid
        ),    extensions_table AS (
            SELECT extension, domain_uuid
            FROM v_extensions
        )
    SELECT destinations_table.destination_number, destinations_table.transfer_extension, extensions_table.extension
    FROM destinations_table, extensions_table
    WHERE extensions_table.extension = destinations_table.transfer_extension
    AND destinations_table.domain_uuid = extensions_table.domain_uuid
), valid_ivrs AS (
    WITH destinations_table AS (
            SELECT v_destinations.destination_number,
                v_destinations.destination_data,
                array_to_string((SELECT regexp_matches(v_destinations.destination_data, '\d+')),',') as transfer_extension,
                v_destinations.destination_description,
                v_destinations.domain_uuid,
                v_domains.domain_name
            FROM v_destinations, v_domains
            WHERE destination_app = 'transfer'
            AND v_destinations.domain_uuid = v_domains.domain_uuid
        ),    ivrs_table AS (
            SELECT ivr_menu_extension, domain_uuid
            FROM v_ivr_menus
        )
    SELECT destinations_table.destination_number, destinations_table.destination_data, destinations_table.transfer_extension, destinations_table.destination_description, destinations_table.domain_name, ivrs_table.ivr_menu_extension
    FROM destinations_table, ivrs_table
    WHERE ivrs_table.ivr_menu_extension = destinations_table.transfer_extension
    AND destinations_table.domain_uuid = ivrs_table.domain_uuid
), valid_ring_groups AS (
    WITH destinations_table AS (
            SELECT v_destinations.destination_number,
                array_to_string((SELECT regexp_matches(v_destinations.destination_data, '\d+')),',') as transfer_extension,
                v_destinations.domain_uuid
            FROM v_destinations, v_domains
            WHERE destination_app = 'transfer'
            AND v_destinations.domain_uuid = v_domains.domain_uuid
        ),    ring_groups_table AS (
            SELECT ring_group_extension, domain_uuid
            FROM v_ring_groups
        )
    SELECT destinations_table.destination_number, destinations_table.transfer_extension, ring_groups_table.ring_group_extension
    FROM destinations_table, ring_groups_table
    WHERE ring_groups_table.ring_group_extension = destinations_table.transfer_extension
    AND destinations_table.domain_uuid = ring_groups_table.domain_uuid
), valid_call_flows AS (
    WITH destinations_table AS (
            SELECT v_destinations.destination_number,
                array_to_string((SELECT regexp_matches(v_destinations.destination_data, '\d+')),',') as transfer_extension,
                v_destinations.domain_uuid
            FROM v_destinations, v_domains
            WHERE destination_app = 'transfer'
            AND v_destinations.domain_uuid = v_domains.domain_uuid
        ),    call_flows_table AS (
            SELECT call_flow_extension, domain_uuid
            FROM v_call_flows
        )
    SELECT destinations_table.destination_number, destinations_table.transfer_extension, call_flows_table.call_flow_extension
    FROM destinations_table, call_flows_table
    WHERE call_flows_table.call_flow_extension = destinations_table.transfer_extension
    AND destinations_table.domain_uuid = call_flows_table.domain_uuid
), valid_call_centers AS (
    WITH destinations_table AS (
            SELECT v_destinations.destination_number,
                array_to_string((SELECT regexp_matches(v_destinations.destination_data, '\d+')),',') as transfer_extension,
                v_destinations.domain_uuid
            FROM v_destinations, v_domains
            WHERE destination_app = 'transfer'
            AND v_destinations.domain_uuid = v_domains.domain_uuid
        ),    call_centers_table AS (
            SELECT queue_extension, domain_uuid
            FROM v_call_center_queues
        )
    SELECT destinations_table.destination_number, destinations_table.transfer_extension, call_centers_table.queue_extension
    FROM destinations_table, call_centers_table
    WHERE call_centers_table.queue_extension = destinations_table.transfer_extension
    AND destinations_table.domain_uuid = call_centers_table.domain_uuid
), valid_conference_centers AS (
    WITH destinations_table AS (
            SELECT v_destinations.destination_number,
                array_to_string((SELECT regexp_matches(v_destinations.destination_data, '\d+')),',') as transfer_extension,
                v_destinations.domain_uuid
            FROM v_destinations, v_domains
            WHERE destination_app = 'transfer'
            AND v_destinations.domain_uuid = v_domains.domain_uuid
        ),    conference_centers_table AS (
            SELECT conference_center_extension, domain_uuid
            FROM v_conference_centers
        )
    SELECT destinations_table.destination_number, destinations_table.transfer_extension, conference_centers_table.conference_center_extension
    FROM destinations_table, conference_centers_table
    WHERE conference_centers_table.conference_center_extension = destinations_table.transfer_extension
    AND destinations_table.domain_uuid = conference_centers_table.domain_uuid
), valid_faxes AS (
    WITH destinations_table AS (
            SELECT v_destinations.destination_number,
                array_to_string((SELECT regexp_matches(v_destinations.destination_data, '\d+')),',') as transfer_extension,
                v_destinations.domain_uuid
            FROM v_destinations, v_domains
            WHERE destination_app = 'transfer'
            AND v_destinations.domain_uuid = v_domains.domain_uuid
        ),    faxes_table AS (
            SELECT fax_extension, domain_uuid
            FROM v_fax
        )
    SELECT destinations_table.destination_number, destinations_table.transfer_extension, faxes_table.fax_extension
    FROM destinations_table, faxes_table
    WHERE faxes_table.fax_extension = destinations_table.transfer_extension
    AND destinations_table.domain_uuid = faxes_table.domain_uuid
), valid_voicemails AS (
    WITH destinations_table AS (
            SELECT v_destinations.destination_number,
                array_to_string((SELECT regexp_matches(v_destinations.destination_data, '\*99\d+')),',') as transfer_extension,
                v_destinations.domain_uuid
            FROM v_destinations, v_domains
            WHERE destination_app = 'transfer'
            AND v_destinations.domain_uuid = v_domains.domain_uuid
        ),    voicemails_table AS (
            SELECT concat('*99',cast(voicemail_id AS varchar(255))) AS voicemail_id_concat, domain_uuid
            FROM v_voicemails
        )
    SELECT destinations_table.destination_number, destinations_table.transfer_extension, voicemails_table.voicemail_id_concat
    FROM destinations_table, voicemails_table
    WHERE voicemails_table.voicemail_id_concat = destinations_table.transfer_extension
    AND destinations_table.domain_uuid = voicemails_table.domain_uuid
), valid_directory_table AS (
    WITH destinations_table AS (
            SELECT v_destinations.destination_number,
                array_to_string((SELECT regexp_matches(v_destinations.destination_data, '\*411')),',') as transfer_extension,
                v_destinations.domain_uuid
            FROM v_destinations, v_domains
            WHERE destination_app = 'transfer'
            AND v_destinations.domain_uuid = v_domains.domain_uuid
    )
    SELECT v_destinations.destination_number, v_destinations.destination_description, v_destinations.destination_data
    FROM v_destinations, destinations_table
    WHERE v_destinations.destination_number = destinations_table.destination_number
    AND destinations_table.transfer_extension = '*411'
), valid_check_voicemail_table AS (
    WITH destinations_table AS (
            SELECT v_destinations.destination_number,
                array_to_string((SELECT regexp_matches(v_destinations.destination_data, '\*98')),',') as transfer_extension,
                v_destinations.domain_uuid
            FROM v_destinations, v_domains
            WHERE destination_app = 'transfer'
            AND v_destinations.domain_uuid = v_domains.domain_uuid
    )
    SELECT v_destinations.destination_number, v_destinations.destination_description, v_destinations.destination_data
    FROM v_destinations, destinations_table
    WHERE v_destinations.destination_number = destinations_table.destination_number
    AND destinations_table.transfer_extension = '*98'
), valid_time_conditions AS (
    WITH destinations_table AS (
            SELECT v_destinations.destination_number,
                array_to_string((SELECT regexp_matches(v_destinations.destination_data, '\d+')),',') as transfer_extension,
                v_destinations.domain_uuid
            FROM v_destinations, v_domains
            WHERE destination_app = 'transfer'
            AND v_destinations.domain_uuid = v_domains.domain_uuid
        ),    time_conditions_table AS (
            SELECT dialplan_number, domain_uuid
            FROM v_dialplans
            WHERE app_uuid = '4b821450-926b-175a-af93-a03c441818b1'
        )
    SELECT destinations_table.destination_number, destinations_table.transfer_extension, time_conditions_table.dialplan_number
    FROM destinations_table, time_conditions_table
    WHERE time_conditions_table.dialplan_number = destinations_table.transfer_extension
    AND destinations_table.domain_uuid = time_conditions_table.domain_uuid
)
SELECT destination_number, destination_description, destination_data
FROM v_destinations
WHERE destination_number NOT IN (SELECT destination_number FROM valid_extensions)
AND destination_number NOT IN (SELECT destination_number FROM valid_ivrs)
AND destination_number NOT IN (SELECT destination_number FROM valid_ring_groups)
AND destination_number NOT IN (SELECT destination_number FROM valid_call_flows)
AND destination_number NOT IN (SELECT destination_number FROM valid_call_centers)
AND destination_number NOT IN (SELECT destination_number FROM valid_conference_centers)
AND destination_number NOT IN (SELECT destination_number FROM valid_faxes)
AND destination_number NOT IN (SELECT destination_number FROM valid_voicemails)
AND destination_number NOT IN (SELECT destination_number FROM valid_directory_table)
AND destination_number NOT IN (SELECT destination_number FROM valid_check_voicemail_table)
AND destination_number NOT IN (SELECT destination_number FROM valid_time_conditions)
AND destination_app = 'transfer'
 
Status
Not open for further replies.