Spreadsheets…. They’re free, right?
It’s still common to find physical infrastructure documentation in spreadsheets, employing completely manual processes. This is not because those responsible are unaware of the variety of asset management or cable management software tools available, nor is it because of budget constraints. The reason spreadsheets are still used today is that Microsoft Excel is readily available on most PCs and, therefore, seemingly “costs nothing.” This methodology has since been adopted beyond the back office in data centers by creating a second, or third, spreadsheet to assist documenting power, space, asset information, etc. Today, there is an increasing awareness that the spreadsheet is not only the most expensive methodology, but also is grossly inadequate for managing today’s mission critical networks, where accurate documentation is essential for: • • • • • • • Implementing ITIL best practices Sarbanes Oxley compliance Asset management Facility management Network infrastructure planning Reconfiguration management / CMDB Troubleshooting
This paper discusses the disadvantages and ineffectiveness of using a spreadsheet, such as Excel, or marked-up floor plans, to manage the physical infrastructure, HVAC and power, versus the advantages and benefits of using iTRACS Physical Layer Manager. The issues addressed in this paper are: • • • • • • • • • Creating and maintaining the data Viewing the data Reporting on the infrastructure Concurrent file access Audit trails Auto routing (service provisioning) Data integrity Integration Network policies
Creating and maintaining the data Although a spreadsheet is quite good for replicating and manipulating data, creating the initial data can be cumbersome and time consuming. Small-tomedium-sized networks generally use straight forward naming conventions for network equipment, but large offices and data center environments often use naming conventions and a complex, sophisticated structure to help locate equipment quickly without referencing documentation. For example an Outlet ID is labeled: 14-E-24AE-67-5 where: 14 refers to the building floor (14th) E refers to being served from the East communications room 24AE refers to the floor grid location of the sub floor box 67 refers to the sub floor box ID 5 refers to the outlet ID in the floor box Creating each unique ID in a spreadsheet is time consuming and tedious, and it is virtually impossible to check for typing errors.
Spreadsheets…. They’re free, right?
Once the fixed infrastructure documentation has been created, there are three parts of the documentation which once known, constantly change: • • • Patching Outlet to which an end device is connected Physical location of end devices
Maintaining the physical connectivity and the location of all network components are critical in managing the infrastructure effectively, and because patching and connectivity/location of end devices are moving targets, they present an updating challenge. Often, the only reliable way to verify accuracy of the information is to conduct an audit and enter it into a spreadsheet / floor plan before they move again. In such cases, a movement freeze is necessary, in conjunction with the audit and data entry, which result in time consuming, inconvenient and costly exercises. In contrast, iTRACS Physical Layer Manager (PLM) contains tools that rapidly create IDs no matter how complex, and can automatically reference a component’s parent name to create its child’s name. The blue elements (see above) in the ID are all descendants of the building’s ID. iTRACS PLM understands the parent-child relationships between these components to rapidly create new components with guaranteed accuracy (see root navigator – right). iTRACS PLM uses IP discovery (called iTRACS iDiscover) to automatically identify, create, locate (on a CAD floor plan) and connect end devices to their appropriate outlets in the database without user intervention (so long as the infrastructure cabling is correct), thereby guaranteeing accuracy. Some organizations may spend millions of dollars annually auditing their infrastructures for unauthorized changes. The audit expense saved with this feature alone makes a significant contribution towards recouping the cost for iTRACS PLM. Further, if iTRACS PLM is rolled out with iTRACS IM’s “intelligent patching” (the ability to discover and monitor patch cord connections automatically), then further audit and data input savings are realized. Viewing the data A single spreadsheet (workbook) is limited to viewing information from a switch port to what it is connected to at the remote end. It is not practical (if not impossible) to document the entire infrastructure between these end points, including all patch panels, ports, patch cords, cables, sub floor boxes and outlets on the same sheet. Typically, this limitation is overcome by using multiple sheets (tabs) in a spreadsheet to document the various intermediary points between the ends of a network connection, which means entering the same data multiple times. For example: • • • • • Switch port to end device Switch port to patch panel / port Patch panel / port to patch panel / port (showing patching) Patch panel / port to sub floor box / wall plate / outlet (showing fixed horizontal cables) Outlet to end device (with different workbooks for different end device types)
It is difficult, time consuming and prone to human error when navigating between multiple sheets to determine the entire connectivity between end points. Further, producing an entire circuit trace (end-to-end) involves multiple occurrences of searching for items, and then cutting and pasting them into a new document.
Spreadsheets…. They’re free, right?
A key advantage of using iTRACS Physical Layer Manager is its ability to search for any component in a circuit (not just end devices) in an Explorer-type parent-child data structure, and then with one click, display a circuit trace of the entire connection with all intermediary components, including physical location regardless of whether it’s a LAN, SAN, KVM, or power. Due to the dynamic nature of logical information (such as IP addresses, Subnets and Host Names), it is impractical to include in a spreadsheet. If it is included, it will very quickly become inaccurate. The only network address that can be meaningfully used in a spreadsheet is a MAC address and Host Name, which is relatively fixed. Using iTRACS PLM with iTRACS iDiscover, the correlation between end devices and their logical network information will be 100% accurate because it is maintained automatically.
Reporting on the infrastructure Other than printing in their native format, spreadsheets do not contain any reporting functions, so a user is strictly limited to viewing the data the way it was created. As mentioned previously, since this will involve several workbooks, viewing data is extremely tedious and confusing, and will again involve cutting and pasting data if meaningful reports are to be provided. iTRACS PLM, on the other hand, incorporates dozens of canned reports, such as switch utilization, spare capacity (rack space, cables, power, cooling) and patching schedules. It also includes a report generator that allows a user to produce user-defined reports, incorporating filters on any aspect of its infrastructure.
Concurrent file access An obvious limitation of spreadsheets is their inability to allow multiple users to update them concurrently (unless extensive checking is performed after merging the files). iTRACS PLM not only allows concurrent access to the database but also contains record-locking features, preventing more than one user attempting to change the same item simultaneously.
Audit trails A substantial drawback to spreadsheets is the inability to track changes unless a separate log is created every time a change is made. Correlating such a log entry to the changed connections is a manual, time-consuming, error-prone process. Unlike spreadsheets, iTRACS PLM automatically creates a time-stamped log, fully documenting every aspect of the change, and all the circuit and network information at the time of the change. For example: • • • • Time/date Planned or unplanned (authorized or unauthorized) Who planned/executed the change Full details of the connections changed, including location information
Spreadsheets…. They’re free, right?
The log entry also is cross-referenced with the actual changed item and vice versa making it easy to look up the log history for any given item. Further, historic analysis can be performed on the log to report on any of the captured information, such as the frequency of changes, their costs, and department codes.
Auto routing (service provisioning) One of the principle purposes of keeping accurate documentation – whether spreadsheet or application management software – is the ability to tell the user what connections are available prior to a reconfiguration-planning process. Using spreadsheets – and planning a reconfiguration given the known location of the end devices and (say) the desired services (for example; 1000BaseT), is possible, but the process is extremely time consuming and error prone due to cross-referencing of multiple sheets. iTRACS PLM includes auto-routing features that will instantaneously create a proposed work order containing all the actions necessary to complete the given request which, if accepted, will then automatically reserve the connections and assign the tasks to teams or individual technicians. The work order is then tracked throughout the change process.
Data integrity Many networks are mission critical, so it is imperative that the data is 100% accurate, if it’s to be relied upon. If the data is not 100% accurate, then verification of the data is needed at every stage during the change cycle to mitigate a catastrophic incident. The verification process takes both time and resources, and its importance should not be underestimated. Using spreadsheets, it is possible to implement a strict change-control process whereby every change is documented, maintaining the accuracy of the data. However, spreadsheets are susceptible to three categories of error caused by: a. Human data entry b. Updating delays c. Bypassing the change control process Spreadsheets cannot detect errors when the user enters the data, nor is it practical for the changes to be entered into the spreadsheet instantaneously when the changes occur. For some organizations, it can take weeks from the time of execution to updating the data. This means at any given time, some of the data is inaccurate. Not surprisingly, the higher the reconfiguration “churns”, the longer the delays to update the documentation. Subject to adherence of the change-control process is iTRACS PLM. The first two categories of error (a and b) are avoided entirely because the work-order system will automatically update the database instantaneously as the technician indicates the assigned tasks are completed, thereby maintaining the integrity of the database. Obviously, neither spreadsheets nor iTRACS PLM will detect errors if someone (usually unknown) bypasses the change-control process (typically the most common point of failure in manual-based documentation systems). If iTRACS PLM is part of a semi-intelligent solution, incorporating iTRACS iDiscover, then bypasses to the change process can be discovered, and the relevant personnel notified. If iTRACS PLM is part of a fully intelligent solution, the database updates in real-time, regardless of where the patching change occurs. In addition to guaranteeing the accuracy at all times in all three categories (human data entry, updating delays, and bypassing the change process), iTRACS PLM becomes a trusted source of information.
Spreadsheets…. They’re free, right?
Integration A major drawback of spreadsheets is that they do not easily integrate with other applications or databases. iTRACS PLM allows users to integrate with any ODBC-compliant application or database. By integrating with other applications, iTRACS PLM can further become a trusted source – either by verifying against other databases, or as a central point for data review. Common integrations include: • • • • • Asset management CMDB Help desk Systems management Facility management
Network policies Spreadsheets (being offline) obviously have no knowledge of logical devices, and are unable to check for violations in network policy without performing extensive custom development. iTRACS PLM, utilizing iTRACS iDiscover, can determine violations automatically from the SNMP “public” string response by reporting on devices without redundancy. Another example would be reporting devices with fall-over connections on the same switch blade. iTRACS PLM also offers another tool named iTRACS iLogin, which can track Windows domain, remote desktop, and shared network drive logins. By tracking logins throughout the infrastructure, iTRACS iLogin can notify administrators of violations (e.g. a user logs into the network from another department).
Conclusion Besides all the feature benefits of iTRACS PLM over a spreadsheet, financial constraints are a reality… but so are mistakes, unfortunately. As networks become more complex, and more integrated into day-to-day business operations, the flaws of the simple spreadsheet and the effort required to keep them accurate become more and more evident. The flaws lead to data integrity errors in the spreadsheet, which in turn cause human mistakes to be made while planning and overseeing the infrastructure. Perhaps some mistakes can be afforded; however others cannot and will be devastating to a business. Knowing with certainty that accurate information is being used to make critical business decisions is, in most cases, regarded as priceless! Or to put it another way, making decisions using what could be inaccurate information, is generally regarded as reckless, sometimes career threatening! Finally, the time required to update a spreadsheet is, without question, substantially longer than updating iTRACS PLM by a factor of up to 10, depending upon the size and complexity of the network. Each move multiplied by this factor adds up to unnecessary cost and delays. Spreadsheets are free?
By Peter L Pela – Founder, Chairman and CTO at iTRACS Corporation.