Integrating Visio 2007 and MOM, Exchange, and Reporting Services
Summary: Learn about the native integration among Visio, Microsoft Operations Manager 2005 (MOM), Exchange, and Reporting Services, and about some additional ways that these programs can be integrated by using Visio Automation. (24 printed pages)
January 2007
Applies to: Microsoft Office Visio 2007, Microsoft Operations Manager 2005, Microsoft Exchange 2003, Microsoft SQL Server Reporting Services
Contents
Visimation, Inc.http://www.visimation.com/January 2007Applies to: Microsoft Office Visio 2007, Microsoft Operations Manager 2005, Microsoft Exchange 2003, Microsoft SQL Server Reporting ServicesContentsIntroductionOverview: Native IntegrationAdvanced IntegrationConclusionAbout the AuthorAdditional Resources
Overview: Native Integration
Advanced Integration
Conclusion
About the Author
Additional Resources
Overview: Native Integration
Microsoft Operations Manager 2005 (MOM) is one of the most powerful tools available for managing and monitoring the stability and performance of your enterprise network. Combined with the Exchange Server Management Pack for MOM 2005, it is now also one of most powerful tools for managing your Microsoft Exchange Server system.
With the Exchange Management Pack installed, MOM 2005 becomes the central mechanism by which you can monitor and control an Exchange deployment. MOM provides two areas to view and manage data about your Exchange environment:
MOM Operator Console
MOM Reporting Console
The MOM Operator Console provides a convenient environment (with a user interface similar to that of Microsoft Outlook) so that you can view information about your network, including system alerts, current status information, live performance data graphs, and network topology diagrams. With the Exchange Management Pack installed, the Operator Console is updated with many additional views that are specific to Exchange Servers and deployments.
Figure 1. The MOM Operator Console
.gif "The MOM Operator Console")
The MOM Reporting Console takes advantage of Microsoft SQL Server Reporting Services to display many types of reports about the performance of your network over time. When the Exchange Management Pack is installed, more than 30 additional reports are added specifically for your Exchange environment, including operational health reports, traffic analysis, and mailbox size and usage reports.
Figure 2. The MOM Reporting Console
.gif "The MOM Reporting Console")
The Diagram View
The MOM 2005 Operator Console includes a Diagram View window, to display topology diagrams of your network. These diagrams include high-quality graphics, complete with status icons to indicate the current state of your computers. You can more easily see if any computer is having trouble; if a computer is having trouble, a warning icon or error icon is displayed next to its status icon in the Diagram View.
Figure 3. The Diagram View
.gif "The Diagram View")
The Diagram View is extensible; you can add to it with Management Packs. Many Management Packs install additional diagram views, including new graphics and layouts that apply specifically to the product or technology that the Management Pack covers. For example, the Exchange Management Pack installs several Exchange-specific diagram views, giving MOM everything it needs to draw a topology of your Exchange environment, including graphics for bridge-heads, front-end servers, back-end servers, and Exchange Computer Groups.
You can export the Diagram View to Microsoft Office Visio 2007 by using the Export To Visio toolbar. This feature saves the MOM diagram in the Visio XML drawing file format (.vdx).
Figure 4. The Export To Visio toolbar
.gif "The Export To Visio toolbar")
Exporting MOM Diagrams as .Vdx Files
MOM takes advantage of DatadiagramML, the Visio open and royalty-free XML schema, to create Visio diagrams dynamically. The XML file format makes it easier for MOM to transform its own diagram data into a Visio file, complete with connected lines, Shape Data, and Visio group shapes.
The Visio .vdx file generated from MOM contains the following important characteristics:
All master shapes are placed on the Document Stencil.
All Exchange Computer shapes have two Shape Data fields (Title and Node Info).
The Node Info field contains a semicolon-delimited list of data about the computer, including IP address and fully qualified domain name (FQDN).
You can see the master shapes if you open the .vdx file in an XML editor, such as Microsoft Visual Studio 2005. Look for the <VisioDocument> node; inside it you find the <Masters> node. The <Masters> node contains all the master shapes for the document. The <Masters> node is essentially the Document Stencil, which is a portable stencil that every Document carries with itself.
The <Master> node contains information about a particular master, including its name, ID, and unique GUID.
<Masters>
<Master ID='2' NameU='ExchangeComputerGroup' Name='ExchangeComputerGroup'
Prompt='' IconSize='1' AlignName='2' MatchByName='0' IconUpdate='1' UniqueID='{5675AACF-0006-0000-8E40-00608CF305B2}'
BaseID='{38F8EE59-41C7-4A11-B005-E81B0F45A33F}' PatternFlags='0' Hidden='0' xmlns='http://schemas.microsoft.com/visio/2003/core'>...
Shape Data (formerly called Custom Properties in earlier versions of Visio) for the Exchange Computer shapes on the diagram are defined in the XML:
<Prop NameU='Row_1' ID='1'>
<Value Unit='STR'>EXCHANGE2003</Value>
<Prompt V='null'/>
<Label>Title</Label>
<Format V='null'/>
<SortKey V='null'/><Type>0</Type><Invisible>0</Invisible><Verify>0</Verify><LangID>1033</LangID><Calendar>0</Calendar>
</Prop>
<Prop NameU='Row_2' ID='2'>
<Value>0</Value>
<Prompt>TESTADDOMAIN\EXCHANGE2003
FQDN: exchange2003.testaddomain.com
IPAddress: 192.168.0.3
Microsoft Windows Current Version: 5.2
AD Site: Default-First-Site
Administrative Group: First Administrative Group
Mailboxes: 4
Organization: Corporate
Routing Group: First Routing Group
Version: Version 6.5 (Build 7638.2: Service Pack 2)
Status: Warning</Prompt>
<Label>Node Info</Label>
<Format>TESTADDOMAIN\EXCHANGE2003;FQDN: exchange2003.testaddomain.com;
IPAddress: 192.168.0.3;Microsoft Windows Current Version: 5.2;AD Site: Default-First-Site;
Administrative Group: First Administrative Group;Mailboxes: 4;Organization: Corporate;
Routing Group: First Routing Group;Version: Version 6.5 (Build 7638.2: Service Pack 2);Status: Warning</Format>
<SortKey V='null'/><Type>1</Type><Invisible>0</Invisible><Verify>0</Verify><LangID>1033</LangID><Calendar>0</Calendar>
</Prop>
Notice that the <Prop>NameU attributes are "Row_1" and "Row_2" respectively for the properties:
<Prop NameU='Row_2' ID='2'>
Next, notice the <Format> node in the second property, which includes a semicolon-delimited list of data about the Exchange server computer:
<Format>TESTADDOMAIN\EXCHANGE2003;FQDN: exchange2003.testaddomain.com;
IPAddress: 192.168.0.3;Microsoft Windows Current Version: 5.2;
AD Site: Default-First-Site;Administrative Group: First Administrative Group;
Mailboxes: 4;Organization: Corporate;Routing Group: First Routing Group;
Version: Version 6.5 (Build 7638.2: Service Pack 2);Status: Warning</Format>
In the next section, we use these two pieces of information to create several advanced integration points.
Advanced Integration
We previously discussed the native integration among MOM, Exchange, and Visio that uses the Diagram View's Export To Visio feature. Next, we examine four ways you can extend that integration by using some advanced Automation of Visio:
Post-process the Visio .vdx file (generated in MOM by using the Export To Visio feature from the MOM Diagram View) to add data to the diagram, and, at the same time, make it compatible with the Microsoft Baseline Security Analyzer Visio add-in.
Add hyperlinks to the Exchange Server shapes that point to specific Reporting Services reports about those shapes.
Add right-click functionality to the page to open the Exchange Server Management Console.
Pull the data from SQL Reporting Services and link shapes in the diagram to the data.
Making the Diagram Compatible with the Visio Connector for the Microsoft Baseline Security Analyzer
The Microsoft Baseline Security Analyzer (MBSA) is a free tool from Microsoft. You can use it to scan computers on a network and determine whether those computers have certain security vulnerabilities or missing updates or patches. The Visio Connector for MBSA is a Microsoft Visio add-in that you can use to scan computers that are using MBSA, directly from within a Visio network diagram.
The Visio Connector for MBSA works by determining if shapes in the Document Stencil have several predefined Shape Data fields. If the fields are present, it enables the scanning of the shapes in the Document Stencil via an MBSA menu item that is made available.
Figure 5. A diagram with the Visio Connector for MBSA enabled
.gif "Diagram with the Visio Connector for MBSA enabled")
Shapes that are included in the Basic Network or Detailed Network stencils that come installed with Visio already have these Shape Data fields and automatically make their diagrams MBSA-enabled. This functionality is not limited to those built-in diagram types, however. Any diagram that has shapes with those fields can be MBSA-enabled, including the Exchange server diagrams that are generated from the MOM Diagram View.
The Visio Connector looks for the following Shape Data fields:
Network Name
IP Address
If it finds either of these fields, the diagram is automatically MBSA-enabled to perform scans directly from the MBSA menu.
Creating a Visio COM Add-in to Post-Process the .Vdx File
The easiest way to manipulate the Visio drawings created by MOM is to create a COM add-in that loads when Visio starts, listens for Exchange topology diagrams to be opened, and automatically updates any Exchange Server shapes with the proper MBSA-required Shape Data fields.
To create a Visio COM add-in, download and install the Microsoft Visio 2007 SDK. After you install the Visio 2007 SDK:
Open Visual Studio.
On the File menu, point to New, point to Project, and then select Visio COM Add-in or Visio COM Add-on.
Follow the wizard to completion.
After the Add-In project opens, open the Connect.cs file, and then navigate to the OnStartupComplete method.
Add the following event handlers.
public void OnStartupComplete(ref System.Array custom) { // Set up the Visio events. vsoApplication.DocumentOpened += new EApplication_DocumentOpenedEventHandler(vsoApplication_DocumentOpened); vsoApplication.DocumentCreated += new EApplication_DocumentCreatedEventHandler(vsoApplication_DocumentCreated); vsoApplication.DocumentChanged += new EApplication_DocumentChangedEventHandler(vsoApplication_DocumentChanged); vsoApplication.MarkerEvent += new EApplication_MarkerEventEventHandler(vsoApplication_MarkerEvent); }Add the following event handler methods for the Visio Document events.
void vsoApplication_DocumentChanged(Document doc) { OnDocumentOpenedCreatedChanged(doc); } void vsoApplication_DocumentCreated(Document doc) { OnDocumentOpenedCreatedChanged(doc); } void vsoApplication_DocumentOpened(Document doc) { OnDocumentOpenedCreatedChanged(doc); }
Next, add the OnDocumentOpenedCreatedChanged method. This method contains code to update the shapes on the Document Stencil with the necessary properties for the MBSA Visio Connector to load, and code to update any shapes on the page.
private void OnDocumentOpenedCreatedChanged(Document doc)
{
// Loop through all the masters,
// find any ExchangeComputer shapes,
// and add empty Shape Data.
foreach (Master master in doc.Masters)
{
if (master.NameU == "ExchangeComputer")
{
AddEmptyCustomProperties(master);
}
}
// Loop through all shapes and configure
// any ExchangeComputer shapes.
foreach (Microsoft.Office.Interop.Visio.Shape shape in doc.Pages[1].Shapes)
{
if (shape.Master != null)
{
System.Diagnostics.Debug.WriteLine("Name = " + shape.Master.NameU);
if (shape.Master.NameU == "ExchangeComputer")
{
AddCustomProperties(shape);
}
}
else if (shape.Shapes.Count > 0)
{
foreach (Microsoft.Office.Interop.Visio.Shape nestedShape in shape.Shapes)
{
try
{
if (nestedShape.Master != null)
{
System.Diagnostics.Debug.WriteLine("Name = " + nestedShape.Master.NameU);
if (nestedShape.Master.NameU == "ExchangeComputer")
{
AddCustomProperties(nestedShape);
}
}
}
catch (Exception)
{
// Swallow this exception.
}
}
}
}
}
The first block of code, shown previously. loops through all the masters in the Document Stencil, looking for the ExchangeComputer master shape, and calls a method that fills the master with empty "IP Address" and "Network Name" Shape Data. These empty properties are read by the MBSA Connector to determine whether the shapes are MBSA-enabled.
The second block of code loops through the shapes on the page, finds any whose master is the "ExchangeComputer" shape, and adds Shape Data to the shape, based on data that is already populated in a pre-existing "Node Info" Shape Data cell.
Paste the following AddEmptyCustomProperties method into your Connect.cs class.
private void AddEmptyCustomProperties(Master master)
{
// Add two named rows, if they do not already exist.
bool hasNetworkNameProp;
bool hasIPAddressProp;
short rowIndex;
Cell labelCell;
hasNetworkNameProp = Convert.ToBoolean(master.Shapes[1].get_CellExists("Prop.NetworkName", 0));
hasIPAddressProp = Convert.ToBoolean(master.Shapes[1].get_CellExists("Prop.IPAddress", 0));
if (!hasNetworkNameProp)
{
rowIndex = master.Shapes[1].AddNamedRow(
(short)(VisSectionIndices.visSectionProp),
"NetworkName",
(short)(VisRowIndices.visRowProp));
// Edit the Label cell.
labelCell = master.Shapes[1].get_CellsSRC(
(short)VisSectionIndices.visSectionProp,
rowIndex,
(short)VisCellIndices.visCustPropsLabel);
labelCell.FormulaU = "\"Network Name\"";
}
if (!hasNetworkNameProp)
{
rowIndex = master.Shapes[1].AddNamedRow(
(short)(VisSectionIndices.visSectionProp),
"IPAddress",
(short)(VisRowIndices.visRowProp));
// Edit the Label cell.
labelCell = master.Shapes[1].get_CellsSRC(
(short)VisSectionIndices.visSectionProp,
rowIndex,
(short)VisCellIndices.visCustPropsLabel);
labelCell.FormulaU = "\"IP Address\"";
}
}
The AddEmptyCustomProperties method adds two Shape Data rows to the ShapeSheet of the ExchangeComputer master (if they do not already exist), "IP Address" and "Network Name", but the method does not configure them.
Paste the following AddCustomProperties method into your Connect.cs file.
private void AddCustomProperties(Microsoft.Office.Interop.Visio.Shape shape)
{
// Add two named rows, if they do not already exist.
bool hasNetworkNameProp;
bool hasIPAddressProp;
short rowIndex;
Cell labelCell;
hasNetworkNameProp = Convert.ToBoolean(shape.get_CellExists("Prop.NetworkName", 0));
hasIPAddressProp = Convert.ToBoolean(shape.get_CellExists("Prop.IPAddress", 0));
if (!hasNetworkNameProp)
{
// Add a user-defined cell to store the network name.
rowIndex = shape.AddNamedRow(
(short)(VisSectionIndices.visSectionUser),
"NetworkName",
(short)(VisRowIndices.visRowUser));
// Get the Value cell and set it to point
// to a value from the NodeInfo property cell.
shape.get_Cells("User.NetworkName.Value").FormulaU = "INDEX(0,Prop.Row_2.Format,\";\")";
// Insert our network name property.
rowIndex = shape.AddNamedRow(
(short)(VisSectionIndices.visSectionProp),
"NetworkName",
(short)(VisRowIndices.visRowProp));
// Edit the Label cell.
labelCell = shape.get_CellsSRC(
(short)VisSectionIndices.visSectionProp,
rowIndex,
(short)VisCellIndices.visCustPropsLabel);
labelCell.FormulaU = "\"Network Name\"";
// Set the value to point to the user cell.
shape.get_Cells("Prop.NetworkName.Value").FormulaU = "User.NetworkName";
}
if (!hasIPAddressProp)
{
// Add a user-defined cell to store the IP address.
rowIndex = shape.AddNamedRow(
(short)(VisSectionIndices.visSectionUser),
"IPAddress",
(short)(VisRowIndices.visRowUser));
// Get the Value cell and set it to point
// to a value from the NodeInfo property cell.
shape.get_Cells("User.IPAddress.Value").FormulaU = "SUBSTITUTE(INDEX(2,Prop.Row_2.Format,\";\"),\"IPAddress: \", \"\")";
// Insert the IPAddress property.
rowIndex = shape.AddNamedRow(
(short)(VisSectionIndices.visSectionProp),
"IPAddress",
(short)(VisRowIndices.visRowProp));
// Edit the Label cell.
labelCell = shape.get_CellsSRC(
(short)VisSectionIndices.visSectionProp,
rowIndex,
(short)VisCellIndices.visCustPropsLabel);
labelCell.FormulaU = "\"IP Address\"";
// Set the value to point to the user cell.
shape.get_Cells("Prop.IPAddress.Value").FormulaU = "User.IPAddress";
}
}
The code above is similar to the AddEmptyCustomProperties method shown previously; however, it goes one step further by actually populating the cells with data. It does this by creating two user-defined cells in the ShapeSheet, to store parsed data from the Node Info Shape Data cell.
Remember from the first section of this article that the XML generated by the Export To Visio .vdx format tool in MOM created a <Prop> node with a <Format> node containing a semicolon-delimited list of data about the Exchange Server. Because we have this delimited list, we can use the ShapeSheet functions INDEX and SUBSTITUTE to parse this data, extract what we need, and put it back into our new Shape Data fields.
After you have this code in place, build your add-in and press F5 to launch Visio. After Visio starts, open an exported MOM .vdx file; it should instantly be upgraded with Shape Data to support the MBSA connector. Because the MBSA Connector checks documents only when they are opened, you need to save and reopen your document to view the MBSA menu. After saving and reopening the document the first time, you always see the MBSA menu when you open your diagram.
Figure 6. A MOM .vdx file with MBSA Connector enabled
.gif "A MOM .vdx file with MBSA Connector enabled")
Adding Reporting Services Hyperlinks
The next way to integrate Visio with MOM and Exchange is to take advantage of Microsoft SQL Server Reporting Services, and to add hyperlinks on the Exchange shapes to point to the Web-based versions of Exchange reports.
The Exchange Management Pack for MOM 2005 provides more than 30 additional reports in SQL Server Reporting Services. The reports include metrics such as:
Capacity planning
Mailbox size and usage
Protocol usage
Operational health
Traffic analysis
When you open the MOM Reporting Console, you are taken to a Web page where you can browse available reports. This Web page enables you to investigate the Exchange categories and find reports such as the Exchange Database Sizes report or the Exchange Server Availability report.
Because these reports are Web-based, you can link to them easily by using hyperlinks. Additionally, the reports are parameterized, and many of them can accept a computer parameter passed by the query string to narrow the report down to a particular server computer. For an example, take a look at the Exchange Server Availability report.
The Exchange Server Availability report requires that the computer parameter be filled out; you can pre-populate this parameter by appending it to the query string:
http://server_name/ReportServer/Pages/ReportViewer.aspx?%2fMicrosoft+Operations+Manager+Reporting%2fMicrosoft+Exchange+Server%2fExchange+2000+and+2003+Health+Monitoring+and+Operations%2fExchange+Server+Availability&rs:Command=Render&Computer=domain_name\EXCHANGE2003
To add this hyperlink programmatically, build from the previous add-in code and add two calls to an AddHyperlinks method, just after the call to AddCustomProperties in OnDocumentOpenedCreatedChanged.
if (nestedShape.Master.NameU == "ExchangeComputer")
{
AddCustomProperties(nestedShape);
AddHyperlinks(nestedShape);
}
Then, add the following method:
private void AddHyperlinks(Microsoft.Office.Interop.Visio.Shape shape)
{
// Add a hyperlink to the Server Availability report.
const string reportAddress = "http://server_name/reports/reportviewer.aspx";
string reportParameters =
"%2fMicrosoft+Operations+Manager+Reporting" +
"%2fMicrosoft+Exchange+Server" +
"%2fExchange+2000+and+2003+Health+Monitoring+and+Operations" +
"%2fExchange+Server+Availability" +
"&rs:Command=Render" +
"&Computer={0}";
bool hasAvailabilityReportLink;
short rowIndex;
hasAvailabilityReportLink = Convert.ToBoolean(shape.get_CellExists("Hyperlinks.ServerAvailability", 0));
if (!hasAvailabilityReportLink)
{
Hyperlink availabilityLink = shape.AddHyperlink();
availabilityLink.Address = reportAddress;
availabilityLink.Description = "Server Availability";
availabilityLink.ExtraInfo = string.Format(reportParameters, shape.get_Cells("User.NetworkName").get_ResultStr(null));
}
}
With that code added, you can run your add-in and open an exported MOM .vdx file, and each of your Exchange Computer shapes automatically have a hyperlink pointing to the availability report for that computer.
Figure 7. A hyperlink to the Server Availability report for this server
.gif "A hyperlink to the Server Availability report")
Figure 8. The Server Availability report
.gif "The Server Availability report")
Add a Shortcut Menu to Open Exchange System Manager
Now that we have integrated the MBSA tool with the exported diagrams and have linked reporting services reports to the Exchange shapes on the diagram, it would be very useful to create a shortcut menu on the page so that we can right-click to open the Exchange System Manager management console.
To add the shortcut menu, build upon the earlier COM add-in work, and add a call to a new method (AddPageAction) just after the call to the AddEmptyCustomProperties method.
private void OnDocumentOpenedCreatedChanged(Document doc)
{
// Loop through all the masters,
// find any ExchangeComputer shapes,
// and add empty Shape Data.
foreach (Master master in doc.Masters)
{
if (master.NameU == "ExchangeComputer")
{
AddEmptyCustomProperties(master);
AddPageAction(doc.Pages[1]);
}
}
The AddPageAction method adds an Actions row to the page in the exported diagram, which adds a shortcut menu option off the page. This action triggers a MarkerEvent, which we can use in our COM add-in to start the Exchange System Manager.
private void AddPageAction(Page page)
{
// Add the Actions section, if it does not already exist.
if (!Convert.ToBoolean(page.PageSheet.get_SectionExists((short)VisSectionIndices.visSectionAction, 0)))
{
page.PageSheet.AddSection((short)VisSectionIndices.visSectionAction);
}
// Add the Actions row, if it does not already exist.
if (!Convert.ToBoolean(page.PageSheet.get_CellExists("Actions.SystemManager",0)))
{
short rowIndex;
rowIndex = page.PageSheet.AddNamedRow(
(short)VisSectionIndices.visSectionAction,
"SystemManager",
(short)VisRowIndices.visRowAction);
// Set the Action cell.
page.PageSheet.get_CellsU("Actions.SystemManager.Action").FormulaU = "QUEUEMARKEREVENT(\"SystemManager\")";
// Set the Menu cell.
page.PageSheet.get_CellsU("Actions.SystemManager.Menu").FormulaU = "\"Open Exchange System Manager\"";
}
}
This creates a shortcut menu on the page.
Figure 9. The Exchange System Manager Action menu
.gif "The Exchange System Manager Action menu")
Notice how the code sets the Action cell, and that it places the ShapeSheet function QUEUEMARKEREVENT in it. This function triggers an event in Visio that we previously configured our add-in to listen for.
To finish, add the following code to the vso_MarkerEvent handler you created earlier.
void vsoApplication_MarkerEvent(Application app, int SequenceNum, string ContextString)
{
switch (ContextString)
{
case "SystemManager":
// We need to open the Exchange System Manager,
// if it is installed.
string fileName = "Exchange System Manager.msc";
string exchangePath = "Exchsrvr\\bin";
string programFilesPath = Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles);
string fullPath = System.IO.Path.Combine(programFilesPath, exchangePath);
fullPath = System.IO.Path.Combine(fullPath, fileName);
if (System.IO.File.Exists(fullPath))
{
System.Diagnostics.Process.Start(fullPath);
}
break;
default:
break;
}
}
This code is triggered when the shortcut menu is clicked; it verifies that Exchange System Manager is present before it attempts to start it.
Further Integration with Reporting Services
To further demonstrate how Visio can integrate with Reporting Services, we can take the examples given previously and build on them to add the ability to import reporting services data and link it directly to shapes on the diagram.
Reporting Services Export Formats
Reporting Services offers several ways to export data for consumption in other applications. From a Reporting Services report Web page, you can view export format options, which include XML, text, Bitmap, PDF, and Excel. Selecting any of these options dynamically generates a file or stream on the Web server, which is then downloaded by the client.
Figure 10. Reporting Services export options
.gif "Reporting Services export options")
By adjusting the URL that we pass to Reporting Services, we can force the server to output the report in the designated format and to download it programmatically from within Visio by using the System.Net.WebClient object. By starting with the URL that we referenced earlier for an individual computer's availability report, removing the computer parameter, and adding "rs:Format=Excel" to the query string, we can get a report for all servers, exported in Excel worksheet format.
We can then use that report as a direct data source, by using the new Visio 2007 data connectivity features.
Create an Action to Download Data
The first thing we need to do is create another shortcut Action menu from our page. In the AddPageAction method, add the following code.
// Add the Import Server Availability Data Action,
// if it does not already exist.
if (!Convert.ToBoolean(page.PageSheet.get_CellExists("Actions.ImportAvailability", 0)))
{
short rowIndex;
rowIndex = page.PageSheet.AddNamedRow(
(short)VisSectionIndices.visSectionAction,
"ImportAvailability",
(short)VisRowIndices.visRowAction);
// Set the Action cell.
page.PageSheet.get_CellsU("Actions.ImportAvailability.Action").FormulaU = "QUEUEMARKEREVENT(\"ImportAvailability\")";
// Set the Menu cell.
page.PageSheet.get_CellsU("Actions.ImportAvailability.Menu").FormulaU = "\"Import Server Availability Data\"";
}
Add the Marker Event to Download and Link Data
Next, we need to download the data from Reporting Services into Excel format and then link Visio and our shapes to the data.
We need some references to the System.Net and System.IO namespaces.
using System.Net;
using System.IO;
We already defined a switch statement in our Marker Event handler earlier (vsoApplication_MarkerEvent), so we need to add another switch case for the ImportAvailability context. First, we create an instance of the WebClient class, and point it to our ReportingServices page for the Exchange availability report.
case "ImportAvailability":
// Make a Web request to get an Excel worksheet
// from Reporting Services.
WebClient request = new WebClient();
const string reportAddress = "http://sql2005/reports/reportviewer.aspx?";
string reportParameters =
"%2fMicrosoft+Operations+Manager+Reporting" +
"%2fMicrosoft+Exchange+Server" +
"%2fExchange+2000+and+2003+Health+Monitoring+and+Operations" +
"%2fExchange+Server+Availability" +
"&rs:Command=Render" +
"&rs:Format=Excel";
request.Credentials = CredentialCache.DefaultNetworkCredentials;
string localFileName = System.IO.Path.Combine(vsoApplication.ActiveDocument.Path, "Exchange Server Availability.xls");
try
{
request.DownloadFile(reportAddress + reportParameters, localFileName);
}
catch (Exception)
{
System.Windows.Forms.MessageBox.Show("Could not connect to reporting services.");
return;
}
Notice we changed the query string to provide data in Excel format. Now that we have downloaded the Excel file and saved it locally, we can connect to it and use it as a data source for Visio, by using the new DataRecordSet feature.
Connecting data to Visio documents is straightforward in Visio 2007. Each Document object has a DataRecordsets collection that you can add new record sets to. You can use a standard connection string and command text structure that is familiar to most ADO.NET programmers:
// The file is downloaded; use it as a data source.
string connection =
string.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;" +
@"User ID=Admin;" +
@"Data Source={0};" +
@"Mode=Read;" +
@"Extended Properties=""HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;"";" +
@"Jet OLEDB:Engine Type=35;",
localFileName);
string command = "select * from `Exchange Server Availability$8:65535`";
DataRecordset data = null;
try
{
data = vsoApplication.ActiveDocument.DataRecordsets.Add(connection, command, 0, "Exchange Server Availability");
}
catch (Exception)
{
System.Windows.Forms.MessageBox.Show("Could not connect to the data source");
return;
}
// Show the External Data window.
Window dataWindow = vsoApplication.ActiveWindow.Windows.get_ItemFromID(
Convert.ToInt16(Microsoft.Office.Interop.Visio.VisWinTypes.visWinIDExternalData,
System.Globalization.CultureInfo.CurrentUICulture));
if (dataWindow != null)
{
dataWindow.Visible = true;
}
Notice the connection string. It specifies that the first row of data contains header text ("HDR=YES"). Notice also that our command text tells Visio which rows we are interested in (Row 8 through Row 65535). We start at Row 8 because the data output from Reporting Services begins on the eighth row.
Figure 11. Typical generated output from Reporting Services
.gif "Typical generated output from Reporting Services")
Now that the record set is added, we can loop through the data and automatically create links to our shapes by using the Shape object's new LinkToData method:
// We have the data. Loop through it and
// loop through our shapes to find any matches.
System.Array rowIds = data.GetDataRowIDs(string.Empty);
System.Array rows;
for (int i = 0; i < rowIds.Length; i++)
{
// Get the data row.
rows = data.GetRowData((int)rowIds.GetValue(i));
string serverName = (string)rows.GetValue(0);
// The first column is the server name.
// Loop through each shape to find a match.
foreach (Microsoft.Office.Interop.Visio.Shape shape in vsoApplication.ActivePage.Shapes)
{
if (shape.Master != null)
{
if (shape.Master.NameU == "ExchangeComputer")
{
// Get the Shape Data for the name.
string nameFromProp = shape.get_Cells("Prop.NetworkName").FormulaU;
if (serverName == nameFromProp)
{
shape.LinkToData(0, i, false);
break;
}
}
}
else if (shape.Shapes.Count > 0)
{
foreach (Microsoft.Office.Interop.Visio.Shape nestedShape in shape.Shapes)
{
try
{
if (nestedShape.Master != null)
{
if (nestedShape.Master.NameU == "ExchangeComputer")
{
// Get the Shape Data for the name.
string nameFromProp = nestedShape.get_Cells("Prop.NetworkName").get_ResultStr(null);
if (serverName == nameFromProp)
{
nestedShape.LinkToData(1, 1, false);
break;
}
}
}
}
catch (Exception)
{
// Swallow this exception.
}
}
}
}
}
break;
Figure 12. The Visio diagram linked to data
.gif "The Visio diagram linked to data")
The data is now imported into Visio, and all matching shapes are linked to the data.
Now that you have established the data connection, many possibilities exist for bringing that data to life in the diagram. For example, you can use Visio DataGraphics features to display custom data graphics based on the linked data, such as data bars to highlight the percent availability, or colors to indicate troubled systems.
Conclusion
We have seen how MOM 2005, Exchange 2003, and Visio 2007 integrate natively when we use Export To Visio from the Operator Console. We have also seen how to take that integration several steps further by customizing exported diagrams to support integration with the MBSA Visio Connector, to support creating hyperlinks to Reporting Services reports, to support opening the Exchange System manager directly from within Visio, and to import Reporting Services data and link it directly to shapes in the diagram.
About the Author
Since 1997, Visimation has helped companies improve their productivity by providing visual software tools to ease business and technical tasks, and by offering a broad range of consulting services focusing on Microsoft Visio as a platform for rapid development of efficient Automation programs.
Additional Resources
For more information, see the following resources:
For more information about Visio 2007 integration, see the following articles: