AutoDiscovery Data Dictionary for Visio Enterprise Network Tools
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.
Chris Wick and Jeff Yarnell
Microsoft Corporation
April 2002
Applies to:
Microsoft Visio Professional 2002
Microsoft Visio Enterprise Network Tools 2002
AutoDiscovery Database Schema Version 4.0
Summary: This AutoDiscovery 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 (24 printed pages).
Contents
Introduction
How AutoDiscovery and Layout Uses the Database
Data Dictionary
Examples
AutoDiscovery Database Schema Version 4.0
Introduction
Microsoft® Visio® Enterprise Network Tools provides an add-on networking template called AutoDiscovery and Layout that integrates with Visio 2002. This add-on solution performs network AutoDiscovery and stores information about network systems and topology in a pair of databases.
The Microsoft® AutoDiscovery database is designed to hold network and device configuration information, most of which is automatically obtained and stored in the database by the AutoDiscovery engine.
The AutoDiscovery 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.
The database schema is designed to store a detailed representation of a network device across multiple tables. Such a network device is made up of many subcomponents, and the subcomponents themselves are also stored in many 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 lifetime of layer-2 and layer-3 objects within the AutoDiscovery database are managed differently. The layer-3 information in the 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 data within the database uses an AutoDiscovery session identifier to mark current records. When AutoDiscovery is performed, records are either added or updated so that their DiscoveryID value matches that of the current AutoDiscovery session. This session ID is incremented with each discovery. Therefore, layer-2 database records with a DiscoveryID value less than that of the highest DiscoveryID in the database should be considered as historical, out-of-date information.
Caution The 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.
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 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.
Network Object Model
The Network Object Model (NOM) is an object-oriented model for data networks. The object model is implemented so that the AutoDiscovery database provides a persistence mechanism for NOM objects. That is, NOM classes implement programming interfaces that enable individual objects to be saved and retrieved from the AutoDiscovery database. Therefore, NOM 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.
NOM objects are uniquely identified by a property named OID. The OID property of a given NOM 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 that are marked as deleted should be included and construct the query accordingly.
Data Dictionary
ManagedSystemElement
The ManagedSystemElement table contains an entry for every network element in the database, regardless of type.
Table 1. ManagedSystemElement table
| 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:
0 Invalid 1 Subnet 3 NetworkElement 4 PhysicalInterface 5 NetworkPort 6 Enterprise 12 NetworkInterface 25 FrameRelayEndPoint 29 Processor 32 FrameRelayCircuit |
AutoDiscovery engine |
| DiscoveryID | Discovery iteration in which the device was found | AutoDiscovery engine |
NetworkElement
The NetworkElement table contains an entry for every network device that has been identified and queried by the AutoDiscovery engine.
Table 2. NetworkElement table
| 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 variable 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 |
| PhysicalMemory | Amount of RAM on the device | WMI Win32_ComputerSystem\ TotalPhysicalMemory |
| Dot1dBaseBridgeAddress | MAC Address that uniquely identifies this device | AutoDiscovery engine |
PhysicalElement
The PhysicalElement table contains entries for network subcomponents that are real physical (for example, hardware) entities. An expansion module and a chassis are examples of physical elements.
Table 3. PhysicalElement table
| 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
The PhysicalPackage table includes details about the size and weight of physical entities on the network. Because this information is user-supplied, units are unspecified.
Table 4. PhysicalPackage table
| 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
The NetworkPackage table serves to associate logical subcomponents of a network device with its corresponding physical subcomponents. The set of physical subcomponents that make up a network device can be thought of as being contained within a network package.
Table 5. NetworkPackage table
| 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
The Chassis table contains information about network packages that are equipped with optional expansion modules.
Table 6. Chassis table
| 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
The Card table contains information about physical packages that are expansion modules or cards optionally installed in a chassis.
Table 7. Card table
| 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 |
| FirmwareVersion | Version of firmware on the card | Unused |
| SoftwareVersion | Version of software on the card | Unused |
| PortSwitchIndex | Physical switch number that the card is on | AutoDiscovery engine |
Rack
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.
Table 8. Rack table
| Column | Description | Source |
|---|---|---|
| ID | Unique identifier of a rack | Automatically assigned |
PhysicalInterface
The PhysicalInterface table contains information about the interfaces for connecting a network device to other network devices.
Table 9. PhysicalInterface table
| 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 |
| CardID | ID of the associated card | AutoDiscovery engine |
| NetworkMediumID | ID of the associated network medium | AutoDiscovery engine |
FrameRelayEndPoint
The FrameRelayEndPoint table contains information about Frame Relay protocol end points, which are necessarily associated with a corresponding physical interface.
Table 10. FrameRelayEndPoint table
| 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
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.
Table 11. NetworkPort table
| Column | Description | Source |
|---|---|---|
| ID | Unique identifier of a port | Automatically assigned |
| PortNumber | Port identifier which usually matches the 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 |
| Duplex | The transmission duplex of the port (for example, half or full) | Various vendor-specific MIBs |
| Dot1dBasePort | Device's unique identifier for this network port | MIB-II (RFC 1213) |
NetworkInterface
The NetworkInterface table contains information about the network layer protocol entity associated with a physical interface.
Table 12. NetworkInterface table
| 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
The SubNetwork table contains entries for each subnet on the enterprise network.
Table 13. SubNetwork table
| 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
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 its current state. A record is added to this table to describe the configuration change.
Table 14. ElementChange table
| 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
The ObjectIdentifier table is used by the AutoDiscovery and Layout solution to uniquely identify one enterprise database from another.
Table 15. ObjectIdentifier table
| 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 |
Vlan
The Vlan table contains entries for each vlan on the enterprise network.
Table 16. Vlan table
| Column | Description | Source |
|---|---|---|
| ID | Unique identifier of a vlan | Automatically assigned |
| DomainVlanID | Vlan identifier on the domain | Various vendor-specific MIBs |
| Domain | Name of the vlan domain | Various vendor-specific MIBs |
| Name | Name of the vlan | Various vendor-specific MIBs |
| LocalVlanID | Vlan identifier on the associated device | Various vendor-specific MIBs |
VlanPort
The VlanPort table contains entries that represent the many-to-many relationship between vlans and network ports.
Table 17. VlanPort table
| Column | Description | Source |
|---|---|---|
| ID | Unique identifier of a vlan port | Automatically assigned |
| VlanID | ID of the associated vlan | AutoDiscovery engine |
| NetworkPortID | ID of the associated network port | AutoDiscovery engine |
SpanningTree
The SpanningTree table contains entries for each spanning tree on the enterprise network.
Table 18. SpanningTree table
| Column | Description | Source |
|---|---|---|
| ID | Unique identifier of a spanning tree | Automatically assigned |
| RootPort | Port with least cost to root (0 if root) | MIB-II (RFC 1286) |
| RootCost | Path cost to root from this bridge (0 if root) | MIB-II (RFC 1286) |
| DesignatedRootID | Bridge ID of the root | MIB-II (RFC 1286) |
| BridgeID | Bridge ID of this spanning tree | MIB-II (RFC 1286) |
| VlanID | ID of the associated vlan | AutoDiscovery engine |
SpanningTreePort
The SpanningTreePort table contains entries that represent the many-to-many relationships between spanning tree ports and network ports.
Table 19. SpanningTreePort table
| Column | Description | Source |
|---|---|---|
| ID | Unique identifier of a spanning tree port. | Automatically assigned |
| PathCost | The assigned cost associated with this link in the network. | MIB-II (RFC 1213) |
| DesignatedPort | The port identifier of the port on the designated bridge for this port's segment. | MIB-II (RFC 1286) |
| DesignatedPortCost | The path cost of the designated port of the segment connected to this port. This value is compared to the root path cost field in received bridge PDUs. | MIB-II (RFC 1286) |
| State | The port's current state as defined by application of the spanning tree protocol. This state controls what action a port takes on reception of a frame. If the bridge has detected a port that is malfunctioning, it will place that port into the broken (6) state. For ports, which are disabled, this object will have a value of disabled (1). | MIB-II (RFC 1286) |
| DesignatedRootID | The unique bridge identifier of the bridge recorded as the root in the configuration BPDUs transmitted by the designated bridge for the segment to which the port is attached. | MIB-II (RFC 1286) |
| DesignatedBridgeID | The bridge identifier of the bridge, which this port considers the designated bridge for this port's segment. | MIB-II (RFC 1286) |
| SpanningTreeID | ID of the associate spanning tree. | AutoDiscovery engine |
| NetworkPortID | ID of the associated network port. | AutoDiscovery engine |
Service
The Service table contains entries that represent all of the services on the enterprise network. Examples include a DHCP service and a time service.
Table 20. Service table
| Column | Description | Source |
|---|---|---|
| ID | Unique identifier for a service | Automatically assigned |
| Name | Name of the service | WMI Win32_Service\Name |
| NetworkElementID | ID of the associated network element | AutoDiscovery engine |
SharedResource
The SharedResource table contains entries that represent all of the shared resources on the enterprise network. Examples of such objects include shared printers (that are not manageable) and shared drives on a network.
Table 21. SharedResource table
| Column | Description | Source |
|---|---|---|
| ID | Unique identifier for a shared resource | Automatically assigned |
| Name | Name of the shared resource | |
| NetworkElementID | ID of the associated network element | AutoDiscovery engine |
OperatingSystem
The OperatingSystem table contains entries that represent all of the operating systems on the enterprise network.
Table 22. Operating System table
| Column | Description | Source |
|---|---|---|
| ID | Unique identifier for the operating system | Automatically assigned |
| Name | Name of the operating system | WMI Win32_OperatingSystem\CSName |
| OSType | Type of the operating system. Possible values include:
0 = Unknown 1 = Other 2 = MACOS 3 = ATTUNIX 4 = DGUX 5 = DECNT 6 = Digital Unix 7 = OpenVMS 8 = HPUX 9 = AIX 10 = MVS 11 = OS400 12 = OS/2 13 = JavaVM 14 = MSDOS 15 = WIN3x 16 = WIN95 17 = WIN98 18 = WINNT 19 = WINCE 20 = NCR3000 21 = NetWare 22 = OSF 23 = DC/OS 24 = Reliant UNIX 25 = SCO UnixWare 26 = SCO OpenServer 27 = Sequent 28 = IRIX 29 = Solaris 30 = SunOS 31 = U6000 32 = ASERIES 33 = TandemNSK 34 = TandemNT 35 = BS2000 36 = LINUX 37 = Lynx 38 = XENIX 39 = VM/ESA 40 = Interactive UNIX 41 = BSDUNIX 42 = FreeBSD 43 = NetBSD 44 = GNU Hurd 45 = OS9 46 = MACH Kernel 47 = Inferno 48 = QNX 49 = EPOC 50 = IxWorks 51 = VxWorks 52 = MiNT 53 = BeOS 54 = HP MPE 55 = NextStep 56 = PalmPilot 57 = Rhapsody |
WMI Win32_OperatingSystem\OSType |
| Version | The version of the operating system | WMI Win32_OperatingSystem\Version |
| TotalVirtualMemory | The total virtual memory on the system | WMI Win32_OperatingSystem\TotalVirtualMemory |
| PatchLevel | The current patch level of the operating system | WMI Win32_OperatingSystem\Major + "." + Win32_OperatingSystem\Minor |
| SerialNumber | The serial number of the operating system | WMI Win32_OperatingSystem\SerialNumber |
| NetworkElementID | ID of the associated network element | AutoDiscovery engine |
LogicalDisk
The LogicalDisk table contains entries that represent all of the logically assigned disks on the enterprise network.
Table 23. LogicalDisk table
| Column | Description | Source |
|---|---|---|
| ID | Unique identifier for the logical disk | Automatically assigned |
| FileSystem | File system of logical disk (for example, "NTFS") | WMI Win32_LogicalDisk\FileSystem |
| Size | The size in bytes of the logical disk | WMI Win32_LogicalDisk\Size |
| Removeable | A flag indicating if the logical disk is removable | True if Win32_LogicalDisk\DeviceID = 2,5, Also use Win32_LogicalDisk\MediaType to determine |
| NetworkElementID | ID of the associated network element | AutoDiscovery engine |
Processor
The Processor table contains entries that represent the processors on the enterprise network.
Table 24. Processor table
| Column | Description | Source |
|---|---|---|
| ID | Unique identifier for the processor | Automatically assigned |
| NetworkPackageID | ID of the associated network package | AutoDiscovery engine |
| ProcessorIndex | Internal identifier of a processor | AutoDiscovery engine |
| Speed | Speed in Mhz of the processor | AutoDiscovery engine |
| Index | Number of the processor (in the case of multiple-processor devices) | AutoDiscovery engine |
NetworkMedium
The NetworkMedium table contains entries that represent the network media on the enterprise network. A network medium represents a physical segment on the network.
Table 25. NetworkMedium table
| Column | Description | Source |
|---|---|---|
| ID | Unique identifier for the network medium | Automatically assigned |
| Type | Type of the physical connection | 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 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'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 Joins 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 4.0
.gif "Aa140345.visAutDisc01thumb(en-us,office.10).gif")
Figure 1. Click to view larger image, then right-click on the larger image and save to your desktop for better viewing.
Jeff Yarnell is a Software Design Engineer Lead in the Microsoft Network Central Group. Jeff has been developing network management software for ten years, and has been with the AutoDiscovery and Layout group for five years.
Christopher Wick is a Software Design Engineer in the Microsoft Network Central Group, working on AutoDiscovery. Chris helped design and implement the Network Object Model (NOM), and has been with Microsoft for four years.