I was ask to make a overview to see which servers are responsible for which application. This overview is for management and must be in a drawing format and they want to have this available when they want to. This is task that can cost a lot of time just for a quick overview. So how can I make it without buying an application and with the least amount of effort? So a went looking around and found out that you can import Excel data in Visio and that Visio can make a drawing of it if you choice the right options.
In most IT environment they will keep track of servers and possible there function in some kind of format. In my case i had two options make a extraction of the vCenter data or export the SharePoint data in to Excel. The first way is pretty strait forward select the view and make a extraction by making a export. In this blog I will go and do it the ”hard” way because of the extra steps you will have to make. Some screenshots are with Dutch sorry for that!
Now on the left side of the site click Actions and click Export to Spreadsheet
You may get this error because of the Query’s that are in the file. click Open
The raw data is now available in Excel and must be transformed in to usable information to have a good import in Visio.First thing that must be done is to remove all hyperlinks that are in the Excel sheet.
Press Alt+F11 and you will be in the Visual Basic editor go to Insert en choose Module
‘Remove all hyperlinks from the active sheet
Go to File and close the Visual Basic editor and return to Excel
In Excel go to Extra and click Macro, ore use Alt+F8, to run the macro you just made.
choose the Module RemoveHyperlinks and run it. The Excel sheet is now free of hyperlinks.
Remove the checkmark from Save Query.
You will get an popup which will tell you that this action can not be undone.
The excel sheet is ready for importing to give you an idea the picture below is an example of how you sheet could look like. You can have as many columns as you like. the important thing is that we would like to use relations in Visio and there for all application names (in this case column B) must all so be in in column A. This is necessary to make the relations. the data in row B 34 and below are queries I use to make a SUM of the numbers above this is not necessary.
Close your Excel sheet and open Visio. Went the sheet is open and you will try to import it in to Visio it will give you an error.
Go to File > Organisation Chart > Organisation Char Wizard
· Select Information that’s already stored…
· Select A text, Org Plus…
· Browse to the excel file
· Select the right columns.
By Name select the servers you would like to see in the drawings By report to select the applications. This way you will get a drawing per application and will see which server is being use for the application
Select the data you want to see in the drawing view
Select the data you want to see in the property field of a shape.
Select I want the wizard to automatically….
When Visio is ready with the import. In my Excel sheet I have 6 differenced applications so I have got 6 TAB in Visio for each Application a differed one. In the top Is the application, the square shape with the double line, and the SUM of usage server, CPU, memory count this is the data from column B34 I mentioned. the square shape below are the servers with there selected data to see.
when you right click one off the square shapes you are able to select the property of the shape like this view.
And choose the order you would like to see the shape.
When importing in Excel, Excel will use the default shapes this can be adjust in front or afterwards use you friend Google to figure this out : ).
This has bean tested on Excel 2007 and 2010 and Visio 2007 and 2010 some time the function is in a other place but you will figure it out 😉 The screenshots are from excel and Visio 2003.