/build/static/layout/Breadcrumb_cap_w.png

SQL Query - Dell Update Catalog - Show Only Upgradable Machines

Trying to only show machines that are upgradable and apply a smart label to them.  However, using the wizard, we cannot accomplish this.  The goal was to write some SQL that would query the database for the pertinent information.  Right now we can only search by active and urgent criticality, and we cannot exclude Upgradable == 0 with Upgradable > '0'.

Question: How do I modify the below SQL query to show only items where Upgradable is greater than 0?  Right now it shows even those with 0 results.

Here is the current SQL that was generated by the wizard:

SELECT
  (SELECT GROUP_CONCAT(DISTINCT PL.NAME
                       ORDER BY PL.NAME SEPARATOR ',,,')
   FROM LABEL PL,
        DELL_PKG_LABEL_JT PLJ
   WHERE PL.ID=PLJ.LABEL_ID
     AND PLJ.DELL_PKG_ID=DELL_PKG.ID) AS PATCH_LABELS,
       IF(KBSYS.DELL_RESOURCE.DOWNLOAD_STATUS = 1, 1, 2) AS CACHE_STATUS,
       1 AS IMPACT_SEQ,
       DELL_CATALOG.NAME AS CATALOG_NAME,
       DELL_PKG.CRITICALITY,
       UNIX_TIMESTAMP(RELEASE_DATE) AS DATEPOSTED_SECONDS,
       DELL_PKG.RELEASE_DATE AS DATEPOSTED,
       DELL_PKG.DESCRIPTION,
       DELL_PKG.DESCRIPTION AS DESCR,

  (SELECT COUNT(*)
   FROM DELL_MACHINE_STATUS MMS
   JOIN MACHINE M ON M.ID=MMS.MACHINE_ID
   JOIN DELL_INVENTORY_APPLICATION_DEVICE_JT IAD ON IAD.DEVICE_ID=MMS.DEVICE_ID
   WHERE MMS.CATALOG_ID=DELL_PKG.DELL_CATALOG_ID
     AND MMS.PACKAGE_DID=DELL_PKG.PACKAGE_DID
     AND MMS.APPLICABLE_UPDATE='DOWNGRADE') AS DOWNGRADES,
       DELL_CRITICALITY.LIST_STYLE AS CRITICALITY_STYLE,
       DELL_PKG.NAME,

  (SELECT COUNT(*)
   FROM DELL_MACHINE_STATUS MMS
   JOIN MACHINE M ON M.ID=MMS.MACHINE_ID
   JOIN DELL_INVENTORY_APPLICATION_DEVICE_JT IAD ON IAD.DEVICE_ID=MMS.DEVICE_ID
   WHERE MMS.CATALOG_ID=DELL_PKG.DELL_CATALOG_ID
     AND MMS.PACKAGE_DID=DELL_PKG.PACKAGE_DID
     AND MMS.APPLICABLE_UPDATE='NOT REQUIRED') AS NOT_REQUIREDS,
       DELL_PKG.ID AS BID,
       DELL_PKG.COMPONENT_DESC,
       DELL_PKG.PACKAGE_DID,
       LABEL.ID,
       DELL_PKG.ID AS UID,
       DATE_FORMAT(DELL_PKG.RELEASE_DATE, '%Y') AS RELEASE_DATE,
       DELL_CRITICALITY.SEQ AS CRITICALITY_SEQ,
       DELL_PKG_STATUS.STATUS,
       DELL_PKG.DELL_CATALOG_ID,

  (SELECT COUNT(*)
   FROM DELL_MACHINE_STATUS MMS
   JOIN MACHINE M ON M.ID=MMS.MACHINE_ID
   JOIN DELL_INVENTORY_APPLICATION_DEVICE_JT IAD ON IAD.DEVICE_ID=MMS.DEVICE_ID
   WHERE MMS.CATALOG_ID=DELL_PKG.DELL_CATALOG_ID
     AND MMS.PACKAGE_DID=DELL_PKG.PACKAGE_DID
     AND MMS.APPLICABLE_UPDATE='UPGRADE') AS UPGRADES,
       KBSYS.DELL_PKG.ID AS TOPIC_ID
FROM KBSYS.DELL_PKG
LEFT JOIN KBSYS.DELL_RESOURCE ON KBSYS.DELL_RESOURCE.FILENAME = KBSYS.DELL_PKG.RESOURCE_FILENAME
AND KBSYS.DELL_RESOURCE.PATH = KBSYS.DELL_PKG.RESOURCE_PATH
INNER JOIN KBSYS.DELL_CATALOG ON DELL_CATALOG.ID = DELL_PKG.DELL_CATALOG_ID
INNER JOIN KBSYS.DELL_CRITICALITY ON DELL_CRITICALITY.NAME = DELL_PKG.CRITICALITY
LEFT JOIN DELL_PKG_LABEL_JT ON DELL_PKG_LABEL_JT.DELL_PKG_ID = DELL_PKG.ID
LEFT JOIN LABEL ON LABEL.ID = DELL_PKG_LABEL_JT.LABEL_ID
LEFT JOIN DELL_PKG_STATUS ON DELL_PKG_STATUS.DELL_PKG_ID = KBSYS.DELL_PKG.ID
WHERE ((DELL_PKG_STATUS.STATUS = '0')
       AND (DELL_PKG.CRITICALITY = 'Urgent'))

0 Comments   [ + ] Show comments

Answers (1)

Posted by: JasonEgg 5 years ago
Red Belt
0

Add this as the very last line:

HAVING UPGRADES > 0

The "HAVING" keyword is basically "WHERE" but for grouped fields

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ