Dell K1000 - Service Desk - Add comment to a ticket per custom rule
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:
Hope for help! Thanks!
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:
SELECTsql-update:
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
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!
2 Comments
[ + ] Show comments
-
Use anyone a similar rule? Perhaps any improvement / suggestions for this case? Thank you! - svmay 6 years ago
-
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/ - stacymarkel 4 years ago
-
I'm glad to hear that this rules works in your Kace. - svmay 4 years ago
Answers (3)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
6 years ago
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.
Comments:
-
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! - svmay 6 years ago
Posted by:
svmay
6 years ago
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:
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:
Hope for help. Thanks!
Comments:
-
What's the error? - chucksteel 6 years ago
-
Sorry - I have edit the answer - svmay 6 years ago
-
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. - chucksteel 6 years ago
-
I get also an error message:
mysqli error: [1054: Unknown column 'PARENT.HD_STATUS_ID' in 'field list'] - svmay 6 years ago -
I edit the update - so I don't get an error message but the update says "selected 1 rows ... 0 updated rows" - svmay 6 years ago
-
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>? - svmay 6 years ago
-
Can you post your select query? Whatever is returned in the ID column will be placed in the <TICKET_IDS> variable. - chucksteel 6 years ago
-
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! :) - svmay 6 years ago
Posted by:
svmay
6 years ago
Top Answer
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:
sql-update:
mail:
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. |
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. |