/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Dell K1000 - Service Desk - Add comment to a ticket per custom rule

10/10/2016 3889 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! :)

Don't be a Stranger!

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

Sign up! or login

View more:

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