/build/static/layout/Breadcrumb_cap_w.png

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
Asked 6 years ago 855 views
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
Red Belt
0
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
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ