This query is useful to easily know what SFPs are installed in the field in all those empty ports! You will need to adjust the SFP model numbers to suit the ones that you use. The query assumes that if the model number is not one that you have defined, that it’s automatically a ISAM GPON.
SELECT
COALESCE(i1.target, i2.target) AS target,
CASE
WHEN COALESCE(i1.intent_config, i2.intent_config) LIKE '%3FE47581BF%' THEN 'Yes'
ELSE 'No'
END AS Lightspan_MPM,
CASE
WHEN COALESCE(i1.intent_config, i2.intent_config) LIKE '%3FE53441BC%' THEN 'Yes'
ELSE 'No'
END AS Lightspan_GPON,
CASE
WHEN COALESCE(i1.intent_config, i2.intent_config) NOT LIKE '%3FE47581BF%'
AND COALESCE(i1.intent_config, i2.intent_config) NOT LIKE '%3FE53441BC%'
AND COALESCE(i1.intent_config, i2.intent_config) IS NOT NULL THEN 'Yes'
ELSE 'No'
END AS ISAM_GPON
FROM (
SELECT
CONCAT(EXTRACTVALUE(intent_config, '//device-name'), '.', EXTRACTVALUE(intent_config, '//pon-id')) AS port_id
FROM intent
WHERE intent_type = 'fiber'
) AS f
LEFT JOIN (
SELECT * FROM intent WHERE intent_type = 'fiber'
) AS i1
ON f.port_id = i1.target
LEFT JOIN (
SELECT * FROM intent WHERE intent_type = 'fiber'
) AS i2
ON REPLACE(f.port_id, '.', '_') = i2.target
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
-- Match both dot and underscore formats for ONT fibre assignment
ON f.port_id = pon_ont_count.fiber_name
OR REPLACE(f.port_id, '.', '_') = pon_ont_count.fiber_name
WHERE pon_ont_count.count IS NULL OR pon_ont_count.count = 0;