Export (0) Print
Expand All

AutoDiscovery Data Dictionary for Visio 2000 Enterprise

Office 2000
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

Aa140245.add1(en-us,office.10).gif

Table 1. ManagedSystemElement table

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

ColumnDescriptionSource
IDUnique identifier of a network elementAutomatically assigned
DescriptionSimple description of a network elementMIB-II (RFC1213) sysDescr, or MIB-II (RFC1213) ifDescr, depending on the object type
DisplayNameUser-assigned name of a network elementUser
DeletedFlag indicating if an element has been marked as deletedAutoDiscovery and Layout
InstallDateDate a network element was installedUnused
ObjectTypeEnumerated 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

Aa140245.add2(en-us,office.10).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.

ColumnDescriptionSource
IDUnique identifier of a network elementAutomatically assigned
DNSNameNetwork name of a deviceNetwork naming services (for example, DNS or WINS)
NameName for device assigned by network managementMIB-II (RFC1213) sysName
ContactContact person associated with the deviceMIB-II (RFC1213) sysContact
LocationDevice locationMIB-II (RFC1213) sysLocation
DeviceAvailableBoolean indicating network availability of deviceUnused
NetworkingSoftwareVersionNetwork software versionUnused
SysObjectIDVendor-assigned identifier for deviceMIB-II (RFC1213) sysObjectID
FunctionsBitmask identifying roles played by deviceAutoDiscovery and Layout
TimeLastAccessedTime device was last queried by AutoDiscovery engineAutoDiscovery engine
TimeLastSeenTime device was identified on the network, but not fully queried by AutoDiscoveryAutoDiscovery engine
DeviceClassCategory of deviceAutoDiscovery and Layout
DiscoveryAddressNetwork address used to query deviceAutoDiscovery engine
DiscoveryAddressTypeNetwork protocol of network address (for example, IP)AutoDiscovery engine
ReadCommunitySNMP community for read accessAutoDiscovery engine
WriteCommunitySNMP community for write accessUnused

PhysicalElement

Aa140245.add3(en-us,office.10).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.

ColumnDescriptionSource
IDUnique identifier of a physical elementAutomatically assigned
AssetTrackingNumberCompany asset numberUnused
ModelModel name/numberUnused
SerialNumberSerial numberUnused
ManufacturerHardware manufacturerUnused
VersionHardware versionUnused

PhysicalPackage

Aa140245.add4(en-us,office.10).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.

ColumnDescriptionSource
IDUnique identifier of a physical packageAutomatically assigned
DepthDepth of physical entityUnused
HeightHeight of physical entityUnused
WidthWidth of physical entityUnused
WeightWeight of physical entityUnused

NetworkPackage

Aa140245.add5(en-us,office.10).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.

ColumnDescriptionSource
IDUnique identifier of a network packageAutomatically assigned
NetworkElementIDID of the associated network elementAutoDiscovery engine
RackIDID of the rack in which the network package is mountedAutoDiscovery engine

Chassis

Aa140245.add6(en-us,office.10).gif

Table 6. Chassis table

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

ColumnDescriptionSource
IDUnique identifier of a chassisAutomatically assigned
NumberOfCardSlotsNumber of expansion slotsUnused
NetworkPackageIDID of the associated network packageAutoDiscovery engine

Card

Aa140245.add7(en-us,office.10).gif

Table 7. Card table

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

ColumnDescriptionSource
IDUnique identifier of cardAutomatically assigned
NumberOfPortsNumber of ports available on cardUnused
SlotNumberSlot number the card is installed in Unused
ChassisIDID of the associated chassisAutoDiscovery engine

Rack

Aa140245.add8(en-us,office.10).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.

ColumnDescriptionSource
IDUnique identifier of a rackAutomatically assigned

PhysicalInterface

Aa140245.add9(en-us,office.10).gif

Table 9. PhysicalInterface table

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

ColumnDescriptionSource
IDUnique identifier of a physical interfaceAutomatically assigned
IfIndexInternal identifier of interface assigned by network device's management agentMIB-II (RFC1213) ifIndex
SpeedSpeed, in bits per second, of the interfaceMIB-II (RFC1213) ifSpeed
MTUSize, in octets, of the largest datagram that can be sent or received on the interface (maximum transmission unit)MIB-II (RFC1213) ifMTU
MACAddressInterface's link layer addressMIB-II (RFC1213) ifPhysAddress
TypeInterface type numberMIB-II (RFC1213) ifType (type values defined by Internet Assigned Numbers Authority – see http://www.iana.org)
AdminStatusAdministrative status of the interface the last time the device was queried by AutoDiscoveryMIB-II (RFC1213) ifAdminStatus
OperStatusOperational status of the interface the last time the device was queried by AutoDiscoveryMIB-II (RFC1213) ifOperStatus
NameName of the interfaceIF-MIB ifName
UserSpeedUser-specified speed, in bits per second, of the interfaceUser
NetworkPackageIDID of the associated network packageAutoDiscovery engine

FrameRelayEndPoint

Aa140245.add10(en-us,office.10).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.

ColumnDescriptionSource
IDUnique identifier of a Frame Relay protocol end pointAutomatically assigned
CIRMaximum amount of data, in bits per second, the network provider agrees to transfer (Commited Information Rate)Frame Relay MIB (RFC1315) frCircuitCommitedBurst
DLCIIdentifier, assigned by the network provider, for the virtual circuitFrame Relay MIB (RFC1315) frCircuitDlci
EIRMaximum amount of uncommitted data, in bits per second, the network provider will attempt to transfer (Excess Information Rate)Frame Relay MIB (RFC1315) frCircuitExcessBurst
UserCIRUser-specified CIRUser
PhysicalInterfaceIDID of the associated physical interfaceAutoDiscovery engine
FrameRelayCircuitIDID of the associated Frame Relay circuitAutoDiscovery engine

NetworkPort

Aa140245.add11(en-us,office.10).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.

ColumnDescriptionSource
IDUnique identifier of a portAutomatically assigned
PortNumberPort identifier which usually matches port number label on exterior of deviceUnused
BridgePortNumberPort identifier assigned by device's network management agentBridge MIB (RFC1286) dot1dBasePort
PhysicalInterfaceIDID of the associated physical interfaceAutoDiscovery engine

NetworkInterface

Aa140245.add12(en-us,office.10).gif

Table 12. NetworkInterface table

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

ColumnDescriptionSource
IDUnique identifier of a network interfaceAutomatically assigned
NetworkAddressNetwork address assigned to a device interfaceMIB-II (RFC1213) ipAdEntAddr
NetworkMaskSubnet mask associated with the network addressMIB-II (RFC1213) ipAdEntNetMask
NetworkAddressTypeNetwork protocol of network address (for example, IP)AutoDiscovery engine
NetworkAddressNumNumeric representation of network addressAutoDiscovery engine
PhysicalInterfaceIDID of the associated physical interfaceAutoDiscovery engine
SubNetworkIDID of the associated subnetAutoDiscovery engine

SubNetwork

Aa140245.add13(en-us,office.10).gif

Table 13. SubNetwork table

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

ColumnDescriptionSource
IDUnique identifier of a subnetAutomatically assigned
NetworkAddressNetwork address assigned to a device interfaceAutoDiscovery engine
NetworkMaskSubnet mask associated with the network addressAutoDiscovery engine
NetworkAddressTypeNetwork protocol of network address (for example, IP)AutoDiscovery engine
NetworkAddressNumNumeric representation of network addressAutoDiscovery engine
TypeAssumed network type based on the interface types on the subnetAutoDiscovery engine

ElementChange

Aa140245.add14(en-us,office.10).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.

ColumnDescriptionSource
IDUnique identifier of a change recordAutomatically assigned by database engine
ElementIDID of the ManagedSystemElement that has been changedAutoDiscovery engine
TimeDate and time of the change to the ManagedSystemElementAutoDiscovery engine
TypeChange typeAutoDiscovery engine
DescriptionDescription of the changeAutoDiscovery engine

ObjectIdentifier

Aa140245.add15(en-us,office.10).gif

Table 15. ObjectIdentifier table

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

ColumnDescriptionSource
HighNumber used by AutoDiscovery and Layout to generate unique identifiers for ID column of most tablesAutoDiscovery engine
GuidGlobally unique identifier of an AutoDiscovery database for a given enterprise networkAutoDiscovery engine
RevisionDatabase schema versionAutoDiscovery engine
DescriptionDescription of the database contentsAutoDiscovery 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.

Microsoft Visio AutoDiscovery and Layout Database Schema

Show:
© 2014 Microsoft