AutoDiscovery Data Dictionary for Visio 2000 Enterprise
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Jeff Yarnell
Microsoft Corporation
December 2000
Applies to:
Microsoft Visio 2000 Enterprise Edition
AutoDiscovery Database Schema Version 2.1
Summary: The AutoDiscovery Data Dictionary helps users and administrators with query and report writing applications when using conventional relational database tools. As an add-on solution and networking template, AutoDiscovery and Layout performs network AutoDiscovery and stores information about network systems and topology in a pair of databases. (18 printed pages)
Contents
Introduction
How AutoDiscovery and Layout Uses the Database Data Dictionary
ManagedSystemElement
NetworkElement
PhysicalElement
PhysicalPackage
NetworkPackage
Chassis
Card
Rack
PhysicalInterface
FrameRelayEndPoint
NetworkPort
NetworkInterface
SubNetwork
ElementChange
ObjectIdentifier
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
.gif)
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
.gif)
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
.gif)
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
.gif)
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
.gif)
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
.gif)
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
.gif)
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
.gif)
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
.gif)
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 http://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
.gif)
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
.gif)
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
.gif)
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
.gif)
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
.gif)
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
.gif)
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 http://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
Click to view larger image.
.gif)