ZENworks for Desktops comes with several views to retrieve information from the inventory database. However, those views might not always give you the information you need exactly how you want it. If you are familiar with the Common Information Model (CIM), Desktop Management Interface (DMI), and have a solid understanding of Relational Database Based Managed Systems (RDBMS), you can access the ZENworks inventory database directly by creating your own queries and views.
Accessing the inventory database can be a powerful tool for creating metrics to measure company growth, user and workstation status, product usage, and so on. The tables in this appendix define the schema and associations in the ZENworks inventory database. Also included in this appendix are sample queries that give you a jump start at accessing the database.
The first concept you must understand when accessing the ZENworks inventory database is what is available and how it is available. The following tables help you understand the schema structure for inventory classes, class associations, association attributes, and class attributes for the following types of inventory objects:
• Miscellaneous Computer System objects
• ZENworks Site objects
• Client Software objects
• Network objects
• Disk objects
The tables in the previous section give you a glimpse at the schema of the ZENworks inventory database. The following sections list some sample queries based on those tables to help you better understand how to use them to perform queries and create your own reports. These sample queries retrieve the inventory information from the ZENworks for Desktops Inventory database.
Retrieve the name and ID of all workstations from the database and also to the tree to which these workstations are registered. The query is as follows:
SELECT u.id$,m.label,m.tree FROM managewise.NDSName m,cim.UnitaryComputerSystem u, managewise.Designates s where s.Designation=m.id$ and s.HOST=u.id$
Retrieve the asset tag, manufacturer, and serial number of all the workstations in the database. The query is as follows:
SELECT m.Tag,m.Manufacturer,m.SerialNumber FROM cim.UnitaryComputerSystem u, zenworks.SystemInfo m,cim.ComputerSystemPackage s WHERE s.Antecedent=m.id$ and s.Dependent=u.id$
Retrieve all the software applications with their versions that are installed on the workstation 'SJOHN164_99_139_79.WS' registered under the 'NOVELL_AUS' tree. The query is as follows:
SELECT m.name,m.version FROM cim.Product m,cim.UnitaryComputerSystem u, zenworks.InstalledProduct s,managewise.NDSName m1,managewise.Designates s1 WHERE (s.Product=m.id$ and s.ComputerSystem=u.id$) AND (s1.Designation=m1.id$ and s1.Host=u.id$) AND m1.label='SJOHN164_99_139_79.WS' and m1.tree='Novell_AUS'
Retrieve the processor information for the workstation 'SJOHN164_99_139_79.WS'.
SELECT m.DeviceID,m.Family,m.Stepping,m.OtherFamilyDescription, m.MaxClockSpeed,m.Role,m.UpgradeMethod FROM cim.Processor m,cim.UnitaryComputerSystem u,cim.ComputerSystemProcessor s managewise.NDSName m1,managewise.Designates s1 WHERE (s.PartComponent=m.id$ and s.GroupComponent=u.id$) AND m1.label='SJOHN164_99_139_79.WS'
Retrieve the ID of the UnitaryComputerSystem used for the workstation 'SJOHN164_99_139_79.WS'.
SELECT s.host FROM managewise.NDSName m,managewise.Designates s WHERE m.label='SJOHN164_99_139_79.WS' and m.id$=s.Designation
Having known the ID of the UnitaryComputerSystem for a particular workstation from the query as shown in Query 5, Query 4 can be modified as follows:
SELECT m.DeviceID,m.Family,m.Stepping,m.OtherFamilyDescription, m.MaxClockSpeed,m.Role,m.UpgradeMethod FROM cim.Processor m,cim.UnitaryComputerSystem u, cim.ComputerSystemProcessor s u.id$=? And s.PartComponent=m.id$ and s.GroupComponent=u.id$
Substitute the ID of the specified workstation in place of the ? value for u.id in the query.
List the IP addresses, IPX addresses, and MAC addresses of all workstations in the database:
SELECT ip.Address, ipx.Address, mac.MACAddress FROM cim.IPProtocolEndpoint ip, cim.IPXProtocolEndpoint ipx, cim.LANEndpoint mac, cim.UnitaryComputerSystem u, cim.HostedAccessPoint s WHERE (s.Dependent=ip.id$ and s.Antecedent=u.id$) AND (s.Dependent=ipx.id$ and s.Antecedent=u.id$) AND (s.Dependent=mac.id$ and s.Antecedent=u.id$)
Modify the same query to get the information for a specified workstation as follows:
SELECT ip.Address, ipx.Address, mac.MACAddress FROM cim.IPProtocolEndpoint ip, cim.IPXProtocolEndpoint ipx, cim.LANEndpoint mac, cim.UnitaryComputerSystem u, cim.HostedAccessPoint s WHERE (s.Dependent=ip.id$ and s.Antecedent=u.id$) AND (s.Dependent=ipx.id$ and s.Antecedent=u.id$) AND (s.Dependent=mac.id$ and s.Antecedent=u.id$)AND u.id$=?
Use the query as shown in Query 5 to retrieve the ID of the specified workstation and substitute the ID in place of the ? value for u.id in the query.
Retrieve the name and other properties of the drives on the hard disk of the specified workstation.
SELECT m.DEVICEID, m.TotalSize, m.VolumeSerialNumber, m.Caption FROM zenworks.LogicalDiskDrive m, cim.UnitaryComputerSystem u, cim.SystemDevice s WHERE s.PartComponent=m.id$ AND s.GroupComponent=u.id$ and u.id$=?
Use the query shown in Query 5 to retrieve the ID of the specified workstation and substitute the ID in place of the ? for u.id$ in the query.