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)
Please log in to answer
Posted by:
JasonEgg
4 years ago
Add this as the very last line:
HAVING UPGRADES > 0
The "HAVING" keyword is basically "WHERE" but for grouped fields
