KACE Product Support

OK, got this working as requested, should work for all statuses and only require one rule.  This will get the statuses from the first ticket (SOURCE) and the referring ticket, then set the referring ticket's status (name) to match the source ticket's status.  Setup the ticket rule to run on ticket save and it should do what you need.  You can pop the Select query in a report or the MySQL Query Browser to see how the tickets correspond.

John
__________________________________________________

*Title*
Synch Ticket Status With Reference Ticket Status

*Order*
50

*Frequency*
on Ticket Save

*Enabled*
<checked>

*Select Query*
SELECT DISTINCT SOURCE.ID as SID, SOURCE.TITLE as STITLE,
SOURCE.NAME as SNAME, SOURCE.HD_STATUS_ID,
HD_TICKET.ID, HD_TICKET.TITLE, S.NAME, HD_TICKET.HD_STATUS_ID
FROM HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.TITLE, S.NAME,
  HD_TICKET.HD_STATUS_ID
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
SOURCE on (SOURCE.ID = R.TO_ID)
WHERE HD_TICKET.HD_STATUS_ID != SOURCE.HD_STATUS_ID
ORDER BY HD_TICKET.ID

*Update Query*
UPDATE HD_TICKET
JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)
JOIN
 (SELECT HD_TICKET.ID, HD_TICKET.TITLE, HD_TICKET.HD_STATUS_ID, S.NAME
  FROM HD_TICKET
  JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
  JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
SOURCE on (SOURCE.ID = R.TO_ID)
SET HD_TICKET.HD_STATUS_ID = SOURCE.HD_STATUS_ID
WHERE HD_TICKET.ID in (<TICKET_IDS>)

Please see this blog for a full explanation of the construction of this ticket rule (and a more efficient version of the SELECT query):

http://www.itninja.com/blog/view/k1000-custom-ticket-rules-using-an-inline-view-to-query-update-tables-in-one-pass

John

I'm close but probably not exactly what you want. I can create a rule that will close tickets referred to but not other statuses. I'll explain as we go. First you'll need to create a generic custom rule using the wizard, you can specify whatever criteria you want, we'll just change it later. Once you have the rule, replace the select query with the following:

 select FROM_ID as ID
FROM HD_TICKET_RELATED, HD_TICKET
JOIN HD_STATUS on HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
WHERE TO_ID = (select HD_TICKET_ID from HD_TICKET_CHANGE WHERE ID = <CHANGE_ID>)
and HD_STATUS.NAME = "Closed"

 update HD_TICKET, HD_STATUS as T5
    set HD_TICKET.HD_STATUS_ID = T5.ID,
HD_TICKET.TIME_OPENED  = IF(T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED), 
HD_TICKET.TIME_CLOSED  = IF(T5.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED), 
HD_TICKET.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED), 
HD_TICKET.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING), 
HD_TICKET.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT)
  where 
T5.NAME = 'Closed' and
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and 
        (HD_TICKET.ID in (<TICKET_IDS>))

If you want the simplest SELECT query possible, use this:

SELECT HD_TICKET.ID
FROM HD_TICKET
JOIN HD_TICKET_RELATED R on (R.FROM_ID = HD_TICKET.ID)

Just be sure to include this line in the UPDATE query (note that I put this in the SELECT query earlier):

WHERE HD_TICKET.HD_STATUS_ID != SOURCE.HD_STATUS_ID

I originally provided a more detailed SELECT query so you could see more info and also use it in a SQL Report (or in the MySQL Query Browser).  Either should work fine, but I'm personally a fan of more info (which is where the extra columns and JOINs are necessary).

John

This could be a complicate rule. I'm also not sure if you're asking about parent/child tickets or just tickets with the See Also relationship defined. I'm pretty sure it's just See Also but you mention child tickets as well. They are setup differently in the database so the SQL will be different.