We have an email notification setup where all category staff members are notified when a new ticket has been created in their queue.  The rule works perfectly until a comment is added in the comment field – no email is sent when this happens.  We are at a loss at what could cause this glitch.

5 Comments   [ + ] Show Comments

Comments

  • Please post your SQL for the rule and we can check it out.
  • The SQL query is:

    select

    -- ticket fields

    HD_TICKET.ID, -- $id

    HD_TICKET.ID as TICKNUM, -- $ticknum

    HD_TICKET.TITLE, -- $title

    date_format(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, -- $created

    date_format(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED, -- $modified



    -- change fields

    C.COMMENT, -- $comment

    C.COMMENT_LOC as CMT_L10N, -- $cmt_l10n

    C.DESCRIPTION, -- $description

    C.LOCALIZED_DESCRIPTION as DESC_L10N, -- $desc_110n

    group_concat( concat('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',

    H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')

    order by H.ID desc separator '\n') as HISTORY, -- $history

    group_concat( concat('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',

    H.LOCALIZED_DESCRIPTION,'\n',H.COMMENT_LOC,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')

    order by H.ID desc separator '\n') as HIST_l10n, -- $hist_l10n



    -- about the updater

    UPDATER.USER_NAME as UPDATER_UNAME, -- $updater_uname

    UPDATER.FULL_NAME as UPDATER_FNAME, -- $updater_fname

    UPDATER.EMAIL as UPDATER_EMAIL, -- $updater_email

    if(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) as UPDATER_CONDITIONAL, -- $updater_conditional



    -- about the owner

    OWNER.USER_NAME as OWNER_UNAME, -- $owner_uname

    OWNER.FULL_NAME as OWNER_FNAME, -- $owner_fname

    OWNER.EMAIL as OWNER_EMAIL, -- $owner_email

    ifnull(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user



    -- about the submitter

    SUBMITTER.USER_NAME as SUBMITTER_UNAME, -- $submitter_uname

    SUBMITTER.FULL_NAME as SUBMITTER_FNAME, -- $submitter_fname

    SUBMITTER.EMAIL as SUBMITTER_EMAIL, -- $submitter_email

    -- about priority

    P.NAME as PRIORITY, -- $priority



    -- about status

    S.NAME as STATUS, -- $status



    -- about impact

    I.NAME as IMPACT, -- $impact



    -- about category

    CAT.NAME as CATEGORY, -- $category



    -- other fields

    -- -- example of static distribution list

    OWNER.EMAIL as NEWTICKETEMAIL -- $newticketemail



    from

    HD_TICKET

    /* latest change ***/ join HD_TICKET_CHANGE C on C.HD_TICKET_ID = HD_TICKET.ID

    and C.ID=<CHANGE_ID> and <TICKET_JOIN>

    /* initial change **/ left join HD_TICKET_CHANGE_FIELD F ON

    F.HD_TICKET_CHANGE_ID = 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

    where

    F.ID is null /* initial entry for ticket */

    /* this is necessary when using group by functions */

    group by HD_TICKET.ID


    The email body is:

    $submitter_fname has submitted a scenario.

    The submission was:
    From: $submitter_fname ($submitter_email)
    Loan Type: $category
    Loan Purpose: $impact
    Priority: $priority


    $comment
    • I don't think I have ever seen <TICKET_JOIN> in a ticket rule before. Did the wizard add that for you?
  • I think that was Dell/Kace support's handy work. I am a bit lost on this as one of my tech's created this queue and he has since left the company.
  • This was a cut and paste from here: http://www.kace.com/support/resources/kb/solutiondetail?sol=SOL111222
    • The join in the solution is different from the join you pasted. This should be the correct join to the HD_TICKET_CHANGE table:
      join HD_TICKET_CHANGE C on C.HD_TICKET_ID = HD_TICKET.ID
      and C.ID=<CHANGE_ID>
  • Made the change...no luck (changed it back). It looks like when we add comments the query isn't selecting a row to send. The logs supporting this are below:

    Log without comments:

    10/22/2013 11:52:36> Starting: 10/22/2013 11:52:36
    10/22/2013 11:52:36> Executing Select Query...
    10/22/2013 11:52:36> selected 1 rows
    10/22/2013 11:52:36> Sending ticket notifications...
    10/22/2013 11:52:36> sent mail to 1 of 1
    10/22/2013 11:52:36> Ending: 10/22/2013 11:52:36

    Log with comments:

    10/22/2013 11:56:22> Starting: 10/22/2013 11:56:22
    10/22/2013 11:56:22> Executing Select Query...
    10/22/2013 11:56:22> selected 0 rows
Please log in to comment

Answers

1

This is the query that finally worked.

select

 

  HD_TICKET.ID,

  HD_TICKET.ID ticknum,

 

  HD_TICKET.TITLE,

  SUBMITTER.FULL_NAME submitter_fname,

  SUBMITTER.EMAIL submitter_email,

  CAT.NAME category,

  IMPACT.NAME impact,

  PRIORITY.NAME priority,

  OWNER.EMAIL EMAILCOLUMN

from

  HD_TICKET

  join HD_TICKET_CHANGE C on HD_TICKET.ID = C.HD_TICKET_ID

    and  C.ID = <CHANGE_ID>

  join USER SUBMITTER on HD_TICKET.SUBMITTER_ID = SUBMITTER.ID

  join USER OWNER on HD_TICKET.OWNER_ID = OWNER.ID

  join HD_CATEGORY CAT on HD_TICKET.HD_CATEGORY_ID = CAT.ID

  join HD_IMPACT IMPACT on HD_TICKET.HD_IMPACT_ID = IMPACT.ID

  join HD_PRIORITY PRIORITY on HD_TICKET.HD_PRIORITY_ID = PRIORITY.ID where

 

  C.DESCRIPTION like '%Ticket Created%'

Answered 11/05/2013 by: cgwillis
White Belt

Please log in to comment
Answer this question or Comment on this question for clarity