Rob's Blog

This is stuff.

Nokia Altiplano – Database query to list all fiber intents with no onts provisioned

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.

SQL
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 LIKE 'fiber'
) AS fiber_list
LEFT JOIN (
    SELECT 
        EXTRACTVALUE(intent_config, '//fiber-name') AS fiber_name, 
        COUNT(*) AS count
    FROM 
        intent
    WHERE 
        intent_type LIKE 'ont'
    GROUP BY 
        BINARY EXTRACTVALUE(intent_config, '//fiber-name')
) AS pon_ont_count
ON fiber_list.concatenated_value = pon_ont_count.fiber_name
WHERE pon_ont_count.count IS NULL OR pon_ont_count.count = 0;
    Leave a Reply

    Your email address will not be published. Required fields are marked *

    © 2025 Rob's Blog