Copy parent info to child ticket
Im trying to setup process and i need a ticket rule that will copy parent ticket data to a child ticket in the process
The parent ticket is one queue, and the child ticket is in a second queue.
Currently I have a select built, it selects everything in second queue.
How would i build the update query. I need it to copy the summary and the title from the parent ticket, and i also need it to add "change request:" next to the ticket title.
I want this to also only update the 1 ticket on save, i do not want this to update all of the already existing tickets, how do i make sure this only runs against the one ticket and not every ticket in the queue, It would need to run against anything created after today. and only if its created by using the process.
below is the select i'm using.
select HD_TICKET.*,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
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,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and HD_TICKET.HD_QUEUE_ID = 13
1 Comment
[ + ] Show comment
Answers (2)
Please log in to answer
Posted by:
svmay
8 years ago
Example:
This custom rule set the asset from the childticket to the 'asset' what is chose in customfield 1 (parentticket).
sql-select:
sql-update:
Note: For CUSTOM_1 (CUSTOM_FIELD_VALUE0) I have the following settings:
>Layout Ticket Fields:
Name: CUSTOM_1
Label: 'your label name'
Requierd: Not Requierd
Permissions: User Create
>Custom Fields:
Name: CUSTOM_1
Select Values: query:SELECT FROM ORG1.ASSET WHERE ASSET_TYPE = 'your asset type id'
This custom rule set the asset from the childticket to the 'asset' what is chose in customfield 1 (parentticket).
sql-select:
SELECT
PARENT.CUSTOM_FIELD_VALUE0,
HD_TICKET.TITLE,
HD_TICKET.ID
FROM
HD_TICKET PARENT, HD_TICKET
WHERE
PARENT.ID = HD_TICKET.PARENT_ID
sql-update:
UPDATE HD_TICKET PARENT, HD_TICKET
SET
HD_TICKET.ASSET_ID = (SELECT ID FROM ORG1.ASSET WHERE ASSET.NAME = PARENT.CUSTOM_FIELD_VALUE0)
WHERE
((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = <TICKET_IDS>)
Note: For CUSTOM_1 (CUSTOM_FIELD_VALUE0) I have the following settings:
>Layout Ticket Fields:
Name: CUSTOM_1
Label: 'your label name'
Requierd: Not Requierd
Permissions: User Create
>Custom Fields:
Name: CUSTOM_1
Select Values: query:SELECT FROM ORG1.ASSET WHERE ASSET_TYPE = 'your asset type id'
Comments:
-
This rule is in the queue for the childticket.
> runs on ticket save - svmay 8 years ago
Posted by:
jshirley@calranch.com
1 year ago
I know this is a long shot, but any chance you can assist with how to apply this rule to my own environment ? What variables do i single out and replace with my own data?
I have a similiar configuration - for the parenttickets I have a queue (q_parent_roomtransfer) and for the childtickets I have a queue (q_child_hardwaretransfer). So that the rule not work on all tickets, create a new status and set the status from the ticket to it, when your rule was executed.
In my case, I set the associated asset from the parent ticket as the title from the child ticket.
Here is the link for the sql-update
http://www.itninja.com/question/dell-k1000-service-desk-parent-child-ticket
Hope it can help you, if not write me an comment below. - svmay 8 years ago