Novell Documentation: ZENworks 6.5 - Inventory Database Schema in ZENworks 6.5 Server Management

Inventory Database Schema in ZENworks 6.5 Server Management

The following section describes the database schema classes and the extensions and associations made to the CIM schema for use in ZENworks 6.5 Server Management. These extensions have ZENworks or ManageWise as their schema name. ZENworks.classname refers to the extended class in the ZENworks schema and ManageWise.classname refers to the extended class in the ManageWise schema.

The following sections will help you understand the ZENworks 6.5 Server Management database schema:

Case Study of CIM Schema Implementation in ZENworks 6.5 Server Management

The following scenario describes an inventoried server that has two parallel ports with a specified interrupt number.

In the following schema diagram, the CIM.UnitaryComputerSystem represents a managed inventory system.

In this illustration, class CIM.PointingDevice associates to CIM.UnitaryComputerSystem using the association CIM.SystemDevice with SystemDevice.GroupComponent pointing to CIM.UnitaryComputerSystem and SystemDevice.PartComponent pointing to CIM.PointingDevice. The relationship between the two classes is one to many. This means a computer system might have more than one pointing devices.

Class CIM.IRQ associates to CIM.PointingDevice using the association CIM.AllocatedResource. Dependent pointing to CIM.PointingDevice and Antecedent pointing to CIM.IRQ.

Class ZENworks.ZENKeyboard associates to CIM.UnitaryComputerSystem using the association CIM.SystemDevice with SystemDevice.GroupComponent pointing to CIM.UnitaryComputerSystem and SystemDevice.PartComponent pointing to ZENworks.ZENKeyboard. The relationship between the two classes is one to one. This means a computer system can have only one Keyboard.

Class ZENworks.BIOS associates to CIM.UnitaryComputerSystem using the association CIM.SystemBIOS with SystemDevice.GroupComponent pointing to CIM.UnitaryComputerSystem and SystemBIOS.PartComponent pointing to ZENworks.BIOS. The relationship between the two classes is one to one. This means a computer system can have only one BIOS.

Class CIM.ZENworks.ParallelPort associates to CIM.UnitaryComputerSystem using the association CIM.SystemDevice with SystemDevice.GroupComponent pointing to CIM.UnitaryComputerSystem and SystemDevice.PartComponent pointing to CIM.ZENworks.ParallelPort. The relationship between the two classes is one to many. This means a computer system might have more than one parallel port.

Class ZENworks.BUS associates to CIM.UnitaryComputerSystem using the association CIM.SystemDevice with SystemDevice.GroupComponent pointing to CIM.UnitaryComputerSystem and SystemDevice.PartComponent pointing to ZENworks.BUS. The relationship between the two classes is one to many. This means a computer system can have more than one bus.

Class ManageWise.User associates to CIM.UnitaryComputerSystem using CurrentLoginUser and LastLoginUser. In the CurrentLoginUser association, the specific instance of User is the one who is currently logged into the inventoried server. In the LastLoginUser association, the specific instance of User is the one who logged last into the inventoried server.

Class CIM.IRQ associates to CIM.ParallelPort using the association CIM.AllocatedResource. Dependent pointing to CIM.ParallelPort and Antecedent pointing to CIM.IRQ.

Schema diagram for CIM.UnitaryComputerSystem with its associations

The schema diagram illustrates the following:

All components that a computer system manages are represented as associations from the UnitaryComputerSystem class. The type of references (1..n, 1..1) between two classes are marked.
Those associations that do not have a schema name are assumed as CIM schema.
There are three instances of ZENworks.ParallelPort associated to one instance of CIM.UnitaryComputerSystem using three instances of CIM.SystemDevice associations. CIM.SystemDevice.GroupComponent references UnitaryComputerSystem and CIM.SystemDevice.PartComponent references ParallelPort.

This is called 1 to n object reference relationship and is depicted in the illustration as 1..*. Similarly, every instance of ParallelPort has a corresponding instance of CIM.IRQ designating the port's irq. This is one-to-one relationship and is depicted as 1..1.

All other classes follow similar representation.

For schema diagrams of other classes, see Schema Diagrams of CIM and the Extension Schema in ZENworks 6.5 Server Management.

Legends for Schema Diagrams

The legends for reading the schema diagrams are as follows:

Class names are enclosed in boxes with the class name as the heading and the attribute names within it.
Red lines connect two classes using an association class.
Blue lines indicate the class inheritance hierarchy. The class pointed by the arrow is the class that is being inherited from. The class from where the arrow emanates is the inheriting class.
The association class name is shown within the line joining two classes.
References of the association class are marked on either side of the associated classes.

For an explanation about CIM schema, see the CIM 2.2 schema specification on the DMTF Web site.

Schema Diagrams of CIM and the Extension Schema in ZENworks 6.5 Server Management

The following schema diagrams of the CIM and extension schema model the Inventory database in ZENworks 6.5 Server Management.

Schema for Processor, Operating Systems, and Video Adapter

Schema for Inventory Scanner and NetWare Client

Schema for Chassis and System Information

Schema for Input devices, Port, Driver, User information, and BIOS

Schema for Network, Modem, and Sound Adapter

Schema for Battery, Card, Cache, Mother Board, and DMA

Software Inventory Schema

The following software schema diagrams model the Inventory database in ZENworks 6.5 Server Management. In the following schema diagram, the CIM.UnitaryComputerSystem represents a managed inventory system.

For more information about the tables, see ZENworks 6.5 Server Management Inventory Attributes.

In the above illustration, class MW_DBA.Software associates to CIM.UnitaryComputerSystem using the association MW_DBA.InstalledSoftware with MW_DBA.InstalledSoftware.ComputerSystem pointing to CIM.UnitaryComputerSystem and MW_DBA.InstalledSoftware.ProductID pointing to MW_DBA.Software. The relationship between the two classes is one to many. This means a computer system might have more than one software information.

MW_DBA.InstalledSoftware association has Foreign key references to the following tables: ProductEdition, SupportPack, Directory, and Installation Repository.

MW_DBA.InstalledVirusScanner inherits the software information from MW_DBA.InstalledSoftware along with virus specific information such as Definition date and Definition version.

In the above illustration, class MW_DBA.Patch associates to MW_DBA.InstalledSoftware using the association MW_DBA.InstalledSoftwarePatch with MW_DBA.InstalledSoftwarePatch.pinstanceID pointing to MW_DBA.InstalledSoftware and MW_DBA.InstalledSoftwarePatch.PatchID pointing to MW_DBA.Patch. The relationship between the two classes is one to many. This means a software might have zero or more patch information.

Schema for File and Directory Information

In the above illustration, class MW_DBA.File associates to MW_DBA.InstalledSoftware using the association MW_DBA.InstalledFile with MW_DBA.InstalledFile.pinstanceID pointing to MW_DBA.InstalledSoftware and MW_DBA.InstalledFile.fileID pointing to MW_DBA.File. The relationship between the two classes is one to many. This means a software might have zero or more file information.

In this illustration, class MW_DBA.Directory associates to MW_DBA.InstalledSoftware using the association MW_DBA.InstalledFile with MW_DBA.InstalledFile.pinstanceID pointing to MW_DBA.InstalledSoftware and MW_DBA.InstalledFile.DirectoryID pointing to MW_DBA.Directory.

In the above illustrations, MW_DBA.MSoffice inherits the software information from MW_DBA.SOftware. This sub-class gets directly the MS office information.

This is also applicable for the following classes:

mw_dba.zfdserver	mw_dba.zfdinventoryserver	mw_dba.zfdagent
mw_dba.zfsserver	mw_dba.zfdinventoryagent	mw_dba.zfsagent
mw_dba.zfsinventoryserver	mw_dba.mspowerpoint	mw_dba.msphotodraw
mw_dba.zfsinventoryagent	mw_dba.msoutlook	mw_dba.zfdwsmanager
mw_dba.zfsrmserver	mw_dba.msaccess	mw_dba.zfdwsimportserver
mw_dba.zfsrmagent	mw_dba.mspublisher	mw_dba.zfdinvdbserver
mw_dba.zfdrmserver	mw_dba.msfrontpage	mw_dba.zfsinvdbserver
mw_dba.zfdrmagent	mw_dba.msinfopath	mw_dba.zfdinvxmlproxyserver
mw_dba.zfsinvxmlproxyserver	mw_dba.zfdimagingagent	mw_dba.zfdimagingserver
mw_dba.zfdnalagent	mw_dba.zfdnalserver	mw_dba.zfdnaldb
mw_dba.middletier	mw_dba.zfsmmsserver	mw_dba.zfspds
mw_dba.zfspxeserver	mw_dba.zfsmmssrvmgmtagent	mw_dba.zfsmmstrafficanalysisagent
mw_dba.zfsmmsadctrendingagent	mw_dba.zfspdsdb	mw_dba.zfhserver
mw_dba.zfhaccesspoin	mw_dba.zfhdesktopsync

In the above illustration, MW_DBA.DiskUsage has the computerID column foreign key references to the CIM.UnitaryComputerSystem.ID. The MW_DBA.DiskUsage table contains the total disk usage and the file extension name.

Sample Inventory Database Queries

The following are sample queries for retrieving the inventory information from the ZENworks 6.5 Server Management Inventory database.

Refer to the schema diagrams in Schema Diagrams of CIM and the Extension Schema in ZENworks 6.5 Server Management to find out the associated schema classes and attribute information.

Retrieve the name and ID of all inventoried servers from the database and also to the eDirectory tree to which these servers are registered. The query is as follows:
```
SELECT
```
```
  u.id$, u.name, m.tree 
```
```
FROM 
```
```
  ManageWise.NDSName m, 
```
```
  CIM.UnitaryComputerSystem u, 
```
```
  ManageWise.Designates s
```
```
WHERE 
```
```
  s.Designation=m.id$ AND s.Host=u.id$;
```
In the above query, the tree name is part of the computer system name.

Retrieve the asset tag, manufacturer, and model number of all the inventoried servers in the database. The query is as follows:

SELECT

  m.AssetTag,

  m.Manufacturer,

  m.ModelNumber,

  m.SerialNumber

FROM

  CIM.UnitaryComputerSystem u,

  CIM.ComputerSystemPackage s,

  ZENworks.SystemInfo m

WHERE

  s.Antecedent=m.id$ AND s.Dependent=u.id$;

Retrieve all the Microsoft applications with their versions and IDs that are installed on the inventoried server 'SJOHN164_99_139_79' registered under the 'NOVELL_AUS' eDirectory tree. The query is as follows:

SELECT

  m.Name,

  m.Version,

  im.ProductIdentifier

FROM

  CIM.UnitaryComputerSystem u,

  MW_DBA.InstalledSoftware im,

  MW_DBA.Software m

WHERE

  u.Name='SJOHN164_99_139_79.Novell_AUS' AND

  (im.computerid=u.id$ and im.productid=m.productid)

  AND m.Vendor LIKE 'Microsoft%';

Retrieve the processor information for the inventoried server 'SJOHN164_99_139_79.NOVELL_AUS'. The query is as follows:

SELECT

  	procr.DeviceID,

  	role.EnumString,

  	family.EnumString,

  	procr.OtherFamilyDescription,

  	upg.EnumString,

  	procr.MaxClockSpeed,

  	procr.CurrentClockSpeed,

  	procr.Stepping

FROM

			  CIM.UnitaryComputerSystem ucs,

  	CIM.ComputerSystemProcessor csp,

	  CIM.Processor procr,

  	CIM.Role_en_US role,

  	CIM.Family_en_US family,

  	CIM.UpgradeMethod_en_US upg

WHERE

 ucs.name='SJOHN164_99_139_79.Novell_AUS' AND

  	csp.PartComponent=procr.id$ AND

	    ( procr.Role IS NOT NULL AND procr.Role=role.Enum ) OR

	    ( procr.Role IS NULL AND role.Enum=1000 )

AND

		procr.Family=family.Enum

AND

	    ( procr.UpgradeMethod IS NOT NULL AND procr.UpgradeMethod=upg.Enum ) OR

	    ( procr.UpgradeMethod IS NULL AND upg.Enum=1000 )

);

Retrieve the ID of the UnitaryComputerSystem used for the inventoried server 'SJOHN164_99_139_79.NOVELL_AUS'. The query is as follows:
```
SELECT 
```
```
  id$ 
```
```
FROM 
```
```
  CIM.UnitaryComputerSystem
```
```
WHERE 
```
```
  Name='SJOHN164_99_139_79.Novell_AUS';
```

Find the number of inventoried servers in the database. The query is as follows:

SELECT

  count(u.id$)

FROM

  CIM.UnitaryComputerSystem u,

  CIM.InstalledSoftwareElement s,

  ZENworks.InventoryScanner m

WHERE

  m.id$=s.Software AND u.id$=s.System;

When you know the ID of the UnitaryComputerSystem for a particular inventoried server from the query as shown in query 5, query 4 can be modified as:

SELECT

	procr.DeviceID,

	role.EnumString,

	family.EnumString,

	procr.OtherFamilyDescription,

	upg.EnumString,

	procr.MaxClockSpeed,

	procr.CurrentClockSpeed,

	procr.Stepping

FROM

			CIM.UnitaryComputerSystem ucs,

	CIM.ComputerSystemProcessor csp,

	CIM.Processor procr,

	CIM.Role_en_US role,

	CIM.Family_en_US family,

	CIM.UpgradeMethod_en_US upg

WHERE

ucs.id$ = ? AND

	csp.PartComponent=procr.id$ AND

	    ( procr.Role IS NOT NULL AND procr.Role=role.Enum ) OR

	    ( procr.Role IS NULL AND role.Enum=1000 )

AND

		procr.Family=family.Enum

AND

	    ( procr.UpgradeMethod IS NOT NULL AND procr.UpgradeMethod=upg.Enum ) OR

	    ( procr.UpgradeMethod IS NULL AND upg.Enum=1000 )

);

Substitute the ID of the specified inventoried server in place of the ?, value for ucs.id$ in the query.

List the IP address, IPX address, and MAC address of all servers in the database. The query is as follows:

SELECT

  u.name,

  ip.Address,

  ipx.Address,

  mac.MACAddress

FROM

  CIM.UnitaryComputerSystem u,

  CIM.HostedAccessPoint s1,

  CIM.IPProtocolEndpoint ip,

  CIM.HostedAccessPoint s2,

  CIM.IPXProtocolEndpoint ipx,

  CIM.HostedAccessPoint s3,

  CIM.LANEndpoint mac

WHERE

  (s1.Dependent=ip.id$ and s1.Antecedent=u.id$) AND

  (s2.Dependent=ipx.id$ and s2.Antecedent=u.id$) AND

  (s3.Dependent=mac.id$ and s3.Antecedent=u.id$);

Retrieve the name and other properties of the drives on the hard disk of the specified inventoried server. The query is as follows:

SELECT

  n.Name,

  m.DeviceID,

  n.FileSystemSize,

  n.AvailableSpace,

  n.FileSystemType,

  m.VolumeSerialNumber,

  m.caption as VolumeLabel

FROM

  CIM.HostedFileSystem s,

  CIM.LocalFileSystem n,

  CIM.ResidesOnExtent r,

  ZENworks.LogicalDiskDrive m

WHERE

  (s.GroupComponent=? and s.PartComponent=n.id$) AND

  (r.Dependent=n.id$ and r.Antecedent=m.id$);

Retrieve all Custom attribute information stored in the database. The query is as follows:
```
SELECT * FROM ZENworks.CustomInformation;
```

Retrieve all Custom attribute information associated to the Class CIM.UnitaryComputerSystem. The query is as follows:

SELECT

FROM

  ZENworks.CustomInformation

WHERE

  extractClass(id) IN

  (SELECT id FROM MW_DBA.t$Class WHERE  
  ClassName='CIM. UnitaryComputerSystem')

Retrieve all the Microsoft Office installations in the enterprise. The query is as follows:

SELECT

  		u.name,

  		m.FriendlyName,

  		im.InternalVersion,

  		im.ProductIdentifier

FROM

  		CIM.UnitaryComputerSystem u,

  		MW_DBA.InstalledSoftware im,

  		MW_DBA.Software m,

  		MW_DBA.MSOffice mso

WHERE

  		mso.id$=m.productid AND

  		m.productid=im.productid AND

  		im.computerid=u.id$;

Retrieve all the Internet Explorer installations in the enterprise.. The query is as follows:
```
SELECT 
```
```
  	u.Name,
```
```
  m.Name,
```
```
  m.Version,
```
```
  im.InternalVersion,
```
```
  im.ProductIdentifier
```
```
FROM 
```
```
  CIM.UnitaryComputerSystem u,
```
```
  MW_DBA.InstalledSoftware im,
```
```
  	MW_DBA.Software m,
```
```
  MW_DBA.InternetExplorer ie
```
```
WHERE 
```
```
  ie.id$=m.productid AND
```
```
  m.productid=im.productid AND
```
```
  im.computerid=u.id$;
```
NOTE: Query 12 and 13 follow nearly the same syntax except for the table relating to the component. A similar approach can be used for the following components as well, Windows Media Player, Outlook Express, Microsoft Word, Microsoft Excel etc. The complete set of these tables are available in the Schema.

Retrieve all the Anti-Virus installations in the enterprise. The query is as follows:

SELECT

  	u.Name,

  m.Name,

  m.Version,

  	im.InternalVersion,

  ivs.DefinitionVersion,

  ivs.DefinitionDate

FROM

  CIM.UnitaryComputerSystem u,

  MW_DBA.InstalledSoftware im,

  	MW_DBA.Software m,

  MW_DBA.InstalledVirusScanner ivs

WHERE

  ivs.pinstanceid=im.pinstanceid AND

  m.productid=im.productid AND

  im.computerid=u.id$;

Retrieve all the applications and the details of the files associated with the application that are installed on the inventoried server 'SJOHN164_99_139_79.NOVELL_AUS'. The query is as follows:

SELECT

  	u.Name,

  m.Name,

  m.Version,

  	m.Category,

  	zfile.company,

  	zfile.productname,

  	zfile.productversion,

  	zfile.name,

  	dir.path,

  	zfile.fileversion,

  	zfile."size",

  	zfile.lastmodified,

  	zfile.internalname,

  	zfile.softwaredictionaryid

FROM

  CIM.UnitaryComputerSystem u,

  MW_DBA.InstalledSoftware iso,

  	MW_DBA.Software m,

  	MW_DBA.InstalledFile ifile,

  	MW_DBA."file" zfile,

  	MW_DBA.Directory dir

WHERE

  u.Name='SJOHN164_99_139_79.Novell_AUS' AND

  iso.computerid=u.id$ AND

  iso.productid=m.productid AND

  iso.pinstanceid=ifile.pinstanceid AND

  	ifile.directoryid=dir.id AND

  	ifile.fileid=zfile.id;

Retrieve all the files present on the inventoried server 'SJOHN164_99_139_79.NOVELL_AUS' which has not been associated with a valid software. The query is as follows:

SELECT

  	u.Name,

  	zfile.name,

  	dir.path,

  	zfile.fileversion,

  	zfile."size",

	  zfile.lastmodified,

  	zfile.internalname,

  	zfile.productversion,

  	zfile.company,

  	zfile.productname

FROM

  CIM.UnitaryComputerSystem u,

  MW_DBA.InstalledFile ifile,

  	MW_DBA."file" zfile,

  	MW_DBA.Directory dir

WHERE

  u.Name='SJOHN164_99_139_79.Novell_AUS' AND

  			u.id$=ifile.computerid AND

  	ifile.fileid=zfile.id AND

  	ifile.directoryid=dir.id AND

  	ifile.pinstanceid is null;

Retrieve the disk usage details of files with known extensions on each inventories machine in the enterprise. The query is as follows:

SELECT

  				u.Name,

  				du.Name,

  				du.TotalDiskUsage

FROM

  				CIM.UnitaryComputerSystem u,

  				MW_DBA.DiskUsage du

WHERE

  				u.id$=du. Computerid AND

  				du.Name is not null;