Rob's Blog

This is stuff.

Nokia Altiplano – Database Query to list quantity of ONTs provisioned on each PON port

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.

SQL
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

    Leave a Reply

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

    © 2025 Rob's Blog