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...
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'