Level: Major

Purpose: A lot of ATCs processes, including SLA outages and Salesforce integration, rely on the network identifier. If an element is incorrectly loaded or loaded using the topology discovery, this field may be incorrect. This will need to be populated using the parent elements site - site number + side identifier e.g. 211597 Xcel Energy Center

Scenario: Elements are missing network identifiers or are set to n/a

Resolution: Update the network identifiers

Manual Action Steps:

Get the affected elements

SELECT * FROM network_element ne
WHERE (ne.network_identifier = 'n/a'
OR ne.network_identifier IS NULL
OR ne.network_identifier = '')
AND ne.on_air
AND ne.show_in_monitor
AND ne_type != 'CUSTOMER-NETWORK'
AND technology NOT IN ('NON_DAS', 'n/a')
AND ne.parent_id != 3045 # orphan
AND ne.date_added > '2022-12-01 00:00:00';

Update the parent elements by their site

UPDATE network_element 
JOIN site_network_element ON site_network_element.network_element_id = network_element.id
JOIN site ON site.id = site_network_element.site_network_elements_id 
SET network_identifier = CONCAT(site.site_number, ' ', site.site_identifier)
WHERE id in 
  (SELECT ne.id FROM network_element ne
    WHERE (ne.network_identifier = 'n/a'
    OR ne.network_identifier IS NULL
    OR ne.network_identifier = '')
    AND ne.on_air
    AND ne.show_in_monitor
    AND ne_type != 'CUSTOMER-NETWORK'
    AND technology NOT IN ('NON_DAS', 'n/a')
    AND ne.parent_id != 3045 # orphan
    AND ne.date_added > '2022-12-01 00:00:00');

Update child elements

UPDATE network_element
JOIN network_element parent ON parent.id = network_element.parent_id
SET network_element.network_identifier = parent.network_identifier
WHERE parent_id in 
  (SELECT ne.id FROM network_element ne
    WHERE (ne.network_identifier = 'n/a'
    OR ne.network_identifier IS NULL
    OR ne.network_identifier = '')
    AND ne.on_air
    AND ne.show_in_monitor
    AND ne_type != 'CUSTOMER-NETWORK'
    AND technology NOT IN ('NON_DAS', 'n/a')
    AND ne.parent_id != 3045 # orphan
    AND ne.date_added > '2022-12-01 00:00:00');

Auto Clear: When network identifiers are updated