/build/static/layout/Breadcrumb_cap_w.png

kace custom report that joins 2 tables that have one-to-many relationship: Results in concatenated values from many rows into one column

The SQL:
SELECT
    MACHINE.NAME AS VirtualHost,
    MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE AS VirtualMachine
FROM
    MACHINE
    JOIN MACHINE_CUSTOM_INVENTORY
      ON
        MACHINE_CUSTOM_INVENTORY.ID = MACHINE.ID AND
        MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 26195
WHERE
    MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE IS NOT NULL AND
    NOT EXISTS
        ( SELECT
              1
          FROM
              MACHINE_CUSTOM_INVENTORY
          WHERE
            MACHINE.ID = MACHINE_CUSTOM_INVENTORY.ID AND
            MACHINE_CUSTOM_INVENTORY.SOFTWARE_ID = 26195 AND
            MACHINE_CUSTOM_INVENTORY.STR_FIELD_VALUE LIKE '%The system cannot find the file specified%' )
ORDER  BY
    MACHINE.NAME 
-- The results:
VirtualHost    VirtualMachine
VHost01    VM01, VM02, VM03

I want to see:
VirtualHost    VirtualMachine
VHost01    VM01
VHost01    VM02
VHost01    VM03
 

2 Comments   [ + ] Show comments
  • How is the data stored in the MACHINE_CUSTOM_INVENTORY table? Take a look with a query tool like MySQL Workbench. Does the machine have a single record with multiple concatenated values in the STR_FIELD_VALUE column? This is likely the case if you are using a single custom inventory rule to gather the data.

    Unfortunately, you'll likely have to do some massaging to get the data into the format you want. - grayematter 8 years ago
  • You were right - the string contains a comma delimited list. That is not what I expected. thanks. - DavidYoder 8 years ago

Answers (0)

Be the first to answer this question

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