Hi,

We are currently using a 2 queue design.  1 queue is for work, the other is for change approvals.  Our process is to create a work ticket, and then if a change approval is required, we create a child ticket that goes into the change request queue.  in the change request queue, the closed states are "change approved", or "change denied".

Currently the only way users know if their change has been approved is to go in periodically and check the work ticket to see if the child ticket has been closed.  I would like to be able to use the SQL to create a ticket rule that automatically adds a comment to the parent ticket that says your "change requested "ticket ID" has been denied, or approved" upon the close of the child ticket.

Has anyone seen any code for this?  
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

1
Yes, this is possible. A select statement like this will return the ID of the parent ticket:
select HD_TICKET.ID AS CHILDTICKETID, 
                        HD_STATUS.NAME AS STATUS_NAME,
                        HD_STATUS.ORDINAL as STATUS_ORDINAL,
                        STATE,
                        case upper(STATE) 
                        when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED) 
                        when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED) 
                        else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
                        
                        Q.NAME as QUEUE_NAME,
PTICK.ID as ID               
                        from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
JOIN HD_TICKET_CHANGE_FIELD ON HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID=<CHANGE_ID>
 and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED='STATUS_NAME'
                        LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
                        LEFT JOIN HD_TICKET PTICK on PTICK.ID = HD_TICKET.PARENT_ID
                        where HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID
                        and HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
                        and HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID
                        and HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID
                        and HD_TICKET_CHANGE_FIELD.AFTER_VALUE = 'change approved'


This will match tickets where the status has changed to 'change approved' when saved. You can use the functionality to append a comment to the ticket. You would need separate rules for 'change approved' and 'change denied'. Using these rules you could also update the status of the parent ticket or a custom field to indicate that the approval status has changed.

Answered 03/23/2016 by: chucksteel
Red Belt

  • Would this go in the Update SQL: section, or the select SQL section when creating the ticket rule?
    • The select section.
      • Chuck,

        Ive been asked to add one last step to this, do you know of a way that i could tell it to update the status on the parent ticket to match the status on the child ticket? Ive looked around but havent found much stuff doing this going in the direction of child updating a parent.
    • I see that it goes in the select SQL section Thanks for you help, tested and works great!
Please log in to comment

Answers

1
This is the SQL code you would place in the update statement to change the status of the parent ticket:
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 = 'change approved' and 
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and 
        (HD_TICKET.ID in (<TICKET_IDS>))
Note that you need to create the status values in the parent queue because they are queue specific.

Answered 03/23/2016 by: chucksteel
Red Belt

  • for my other option, change is denied, would i just replace the 'change approved' with 'change denied'?
    • Yes, the easiest thing is to have two rules for the different status possibilities.
      • thats what im doing, Question for you, is the way this knows to update the parent ticket, because the sql select from the first piece of code is selecting the parent ticket? Just trying to get an understanding of what each thing is doing.
      • Yes, the select statement returns a column named ID which KACE uses as the <TICKET_IDS> variable. Normally you would have the select statement return the ID or IDs of the tickets being changed but in this case the select statement returns the parent ticket's ID instead.
      • Ok that makes sense, I almost have this complete now, Last step i have to do is figure out how to make the child ticket in the pull the summary and title from the parent ticket, and then change the title to say "change request: parent title" No custom fields so i don't think this part will be to hard.
Please log in to comment
Answer this question or Comment on this question for clarity

Share