K1000 - Update Query from 'New' to 'Open'
I am making a rule to send out a confirmation email for newly created tickets to the submitter. I have a selection query made that selects records on the condition they have a new status.
I want the update query to change the status from new to open after the email is sent but I cant seem to get the automatically generated one (from the k1000) to work.
Does anyone have a SQL update query that does this? Or can someone explain the tables needed to change the status correctly and I can write it myself?
Here is the code the k1000 generated:
update HD_TICKET, HD_STATUS as T5
set HD_TICKET.HD_STATUS_ID = T5.ID,
HD_TICKET.TIME_OPENED = IF(T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED),
HD_TICKET.TIME_CLOSED = IF(T5.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED),
HD_TICKET.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED),
HD_TICKET.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING),
HD_TICKET.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT)
where T5.NAME = 'Open' and
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and
(HD_TICKET.ID in ())
And the error log:
Executing Update Query... 06/30/2017 11:58:48> 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 ',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,))' at line 10] in EXECUTE("update HD_TICKET, HD_STATUS as T5 set HD_TICKET.HD_STATUS_ID = T5.ID, HD_TICKET.TIME_OPENED = IF(T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED), HD_TICKET.TIME_CLOSED = IF(T5.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED), HD_TICKET.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED), HD_TICKET.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING), HD_TICKET.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT) where T5.NAME = 'Open' and HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and (HD_TICKET.ID in (,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,))") 06/30/2017 11:58:48> Ending: 06/30/2017 11:58:48
EDIT: Here is the selection query. I got it off another post that seemed to be doing similar things. I removed the hard coded email and domain addresses.
Select
HD_TICKET.ID As ticket_number,
HD_TICKET.ID As TICKNUM,
'' As EMAILCC,
HD_TICKET.TITLE As 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,
HD_STATUS.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,
Unix_Timestamp(TICKETCHANGE.TIMESTAMP),
TICKETCHANGE.COMMENT As COMMENT,
TICKETINITIAL.COMMENT As INITIAL_COMMENT,
TICKETCHANGE.DESCRIPTION As CHANGE_DESCRIPTION,
HD_CATEGORY.CC_LIST As CATEGORYCC,
HD_CATEGORY.NAME As CATEGORY_NAME,
HD_PRIORITY.NAME As TICKET_PRIORITY,
HD_QUEUE.NAME As QUEUE_NAME
From
HD_TICKET Join
HD_TICKET_CHANGE TICKETCHANGE On TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID Join
HD_TICKET_CHANGE TICKETINITIAL On TICKETINITIAL.HD_TICKET_ID = HD_TICKET.ID
And TICKETINITIAL.ID = (Select
Min(HD_TICKET_CHANGE.ID)
From
HD_TICKET_CHANGE
Where
HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.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 = TICKETCHANGE.USER_ID Left Join
HD_QUEUE On HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY
Where
HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID And
HD_STATUS.ID = HD_TICKET.HD_STATUS_ID And
HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID And
HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID And
(HD_STATUS.NAME Like '%New%' And
U2.EMAIL Like '%@%' And
TICKETCHANGE.DESCRIPTION Like '%Ticket Created%' And
HD_TICKET.HD_QUEUE_ID = 1)
I want the update query to change the status from new to open after the email is sent but I cant seem to get the automatically generated one (from the k1000) to work.
Does anyone have a SQL update query that does this? Or can someone explain the tables needed to change the status correctly and I can write it myself?
Here is the code the k1000 generated:
update HD_TICKET, HD_STATUS as T5
set HD_TICKET.HD_STATUS_ID = T5.ID,
HD_TICKET.TIME_OPENED = IF(T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED),
HD_TICKET.TIME_CLOSED = IF(T5.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED),
HD_TICKET.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED),
HD_TICKET.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING),
HD_TICKET.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT)
where T5.NAME = 'Open' and
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and
(HD_TICKET.ID in (
And the error log:
Executing Update Query... 06/30/2017 11:58:48> 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 ',,,,,,,,,,,,,,,,,,,,,,,,,,,,,,))' at line 10] in EXECUTE("update HD_TICKET, HD_STATUS as T5 set HD_TICKET.HD_STATUS_ID = T5.ID, HD_TICKET.TIME_OPENED = IF(T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED), HD_TICKET.TIME_CLOSED = IF(T5.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED), HD_TICKET.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED), HD_TICKET.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING), HD_TICKET.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT) where T5.NAME = 'Open' and HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and (HD_TICKET.ID in (,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,))") 06/30/2017 11:58:48> Ending: 06/30/2017 11:58:48
EDIT: Here is the selection query. I got it off another post that seemed to be doing similar things. I removed the hard coded email and domain addresses.
Select
HD_TICKET.ID As ticket_number,
HD_TICKET.ID As TICKNUM,
'
HD_TICKET.TITLE As 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,
HD_STATUS.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,
Unix_Timestamp(TICKETCHANGE.TIMESTAMP),
TICKETCHANGE.COMMENT As COMMENT,
TICKETINITIAL.COMMENT As INITIAL_COMMENT,
TICKETCHANGE.DESCRIPTION As CHANGE_DESCRIPTION,
HD_CATEGORY.CC_LIST As CATEGORYCC,
HD_CATEGORY.NAME As CATEGORY_NAME,
HD_PRIORITY.NAME As TICKET_PRIORITY,
HD_QUEUE.NAME As QUEUE_NAME
From
HD_TICKET Join
HD_TICKET_CHANGE TICKETCHANGE On TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID Join
HD_TICKET_CHANGE TICKETINITIAL On TICKETINITIAL.HD_TICKET_ID = HD_TICKET.ID
And TICKETINITIAL.ID = (Select
Min(HD_TICKET_CHANGE.ID)
From
HD_TICKET_CHANGE
Where
HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.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 = TICKETCHANGE.USER_ID Left Join
HD_QUEUE On HD_QUEUE.ID = HD_TICKET.HD_QUEUE_ID,
HD_PRIORITY,
HD_STATUS,
HD_IMPACT,
HD_CATEGORY
Where
HD_PRIORITY.ID = HD_TICKET.HD_PRIORITY_ID And
HD_STATUS.ID = HD_TICKET.HD_STATUS_ID And
HD_IMPACT.ID = HD_TICKET.HD_IMPACT_ID And
HD_CATEGORY.ID = HD_TICKET.HD_CATEGORY_ID And
(HD_STATUS.NAME Like '%New%' And
U2.EMAIL Like '%@
TICKETCHANGE.DESCRIPTION Like '%Ticket Created%' And
HD_TICKET.HD_QUEUE_ID = 1)
2 Comments
[ + ] Show comments
-
Your select statement is returning multiple null values as indicated by the (,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,) in the query. Can you post it so we can see why it isn't returning a ticket number? - chucksteel 7 years ago
-
I edited in the select statement into the question above. - tmoyer 7 years ago
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
Hobbsy
7 years ago
Top Answer
The quickest and easiest way to do this is via the ticket rule wizard, create a rule that selects tickets that are new, then update it so the Status is open. Then edit your rule to setup the email functionality that you need.
Comments:
-
That seemed to work really well, I don't know why didn't do that before.
As far as the email part goes, can you not use the email variables like you see in the 'Email on Events' Section? I tried this email and it took it as plaintext instead of translating them.
A ticket was created for your issue ' $ticket_title '. Ticket number $ticket_number and has been assigned to $ticket_custom_3_value . - tmoyer 7 years ago-
The email variables that you use in the ticket rule need to be selected in the select statement of the Ticket rule, only then can you use them. so for example if your select statement says "SELECT HD_TICKET.CUSTOM_FIELD_VALUE16 as CUSTOMER_EMAIL" you could then use CUSTOMER_EMAIL in the address box or $customer_email in the text of the email. Check out the wizard created select statement to see what is selected as default - Hobbsy 7 years ago
-
Thanks for the info!
I added to the select statement to get the variables I wanted however its still not filling them.
Things I added:
HD_TICKET.TITLE AS TICKET_TITLE,
HD_TICKET.ID AS TICKET_ID,
~I already had submitter email and owner full name.
It seems to fill out the submitter email but doesn't convert any of the other 'variables'.
Here is the email:
Subject: (TICKET_ID) TICKET_TITLE
A ticket was created for your issue ' TICKET_TITLE '. Ticket number TICKET_ID and has been assigned to OWNER_FULLNAME .
EDIT: and its being sent to SUBMITTER_EMAIL.
I have also tried with the $ which denotes variables in the email events; to the same results.
($TICKET_ID) $TICKET_TITLE
A ticket was created for your issue ' $TICKET_TITLE '. Ticket number $TICKET_ID and has been assigned to $OWNER_FULLNAME . - tmoyer 7 years ago -
Column names become variables in the form of $variable, so you need to use $ticket_title. I recommend that you read the documentation available for creating ticket rules to get a better understanding of how they work. - chucksteel 7 years ago
-
I see now. I was putting it in all caps just like the select statement.
Thanks both of you! - tmoyer 7 years ago
Posted by:
chucksteel
7 years ago
I think that the problem is with your select query. Perhaps you made another change that wasn't quite right. One thing that I did notice is that it doesn't include the specific change ID when joining the HD_TICKET_CHANGE. Here is the select statement on our rule that emails the user and sets the status to open:
select HD_TICKET.*, 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,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME,
HD_TICKET_CHANGE.COMMENT AS COMMENT
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and HD_TICKET_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 = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_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 ((( (1 in (select 1 from USER where HD_TICKET.OWNER_ID = USER.ID and USER.FULL_NAME is not null)) ) AND HD_STATUS.NAME = 'New') and HD_TICKET.HD_QUEUE_ID = 2 and HD_TICKET.OWNER_ID != HD_TICKET.SUBMITTER_ID)
