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

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.
  • You were right - the string contains a comma delimited list. That is not what I expected. thanks.
Please log in to comment

There are no answers at this time

Answers

Answer this question or Comment on this question for clarity