Hello It-ninja's


I try to line out a automatic email within a ticket rule, but noting seams to work, not even HTML seams to work in this.
My goal would be to get very column on the next line for example my email would look like this:

Created

Title

Comment

Updater Fname

Priority

Apr 04 2016 04:50:04 PM

big title big title big title big title big title big title

lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text

Bond, James

Medium


when I would like it to be ordered below each other like this:


Title                       Big title big title big title big title big title big title
Created                 Apr 04 2016 04:50:04 PM
Priority                  Medium
Comment              lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text lots of text
Updater Fname    Bond, James


Reason for this is that big e-mails are much better readable than the current layout.
This is my current ticket rule:

  SELECT
 
      DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
      HD_TICKET.TITLE, -- $title
      C.COMMENT, -- $comment
      UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
      P.NAME AS PRIORITY, -- $priority
      'helpdesk@test.com' AS NEWTICKETEMAIL -- $newticketemail
    FROM HD_TICKET
     /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
                            AND C.ID=
     /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
     /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
     /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
     /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
     /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
     /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
     /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
     /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID


Thanks in advance,
stefan
Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answer Chosen by the Author

0
It looks like you are using the option to email the results of the query to a user. That is normally used for debugging or as a kind of report. To format the email you should use the option to Email each recipient in query results. You need to specify a column that was selected as the to address, so if you want the email to go to the user make sure you select the submitter's email address in the select statement.
Answered 04/05/2016 by: chucksteel
Red Belt

  • Thanks for answering but could you elaborate this a little more, I don't know SQL at all. And I'm struggling to comprehend it all, probably because i have only basic knowledge about programing to begin with. Again, thanks in advance, Stefan
    • A SQL statement is made up of several clauses. The first clause is a select clause which specifies columns from tables to be included in the output. This would be things like HD_TICKET.TITLE, SUBMITTER.EMAIL, HD_PRIORITY.NAME, etc. After that you use a FROM statement to specify the tables you will be pulling data from, e.g. HD_TICKET. You can make joins to other tables based on relationships between tables. For instance, the first join statement above is:
      JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
      AND C.ID=<CHANGE_ID>

      This says, that there is a relationship between the HD_TICKET_CHANGE table (which we will call C from now on) that C.HD_TICKET_ID is equal to HD_TICKET.ID, we are further limiting that relationship to rows where C.ID (the ID of the individual change) is equal to <CHANGE_ID>. <CHANGE_ID> is a variable that KACE will replace with the specific ID of the change made when a ticket was saved, so this is only applicable to ticket rules that run on ticket save. Keep in mind that the browser might try to interpret <CHANGE_ID> as an HTML tag so it won't always be displayed in answers here, if you copy and paste you may need to manually add it back.

      Other joins in the statement above make relationships to the USER table based on different columns in HD_TICKET. For instance, there is a relationship setup based on the submitter which allows you to get information about the submitter. There are also relationships setup for the owner and the updater.

      The statement you posted does not include a WHERE clause but normally you would want to limit the tickets returned by a query. For instance, you may want to look for tickets that were created with this save, or tickets that had a specific field changed. That will vary depending on when you want to email the user or take another action with this rule.

      So, with all of that said, to send an email to a user you need to include the SUBMITTER.EMAIL in the list of columns being returned by the query. That is a comma separated list, so you can add this line before the FROM line:
      ,SUBMITTER.EMAIL as SUBMITTER_EMAIL
      This allows you to use SUBMITTER_EMAIL as the column containing the email address in the KACE rule.

      Also, any columns included in the select statement can be used as variables in the email template. For example, the full name of the person making the change (UPDATER.FULL_NAME) is available as $updater_fname.

      I hope that helps.
      • Thank you so much, this will really help me, thanks again!
Please log in to comment

Answers

Answer this question or Comment on this question for clarity