I am working on a custom task that will email a customer if a ticket is not closed and over 7 days old.
I want the email to have the current status and last comment added to the ticket.
When I tested the sql in workbench it seemed to work correctly. When I ran it in the system to test it I received an email but it only contained the comment. I also noticed the run log is showing  “> Executing Select Query...” but nothing else.
Below is the query I am testing this with, I was wondering if anyone sees anything wrong with it?

select
HD_TICKET.ID as TICKNUM,
HD_TICKET.TITLE,
U1.USER_NAME as OWNER_NAME,
U3.USER_NAME as LASTINPUTNAME,  
STATE,
U1.FULL_NAME as OWNER_FULLNAME,
U2.USER_NAME as SUBMITTER_NAME,  
U2.EMAIL as SUBMITTER_EMAIL,
U3.EMAIL as UPDATEREMAIL,
HD_TICKET.CREATED as TICKETCREATED,
COMMENT
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>
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_CHANGE.USER_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_STATUS.NAME != 'Closed'
HD_TICKET_CHANGE.DESCRIPTION LIKE '%"Status Update"%'

For testing purposes I created a status called “Status Update” to email the ticket instead of a date range. I planned to add “and HD_TICKET.CREATED < DATE_SUB(NOW(), INTERVAL 7 DAY)” instead of the status if I can get this working correctly.

In the email body I was looking to include:
$ticknum
$comment
$owner_Name
$submitter_email
$ticketcreated

1 Comment   [ + ] Show Comment

Comments

  • the email links no longer work in v6
    example taken from the post:

    <mailto:hr@company.com?subject=[TICK:$id]&body=@approval%20=%20Approved>
Please log in to comment

Community Chosen Answer

4

Have you tried checking the box to email the query results to an email address and have them sent to you? That might show what the query is returning.

Answered 05/29/2012 by: chucksteel
Red Belt

Please log in to comment

Answers

3

I have several custom ticket rules with emails setup in this post:

http://www.itninja.com/blog/view/k1000-service-desk-setup-tips-things-i-have-learned

The Unassigned Tickets or Waiting On Approval examples should help you get what you want working.  As chucksteel said, make sure to check the "Send an email for each result row" option and to specify & alias an email address in the Select query (the alias is referenced in the Email Column field.

If you get stuck, let us know.

John

 

Answered 05/29/2012 by: jverbosk
Red Belt

  • I really do not understand this new website.
    I want to check this link out you sent me. I open it and all i can see is 6 comments of people saying good things about your post. I cant actually see your post or info.
  • I noticed that too, since they changed the site a few days ago. So I guess it's not just my computer rendering it badly.

    John
Please log in to comment
2

Since that blog is down due to technical issues, here's the custom ticket rules I was referring to.  Just one note (and I'm going to update this in the blog once I have a chance to test it), the Waiting on Approval ticket rule references HD_STATUS.NAME, which hasn't been changing like when this was originally setup.  So I'm planning on referencing the HD_TICKET.APPROVAL field instead, since this is what actually gets updated when the approver replies to the email using the included link (and prevents the rule from running over and over).  I've picked up a few MySQL tricks since posting this - I'm sure you can understand finding ways to improve old solutions when you've learned a few more things.  Anyways, hope that helps!

John

________________

Title:
Unassigned Tickets
************************************
I believe I borrowed this from an AppDeploy post, modified for my own use.
************************************

Order:
11

Notes:
Sends an email when a ticket has sat in queue longer than specified period (1 hour).
Create this ticket rule in Queue 1 (IT Helpdesk).

Frequency:
15 minutes

Select Query:
Select 'itdept@company.com' as SUPPORT, T.ID as ID, T.TITLE as Issue, S.FULL_NAME as Submitter, T.CREATED as Created FROM HD_TICKET T
JOIN USER S ON (S.ID = T.SUBMITTER_ID)
WHERE T.CREATED < SUBDATE(NOW(), INTERVAL 1 HOUR)
AND T.OWNER_ID = 0
ORDER BY T.ID ASC

X Send an email for each result row

Subject:
ATTENTION - Unassigned Ticket in KBOX Helpdesk Queue

Email Column:
SUPPORT

Email Body:
Unassiged ticket in KBOX Helpdesk queue, please review.

Created:    $created
Ticket ID:    $id
Issue:        $issue
Submitter:    $submitter
________________

Title:
Waiting On Approval
************************************
I actually have two of these ticket rules in place, depending on whether a user needs remote access or not.  As I mentioned earlier, our CEO has mandated that anyone requiring remote access first have his explicit approval before said access is granted.  So in the actual tickets, there are two things that need set (1) Remote Access - Yes or No, and (2) Approver - CEO or IT Director.  When set correctly (i.e. Remote Access - yes, Approver - CEO ~ or vice versa), this ticket rule fires off on ticket save and an email is sent to the appropriate person, who just needs to click on the link in the email to update the approval field from None to Approved or Rejected.  Inbound KBOX email routing needs to be working for this to go through.  If you want the opposite rule (i.e. Remote Access - No), just change the valued of HD_TICKET.CUSTOM_FIELD_VALUE13 in the last line of the select query from "Yes" to "No".
************************************

Order:
12

Notes:
Sends an email to CEO when a ticket's Status is Waiting On Approval and the Remote Access (VPN, Citrix) is Yes.

Frequency:
on Ticket Save

X Send an email for each result row

Subject:
[TICK:$id] NEW TICKET: $status_name

Email Column:
APPROVER

Email Body:
A ticket in the HR Helpdesk queue needs your approval, please review.

Ticket ID:                    $id
Created:                      $created
Issue:                        $issue
Category:                     $category
Status:                       $status_name
Remote Access (VPN/Citrix):   $remote

User:                         $fullname
Date of Hire:                 $dateofhire
Job Title:                    $jobtitle
Department:                   $department
Location:                     $location
Manager:                      $manager
___________________________________________________________________

To APPROVE opening this ticket, please click here:
<mailto:hr@company.com?subject=[TICK:$id]&body=@approval%20=%20Approved>

To REJECT opening this ticket, please click here:
<mailto:hr@company.com?subject=[TICK:$id]&body=@approval%20=%20Rejected>
___________________________________________________________________

Thanks,

Company IT

Select Query:

select 'ceo@company.com' as APPROVER, HD_TICKET.CUSTOM_FIELD_VALUE2 as FULLNAME, HD_TICKET.CUSTOM_FIELD_VALUE6 as DATEOFHIRE, HD_TICKET.CUSTOM_FIELD_VALUE5 as MANAGER, HD_TICKET.CUSTOM_FIELD_VALUE3 as DEPARTMENT, HD_TICKET.CUSTOM_FIELD_VALUE4 as JOBTITLE, HD_TICKET.CUSTOM_FIELD_VALUE0 as LOCATION, HD_TICKET.CUSTOM_FIELD_VALUE13 as REMOTE, HD_TICKET.TITLE as Issue, HD_CATEGORY.NAME as CATEGORY, 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                       
                        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_STATUS.NAME = 'Waiting On Approval') AND HD_TICKET.CUSTOM_FIELD_VALUE13 = 'Yes') and HD_TICKET.HD_QUEUE_ID = 2 )
________________

Answered 06/01/2012 by: jverbosk
Red Belt

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

Share