/build/static/layout/Breadcrumb_cap_w.png

Add comment to parent when child is closed

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?  

0 Comments   [ + ] Show comments

Answers (2)

Answer Summary:
Posted by: chucksteel 8 years ago
Red Belt
1

Top Answer

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.


Comments:
  • Would this go in the Update SQL: section, or the select SQL section when creating the ticket rule? - brianfulcher15 8 years ago
    • The select section. - chucksteel 8 years ago
      • 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. - brianfulcher15 8 years ago
    • I see that it goes in the select SQL section Thanks for you help, tested and works great! - brianfulcher15 8 years ago
Posted by: chucksteel 8 years ago
Red Belt
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.


Comments:
  • for my other option, change is denied, would i just replace the 'change approved' with 'change denied'? - brianfulcher15 8 years ago
    • Yes, the easiest thing is to have two rules for the different status possibilities. - chucksteel 8 years ago
      • 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. - brianfulcher15 8 years ago
      • 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. - chucksteel 8 years ago
      • 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. - brianfulcher15 8 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ