Kace Ticket Rule - Closed Status - New Comment - Reply to Submitter Ticket is Closed
Hi All,
I've been trying to find information around this. Kace does a great job of handling issues with built in functions when you want a ticket to reopen, but we don't. Once a ticket is closed we want it to remain closed and have turned off those rules. The issue is when a user submits a new comment to an old ticket, it just sits there without informing the user they need to start a new ticket.
I am trying to create a ticket rule that will respond to the comment submitter that the ticket is closed and to create a new one if the ticket is in a closed state. Any guidance is appreciated.
0 Comments
[ + ] Show comments
Answers (1)
Please log in to answer
Posted by:
chucksteel
4 years ago
This SQL query should fit your needs.
select HD_TICKET.ID,
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
U2.USER_NAME as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
U3.FULL_NAME as UPDATERNAME,
U4.FULL_NAME as INITIALNAME,
UNIX_TIMESTAMP(THISCHANGE.TIMESTAMP),
CHANGE.COMMENT,
CHANGE.DESCRIPTION as CHANGE_DESCRIPTION,
INITIAL_CHANGE.COMMENT as INITIAL_COMMENT,
HD_CATEGORY.CC_LIST AS NEWTICKETEMAIL,
HD_CATEGORY.NAME AS CATEGORY_NAME,
U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,
HD_PRIORITY.NAME AS TICKET_PRIORITY,
HD_QUEUE.NAME AS QUEUE_NAME
from ( HD_TICKET,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY)
JOIN HD_TICKET_CHANGE CHANGE ON CHANGE.HD_TICKET_ID = HD_TICKET.ID
and CHANGE.ID=<CHANGE_ID>
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 = THISCHANGE.USER_ID
left join USER U4 on U4.ID = INITIAL_CHANGE.USER_ID
left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID
left JOIN ASSET SUBMITTER_LOCATION on SUBMITTER_LOCATION.ID = U2.LOCATION_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.SUBMITTER_ID = CHANGE.USER_ID and -- Submitter made the change
HD_STATUS.STATE = 'closed' -- the ticket is closed
Comments:
-
Hello, this is currently erroring.
Eh I may just have no idea what I am doing. Reverted some of the tweaks back to the code you initially sent.
Running the above code returns an error:
mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHANGE ON CHANGE.HD_TICKET_ID = HD_TICKET.ID and CHANGE.ID=645859 left join US' at line 33] - joshSC 4 years ago-
Can you verify that there is a carriage return after the <CHANGE_ID> and the word left? - chucksteel 4 years ago
-
You ever get this to work? I'm needing the exact same thing.
I get an error with that code on lookup - kkattfish 3 years ago
