Posey's Tips & Tricks
Using Microsoft Office to Build a Network Diagram, Part 1
Keeping documentation is a great way to ease your future hardware refresh and have what you need for insurance purposes.
I recently completed a massive hardware refresh in which I updated nearly all of my PC and server hardware. As I am sure you can imagine, I also took the opportunity to make some architectural changes and some configuration changes. Now that all of the work is complete however, I wanted to recreate my network diagram.
At one time, Windows included a built-in tool for diagraming a network. Sadly, this tool was removed some time ago. Even so, it is possible to create a detailed network diagram using only Windows 11 and Microsoft Office (including tools such as Excel and Visio). In this article series, I will walk you through the process of documenting a small network.
Before I Begin
Before I get started, I want to point out that what I am going to be creating in this article series is a detailed hardware inventory that will be linked to a series of diagrams. However, there are other types of network documentation. In fact,
I wrote an entirely separate article discussing the documentation that I consider to be the most important on a small network. So with that said, let's go ahead and get started.
Compiling a Network Inventory
The first step in documenting your network is to compile a hardware inventory. There are, of course, countless commercially available network documentation tools. Most of these tools, however, have a sky high price tag. Since my goal in writing this article series is to walk you through the process of linking inventory data to a Visio diagram, I will be using Excel to compile the hardware inventory (Visio doesn't directly support the use of third party network inventory tools).
I have to admit that although I knew that you could use an Excel spreadsheet as a tool for importing network inventory data into Visio, I wasn't sure what format the spreadsheet was supposed to be in. I had assumed that there needed to be a header row that was populated with very specific column names that would be recognizable to Visio. As it turns out, you can create any columns that you want. Visio doesn't really care what you have in your header row, so long as the spreadsheet has a header row.
This of course, raises the question of what columns you should create within your network inventory spreadsheet. Everyone's requirements are going to be different, but the answer to this question really comes down to the type of documentation that you want to create.
In my case, I had a few different goals for the hardware inventory spreadsheet. One of these goals was, of course, to be able to use it as a source of information from which to create a series of network diagrams.
A second goal was to create a detailed inventory that I could use for insurance purposes. Obviously, I hope that I never suffer any kind of loss, but if catastrophe were to strike being able to provide detailed information to the insurance company is key to getting a claim approved and receiving a favorable settlement.
A third goal for my network inventory spreadsheet was that I wanted to be able to use it to assist in future hardware refreshes. Typically, I refresh my network hardware every five years or so. However, this refresh schedule is not set in stone. If a hardware device begins to feel dated before my scheduled refresh date then I will usually check to see if the device can be upgraded or if I need to go ahead and replace it. Conversely, I have a few peripheral devices (such as printers and drawing tablets) that I have no intention of replacing in spite of their age because they work well and fully meet my needs.
A Spreadsheet Strategy
When I began to create a hardware inventory spreadsheet, I quickly realized that it just did not make sense to list all of my hardware on a single sheet. While there are some fields that might be common to all of my hardware devices (such as manufacturer, model, and serial number), there were other fields that tended to be specific to a particular device type. For example, a Surface Tablet doesn't really have a lot in common with an enterprise NAS appliance.
My solution was to create a single Excel workbook consisting of multiple tabs. In my case, I created one tab for endpoints (desktops, laptops, tablets, etc.), a second tab for servers, and then a third tab for everything else. While it's true that my "everything else" tab contains a lot of dissimilar devices, my network just isn't big enough to justify creating a separate tab for each device type. For example, I only have one printer on my network, so it probably doesn't make a lot of sense to put it on a separate tab.
As I'm sure you can imagine, populating a hardware inventory spreadsheet can involve quite a bit of work, depending on how detailed you want the spreadsheet to be. In Part 2 of this series, I will share with you some strategies for making the data collection process easier.
About the Author
Brien Posey is a 22-time Microsoft MVP with decades of IT experience. As a freelance writer, Posey has written thousands of articles and contributed to several dozen books on a wide variety of IT topics. Prior to going freelance, Posey was a CIO for a national chain of hospitals and health care facilities. He has also served as a network administrator for some of the country's largest insurance companies and for the Department of Defense at Fort Knox. In addition to his continued work in IT, Posey has spent the last several years actively training as a commercial scientist-astronaut candidate in preparation to fly on a mission to study polar mesospheric clouds from space. You can follow his spaceflight training on his Web site.