The following section describes the database schema classes and the extensions and associations made to the CIM schema for use in ZENworks 7 Desktop 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 help you understand the ZENworks 7 Desktop Management database schema:
The following scenario describes an inventoried workstation 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 device.
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 workstation. In the LastLoginUser association, the specific instance of User is the one who logged last into the inventoried workstation.
Class CIM.IRQ associates to CIM.ParallelPort using the association CIM.AllocatedResource. Dependent pointing to CIM.ParallelPort and Antecedent pointing to CIM.IRQ.
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 7 Desktop Management.
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 originates 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 of the CIM schema, see the CIM 2.2 schema specification on the DMTF Web site.
The following schema diagrams of the CIM and extension schema model the Inventory database in ZENworks 7 Desktop Management.
A custom inventory attribute has the following qualifiers in the database:
Table 75-1 Qualifiers of Custom Inventory attributes
Qualifier |
Description |
---|---|
Name |
The name of the custom attribute |
Value |
The value assigned to the custom attribute |
Type |
The data type of the custom attribute. ZENworks 7 Desktop Management treats all custom attributes as type “String” only. |
ClassName |
The Inventory Class (table) to which the custom attribute is associated to. |
Instance |
The number of values assigned to a given custom attribute. In ZENworks 7 Desktop Management, you cannot have more than one value for a custom attribute. |
ID |
The id (id$) of the Inventory object to which the custom attribute is associated. |
CID |
The unique ID of the custom attribute name. |
The custom inventory information is stored in two tables: ZENworks.CustomName and Zenworks.CustomValue.
The ZENworks.CustomName table contains the attribute name, ID, type (not used) and the class name. The attribute ID is automatically incremented when a new custom attribute is stored.
A sample ZENworks.CustomName table is as follows:
ID |
Name |
Class Name |
---|---|---|
1 |
Cost |
ZENworks.VideoAdapter |
2 |
ProductUnit |
CIM.UnitaryComputerSystem |
The Zenworks.CustomValue table contains the object instance ID, the attribute value, and the reference to the associated custom attribute name. In the CustomValue table, a custom attribute can have multiple values for different instances of an inventory object but not for the same instance of the inventory object.
A sample Zenworks.CustomValue table is as follows:
ID |
Instance |
CID |
Value |
---|---|---|---|
28147497671065605 |
1 |
1 |
200.39 |
28147497671065606 |
1 |
1 |
345 |
69147497671045662 |
1 |
2 |
BAY-2 |
In the preceding example, the CustomName table has two custom attributes; Cost and ProductUnit. There are three rows in CustomValue table; two of them describe the cost of the video adapter, and the third one is the value of the ProductUnit attribute assigned directly to ComputerSystem.
The following software schema diagrams model the Inventory database in ZENworks 7 Desktop Management.In the following schema diagram, the CIM.UnitaryComputerSystem represents a managed inventory system.
For more information about the tables, see Section O.0, ZENworks 7 Desktop 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.
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.
The following are sample queries for retrieving the inventory information from the ZENworks 7 Desktop Management Inventory database.
Refer to the schema diagrams in Schema Diagrams of CIM and the Extension Schema in ZENworks 7 Desktop Management to find out the associated schema classes and attribute information.
Retrieve the name and ID of all inventoried workstations from the database and also to the Novell eDirectory™ tree to which these workstations 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 workstations 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 workstation ’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 workstation ’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 workstation ’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 workstations 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 workstation 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 workstation in place of the ?, value for ucs.id$ in the query.
List the IP address, IPX address, and MAC address of all workstations 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 workstation. 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 components such as Windows Media Player, Outlook Express, Microsoft Word, and Microsoft Excel. The complete set of these tables is 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 workstation ’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 workstation ’SJOHN164_99_139_79.NOVELL_AUS’ that 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 inventoried 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;