Rob's Blog

This is stuff.

Nokia Altiplano – Database query to compare ont intents vs l2-user intents

Used for cleaning up data, shows ONT records that don’t have corresponding l2-user entries. Be warned that the query may take considerable time to complete depending on the number of entries present. As a guideline, 10,000 records takes just under 3 minutes to run on our all-flash database server.

SQL
SELECT 
    ont_query.ont,
    ont_query.required_network_state,
    ont_query.fiber_name,
    ont_query.ont_type,
    ont_query.expected_serial_number
FROM 
    (
        SELECT 
            target AS ont,
            required_network_state,
            REPLACE(REPLACE(REPLACE(REGEXP_SUBSTR(intent_config, '<fiber-name>([^<]+)</fiber-name>'), '<fiber-name>', ''), '</fiber-name>', ''), '_', '.') AS fiber_name,
            REPLACE(REPLACE(REPLACE(REGEXP_SUBSTR(intent_config, '<ont-type>([^<]+)</ont-type>'), '<ont-type>', ''), '</ont-type>', ''), '_', '.') AS ont_type,
            REPLACE(REPLACE(REPLACE(REGEXP_SUBSTR(intent_config, '<expected-serial-number>([^<]+)</expected-serial-number>'), '<expected-serial-number>', ''), '</expected-serial-number>', ''), '_', '.') AS expected_serial_number
        FROM 
            ac.intent
        WHERE 
            intent_type LIKE 'ont'
    ) AS ont_query
LEFT JOIN 
    (
        SELECT 
            REPLACE(REPLACE(REPLACE(REGEXP_SUBSTR(intent_config, '<user-device-name>([^<]+)</user-device-name>'), '<user-device-name>', ''), '</user-device-name>', ''), '_', '.') AS target
        FROM 
            ac.intent
        WHERE 
            intent_type LIKE 'l2-user'
    ) AS l2_user_query
ON 
    ont_query.ont = l2_user_query.target
WHERE 
    l2_user_query.target IS NULL;
    Leave a Reply

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

    © 2025 Rob's Blog