ZCM Snippets [SpectralCoding Wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== ZCM Snippets ======

===== Blank Site/Location =====

  SELECT
    ZDEVICE.HOSTNAME,
    ZDEVICE.OSNAME,
    ZDEVICE.SITE,
    ZDEVICE.LOCATION,
    ZDEVICE.SERIALNUMBER
  FROM
    ZCM_OWNER.ZDEVICE
  WHERE
    ZDEVICE.SITE IS NULL
    OR
    ZDEVICE.LOCATION IS NULL

===== See all bundles and their associated users =====

  SELECT
    BUNDLETBL.ZUID BUNDLEZUID,
    BUNDLETBL.NAME BUNDLENAME,
    BUNDLETBL.PATH BUNDLEPATH,
    BUNDLETBL.DESCRIPTION BUNDLEDESC,
    ZASSIGN.CONSUMERUID ASSIGNCUID,
    TARGETTBL.NAME TARGETNAME,
    TARGETTBL.PATH TARGETPATH
  FROM
    ZCM_OWNER.ZZENOBJECT BUNDLETBL
    LEFT OUTER JOIN ZCM_OWNER.ZASSIGNMENT ZASSIGN
      ON BUNDLETBL.ZUID = ZASSIGN.CONTENTUID
    LEFT OUTER JOIN ZCM_OWNER.ZZENOBJECT TARGETTBL
      ON ZASSIGN.CONSUMERUID = TARGETTBL.ZUID
  WHERE
    BUNDLETBL.PRIMARYTYPE = 'Bundle'
    AND
    TARGETTBL.NAME IS NOT NULL
    AND
    TARGETTBL.PATH LIKE '/System/References/%'

===== Broken ZCM11 Upgrades - ERRORED =====

  SELECT
    ZCM_OWNER.ZDEVICE.HOSTNAME,
    ZCM_OWNER.ZZENOBJECT.NAME,
    ZCM_OWNER.ZSYSTEMUPDATEDEVICEINFO.UPDATESTATUS,
    ZCM_OWNER.ZSYSTEMUPDATEDEVICEINFO.STATUSMESSAGEKEY,
    ZCM_OWNER.ZSYSTEMUPDATEDEVICEINFO.STATUSMESSAGEPARAMS,
    ZCM_OWNER.ZDEVICE.OSNAME
  FROM
    ZCM_OWNER.ZSYSTEMUPDATEDEVICEINFO
    LEFT OUTER JOIN ZCM_OWNER.ZDEVICE
      ON ZCM_OWNER.ZSYSTEMUPDATEDEVICEINFO.DEVICEUID = ZCM_OWNER.ZDEVICE.ZUID
    LEFT OUTER JOIN ZCM_OWNER.ZZENOBJECT
      ON ZCM_OWNER.ZZENOBJECT.ZUID = ZCM_OWNER.ZDEVICE.PRIMARYUSER
  WHERE
    ZCM_OWNER.ZSYSTEMUPDATEDEVICEINFO.UPDATESTATUS != 'FINISHED'
    AND
    ZCM_OWNER.ZSYSTEMUPDATEDEVICEINFO.UPDATESTATUS != 'ASSIGNED'
    AND
    ZCM_OWNER.ZSYSTEMUPDATEDEVICEINFO.UPDATESTATUS != 'AWAITING_REBOOT'
    AND
    ZCM_OWNER.ZSYSTEMUPDATEDEVICEINFO.UPDATESTATUS != 'INSTALLING'

===== Broken ZCM11 Upgrades - ERRORED/INSTALLING/AWAITING REBOOT =====

  SELECT
    ZCM_OWNER.ZDEVICE.HOSTNAME,
    ZCM_OWNER.ZZENOBJECT.NAME,
    ZCM_OWNER.ZSYSTEMUPDATEDEVICEINFO.UPDATESTATUS,
    ZCM_OWNER.ZSYSTEMUPDATEDEVICEINFO.STATUSMESSAGEKEY,
    ZCM_OWNER.ZSYSTEMUPDATEDEVICEINFO.STATUSMESSAGEPARAMS,
    ZCM_OWNER.ZDEVICE.OSNAME
  FROM
    ZCM_OWNER.ZSYSTEMUPDATEDEVICEINFO
    LEFT OUTER JOIN ZCM_OWNER.ZDEVICE
      ON ZCM_OWNER.ZSYSTEMUPDATEDEVICEINFO.DEVICEUID = ZCM_OWNER.ZDEVICE.ZUID
    LEFT OUTER JOIN ZCM_OWNER.ZZENOBJECT
      ON ZCM_OWNER.ZZENOBJECT.ZUID = ZCM_OWNER.ZDEVICE.PRIMARYUSER
  WHERE
    ZCM_OWNER.ZSYSTEMUPDATEDEVICEINFO.UPDATESTATUS != 'FINISHED'
    AND ZCM_OWNER.ZSYSTEMUPDATEDEVICEINFO.UPDATESTATUS != 'ASSIGNED'