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
Answers (0)
Please log in to answer
Be the first to answer this question
Unfortunately, you'll likely have to do some massaging to get the data into the format you want. - grayematter 8 years ago