AutoDiscovery Data Dictionary for Visio 2000 Enterprise

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Published: October 1, 2000

By Jeff Yarnell

Applies to:

• Microsoft Visio 2000 Enterprise Edition

• AutoDiscovery Database Schema Version 2.1

Summary: Microsoft Visio 2000 Enterprise Edition offers several networking templates, including AutoDiscovery and Layout. This add-on solution performs network AutoDiscovery, and stores information about network systems and topology in a pair of databases. The AutoDiscovery Data Dictionary helps users and administrators with query and report writing applications when using conventional relational database tools.

On This Page

Introduction
How AutoDiscovery and Layout Uses the Database
Examples
AutoDiscovery Database Schema Version 2.1

Introduction

The Microsoft® Visio® AutoDiscovery database is designed to hold network configuration information, most of which is automatically obtained and stored in the database by the AutoDiscovery engine. The AutoDiscovery database is designed to store information about the network devices, as well as information about how they are connected.

The database schema is designed to store a detailed representation of a network device involving many tables. A network device is made up of many subcomponents, and the subcomponents themselves are stored in multiple tables. For example, a router may be represented in the AutoDiscovery database as a chassis with several optional modules and many physical interfaces. In this case, the router and its modules and interfaces are all considered subcomponents.

The Visio AutoDiscovery engine manages the two databases slightly differently. The AutoDiscovery (layer-3) database is updated to reflect the current state of a discovered network device when network AutoDiscovery is performed. Therefore, as network device changes are discovered, their corresponding representation in the database is changed. Depending on the change, database records may be updated, added, or marked as deleted.

On the other hand, the layer-2 database uses an AutoDiscovery session identifier to mark current records. The LastDiscovery table contains information describing the ID (DiscoveryID) and time (DiscoveryDate) of the most recent AutoDiscovery session. When AutoDiscovery is performed, records are either added or updated so that their DiscoveryID value matches that of the current AutoDiscovery session. Therefore, layer-2 database records with a DiscoveryID value less than that of the most recent in the LastDiscovery table should be considered as historical, out-of-date information.

This data dictionary is intended to help users and administrators wishing to use the AutoDiscovery database with conventional relational database tools, such as query and report writing applications.

Caution: The Visio AutoDiscovery and Layout solution depends upon the integrity of the database schema and contents. End users should not insert records into or delete records from the database.

For more information on the layer-2 database, see Layer-2 Data Dictionary for Visio 2000 Enterprise.

Primary Keys

The AutoDiscovery database is made up of many tables. The subcomponents of a network device are then stored as rows in the database. Within most tables, the ID column (32-bit integer) uniquely identifies each subcomponent. The ID is the primary key for most tables in the database. Therefore, retrieving all of the properties for a particular network subcomponent requires a Structured Query Language (SQL) JOIN operation on two or more tables using the ID column.

The ID value for a new row in the database is automatically generated by the AutoDiscovery and Layout solution, and is guaranteed to be unique.

Foreign Keys

Relationships between network subcomponents are achieved using the identifier of one subcomponent within a row of data corresponding to another subcomponent. This foreign key relationship enables an association of one network subcomponent with others. In many cases, these are one-to-many relationships. For example, network interfaces are represented by data in the NetworkInterface table. The table's SubNetworkID column serves as an identifier for a row in the SubNetwork table, which represents the corresponding IP subnet to which an interface is attached.

Dates

Date fields in the AutoDiscovery database are stored using 32-bit numbers. A date value is represented as the number of seconds since January 1, 1970.

How AutoDiscovery and Layout Uses the Database

The AutoDiscovery and Layout solution for Visio uses the network topology information in the database to create network documentation featuring shapes from the Visio Network Equipment library, annotated with actual properties of the network devices.

Assigning Database Values

The AutoDiscovery engine uses standard network protocols to determine what devices are on the network, how they are configured, and how they are connected. This information is obtained by querying the network devices themselves, and then saved in the AutoDiscovery database. However, not all the AutoDiscovery database fields are automatically assigned (columns, for example). Some properties are either unable to be automatically discovered, or are intended to be assigned by users of the AutoDiscovery and Layout solution (AssetTrackingNumber, for example).

In some cases, two fields representing the same property are available. One field is for storing the discovered property value while the other is for a user-supplied value for the same property. The reason for this is that while some properties can be discovered and automatically assigned, the network device's management agent sometimes incorrectly represents them. Therefore, the AutoDiscovery and Layout solution for Visio gives precedence to the user-supplied property value. For example, the PhysicalInterface table includes the columns Speed and UserSpeed.

Visio Network Object Model

The Visio Network Object Model (VNOM) is an object-oriented model for data networks. The object model is implemented so that the AutoDiscovery database provides a persistence mechanism for VNOM objects. That is, VNOM classes implement programming interfaces that enable individual objects to be saved and retrieved from the AutoDiscovery database. Therefore, VNOM makes it possible to write an application using a programming language like Microsoft® Visual Basic®, which uses the AutoDiscovery database without writing any SQL statements.

VNOM objects are uniquely identified by a property named OID. The OID of a given VNOM object matches the ID column referred to in the section Primary Keys.

Deleting Records

The AutoDiscovery database includes a Deleted column in the ManagedSystemElement table. This field is used to mark network subcomponents as deleted without actually deleting them from the database. Consequently, the AutoDiscovery and Layout solution offers a recycle bin that contains network entities that have been marked as deleted in the database.

When querying the database for network devices, be sure to consider whether devices marked as deleted should be included and construct the query accordingly.

Data Dictionary

ManagedSystemElement

Table 1 ManagedSystemElement table

The ManagedSystemElement table contains an entry for every network element in the database, regardless of type.

Column	Description	Source
ID	Unique identifier of a network element	Automatically assigned
Description	Simple description of a network element	MIB-II (RFC1213) sysDescr, or MIB-II (RFC1213) ifDescr, depending on the object type
DisplayName	User-assigned name of a network element	User
Deleted	Flag indicating if an element has been marked as deleted	AutoDiscovery and Layout
InstallDate	Date a network element was installed	Unused
ObjectType	Enumerated integer identifying the type of a network element. Possible values include those shown below: Value Description 0 Invalid 1 Subnet 3 NetworkElement 4 PhysicalInterface 5 NetworkPort 6 Enterprise 12 NetworkInterface 25 FrameRelayEndPoint 29 Processor 32 FrameRelayCircuit	AutoDiscovery engine

NetworkElement

Table 2 NetworkElement table

The NetworkElement table contains an entry for every network device that has been identified and queried by the AutoDiscovery engine.

Column	Description	Source
ID	Unique identifier of a network element	Automatically assigned
DNSName	Network name of a device	Network naming services (for example, DNS or WINS)
Name	Name for device assigned by network management	MIB-II (RFC1213) sysName
Contact	Contact person associated with the device	MIB-II (RFC1213) sysContact
Location	Device location	MIB-II (RFC1213) sysLocation
DeviceAvailable	Boolean indicating network availability of device	Unused
NetworkingSoftwareVersion	Network software version	Unused
SysObjectID	Vendor-assigned identifier for device	MIB-II (RFC1213) sysObjectID
Functions	Bitmask identifying roles played by device	AutoDiscovery and Layout
TimeLastAccessed	Time device was last queried by AutoDiscovery engine	AutoDiscovery engine
TimeLastSeen	Time device was identified on the network, but not fully queried by AutoDiscovery	AutoDiscovery engine
DeviceClass	Category of device	AutoDiscovery and Layout
DiscoveryAddress	Network address used to query device	AutoDiscovery engine
DiscoveryAddressType	Network protocol of network address (for example, IP)	AutoDiscovery engine
ReadCommunity	SNMP community for read access	AutoDiscovery engine
WriteCommunity	SNMP community for write access	Unused

PhysicalElement

Table 3 PhysicalElement table

The PhysicalElement table contains entries for network subcomponents that are real physical entities (hardware, for example). An expansion module and a chassis are examples of physical elements.

Column	Description	Source
ID	Unique identifier of a physical element	Automatically assigned
AssetTrackingNumber	Company asset number	Unused
Model	Model name/number	Unused
SerialNumber	Serial number	Unused
Manufacturer	Hardware manufacturer	Unused
Version	Hardware version	Unused

PhysicalPackage

Table 4 PhysicalPackage table

The PhysicalPackage table includes details about the size and weight of physical entities on the network. Units are unspecified since this information is user-supplied.

Column	Description	Source
ID	Unique identifier of a physical package	Automatically assigned
Depth	Depth of physical entity	Unused
Height	Height of physical entity	Unused
Width	Width of physical entity	Unused
Weight	Weight of physical entity	Unused

NetworkPackage

Table 5 NetworkPackage table

The NetworkPackage table serves to associate logical subcomponents of a network device with its corresponding physical subcomponents. The set of physical subcomponents making up a network device can be thought of as being contained within a network package.

Column	Description	Source
ID	Unique identifier of a network package	Automatically assigned
NetworkElementID	ID of the associated network element	AutoDiscovery engine
RackID	ID of the rack in which the network package is mounted	AutoDiscovery engine

Chassis

Table 6 Chassis table

The Chassis table contains information about network packages that are equipped with optional expansion modules.

Column	Description	Source
ID	Unique identifier of a chassis	Automatically assigned
NumberOfCardSlots	Number of expansion slots	Unused
NetworkPackageID	ID of the associated network package	AutoDiscovery engine

Card

Table 7 Card table

The Card table contains information about physical packages that are expansion modules or cards optionally installed in a chassis.

Column	Description	Source
ID	Unique identifier of card	Automatically assigned
NumberOfPorts	Number of ports available on card	Unused
SlotNumber	Slot number the card is installed in	Unused
ChassisID	ID of the associated chassis	AutoDiscovery engine

Rack

Table 8 Rack table

The Rack table contains entries for each rack in the AutoDiscovery database. Because network equipment racks are not manageable or discoverable, the AutoDiscovery engine does not insert rack entries.

Column	Description	Source
ID	Unique identifier of a rack	Automatically assigned

PhysicalInterface

Table 9 PhysicalInterface table

The PhysicalInterface table contains information about the interfaces that connect a network device to other network devices.

Column	Description	Source
ID	Unique identifier of a physical interface	Automatically assigned
IfIndex	Internal identifier of interface assigned by network device's management agent	MIB-II (RFC1213) ifIndex
Speed	Speed, in bits per second, of the interface	MIB-II (RFC1213) ifSpeed
MTU	Size, in octets, of the largest datagram that can be sent or received on the interface (maximum transmission unit)	MIB-II (RFC1213) ifMTU
MACAddress	Interface's link layer address	MIB-II (RFC1213) ifPhysAddress
Type	Interface type number	MIB-II (RFC1213) ifType (type values defined by Internet Assigned Numbers Authority – see https://www.iana.org)
AdminStatus	Administrative status of the interface the last time the device was queried by AutoDiscovery	MIB-II (RFC1213) ifAdminStatus
OperStatus	Operational status of the interface the last time the device was queried by AutoDiscovery	MIB-II (RFC1213) ifOperStatus
Name	Name of the interface	IF-MIB ifName
UserSpeed	User-specified speed, in bits per second, of the interface	User
NetworkPackageID	ID of the associated network package	AutoDiscovery engine

FrameRelayEndPoint

Table 10 FrameRelayEndPoint table

The FrameRelayEndPoint table contains information about Frame Relay protocol end points, which are necessarily associated with a corresponding physical interface.

Column	Description	Source
ID	Unique identifier of a Frame Relay protocol end point	Automatically assigned
CIR	Maximum amount of data, in bits per second, the network provider agrees to transfer (Commited Information Rate)	Frame Relay MIB (RFC1315) frCircuitCommitedBurst
DLCI	Identifier, assigned by the network provider, for the virtual circuit	Frame Relay MIB (RFC1315) frCircuitDlci
EIR	Maximum amount of uncommitted data, in bits per second, the network provider will attempt to transfer (Excess Information Rate)	Frame Relay MIB (RFC1315) frCircuitExcessBurst
UserCIR	User-specified CIR	User
PhysicalInterfaceID	ID of the associated physical interface	AutoDiscovery engine
FrameRelayCircuitID	ID of the associated Frame Relay circuit	AutoDiscovery engine

NetworkPort

Table 11 NetworkPort table

The NetworkPort table contains entries that provide a logical representation of a physical interface, often acting as a port in a bridged or switched environment.

Column	Description	Source
ID	Unique identifier of a port	Automatically assigned
PortNumber	Port identifier which usually matches port number label on exterior of device	Unused
BridgePortNumber	Port identifier assigned by device's network management agent	Bridge MIB (RFC1286) dot1dBasePort
PhysicalInterfaceID	ID of the associated physical interface	AutoDiscovery engine

NetworkInterface

Table 12 NetworkInterface table

The NetworkInterface table contains information about the network layer protocol entity associated with a physical interface.

Column	Description	Source
ID	Unique identifier of a network interface	Automatically assigned
NetworkAddress	Network address assigned to a device interface	MIB-II (RFC1213) ipAdEntAddr
NetworkMask	Subnet mask associated with the network address	MIB-II (RFC1213) ipAdEntNetMask
NetworkAddressType	Network protocol of network address (for example, IP)	AutoDiscovery engine
NetworkAddressNum	Numeric representation of network address	AutoDiscovery engine
PhysicalInterfaceID	ID of the associated physical interface	AutoDiscovery engine
SubNetworkID	ID of the associated subnet	AutoDiscovery engine

SubNetwork

Table 13 SubNetwork table

The SubNetwork table contains entries for each subnet on the enterprise network.

Column	Description	Source
ID	Unique identifier of a subnet	Automatically assigned
NetworkAddress	Network address assigned to a device interface	AutoDiscovery engine
NetworkMask	Subnet mask associated with the network address	AutoDiscovery engine
NetworkAddressType	Network protocol of network address (for example, IP)	AutoDiscovery engine
NetworkAddressNum	Numeric representation of network address	AutoDiscovery engine
Type	Assumed network type based on the interface types on the subnet	AutoDiscovery engine

ElementChange

Table 14 ElementChange table

The ElementChange table is used to record changes to a device's representation in the AutoDiscovery database. When the AutoDiscovery engine detects a change to a device, it updates the device's representation in the AutoDiscovery database to reflect it's current state. A record is added to this table to describe the configuration change.

Column	Description	Source
ID	Unique identifier of a change record	Automatically assigned by database engine
ElementID	ID of the ManagedSystemElement that has been changed	AutoDiscovery engine
Time	Date and time of the change to the ManagedSystemElement	AutoDiscovery engine
Type	Change type	AutoDiscovery engine
Description	Description of the change	AutoDiscovery engine

ObjectIdentifier

Table 15 ObjectIdentifier table

The ObjectIdentifier is used by the AutoDiscovery and Layout solution to uniquely identify one enterprise database from another.

Column	Description	Source
High	Number used by AutoDiscovery and Layout to generate unique identifiers for ID column of most tables	AutoDiscovery engine
Guid	Globally unique identifier of an AutoDiscovery database for a given enterprise network	AutoDiscovery engine
Revision	Database schema version	AutoDiscovery engine
Description	Description of the database contents	AutoDiscovery engine

Examples

How many routers are on my network?

To answer the question, simply query the NetworkElement table that contains a row for each network device found by AutoDiscovery. The DeviceClass column is a number that represents the category of the device. Possible values include: router (1), hub (2), probe (3), switch (4), bridge (5), server (6), printer (7), and other (0).

SELECT COUNT(ID) AS Total
FROM NetworkElement 
WHERE DeviceClass = 1

This simple query counts all records in the NetworkElement table where the DeviceClass column has the value 1 and returns the count as 'Total.'

Recall that the AutoDiscovery database schema includes a Deleted field, which is non-zero if a network subcomponent has been deleted by the AutoDiscovery and Layout solution. To get a total number of routers (excluding any that may have been marked as deleted), use the following query:

SELECT COUNT(NetworkElement.ID) AS Total
FROM NetworkElement INNER JOIN
ManagedSystemElement ON 
NetworkElement.ID = ManagedSystemElement.ID
WHERE (NetworkElement.DeviceClass = 1) AND 
(ManagedSystemElement.Deleted = 0)

How many devices from vendor X do I have on my network?

Identifying Simple Network Management Protocol (SNMP) manageable devices from a particular vendor can be achieved by examining the SysObjectID field in the NetworkElement table. Every vendor has a unique number that is used in the seventh position of the SNMP object identifier named 'SysObjectID' in MIB-II. The numbers assigned to vendors are administered by the Internet Assigned Numbers Authority (IANA) and can be found at https://www.iana.org.

For example, Cisco System's enterprise number is 9. Therefore, a query for the total number of Cisco devices would be:

SELECT COUNT(ID) AS TOTAL
FROM NetworkElement
WHERE (SysObjectID LIKE '1.3.6.1.4.1.9.%')

Show me the names and speeds of all interfaces on a specific router

Because network devices are represented by a collection of their logical and physical subcomponents, this query involves several tables.

SELECT PhysicalInterface.Name,
PhysicalInterface.Speed
FROM NetworkElement INNER JOIN
NetworkPackage ON 
NetworkElement.ID = NetworkPackage.NetworkElementID INNER
JOIN
PhysicalInterface ON 
NetworkPackage.ID = PhysicalInterface.NetworkPackageID
WHERE (NetworkElement.DNSName = 'gateway.adventureworks.com')

Obtaining the records from the PhysicalInterface table corresponding to the device with the specified DNSName requires joining an intermediate table, namely the NetworkPackage table.

Show me all IP interfaces on my network, sorted by IP address

All device interfaces are represented by rows in the PhysicalInterface and NetworkInterface tables. The NetworkInterface table is related to the PhysicalInterface table by its PhysicalInterfaceID column. The rest of the SQL JOIN statements bring together the information about each row in the PhysicalInterface table from the NetworkElement and NetworkPackage tables. Notice that the query includes a SQL WHERE clause that excludes loopback and NULL addresses.

SELECT NetworkInterface.NetworkAddressNum, 
NetworkInterface.NetworkAddress, 
NetworkInterface.NetworkMask, PhysicalInterface.MACAddress, 
NetworkElement.Name, PhysicalInterface.AdminStatus
FROM NetworkInterface INNER JOIN
PhysicalInterface INNER JOIN
NetworkPackage INNER JOIN
NetworkElement ON 
NetworkElement.ID = NetworkPackage.NetworkElementID
ON 
NetworkPackage.ID = PhysicalInterface.NetworkPackageID
ON 
PhysicalInterface.ID = NetworkInterface.PhysicalInterfaceID
WHERE (NetworkInterface.NetworkAddress <> '127.0.0.1') AND 
(NetworkInterface.NetworkAddress <> '0.0.0.0') AND 
(NetworkInterface.NetworkAddress <> '127.0.0.2')
GROUP BY NetworkInterface.NetworkAddressNum, 
NetworkInterface.NetworkAddress, 
NetworkInterface.NetworkMask, 
PhysicalInterface.MACAddress, 
NetworkElement.Name, PhysicalInterface.AdminStatus

Show me the IP Interfaces on a specific subnet

This is another query involving the NetworkInterface and PhysicalInterface tables. However, this query's WHERE clause limits the results to only those rows in the NetworkInterface table that are related to an entry in the SubNetwork table with NetworkAddress 192.168.100.0.

SELECT NetworkInterface.NetworkAddressNum, 
NetworkInterface.NetworkAddress, 
NetworkInterface.NetworkMask, PhysicalInterface.MACAddress, 
NetworkElement.Name, 
ManagedSystemElement.Description
FROM SubNetwork INNER JOIN
NetworkInterface INNER JOIN
ManagedSystemElement INNER JOIN
PhysicalInterface INNER JOIN
NetworkPackage INNER JOIN
NetworkElement ON 
NetworkElement.ID = etworkPackage.NetworkElementID
ON 
NetworkPackage.ID = PhysicalInterface.NetworkPackageID
ON 
PhysicalInterface.ID = ManagedSystemElement.ID ON 
NetworkInterface.PhysicalInterfaceID = ManagedSystemElement.ID
ON 
NetworkInterface.SubNetworkID = SubNetwork.ID
WHERE (NetworkInterface.NetworkAddress <> '127.0.0.1') AND 
(NetworkInterface.NetworkAddress <> '0.0.0.0') AND 
(NetworkInterface.NetworkAddress <> '127.0.0.2') AND 
(ManagedSystemElement.Deleted = 0) AND 
(SubNetwork.NetworkAddress = '192.168.100.0')
GROUP BY NetworkInterface.NetworkAddressNum, 
NetworkInterface.NetworkAddress, 
NetworkInterface.NetworkMask, 
PhysicalInterface.MACAddress, 
NetworkElement.Name, 
ManagedSystemElement.Description

AutoDiscovery Database Schema Version 2.1