Export (0) Print
Expand All
Expand Minimize

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

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:

0 Invalid

1 Subnet

3 NetworkElement

4 PhysicalInterface

5 NetworkPort

6 Enterprise

12 NetworkInterface

25 FrameRelayEndPoint

29 Processor

32 FrameRelayCircuit

AutoDiscovery engine
DiscoveryIDDiscovery iteration in which the device was foundAutoDiscovery 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

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 variable 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
PhysicalMemoryAmount of RAM on the deviceWMI Win32_ComputerSystem\ TotalPhysicalMemory
Dot1dBaseBridgeAddressMAC Address that uniquely identifies this deviceAutoDiscovery 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

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

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

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

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

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

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

Table 6. Chassis table

ColumnDescriptionSource
IDUnique identifier of a chassisAutomatically assigned
NumberOfCardSlotsNumber of expansion slotsUnused
NetworkPackageIDID of the associated network packageAutoDiscovery 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

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
FirmwareVersionVersion of firmware on the cardUnused
SoftwareVersionVersion of software on the cardUnused
PortSwitchIndexPhysical switch number that the card is onAutoDiscovery 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

ColumnDescriptionSource
IDUnique identifier of a rackAutomatically assigned

PhysicalInterface

The PhysicalInterface table contains information about the interfaces for connecting a network device to other network devices.

Table 9. PhysicalInterface table

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
CardIDID of the associated cardAutoDiscovery engine
NetworkMediumIDID of the associated network mediumAutoDiscovery 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

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

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

ColumnDescriptionSource
IDUnique identifier of a portAutomatically assigned
PortNumberPort identifier which usually matches the 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
DuplexThe transmission duplex of the port (for example, half or full)Various vendor-specific MIBs
Dot1dBasePortDevice's unique identifier for this network portMIB-II (RFC 1213)

NetworkInterface

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

Table 12. NetworkInterface table

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

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

Table 13. SubNetwork table

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

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

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

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

Table 15. ObjectIdentifier table

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

Vlan

The Vlan table contains entries for each vlan on the enterprise network.

Table 16. Vlan table

ColumnDescriptionSource
IDUnique identifier of a vlanAutomatically assigned
DomainVlanIDVlan identifier on the domainVarious vendor-specific MIBs
DomainName of the vlan domainVarious vendor-specific MIBs
NameName of the vlanVarious vendor-specific MIBs
LocalVlanIDVlan identifier on the associated deviceVarious 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

ColumnDescriptionSource
IDUnique identifier of a vlan portAutomatically assigned
VlanIDID of the associated vlanAutoDiscovery engine
NetworkPortIDID of the associated network portAutoDiscovery engine

SpanningTree

The SpanningTree table contains entries for each spanning tree on the enterprise network.

Table 18. SpanningTree table

ColumnDescriptionSource
IDUnique identifier of a spanning treeAutomatically assigned
RootPortPort with least cost to root (0 if root)MIB-II (RFC 1286)
RootCostPath cost to root from this bridge (0 if root)MIB-II (RFC 1286)
DesignatedRootIDBridge ID of the root MIB-II (RFC 1286)
BridgeIDBridge ID of this spanning treeMIB-II (RFC 1286)
VlanIDID of the associated vlanAutoDiscovery 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

ColumnDescriptionSource
IDUnique identifier of a spanning tree port.Automatically assigned
PathCostThe assigned cost associated with this link in the network.MIB-II (RFC 1213)
DesignatedPortThe port identifier of the port on the designated bridge for this port's segment.MIB-II (RFC 1286)
DesignatedPortCostThe 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)
StateThe 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)
DesignatedRootIDThe 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)
DesignatedBridgeIDThe bridge identifier of the bridge, which this port considers the designated bridge for this port's segment.MIB-II (RFC 1286)
SpanningTreeIDID of the associate spanning tree.AutoDiscovery engine
NetworkPortIDID 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

ColumnDescriptionSource
IDUnique identifier for a serviceAutomatically assigned
NameName of the serviceWMI Win32_Service\Name
NetworkElementIDID of the associated network elementAutoDiscovery 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

ColumnDescriptionSource
IDUnique identifier for a shared resourceAutomatically assigned
NameName of the shared resource 
NetworkElementIDID of the associated network elementAutoDiscovery engine

OperatingSystem

The OperatingSystem table contains entries that represent all of the operating systems on the enterprise network.

Table 22. Operating System table

ColumnDescriptionSource
IDUnique identifier for the operating systemAutomatically assigned
NameName of the operating systemWMI Win32_OperatingSystem\CSName
OSTypeType 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
VersionThe version of the operating systemWMI Win32_OperatingSystem\Version
TotalVirtualMemoryThe total virtual memory on the systemWMI Win32_OperatingSystem\TotalVirtualMemory
PatchLevelThe current patch level of the operating systemWMI Win32_OperatingSystem\Major + "." + Win32_OperatingSystem\Minor
SerialNumberThe serial number of the operating systemWMI Win32_OperatingSystem\SerialNumber
NetworkElementIDID of the associated network elementAutoDiscovery engine

LogicalDisk

The LogicalDisk table contains entries that represent all of the logically assigned disks on the enterprise network.

Table 23. LogicalDisk table

ColumnDescriptionSource
IDUnique identifier for the logical diskAutomatically assigned
FileSystemFile system of logical disk (for example, "NTFS")WMI Win32_LogicalDisk\FileSystem
SizeThe size in bytes of the logical diskWMI Win32_LogicalDisk\Size
RemoveableA flag indicating if the logical disk is removableTrue if Win32_LogicalDisk\DeviceID = 2,5, Also use Win32_LogicalDisk\MediaType to determine
NetworkElementIDID of the associated network elementAutoDiscovery engine

Processor

The Processor table contains entries that represent the processors on the enterprise network.

Table 24. Processor table

ColumnDescriptionSource
IDUnique identifier for the processorAutomatically assigned
NetworkPackageIDID of the associated network packageAutoDiscovery engine
ProcessorIndexInternal identifier of a processorAutoDiscovery engine
SpeedSpeed in Mhz of the processorAutoDiscovery engine
IndexNumber 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

ColumnDescriptionSource
IDUnique identifier for the network mediumAutomatically assigned
TypeType of the physical connectionAutoDiscovery 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

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.

Show:
© 2014 Microsoft