Scripting

Hi @TehH3ro0fTiem,

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

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