This Excel based macro will take links identified as Site A - Site B and generate a network summary based on how all the links are connected. The tool will provide a summary of network related information, i.e. # of hops from one site to a main site, link types (is the link in a ring configuration?), etc...
The attached Excel spreadsheet has 4 tabs. The "Network Diagram" tab is for information purposes only since it is not involved in any calculations. The "Link List Input" and "Fiber ID Input" are the two tabs that the macro will require for the calculations. The "Link List Input" identifies the site to site link list and the "Fiber ID Input" identifies the main hubs.
The "Link Output" has three columns that require calculations. The "Link Ouput" should basically recognize the connectivity as shown in the "Network Diagram" based on the "Link List Input". The Fiber PoP ID column is determined from its association with all the links, i.e. all sites are required to home back into a single Fiber PoP. The Link Type will either be a ring link or a spur link. The spur link will be further classified with "X - Y Spur" where X is the number of links away from the ring node and Y is the total number of spur links on that specific chain. The "Sites Traversing the Link" column counts the number of sites that will traverse that specific link back to the Fiber PoP based on a the minimum # of hops.
The "Site Output" tab generates a summary based on sites. The "Site Classification" will be either
3)Hub (intermediate site on a daisy chain)
"Min # of hops" column will generate the least number of hops from the site back to its respective Fiber PoP. "Max # of hops" column will generate the max number of hops from the site back to its respective Fiber PoP. Columns F and G will output the actual path as shown in attachment. The "# of remote sites homing" will output the number of sites that is passing through the site in question (including the site).
This tool should be flexible to include any number of links and fiber PoPs.