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

 
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

Change the LEFT JOINs to just JOIN.

Answered 07/24/2013 by: dugullett
Red Belt

  • 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
  • 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?
  • 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.
    • 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%')
Please log in to comment
Answer this question or Comment on this question for clarity