/build/static/layout/Breadcrumb_cap_w.png
05/08/2019 223 views

Hi,


Currently using KSM K1000 on 9.1.317. We have a custom ticket rule that sends an email out when a ticket changes from 'UNASSIGNED' to an owner. The SQL code is below show below - the email 'Message:' is 'This ticket has been claimed', however, I'd like to change that to 'This ticket has been claimed by:' and then list the tech who claimed it.

How best do I go about changing this? Any help is appreciated!


SELECT 

HD_TICKET_CHANGE.HD_TICKET_ID AS TICKNUM,

HD_TICKET.ID,

HD_TICKET.TITLE,

HD_TICKET_CHANGE_FIELD.FIELD_CHANGED,

HD_TICKET_CHANGE_FIELD.BEFORE_VALUE, 

HD_TICKET_CHANGE_FIELD.AFTER_VALUE,

'EMAIL@DOMAIN.COM' AS NEWTICKETEMAIL 

FROM HD_TICKET_CHANGE, 

HD_TICKET_CHANGE_FIELD,

HD_TICKET

WHERE HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID

AND HD_TICKET_CHANGE.ID = HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID

AND HD_TICKET_CHANGE_FIELD.FIELD_CHANGED = 'OWNER_ID'

AND HD_TICKET_CHANGE_FIELD.BEFORE_VALUE = 0

AND HD_TICKET_CHANGE_FIELD.AFTER_VALUE NOT LIKE '%0%'

AND HD_TICKET.OWNER_ID NOT LIKE  '%0%'

AND HD_TICKET_CHANGE.DESCRIPTION LIKE '%Unassigned%'

AND HD_TICKET_CHANGE.TIMESTAMP = HD_TICKET.MODIFIED

Answer Summary:
0 Comments   [ + ] Show comments

Comments


Answer Chosen by the Author

1

You need to add two things to your query:

  • A join to the user table 
  • Select a column from that table

Joins are added after the FROM clause and you need to specify the relationship between the tables, like this:

JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID

In this case I am making an alias for the USER table and calling it OWNER. This makes it more obvious who I am referring to in other parts of the query. Also, if you wanted to include information about the submitter, you could join to the USER table again with a different alias. Once that relationship is in place, you can refer to the table in the SELECT clause of the statement.

The SELECT clause is a comma separated list of column names to return. The SMA will create a variable for each column in your query, so you can add another column to the query by inserting a comma after the last entry and then the name of the column you want to return. Using an alias on the column name makes the variables more human readable (the same applies when creating reports). In your case you want the technician's name, which is in the FULL_NAME column.

OWNER.FULL_NAME as TECHNICIAN

The full query should now look like this:

SELECT 
HD_TICKET_CHANGE.HD_TICKET_ID AS TICKNUM,
HD_TICKET.ID,
HD_TICKET.TITLE,
HD_TICKET_CHANGE_FIELD.FIELD_CHANGED,
HD_TICKET_CHANGE_FIELD.BEFORE_VALUE, 
HD_TICKET_CHANGE_FIELD.AFTER_VALUE,
'EMAIL@DOMAIN.COM' AS NEWTICKETEMAIL,
    
    OWNER.FULL_NAME AS TECHNICIAN
FROM HD_TICKET_CHANGE, 
HD_TICKET_CHANGE_FIELD,
HD_TICKET
    
    JOIN USER OWNER on OWNER.ID = HD_TICKET.OWNER_ID
WHERE HD_TICKET.ID = HD_TICKET_CHANGE.HD_TICKET_ID
AND HD_TICKET_CHANGE.ID = HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID
AND HD_TICKET_CHANGE_FIELD.FIELD_CHANGED = 'OWNER_ID'
AND HD_TICKET_CHANGE_FIELD.BEFORE_VALUE = 0
AND HD_TICKET_CHANGE_FIELD.AFTER_VALUE NOT LIKE '%0%'
AND HD_TICKET.OWNER_ID NOT LIKE  '%0%'
AND HD_TICKET_CHANGE.DESCRIPTION LIKE '%Unassigned%'
AND HD_TICKET_CHANGE.TIMESTAMP = HD_TICKET.MODIFIED


With those changes in place, you can now change the subject of the email to include $technician (variables need to be in lower case).

Answered 05/09/2019 by: chucksteel
Red Belt

  • I couldn't have described it better.
    cheers
  • Awesome - This fixed it!

    Thanks so much!

All Answers

1

Hi @,

I have a similar rule, which is used when someone comments on a ticket. An email will be sent to the ticket owner telling them who commented on the ticket.

You need a reference in your SQL query to show you the name of the ticket owner - you can include this reference with "$ sign" in your sent mail.


Greetings
svmay


Answered 05/09/2019 by: svmay
Red Belt