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

Comments

  • Hello,
    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.
Please log in to comment

Answers

1
Example:
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'

Answered 10/10/2016 by: svmay
Sixth Degree Black Belt

  • This rule is in the queue for the childticket.
    > runs on ticket save
Please log in to comment
Answer this question or Comment on this question for clarity

Share