Can't get the layout right of an automatic email on ticked creation within a Ticket Rule
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:
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
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
0 Comments
[ + ] Show comments
Answers (1)
Answer Summary:
Please log in to answer
Posted by:
chucksteel
8 years ago
Top Answer
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.
Comments:
-
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 - Servicedeks-admin 8 years ago
-
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. - chucksteel 8 years ago-
Thank you so much, this will really help me, thanks again! - Servicedeks-admin 8 years ago
