When we get multiple similar tickets about one problem, we use the 'see also' to refer to the first ticket of this problem. So we see al the tickets for which we used the 'see also' field to this ticket in the 'Refferers' field.

With a ticket rule we want to change the status of al these similar tickets to the satus of the first, every time the satus of this ticket changes ( on save). I know that is is nearly possible with the parent ticket. But then you can only share a resolution with the child tickets on closing the parent ticket and close the childeren.

I want the status always the same as the parent.

If I start the ticket rule wizard I can't select the 'referrers' or the 'parent ticket' field, so it is not easy to build the needed query for this ticket rule. Can anybody help me with this ticket rule?

Answer Summary:
The following select statement will return the tickets referred to (note that jverbosk uses a different select statement that is more complicated): 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>) Then use this update query (credit to jverbosk): UPDATE HD_TICKET T JOIN HD_STATUS S on (S.ID = T.HD_STATUS_ID) JOIN HD_TICKET_RELATED R on (R.FROM_ID = T.ID) JOIN (SELECT T.ID, T.TITLE, S.NAME FROM HD_TICKET T JOIN HD_STATUS S on (S.ID = T.HD_STATUS_ID) JOIN HD_TICKET_RELATED R on (R.TO_ID = T.ID)) SOURCE on (SOURCE.ID = R.TO_ID) SET S.NAME = SOURCE.NAME WHERE (S.NAME != SOURCE.NAME AND T.ID in (<TICKET_IDS>))
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

2

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>)

Answered 10/05/2012 by: jverbosk
Red Belt

  • This content is currently hidden from public view.
    Reason: Removed by user request
    For more information, visit our FAQ's.
  • The update query works successfully, that's great! Your select statement is giving me problems, however, so I had to use mine (without the "and HD_STATUS.NAME = "Closed"" line.
  • Hold that thought, upon further review the update query is not quite there....

    John
  • Arg... it works when run manually but not on ticket save, reviewing further...

    John
  • OK, it's working now. I forgot the ticket rules aren't a fan of aliasing the table that is getting updated, hence the error with the previous query:

    08:24> Executing Select Query...
    08:24> mysql error: [1054: Unknown column 'HD_TICKET.ID' in 'on clause'] in EXECUTE("SELECT.... etc, etc

    Removing the HD_TICKET alias (i.e. FROM HD_TICKET T) from everything and typing it out everywhere addressed it.

    Chuck - I remembered this from a while back after you said something, but it has been a while since I've done any ticket rules. Was working fine in the query browser and when running the ticket rule manually, but not on ticket save... but I've fixed it and it should work just fine now. Let me know if you get any further errors, but it's working fine here now. ^_^

    John
  • Did one last tweak so the SELECT query will only select tickets where the HD_STATUS_ID column in the referring ticket doesn't match the source (referenced) ticket. I had this in the UPDATE query initially, as I wanted to be able to see *all* of the tickets with this relationship initially.

    To view all tickets that reference, use this SELECT query (in the query browser or a report):

    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)
    ORDER BY HD_TICKET.ID
    • I would like to adjust this query to get the email addresses of the owners of the see-also tickets

      i tried this
      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, OWNER.EMAIL as OWNER_EMAIL
      FROM HD_TICKET
      JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
      LEFT JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID)
      JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
      SOURCE on (SOURCE.ID = R.TO_ID)
      ORDER BY HD_TICKET.ID

      i expected a column 'owner_email' (just like SID) when i view the sql report but i'm kind of stuck here... could you help me?
    • I would like to alter this query to retrieve the owner email addresses of the see-also tickets. I've tried the following

      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, OWNER.EMAIL as OWNER_EMAIL
      FROM HD_TICKET
      JOIN HD_STATUS S on (S.ID = HD_TICKET.HD_STATUS_ID)
      LEFT JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID
      JOIN HD_TICKET_RELATED R on (R.TO_ID = HD_TICKET.ID))
      SOURCE on (SOURCE.ID = R.TO_ID)
      ORDER BY HD_TICKET.ID

      this doesn't work at all. Could you help me, please?
  • Works fine. Thanks John!

    Danny
  • We made an extension to the update query, so the owner of the ticket is also synchronized.

    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, HD_TICKET.OWNER_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)
    SET HD_TICKET.HD_STATUS_ID = SOURCE.HD_STATUS_ID, HD_TICKET.OWNER_ID = SOURCE.OWNER_ID
    WHERE HD_TICKET.ID in (<TICKET_IDS>)

    Danny
  • Nice! Adjustments aren't too bad once you see how things work. ^_^

    Also, see my other answer for a very short SELECT query, if you are looking for the shortest one possible.

    John
Please log in to comment

Answers

1

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

Answered 10/10/2012 by: jverbosk
Red Belt

Please log in to comment
1

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"

 

This selects ticket IDs from the HD_TICKET_RELATED table that have our ticket marked as "See Also". One of the tricks to writing rules is that the select statement needs to return an ID column and the values returned are available in the update query as <TICKET_IDS>.

Now check the box for Run an update query... and enter the following code:

 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>))
 
This combination of select and update will change the status on the referred tickets to Closed. The reason I'm being specific to the Closed status is that I can't get the status from the first ticket. With MySQL you can't query the same table that you're updating. This means that I can't get the value for HD_TICKET.HD_STATUS_ID for the "See Also" ticket while I'm trying to update the referred ticket. So, if you want to truly syncronize the status between tickets for different values you'll need to create seperate rules for each status you have in your queue (unless someone out there is more clever than me with MySQL. 
 
Set this rule to run on ticket save and it should perform as advertised. I also configured mine to add a comment to the tickets which makes it clear that the status was changed because of the other tickets status being changed. 
Answered 10/03/2012 by: chucksteel
Red Belt

Please log in to comment
1

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

Answered 10/08/2012 by: jverbosk
Red Belt

Please log in to comment
0

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.

 

Answered 10/02/2012 by: chucksteel
Red Belt

  • We want IT for 'see also' tickets. Thanks
Please log in to comment
Answer this question or Comment on this question for clarity