OK so i ask this Question http://www.itninja.com/question/email-on-call-staff-when-a-new-ticket-getes-created-by-user

and JasonEgg did a great rule for me on-call over the weekend. I have is texting my iphone but i can't get variables in the text msg.

so if i go to Help-Desk -> Configuration -> Rules

Then click on are New IT on Call rule then with the option "Email each recipient in query results"

I can enter a Subject: and the Message: i want to sent when a ticket get is created.

How can i added the tickets info inĀ  to the Message and the title in to the Subject with the recipient name so i can see if it's the CEO or not.


i triedĀ  HD_TICKET.TITLE
and
$HD_TICKET.TITLE
and
$ticknum
$owner_name

What are the variables i can use and how do i added them in ?




Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
Any columns selected in the select query will be available as variables for the email message. In the case of the query that JasonEgg setup he selected HD_TICKET.*, this will make all of the columns in the HD_TICKET table available, they will not be proceeded by the table name, so the TITLE column will be available as $title (variables are lower case), ID will be $id.

The query does not include the joins normally in place for owner, submitter, etc. In order to select those columns you will need to add those join statements and select the appropriate columns. For ticket submitter the join statement is:
JOIN USER SUBMITTER on USER.ID = HD_TICKET.SUBMITTER_ID

The select clause uses a comma separated list of columns, so to select the submitter's full name you would add SUBMITTER.FULL_NAME to the list after your ON_CALL column. 

The updated query should look like this:
select HD_TICKET.*,
  '5555555555@verizon.net' AS ON_CALL,SUBMITTER.FULL_NAME
from HD_TICKET 
  join HD_TICKET_CHANGE on 
    (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
     AND HD_TICKET_CHANGE.ID = <CHANGE_ID>)join USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
where 
  DAYOFWEEK(CREATED) in (1,7)
    # 1 = Sunday, 7 = Saturday
  AND TIME(CREATED) >= TIME('8:00:00')
    # at or after 8am
  AND TIME(CREATED) <= TIME('17:00:00')
    # before or at 5pm
  AND HD_TICKET_CHANGE.DESCRIPTION like 'Ticket Created%'
    # Only send email on creation of ticket

Answered 05/10/2017 by: chucksteel
Red Belt

  • I don't have a column in the USER table called SUBMITTER. And here is my Log:

    05/10/2017 13:55:10> Starting: 05/10/2017 13:55:10 05/10/2017 13:55:10> Executing Select Query... 05/10/2017 13:55:10> mysqli error: [1052: Column 'CREATED' in where clause is ambiguous] in EXECUTE("select HD_TICKET.*, '5555555555@messaging.sprintpcs.com' AS MIKE from HD_TICKET join HD_TICKET_CHANGE on (HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID AND HD_TICKET_CHANGE.ID = 0) join USER SUBMITTER on SUBMITTER.ID = HD_TICKET.SUBMITTER_ID where DAYOFWEEK(CREATED) in (1,4,7) # 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday 5, Thursday 6 = Friday, 7 = Saturday AND TIME(CREATED) >= TIME('9:00:00') # at or after 8am AND TIME(CREATED) <= TIME('17:00:00') # before or at 5pm AND HD_TICKET_CHANGE.DESCRIPTION like 'Ticket Created%' # Only send email on creation of ticket")
    • When you join to a table you can alias it as another name, this allows you to join to the same table multiple times, e.g. the submitter, the owner, etc. In this case I am making an alias for the USER table called SUBMITTER to make it obvious that we are referencing the ticket submitter.

      There is also a column in the USER table named CREATED, so that is what generated the error. change the TIME(CREATED) to TIME(HD_TICKET.CREATED).
      • Look at that i learn something new Thank you works great
Please log in to comment

Answers

Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share