/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


K1000 - Update Query from 'New' to 'Open'

06/30/2017 1097 views
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)
Answer Summary:
2 Comments   [ + ] Show comments

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?
  • I edited in the select statement into the question above.

Answer Chosen by the Author

0
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.
Answered 07/03/2017 by: Hobbsy
Red Belt

  • 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 .
    • 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
      • 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 .
      • 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.
      • I see now. I was putting it in all caps just like the select statement.
        Thanks both of you!

All Answers

0
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)

Answered 07/05/2017 by: chucksteel
Red Belt

 
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