/build/static/layout/Breadcrumb_cap_w.png
10/23/2018 89 views
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

Comments


There are no answers at this time