ZDM Custom Inventory SQL query on 'installed module name'

  • Document ID:7005709
  • Creation Date:08-Apr-2010
  • Modified Date:30-Apr-2012
    • Micro Focus Products:
      ZENworks Desktop Management

Environment

Novell ZENworks 6.5 Desktop Management Support Pack 2 - ZDM6.5 SP2 Inventory
Novell ZENworks 7 Desktop Management Support Pack 1 - ZDM7 SP1 Inventory
Novell ZENworks 7 Desktop Management on Linux Support Pack 1 - ZDML7 SP1 Inventory

Situation

Needed data that built-in queries do not cover in the ZDM Documentaion (sections 44.1, 75.3, 77.3).
 
Conditions for 'Installed Module Name ' is not found in the ZDM documentation:
 
Display Items:  Logged-on ID, Computer Name, IP address, Installed Modules Name
 
SELECT
u.id$, <computer name>, ip.Address, im.ProductIdentifier
FROM
CIM.UnitaryComputerSystem u,
MW_DBA.InstalledSoftware im,
CIM.IPProtocolEndpoint ip,
WHERE
<conditions>

Resolution

Custom SQL query that provided the desired report:
 
select ip.ucsid, lt.name, ip.Address, im.productidentifier
from MW_DBA.InstalledSoftware im INNER JOIN  CIM.t$IPProtocolEndpoint ip
    ON ip.ucsid=im.computerid
    JOIN MW_DBA.t$LockTable lt
    ON im.computerid = lt.id$
 
Note for above query:

      u.id$ is the workstation id.
      lt.name   is the name of the workstation
      ip.address   is the Ip address
      im.productidentifier is the installed software identifier

Additional Information

Suggested and tested, but not quite what was wanted:
 
select  u.id$, u.name, ip.address, im.productidentifier, im.friendlyname
from 
  CIM.UnitaryComputerSystem u
  INNER JOIN CIM.IPProtocolEndpoint ip
  ON u.id$ = ip.ucsid
  JOIN MW_DBA.InstalledSoftware im
  ON ip.ucsid=im.computerid;
 
Note for above query:

      u.id$ is the workstation id.
      u.name   is the name of the workstation
      ip.address   is the Ip address
      im.productidentifier is the installed software identifier
      im.friendlyname   is the Software name.