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:
Please log in to answer
Posted by:
chucksteel
8 years ago
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
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
