/bundles/itninjaweb/img/Breadcrumb_cap_w.png
This is what was working before with 6.4:
What do I have to change to make it work with 7.0?

select HD_TICKET.ID, 

HD_TICKET.ID as TICKNUM, 

'informationservices@wylietexas.gov' as EMAILCC,

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,

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,

U2.LOCATION AS SUBMITTER_LOCATION,

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 TICKETCHANGE ON TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID 

 and TICKETCHANGE.ID=<CHANGE_ID>

JOIN HD_TICKET_CHANGE TICKETINITIAL ON TICKETINITIAL.HD_TICKET_ID = HD_TICKET.ID

 and TICKETINITIAL.ID=(select MIN(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

 

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

TICKETCHANGE.DESCRIPTION like '%Ticket Created%' and

HD_TICKET.HD_QUEUE_ID = 1 and

HD_STATUS.NAME != 'Closed'
Answer Summary:
Cancel
0 Comments   [ - ] Hide Comments

Comments

Please log in to comment

Answer Chosen by the Author


Answers

1
We had the similar problem in our Notification ticket rule. They made changes to the user Table. One of the was Location.

Try commenting out the U2.LOCATION AS SUBMITTER_LOCATION, and see if you start receiving notifications
Answered 01/06/2017 by: MiguelT
Senior White Belt

  • I did as you suggested. Or at least tried. I don't know anything about SQL. I had originally copied and pasted that whole bit of text from this forum and simply changed the email address in the EMAILCC line.
    I changed that line to --U2.LOCATION AS SUBMITTER_LOCATION, which is what I was told meant to comment it out by a SQL person and it didn't change anything.
    • Try removing that line completely, And this is ticket rule is only for new tickets coming in correct?
      • Yes that is correct. When a new ticket comes, it sends an email to our group mailbox with a customized email with the ticket information.
      • It worked now after I removed the line. Thank you!!!
Please log in to comment
Answer this question or Comment on this question for clarity

Answers