/build/static/layout/Breadcrumb_cap_w.png
10/10/2016 3236 views
Hello,

at the service desk, we have a custom rule that close parenttickets, when the childticket is closed. How can I add an comment to the parent ticket that its closed?

Here is the rule we use:

sql-select:
SELECT
PARENT.CUSTOM_FIELD_VALUE0,
PARENT.OWNER_ID,
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
PARENT.HD_STATUS_ID = HD_TICKET.HD_STATUS_ID,
PARENT.TIME_CLOSED = IF(HD_TICKET.HD_STATUS_ID = '107', NOW(), HD_TICKET.TIME_CLOSED)
WHERE
((PARENT.ID = HD_TICKET.PARENT_ID) AND HD_TICKET.ID = )

Hope for help! Thanks!



Answer Summary:
2 Comments   [ + ] Show comments

Comments

  • Use anyone a similar rule? Perhaps any improvement / suggestions for this case? Thank you!
  • Rule 3 set is much more elaborate and it completes the process. SQL update also works fine. For more information visit https://www.delltechsupportnumbers.com/blog/fix-dell-error-code-0xc0000001/
    • I'm glad to hear that this rules works in your Kace.

Answer Chosen by the Author

1
Solution:
I have 3 rules for that case;   rule 1: Close the parent ticket when child ticket is closed and comment child ticket
                                              rule 2: Add comment to the parent ticket
                                              rule 3: Send mail to the submitter that the ticket is closed

rule 1,
sql-select:
SELECT      HD_TICKET.ID,
            HD_TICKET.TITLE,
           HD.SUMMARY,
           HD.TICKET.OWNER_ID,
            HD_TICKET.SUBMITTER_ID,
            HD_TICKET.HD_STATUS_ID,
            CHILDSTATUS.STATE,
   
            PARENT.TITLE,
            PARENT.SUMMARY,
            PARENT.OWNER_ID,
            PARENT.HD_STATUS_ID,
            PARENTSTATUS.STATE

FROM        HD_TICKET

    LEFT JOIN    HD_STATUS AS CHILDSTATUS
            ON    CHILDSTATUS.ID = HD_TICKET.HD_STATUS_ID

    LEFT JOIN    HD_TICKET AS PARENT
            ON    PARENT.ID = HD_TICKET.PARENT_ID                 
    LEFT JOIN    HD_STATUS AS PARENTSTATUS
            ON    PARENTSTATUS.ID = PARENT.HD_STATUS_ID

WHERE           HD_TICKET.IS_PARENT = 0    AND
                PARENT.HD_QUEUE_ID = 13    AND
                CHILDSTATUS.STATE = 'closed'

[x] Add comment to ticket

Your comment text.
sql-update:
UPDATE	HD_TICKET PARENT, HD_TICKET, HD_STATUS

SET     PARENT.HD_STATUS_ID = HD_STATUS.ID,
PARENT.RESOLUTION = 'Closed',
        PARENT.TIME_OPENED = IF(HD_STATUS.STATE = 'opened', NOW(), PARENT.TIME_OPENED),
PARENT.TIME_CLOSED = IF(HD_STATUS.STATE = 'closed', NOW(), PARENT.TIME_CLOSED),
PARENT.TIME_STALLED = IF(HD_STATUS.STATE = 'stalled', NOW(), PARENT.TIME_STALLED),
        PARENT.SATISFACTION_RATING = IF(HD_STATUS.STATE = 'closed', NULL, PARENT.SATISFACTION_RATING),
PARENT.SATISFACTION_COMMENT = IF(HD_STATUS.STATE = 'closed', NULL, PARENT.SATISFACTION_COMMENT)

WHERE     HD_STATUS.NAME = 'Closed' AND
    PARENT.HD_QUEUE_ID = HD_STATUS.HD_QUEUE_ID AND
    PARENT.ID = HD_TICKET.PARENT_ID AND
    HD_TICKET.ID = (<TICKET_IDS>)


rule 2,

sql-select:

SELECT      HD_TICKET.ID,
            HD_TICKET.TITLE,
           HD.SUMMARY,
           HD.TICKET.OWNER_ID,
            HD_TICKET.SUBMITTER_ID,
            HD_TICKET.HD_STATUS_ID,
            CHILDSTATUS.STATE,
   
            PARENT.ID,
            PARENT.TITLE,
            PARENT.SUMMARY,
            PARENT.OWNER_ID,
            PARENT.HD_STATUS_ID,
            PARENTSTATUS.STATE

FROM        HD_TICKET

    LEFT JOIN    HD_STATUS AS CHILDSTATUS
            ON    CHILDSTATUS.ID = HD_TICKET.HD_STATUS_ID

    LEFT JOIN    HD_TICKET AS PARENT
            ON    PARENT.ID = HD_TICKET.PARENT_ID                 
    LEFT JOIN    HD_STATUS AS PARENTSTATUS
            ON    PARENTSTATUS.ID = PARENT.HD_STATUS_ID

WHERE           HD_TICKET.IS_PARENT = 0    AND
                PARENT.HD_QUEUE_ID = 13    AND
                CHILDSTATUS.STATE = 'closed'

[x] Add comment to ticket

Your comment text.

rule 3,

sql-select:

SELECT      HD_TICKET.ID,
            HD_TICKET.TITLE,
           HD.SUMMARY,
           HD.TICKET.OWNER_ID,
            HD_TICKET.SUBMITTER_ID,
            SUBMITTER.FULL_NAME as SUBMITTER_NAME,
            SUBMITTER.EMAIL as SUBMITTER_EMAIL,
            HD_TICKET.HD_STATUS_ID,
            CHILDSTATUS.STATE,
   
            PARENT.ID,
            PARENT.TITLE,
            PARENT.SUMMARY,
            PARENT.OWNER_ID,
            PARENT.HD_STATUS_ID,
            PARENTSTATUS.STATE

FROM        HD_TICKET

    LEFT JOIN    HD_STATUS AS CHILDSTATUS
            ON    CHILDSTATUS.ID = HD_TICKET.HD_STATUS_ID
    LEFT JOIN    USER AS SUBMITTER
            ON    SUBMITTER.ID = HD_TICKET.SUBMITTER_ID

    LEFT JOIN    HD_TICKET AS PARENT
            ON    PARENT.ID = HD_TICKET.PARENT_ID                 
    LEFT JOIN    HD_STATUS AS PARENTSTATUS
            ON    PARENTSTATUS.ID = PARENT.HD_STATUS_ID

WHERE           HD_TICKET.IS_PARENT = 0    AND
                PARENT.HD_QUEUE_ID = 13    AND
                CHILDSTATUS.STATE = 'closed'

[x] send an email to..

subject:                                                                  column with mail adress:

[TICKET-ID:$id] Ticket closed.SUBMITTER_EMAIL                             

mail:
Your email text.





 

Answered 10/12/2016 by: svmay
Red Belt

All Answers

2
You might be able to create a separate rule that detects when a ticket is closed by this rule and use the add a comment to a ticket functionality. Look in the database to see what the change looks like when the rule closes the ticket and craft another rule that finds that.
Answered 10/10/2016 by: chucksteel
Red Belt

  • I haven't thought of an easy solution .. I had only seen the difficult way.
    So I will test your way - Thanks for your answer and for your help chuck!
2
I have created a new custom rule (run in the queue for childtickets). My sql-select and the comment function works.
But my sql-update not:

sql-update:
UPDATE
HD_TICKET AS CHILD, HD_STATUS

JOIN
HD_TICKET AS PARENT ON PARENT.ID = CHILD.PARENT_ID

SET
PARENT.HD_STATUS_ID = HD_STATUS.ID,
PARENT.RESOLUTION = 'Closed',
PARENT.TIME_OPENED = IF(HD_STATUS.STATE = 'opened', NOW(), PARENT.TIME_OPENED),
PARENT.TIME_CLOSED = IF(HD_STATUS.STATE = 'closed', NOW(), PARENT.TIME_CLOSED),
PARENT.TIME_STALLED = IF(HD_STATUS.STATE = 'stalled', NOW(), PARENT.TIME_STALLED),
PARENT.SATISFACTION_RATING = IF(HD_STATUS.STATE = 'closed', NULL, PARENT.SATISFACTION_RATING),
PARENT.SATISFACTION_COMMENT = IF(HD_STATUS.STATE = 'closed', NULL, PARENT.SATISFACTION_COMMENT)

WHERE
     HD_STATUS.NAME = 'Closed'
AND PARENT.HD_QUEUE_ID = HD_STATUS.HD_QUEUE_ID
AND CHILD.ID =(<TICKET_IDS>)


I recieve the following error message:


8Jv1S4.png


Hope for help. Thanks!


Answered 10/11/2016 by: svmay
Red Belt

  • What's the error?
  • Sorry - I have edit the answer
  • Try removing the AS when you alias the HD_TICKET table as CHILD, so just UPDATE HD_TICKET CHILD, HD_STATUS. You also don't need it in the join statement.
  • I get also an error message:
    mysqli error: [1054: Unknown column 'PARENT.HD_STATUS_ID' in 'field list']
  • I edit the update - so I don't get an error message but the update says "selected 1 rows ... 0 updated rows"
  • I write in the update query the right ticketid instead of <TICKET_IDS> and then the update works.. Any idea why the update doesn't work with <TICKET_IDS>?
    • Can you post your select query? Whatever is returned in the ID column will be placed in the <TICKET_IDS> variable.
      • I had select two ids child and parent id. I delete the parent id in the select, because i need this id only in the workbench. And with this, the rule works. Answer with the 3 rules I created is on the top.
        Thanks chuck! :)