I’ve been trying to use a slightly modified KACE KB ticket rule to notify a ticket owner when a ticket has been assigned to them. Why, because when a ticket rule assigns the ticket to an owner the built-in emailing mechanism (email owner on every change) doesn’t work. I took the canned rule and changed the C.DESCRIPTION LIKE to C.COMMENT LIKE and that’s it. When I test this query, by adding an appropriate Change ID in place of <CHANGE_ID> the query returns the desired results. I need to use the COMMENT because there is no DESCRIPTION added to the history of the ticket for the actions of the rule, so each rule I create adds a comment.

I have this canned email rule in use throughout the kbox queues but they are all based on C.DESCRIPTION or some other data set (CATEGORY). I’ve also ordered this rule to the very bottom of the list so it is the last rule to run.

Any suggestions would be most appreciated as I have a queue owner whining loudly about his team’s SLAs because they don’t get notified when a ticket is assigned to them.

Thank you

***********query**************

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.DESCRIPTION, -- $description
 GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
 H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at https://aerocare.panasonic.aero/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
 ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
 -- 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
 'aerocare@panasonic.aero' 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>
 /* 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
 C.COMMENT LIKE 'Owner Assigned:%'
 /* this is necessary when using group by functions */
 GROUP BY HD_TICKET.ID
 HAVING 1=1

 

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

2

Instead of looking for the comment on the change you could also look for the owner changing. You would need to add the following join:

 JOIN HD_TICKET_CHANGE_FIELD CF ON CF.HD_TICKET_CHANGE_ID = <CHANGE_ID> AND CF.FIELD_CHANGED = OWNER_ID

Then your where statement becomes

WHERE CF.BEFORE_VALUE != CF.AFTER_VALUE

That should only match tickets where the owner has changed. 

Answered 04/26/2013 by: chucksteel
Red Belt

  • We though about this Chuck but the queue is set to Notify Owner on Any Change and we kind of need it like that. Setting the rule like this would send two emails.
  • I too am trying to use this rule. I get:
    12/20/2013 13:13:16> Starting: 12/20/2013 13:13:16
    12/20/2013 13:13:16> Executing Select Query...
    12/20/2013 13:13:16> selected 0 rows

    when I try to run this rule based on chucksteel's suggestions. Does anyone have anymore information on this? I was using OWNER_EMAIL for the email column.
    • Can you post your select query?
      • 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.DESCRIPTION, -- $description
        GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
        H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://helpdesk/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
        ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history

        -- 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
        'xx@xx.com' AS NEWTICKETEMAIL -- $newticketemail

        FROM HD_TICKET
        JOIN HD_TICKET_CHANGE_FIELD CF ON CF.HD_TICKET_CHANGE_ID = <CHANGE_ID> AND CF.FIELD_CHANGED = OWNER_ID
        /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
        AND C.ID=<CHANGE_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
        CF.BEFORE_VALUE != CF.AFTER_VALUE
        /* this is necessary when using group by functions */
        GROUP BY HD_TICKET.ID
        HAVING 1=1
      • The code looks fine. I'd recommend turning on the option to send the query results to email and change the owner on a ticket to see what it shows you.
      • If I change the owner from unassigned to an owner, I get:
        'Executing Select Query' and then 'selected 0 rows' and I dont get a "send the query results" email.

        On the other hand, if I change the ticket from an Owner to Unassigned, I get a "send the query results" email with all of the ticket information for that one ticket that I made the change on, but the email is not working because there is no email designated for the 'Owner User Unassigned'.
        13:47:56> Sending ticket notifications...
        12/23/2013 13:47:56> sent mail to 0 of 1
        12/23/2013 13:47:56> Ending: 12/23/2013 13:47:56
        It's like I typed a value backwards or something. I'm using the select query posted above.
      • Thank you so much Chuck!
  • Chuck,
    I was able to get this to work using the information that you posted in: http://www.itninja.com/question/custom-email-to-submitter-on-owner-change
    Thanks for your help.
    How difficult would it to have a similar rule which emails the Before owner and lets them know that the ticket is no longer assigned to them?
    • Here's my select statement for a rule which does just that:

      select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
      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,
      STATE,
      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(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
      Q.NAME as QUEUE_NAME,
      U4.EMAIL as PREVIOUS_OWNER_EMAIL,
      U5.EMAIL as NEW_OWNER_EMAIL
      from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
      JOIN HD_TICKET_CHANGE ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
      and HD_TICKET_CHANGE.ID=<CHANGE_ID>
      JOIN HD_TICKET_CHANGE_FIELD ON HD_TICKET_CHANGE_FIELD.HD_TICKET_CHANGE_ID=<CHANGE_ID>
      and HD_TICKET_CHANGE_FIELD.FIELD_CHANGED='OWNER_ID'
      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 USER U4 on U4.ID = HD_TICKET_CHANGE_FIELD.BEFORE_VALUE
      LEFT JOIN USER U5 on U5.ID = HD_TICKET_CHANGE_FIELD.AFTER_VALUE
      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 (( (1 in (select 1 from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)) ) and HD_TICKET.HD_QUEUE_ID = 2 and HD_TICKET_CHANGE.USER_ID != U4.ID)

      Note that the HD_QUEUE_ID needs to be changed from 2 to match your queue ID. Also, this rule won't match tickets if you change the owner to someone else, only if another technician makes the change so keep that in mind when testing.

      I have the rule send an email with the following options:

      Subject: Ticket $id has been transferred to $new_owner_email
      Email Column: PREVIOUS_OWNER_EMAIL
      Email Body: Ticket $id, $title, has been transferred to $new_owner_email.
      • Thanks Chuck!! That is exactly what I was looking for. You the man
      • Hey Chuck, How would I pull the updater email in this query? I have been trying for a couple of days to figure it out, but I am stuck. Any advice?
      • You need to add two things:
        Add this in the joins section, beneath the U5 join (I personally wouldn't use these generic U1, U2, etc aliases but that's what the wizard used so I'm sticking with that convention).
        LEFT JOIN USER U6 on U6.ID = HD_TICKET_CHANGE.USER_ID

        Further up in the select fields add this below the U5.EMAIL line, be sure to add at the end of U5.EMAIL as NEW_OWNER_EMAIL or the syntax will break:
        U6.EMAIL as UPDATER_EMAIL
Please log in to comment
2

You could also use one of the checkboxes to notify the owner, submitter, or approver on any ticket change.

Answered 04/26/2013 by: GeekSoldier
Red Belt

  • The queue is set to Notify Owner on any change but when a ticket rule sets the owner the kbox doesn't see a "user" making the change and it doesn't send the email. It's been like that since we started with it on 5.2
    • I follow you now. Sorry for the misunderstanding. SQL away!
Please log in to comment
1

When you set this canned ticket rule, you set NEWTICKETEMAIL as the alias for that particular email.  You understand that the EMAIL column of the ticket rule SEND RESULTS FOR EACH ROW is hard coded with a $?

 

When you do a RUN NOW, what does the run log say for your rule?

Answered 05/01/2013 by: Wildwolfay
Red Belt

  • Side note: If you have your KACE set to auto assign, it doesn't place a comment in the ticket comment section that says: Owner Assigned: so your where statement would find nothing unless it's manually assigned to someone.
  • The Run Log shows -
    05/01/2013 08:13:41> Starting: 05/01/2013 08:13:41
    05/01/2013 08:13:41> Executing Select Query...
    05/01/2013 08:13:41> selected 0 rows
  • Every rule we run (100s in 11 queues) adds an Owners Only Comment to the ticket so we know what rule made a change.
  • This has more to do (I think) with the System making the change and not a logged in user. I just can't figure out what portion of the query to modify so that it's not checking for the User who made the change. I don't really care who made the change, the rule simply needs to recognize a change has been made and that change resulted in the COMMENT being added.
Please log in to comment
This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

Answer this question or Comment on this question for clarity

Share