KACE Product Support

I can't guarantee that this is a working answer as I don't have a test system to play with, but hopefully this will get you close enough to have it working if you play with it.  The first Select query is to show where the comments are located in relation to the tickets.  The second query is an informational Select query (what I would call a "research" or "work in progress query") which lists all of the child and associated parent tickets with comments.  The last Select query is what I would suggest trying in an actual ticket rule.

The WHERE statements (aside from the implied JOINs) include a few filters:

1) AND PCHANGE.USER_ID != 0 >>> this says "only show parent ticket comments added by users" (i.e. don't show comments added by the system, USER_ID = 0)

2) AND PCHANGE.COMMENT != '' >>> this says "don't show empty/blank comments"

3) AND HD_TICKET.HD_QUEUE_ID = 1 >>> this says only target (child) tickets in the first queue (something you might want or not, but including it just in case)

The Update query is what I'm unable to test and can't guarantee will work, as it needs to target the HD_TICKET_CHANGE table which the Update query may not like.  This is just an "on ticket save" proof of concept query, so tread with caution here.  Of course, if you have a test system (or have time to recover from backups), play away!   ^_^

Hope that at least helps you get started!

John

_____________________________________

Initial Select Query:
SELECT HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET_CHANGE.COMMENT
FROM HD_TICKET
JOIN HD_TICKET_CHANGE ON (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
WHERE HD_TICKET_CHANGE.USER_ID != 0
AND HD_TICKET_CHANGE.COMMENT != ''
AND HD_TICKET.HD_QUEUE_ID = 1
ORDER BY HD_TICKET.ID

Informational Select Query:
SELECT HD_TICKET.ID, PARENT.ID,
HD_TICKET.TITLE, PARENT.TITLE AS PTITLE,
HD_TICKET_CHANGE.COMMENT, PCHANGE.COMMENT AS PCOMMENT
FROM HD_TICKET PARENT, HD_TICKET, HD_TICKET_CHANGE PCHANGE, HD_TICKET_CHANGE
WHERE HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
AND PCHANGE.HD_TICKET_ID = PARENT.ID
AND PARENT.ID = HD_TICKET.PARENT_ID
AND PCHANGE.USER_ID != 0
AND PCHANGE.COMMENT != ''
AND HD_TICKET.HD_QUEUE_ID = 1
ORDER BY HD_TICKET.ID

Select Query:
SELECT HD_TICKET.ID, HD_TICKET_CHANGE.COMMENT, PCHANGE.COMMENT AS PCOMMENT
FROM HD_TICKET PARENT, HD_TICKET, HD_TICKET_CHANGE PCHANGE, HD_TICKET_CHANGE
WHERE HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
AND PCHANGE.HD_TICKET_ID = PARENT.ID
AND PARENT.ID = HD_TICKET.PARENT_ID
AND PCHANGE.USER_ID != 0
AND PCHANGE.COMMENT != ''
AND HD_TICKET.HD_QUEUE_ID = 1
ORDER BY HD_TICKET.ID

Update Query:
UPDATE HD_TICKET PARENT, HD_TICKET, HD_TICKET_CHANGE PCHANGE, HD_TICKET_CHANGE
SET HD_TICKET_CHANGE.COMMENT = PCHANGE.COMMENT
WHERE HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
AND PCHANGE.HD_TICKET_ID = PARENT.ID
AND PARENT.ID = HD_TICKET.PARENT_ID
AND HD_TICKET.ID = <TICKET_IDS>