Used for cleaning up data, shows ONT records that don’t have corresponding l2-user entries. Be warned that the query may take considerable time to complete depending on the number of entries present. As a guideline, 10,000 records takes just under 3 minutes to run on our all-flash database server.
SELECT
ont_query.ont,
ont_query.required_network_state,
ont_query.fiber_name,
ont_query.ont_type,
ont_query.expected_serial_number
FROM
(
SELECT
target AS ont,
required_network_state,
REPLACE(REPLACE(REPLACE(REGEXP_SUBSTR(intent_config, '<fiber-name>([^<]+)</fiber-name>'), '<fiber-name>', ''), '</fiber-name>', ''), '_', '.') AS fiber_name,
REPLACE(REPLACE(REPLACE(REGEXP_SUBSTR(intent_config, '<ont-type>([^<]+)</ont-type>'), '<ont-type>', ''), '</ont-type>', ''), '_', '.') AS ont_type,
REPLACE(REPLACE(REPLACE(REGEXP_SUBSTR(intent_config, '<expected-serial-number>([^<]+)</expected-serial-number>'), '<expected-serial-number>', ''), '</expected-serial-number>', ''), '_', '.') AS expected_serial_number
FROM
ac.intent
WHERE
intent_type LIKE 'ont'
) AS ont_query
LEFT JOIN
(
SELECT
REPLACE(REPLACE(REPLACE(REGEXP_SUBSTR(intent_config, '<user-device-name>([^<]+)</user-device-name>'), '<user-device-name>', ''), '</user-device-name>', ''), '_', '.') AS target
FROM
ac.intent
WHERE
intent_type LIKE 'l2-user'
) AS l2_user_query
ON
ont_query.ont = l2_user_query.target
WHERE
l2_user_query.target IS NULL;