Scripting : Help with new queue and sending a message to the users of K BOX

Help with new queue and sending a message to the users of K BOX

When a new ticket is generated in the Message field sent to the technicians, we have

Ticket: $ticknum

From: $submitter_fullname ($submitter_Email)

Catergory: Excel

Comment: $initial_comment

Everything seems to work except we don't see the initial comment made by the submitter such as "In Excel, i am having a hard time with a formula, can you help."

Can anyone help me get the last part of the message we want sent to the help desk techs so the first comment is included?

Thanks, Jay

10 Comments [ + ] Show comments

Are you using the system rules or a custom rule? If you are using a custom rule, please post the select statement part of the rule. - chucksteel 6 years ago
Hi Chuck thanks for giving this some time. It is a custom rule. Here is the Select Statement part of the rule: select HD_TICKET.*,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
HD_CATEGORY.NAME as CATEGORY,
HD_TICKET.ID AS TICKNUM,
'xxxxxxxx@yyyyy.com as MULTIEMAIL,
STATE,
if(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(HD_TICKET.APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
LEFT JOIN USER U1 on U1.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER U2 on U2.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER U3 on U3.ID = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID
where HD_PRIORITY.ID = HD_PRIORITY_ID
and HD_STATUS.ID = HD_STATUS_ID
and HD_IMPACT.ID = HD_IMPACT_ID
and HD_CATEGORY.ID = HD_CATEGORY_ID
and (( HD_TICKET.TITLE like '%Excel%') and HD_TICKET.HD_QUEUE_ID = 8 ) - obrienj 6 years ago
Here is the message portion of our rule: The submission was:
Ticket: $ticknum
From: $submitter_fullname ($submitter_email)
Category: Excel
Opening Comment: INITIAL_COMMENT
Summary: $Summary

When the E-Mail comes through, we see everything except the initial comment from the end user. - obrienj 6 years ago
Hi Chuck, I appreciate your insight and help with this. . We aren’t seeing an email generate to the email addresses attached as cc’ addresses. The initial email is working fine as it’s notifying me as my address is attached to the “other” category. Any ideas why the cc addresses aren't receiving an E-Mail? - obrienj 6 years ago
- Are you referring to the category CC list, or the CC field on the ticket? They are separate things and either one would need to be included in the select statement as a column. That column would then need to be used for the "Column Containing Email Addresses" field on the ticket rule. - chucksteel 6 years ago
Thank you Chuck. I was referring the category CC list, the E-Mail addresses that are part of the MULTIMAIL statement. - obrienj 6 years ago
- Currently your multiemail column only contains this:
  'jay.obrien@jud.state.ma.us' as MULTIEMAIL,
  If you want it to be the category CC then you need to change that to this:
  HD_CATEGORY.CC_LIST as MULTIEMAIL, - chucksteel 6 years ago
Thanks Chuck, this is very much appreciated. Have a nice weekend. -Jay - obrienj 6 years ago
Chuck, We have the rule configured to run on ticket save. As a result, the email accounts identified in the group we’ve labeled ‘MUTLIEMAIL’ are receiving the same email that includes the initial comment each time a comment is added to the ticket. Can you see anything that might be causing this? Thanks, Jay - obrienj 6 years ago
- If you only want the email sent when the ticket is created, you will need to:
  Add this join after the other join statements:
  JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
  and HD_TICKET_CHANGE.ID=<CHANGE_ID>
  
  FYI, the <CHANGE_ID> is a variable that will be replaced by the current change, once you add this using the link to view the query results won't work.
  
  Once that join statement is in place, add this to the end of the statement:
  and HD_TICKET_CHANGE.DESCRIPTION LIKE '%Created%' - chucksteel 6 years ago
Thanks Chuck, we will give that a shot. - obrienj 6 years ago
Hi Chuck,
We are still having some trouble with our new queue. If I may, I'd like to lay out what we are trying to accomplish.
We have several category values defined in the queue (e.g. ‘Excel’). Each category has multiple individuals defined in the “CC List”.
We have created a ticket rule using the KACE wizard to assign a category based on specified text in the subject/title. For example, a ticket that includes the word “Excel” in the title will change the category to “Excel”. We would like for the users defined in the “CC List” to be notified when the ticket is submitted as well as for any subsequent changes to the ticket. We would like for the initial comments to be included in each email notification. We would also like an additional notification to be sent to a different email address upon ticket creation. That address does not need to be included on ticket updates.
Do you happen to see anything in the Select SQL statement that would be the problem? Again, I truly appreciate any time you spend on this. Thanks, Jay - obrienj 6 years ago
- I would use multiple rules for this and make sure that the order is set correctly:
  Rule 1: Sets the category on ticket creation
  Rule 2: Email the category CC on ticket creation
  
  The rule to email the other address upon ticket creation can run separately from these, unless you want it to include the category information that rule 1 would set.
  
  You will also need a rule for ticket changes. Your ticket creation notifications should contain this line:
  HD_TICKET_CHANGE.DESCRIPTION LIKE '%Created%'
  
  Your ticket change notifications should include this line:
  HD_TICKET_CHANGE.DESCRIPTION NOT LIKE '%Created%'
  
  In general, I try to keep my notification email rules separate from my update rules. This results in more rules in each queue, but it makes trouble shooting easier. I also tend to check the box to add a comment to the ticket with a simple comment that indicates that the rule ran, i.e. "Email sent to category CC on ticket creation" - chucksteel 6 years ago
Thank you again Chuck, I'll take a crack at this. - obrienj 6 years ago

Answers (1)

Posted by: chucksteel 6 years ago

The variables that are available in the email are created based on the columns selected in the SQL Select Statement. In your case, the $initial_comment variable is not created, because there are no columns selected and given that name. In order to get the initial comment you need to add the following join statement:

JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID = HD_TICKET.ID
 and INITIAL_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)

This should be added after this line:

LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_ID

Now that the join is in place to the HD_TICKET_CHANGE table (which we are calling INITIAL_CHANGE) you can add INITIAL_CHANGE.COMMENT as INITIAL_COMMENT to the list of selected fields. You can just add it to the end of the comma separated list, like this:

Q.NAME as QUEUE_NAME,
INITIAL_CHANGE.COMMENT as INITIAL_COMMENT

When the rule runs the $initial_comment variable will now be generated and contain the comment from the first change on the ticket (creating the ticket counts as a change in this instance).

Comments:

Thank you, I will test that out this morning. Thank you very much. Jay - obrienj 6 years ago

Answer this question

Posted by:

View more:

Scripting Questions

question