Sample Inventory Database Queries

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.

Query 1

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:

[View full width]
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$

Query 2

Retrieve the asset tag, manufacturer, and serial number of all the workstations in the database. The query is as follows:

[View full width]
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$

Query 3

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:

[View full width]
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'

Query 4

Retrieve the processor information for the workstation 'SJOHN164_99_139_79.WS'.

[View full width]
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'

Query 5

Retrieve the ID of the UnitaryComputerSystem used for the workstation 'SJOHN164_99_139_79.WS'.

[View full width]
SELECT s.host FROM managewise.NDSName m,managewise.Designates s WHERE m.label='SJOHN164_99_139_79.WS' and m.id$=s.Designation

Query 6

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:

[View full width]
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.

Query 7

List the IP addresses, IPX addresses, and MAC addresses of all workstations in the database:

[View full width]
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:

[View full width]
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.

Query 8

Retrieve the name and other properties of the drives on the hard disk of the specified workstation.

[View full width]
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.