/build/static/layout/Breadcrumb_cap_w.png

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)

Posted by: chucksteel 5 years ago
Red Belt
0

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 5 years ago
    • Can you verify that there is a carriage return after the <CHANGE_ID> and the word left? - chucksteel 5 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 4 years ago

Don't be a Stranger!

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

Sign up! or login

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