This query will count the number of ONTs provisioned per port. Useful for reporting and capacity planning to ensure split ratios are followed correctly. This query normalises the output of the fiber/PON names between ISAM/Lightspan by replacing underscores in the name with periods.
SELECT
fiber_name_clean AS fiber_name,
COUNT(*) AS count
FROM (
SELECT
REPLACE(REPLACE(REPLACE(REGEXP_SUBSTR(intent_config, '<fiber-name>([^<]+)</fiber-name>'), '<fiber-name>', ''), '</fiber-name>', ''), '_', '.') AS fiber_name_clean
FROM
intent
WHERE
intent_type LIKE 'ont'
) AS extracted
GROUP BY
BINARY fiber_name_clean