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$;

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;