How do you omit output that has no data in the field in a report?
I'm having trouble finding the correct SQL code for a particular report that's used to list of computers that have any version of Adobe Acrobat installed grouped by versions. Issue is there are duplicate records being outputted causing skewed reporting. I'd like it to omit any data that has blank fields in the columns, DISPLAY_VERSION and MACHINE_NAME_GROUPED or to prevent duplicates from being displayed to begin with. The following is what i've been working off of and would appreciate any suggestions. Thanks!
SELECT
DISPLAY_NAME,
DISPLAY_VERSION,
GROUP_CONCAT(DISTINCT MACHINE.NAME
SEPARATOR '
') AS MACHINE_NAME_GROUPED,
VERSION
FROM
SOFTWARE
LEFT JOIN
MACHINE_SOFTWARE_JT ON (MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID)
LEFT JOIN
MACHINE ON (MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID)
WHERE
(NOT SOFTWARE.IS_PATCH)
AND (DISPLAY_NAME like 'Adobe Acrobat%')
GROUP BY SOFTWARE.ID
ORDER BY DISPLAY_NAME
Answers (1)
Change the LEFT JOINs to just JOIN.
Comments:
-
Try something like this.
SELECT DISPLAY_NAME,DISPLAY_VERSION,
GROUP_CONCAT(DISTINCT M.NAME ORDER BY M.NAME SEPARATOR '\n') AS MACHINE_NAME_GROUPED,
VERSION
FROM SOFTWARE S
JOIN MACHINE_SOFTWARE_JT MSJT ON (MSJT.SOFTWARE_ID = S.ID)
JOIN MACHINE M ON (M.ID = MSJT.MACHINE_ID)
WHERE (NOT S.IS_PATCH)
AND (DISPLAY_NAME like 'Adobe Acrobat %')
AND (DISPLAY_NAME NOT RLIKE 'UPDATER|CPSID')
GROUP BY DISPLAY_NAME
ORDER BY DISPLAY_NAME, DISPLAY_VERSION - dugullett 11 years ago -
I just tested it, and looks a lot better but still see a duplicates for all versions 8 and 9 related outputs. noticed that items with the version included in the DISPLAY_NAME field had blank DISPLAY_VERSION and vice versa. example: "Adobe Acrobat 9.5.5 - CPSID_83708" would have a blank field for the DISPLAY_VERSION and another line item for the same software would show "Adobe Acrobat 9 Pro - English, Français, Deutsch" for the DISPLAY_NAME and "9.5.5" for the DISPLAY_VERSION". Thoughts and maybe some more great help? - wchoi2104 11 years ago
-
the recent response was for your initial comment. tried the suggested code, and although it removed all DISPLAY_NAME items that ended with -CPSID_XXXX, it also removed some completely off the list. - wchoi2104 11 years ago
-
On mine if there was anything that had the CPSID it also had another entry for Adobe Pro. That's the only reason I did that. To avoid machines being listed twice.
It really depends on how Kace inventories it. It may be better to add specific versions? I know we have so many machines/versions it's cleaner this way.
Report for 9:
AND (DISPLAY_NAME like 'Adobe Acrobat 9%')
Report for 10:
AND (DISPLAY_NAME like 'Adobe Acrobat X%')
Report for 11:
AND (DISPLAY_NAME like 'Adobe Acrobat XI%') - dugullett 11 years ago
