Network Switch Connection Diagram Generator
This tool shows how network switch connections diagrams can be created in Excel using interface data from the switch. Excel can be a very handy tool for such drawings when there is a similar configuration across the network.
Download
The download file shows switch configurations for a small office consisting of three 8 port Cisco switches - one core switch, and two access switches one of which serves the Sales department and the other servers the Accounts department. The network diagram shows which port is in use (connected), available (enabled but not connected) or disabled administratively for network security.
How to use
Connection information from the swtiches are stored under the different sheets in the workbook. For a Cisco switch, this information can be obtained by running the command 'sh int status' on the command line interface of the switch. The output from this command is copied is pasted onto the relevant sheet. The data is converted stored in an Excel table, wtih the table name being the same as the switch name.
The connection drawing for each swtich can be obtained by choosing the relevant switchname from the dropdown option on the Drawing sheets.
How it works
The drawing on sheet "Drawing1" is created from a stock image of the switch. If a suitable image is not availble, it is easy to draw a swtich using just cell borders and fill colours as shown on the sheet "Drawing2".
The drawings on "Drawing1" and "Drawing2" use formulas to dynamically display network connection information by pulling data from the tables on the "Core", "Sales", and "Accounts" sheets.
Each drawing sheet represents a switch and its port connections.
Cells in the drawings (e.g., C7, AC7, C10, etc.) contain formulas that make use of the INDIRECT function to show the connections and statuses. These formulae are of the type:
=INDEX(INDIRECT(Switch&"[Name]"),PortX)and
=INDEX(INDIRECT(Switch&"[Status]"),PortX)The variable Switch is set to the relevant table name (e.g., "Core", "Sales", or "Accounts") depending on the switch being diagrammed.
PortX refers to the port number (e.g., Port1, Port2, etc.).
The formula retrieves the "Name" or "Status" for each port from the corresponding table on the other sheets.
In summary, the drawings use formulas to look up and display port names and statuses from the detailed tables on the "Core", "Sales", and "Accounts" sheets, so any changes in those tables are automatically reflected in the drawings.
The "lights" on the various ports of the swtich are created using Excel cells underneath the image. The ports in the switch image were set to transparent colour to make these cells visible. Conditional formatting is used to change the fill colours of these cells to show the port status. The port is shown red if it is disabled administratively, orange if it is available but not connected and green if it is connected.