Useful for checking the utilisation of your OLTs, this query will list all of the PONs which do not have any ONTs provisioned on them. It takes into account the different naming conventions between ISAM vs Lightspan as well.
SELECT
fiber_list.concatenated_value
FROM (
SELECT
CONCAT(
EXTRACTVALUE(intent_config, '//device-name'),
'.',
EXTRACTVALUE(intent_config, '//pon-id')
) AS concatenated_value
FROM intent
WHERE intent_type = 'fiber'
) AS fiber_list
LEFT JOIN (
SELECT
EXTRACTVALUE(intent_config, '//fiber-name') AS fiber_name,
COUNT(*) AS count
FROM intent
WHERE intent_type = 'ont'
GROUP BY BINARY EXTRACTVALUE(intent_config, '//fiber-name')
) AS pon_ont_count
ON fiber_list.concatenated_value = pon_ont_count.fiber_name
OR REPLACE(fiber_list.concatenated_value, '.', '_') = pon_ont_count.fiber_name
WHERE pon_ont_count.count IS NULL OR pon_ont_count.count = 0;