KACE Product Support : K1000 Email on Ticket Creation

K1000 Email on Ticket Creation

Hi All-

I assume this has been dicussed before, but I'm running into issues when trying to create a custom rule to notify via email when any ticket is created. I've used the pre-made SQL code at http://www.kace.com/support/resources/kb/solutiondetail?sol=SOL111222 but still cannot get it to go.

All I need is the code for sending a message to "alerts@mycompany.com" when any ticket is created.

Any light you can shed? Thanks in advance!

3 Comments [ + ] Show comments

You need to figure out the queue ID number and replace the "<Citizant>" entry. There should be no quotes and only a number; e.g. 6. To determine your main queue ID number, go to Service Desk, click on configuration, click on Queues, and then click on your main queue. Now, notice the number in the URL; should read something like http://kbox/......queue.php?ID=10 In this example the queue ID is number 10. If you do not see anything in the address bar, replace kbox/admin with kbox/adminui which will show you the full URLS. Let me know if this works. - Jbr32 10 years ago
Hi

I want to email the ticket details to submitter after submitting the tickets.. any rules to do this? - rahimpal 7 years ago
under which table we have this field TICKETCHANGE.DESCRIPTION like '%Ticket Created%' and? - rahimpal 7 years ago

Answers (2)

Answer Summary:

Posted by: Jbr32 10 years ago

Here is another suggestion, we use this to notify internal IT professionals when a new ticket comes in:

select HD_TICKET.ID,

HD_TICKET.ID as TICKNUM,

'someEmail@company.com, someOneElse@company' as EMAILCC,

HD_TICKET.TITLE,

U1.USER_NAME as OWNER_NAME,

U3.USER_NAME as LASTINPUTNAME,

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,

STATE,

U1.FULL_NAME as OWNER_FULLNAME,

U1.EMAIL as OWNER_EMAIL,

U2.USER_NAME as SUBMITTER_NAME,

U2.FULL_NAME as SUBMITTER_FULLNAME,

U2.EMAIL as SUBMITTER_EMAIL,

U3.EMAIL as UPDATEREMAIL,

U3.FULL_NAME as UPDATERNAME,

UNIX_TIMESTAMP(TICKETCHANGE.TIMESTAMP),

TICKETCHANGE.COMMENT as COMMENT,

TICKETINITIAL.COMMENT as INITIAL_COMMENT,

TICKETCHANGE.DESCRIPTION as CHANGE_DESCRIPTION,

HD_CATEGORY.CC_LIST AS CATEGORYCC,

HD_CATEGORY.NAME AS CATEGORY_NAME,

U2.LOCATION AS SUBMITTER_LOCATION,

U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,

HD_PRIORITY.NAME AS TICKET_PRIORITY,

HD_QUEUE.NAME AS QUEUE_NAME

from ( HD_TICKET,

HD_PRIORITY,

HD_STATUS,

HD_IMPACT,

HD_CATEGORY)

JOIN HD_TICKET_CHANGE TICKETCHANGE ON TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID

and TICKETCHANGE.ID=<CHANGE_ID>

JOIN HD_TICKET_CHANGE TICKETINITIAL ON TICKETINITIAL.HD_TICKET_ID = HD_TICKET.ID

and TICKETINITIAL.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.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 = TICKETCHANGE.USER_ID

left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_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

TICKETCHANGE.DESCRIPTION like '%Ticket Created%' and

HD_TICKET.HD_QUEUE_ID = <CHANGE THIS TO THE QUEUE ID> and

HD_STATUS.NAME != 'Closed'

__________________________________________

We run this on ticket save and you want to send an email for each result

________________________________________

Please note that a new ticket has come into $queue_name Queue!

Here are the details:

Created: $created in $queue_name

Created by: $submitter_fullname at $submitter_email

URL http://kbox/adminui/ticket?ID=$ticknum

*******MESSAGE*********

$initial_comment

****END OF MESSAGE*****

_______________________________

For the email column, put in "EMAILCC" without the $

For the subject field, you the $ sign.

Write back if you have issues.

Comments:

Hi, thanks so much for this. It looks like I'm close, but still no go. Whenever a ticket is saved, this rule runs but it throws an error, see below (line 3). Is the queue ID different than the queue name? I only have one queue in my setup.

Thanks again for your help!

---------------------------------------------------------------------------------------------------------------------
01/14/2014 18:31:01> Starting: 01/14/2014 18:31:01
01/14/2014 18:31:01> Executing Select Query...
01/14/2014 18:31:01> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<Citizant> and

HD_STATUS.NAME != 'Closed'
and (HD_TICKET.ID = 3786)' at line 95] in EXECUTE("select HD_TICKET.ID,

HD_TICKET.ID as TICKNUM,

'alerts@citizant.com' as EMAILCC,

HD_TICKET.TITLE,

U1.USER_NAME as OWNER_NAME,

U3.USER_NAME as LASTINPUTNAME,

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,

STATE,

U1.FULL_NAME as OWNER_FULLNAME,

U1.EMAIL as OWNER_EMAIL,

U2.USER_NAME as SUBMITTER_NAME,

U2.FULL_NAME as SUBMITTER_FULLNAME,

U2.EMAIL as SUBMITTER_EMAIL,

U3.EMAIL as UPDATEREMAIL,

U3.FULL_NAME as UPDATERNAME,

UNIX_TIMESTAMP(TICKETCHANGE.TIMESTAMP),

TICKETCHANGE.COMMENT as COMMENT,

TICKETINITIAL.COMMENT as INITIAL_COMMENT,

TICKETCHANGE.DESCRIPTION as CHANGE_DESCRIPTION,

HD_CATEGORY.CC_LIST AS CATEGORYCC,

HD_CATEGORY.NAME AS CATEGORY_NAME,

U2.LOCATION AS SUBMITTER_LOCATION,

U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,

HD_PRIORITY.NAME AS TICKET_PRIORITY,

HD_QUEUE.NAME AS QUEUE_NAME

from ( HD_TICKET,

HD_PRIORITY,

HD_STATUS,

HD_IMPACT,

HD_CATEGORY)

JOIN HD_TICKET_CHANGE TICKETCHANGE ON TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID

and TICKETCHANGE.ID=6459

JOIN HD_TICKET_CHANGE TICKETINITIAL ON TICKETINITIAL.HD_TICKET_ID = HD_TICKET.ID

and TICKETINITIAL.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.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 = TICKETCHANGE.USER_ID

left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_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

TICKETCHANGE.DESCRIPTION like '%Ticket Created%' and

HD_TICKET.HD_QUEUE_ID = <Citizant> and

HD_STATUS.NAME != 'Closed'
and (HD_TICKET.ID = 3786) ") - gmercedesbenz 10 years ago
- HD_TICKET.HD_QUEUE_ID = <Citizant> is the error. The queue id should be a number from HD_QUEUE, not the name of the queue. - grayematter 10 years ago
  - You should also remove
    
    and (HD_TICKET.ID = 3786)
    
    but that is probably there for testing - Jbr32 10 years ago
Gotcha. Where would I find the queue ID? I only have one queue and replaced "citizant with "1" but still got the same error. - gmercedesbenz 10 years ago
- You need to figure out the queue ID number and replace the "<Citizant>" entry. There should be no quotes and only a number; e.g. 6. To determine your main queue ID number, go to Service Desk, click on configuration, click on Queues, and then click on your main queue. Now, notice the number in the URL; should read something like http://kbox/......queue.php?ID=10 In this example the queue ID is number 10. If you do not see anything in the address bar, replace kbox/admin with kbox/adminui which will show you the full URLS. Let me know if this works. - Jbr32 10 years ago
  - Ahhh I see. My queue ID is 1. However, after replacing it, I still get the error below when it runs.
    
    01/15/2014 10:35:03> Starting: 01/15/2014 10:35:03
    01/15/2014 10:35:03> Executing Select Query...
    01/15/2014 10:35:03> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<1> and - gmercedesbenz 10 years ago
  - Try creating a ticket and see if you get a notification. I have run into issues where if I directly run a ticket rule I get that error, but in general it works. Can you post back your SQL - Jbr32 10 years ago
Here is the query I'm using, below is the result.

-----------------------------------------------------------------------------
select HD_TICKET.ID,

HD_TICKET.ID as TICKNUM,

'alerts@citizant.com' as EMAILCC,

HD_TICKET.TITLE,

U1.USER_NAME as OWNER_NAME,

U3.USER_NAME as LASTINPUTNAME,

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,

STATE,

U1.FULL_NAME as OWNER_FULLNAME,

U1.EMAIL as OWNER_EMAIL,

U2.USER_NAME as SUBMITTER_NAME,

U2.FULL_NAME as SUBMITTER_FULLNAME,

U2.EMAIL as SUBMITTER_EMAIL,

U3.EMAIL as UPDATEREMAIL,

U3.FULL_NAME as UPDATERNAME,

UNIX_TIMESTAMP(TICKETCHANGE.TIMESTAMP),

TICKETCHANGE.COMMENT as COMMENT,

TICKETINITIAL.COMMENT as INITIAL_COMMENT,

TICKETCHANGE.DESCRIPTION as CHANGE_DESCRIPTION,

HD_CATEGORY.CC_LIST AS CATEGORYCC,

HD_CATEGORY.NAME AS CATEGORY_NAME,

U2.LOCATION AS SUBMITTER_LOCATION,

U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,

HD_PRIORITY.NAME AS TICKET_PRIORITY,

HD_QUEUE.NAME AS QUEUE_NAME

from ( HD_TICKET,

HD_PRIORITY,

HD_STATUS,

HD_IMPACT,

HD_CATEGORY)

JOIN HD_TICKET_CHANGE TICKETCHANGE ON TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID

and TICKETCHANGE.ID=<CHANGE_ID>

JOIN HD_TICKET_CHANGE TICKETINITIAL ON TICKETINITIAL.HD_TICKET_ID = HD_TICKET.ID

and TICKETINITIAL.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.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 = TICKETCHANGE.USER_ID

left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_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

TICKETCHANGE.DESCRIPTION like '%Ticket Created%' and

HD_TICKET.HD_QUEUE_ID = <1> and

HD_STATUS.NAME != 'Closed'
---------------------------------------------------------------------------------------------------------

When I submitted a ticket, it was created, but no notification message was sent. Here is the run log.

------------------------------------------------------------------------------------------
01/15/2014 10:45:26> Starting: 01/15/2014 10:45:26
01/15/2014 10:45:26> Executing Select Query...
01/15/2014 10:45:26> mysql error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '<1> and

HD_STATUS.NAME != 'Closed'
and (HD_TICKET.ID = 3791)' at line 95] in EXECUTE("select HD_TICKET.ID,

HD_TICKET.ID as TICKNUM,

'alerts@citizant.com' as EMAILCC,

HD_TICKET.TITLE,

U1.USER_NAME as OWNER_NAME,

U3.USER_NAME as LASTINPUTNAME,

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,

STATE,

U1.FULL_NAME as OWNER_FULLNAME,

U1.EMAIL as OWNER_EMAIL,

U2.USER_NAME as SUBMITTER_NAME,

U2.FULL_NAME as SUBMITTER_FULLNAME,

U2.EMAIL as SUBMITTER_EMAIL,

U3.EMAIL as UPDATEREMAIL,

U3.FULL_NAME as UPDATERNAME,

UNIX_TIMESTAMP(TICKETCHANGE.TIMESTAMP),

TICKETCHANGE.COMMENT as COMMENT,

TICKETINITIAL.COMMENT as INITIAL_COMMENT,

TICKETCHANGE.DESCRIPTION as CHANGE_DESCRIPTION,

HD_CATEGORY.CC_LIST AS CATEGORYCC,

HD_CATEGORY.NAME AS CATEGORY_NAME,

U2.LOCATION AS SUBMITTER_LOCATION,

U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,

HD_PRIORITY.NAME AS TICKET_PRIORITY,

HD_QUEUE.NAME AS QUEUE_NAME

from ( HD_TICKET,

HD_PRIORITY,

HD_STATUS,

HD_IMPACT,

HD_CATEGORY)

JOIN HD_TICKET_CHANGE TICKETCHANGE ON TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID

and TICKETCHANGE.ID=6464

JOIN HD_TICKET_CHANGE TICKETINITIAL ON TICKETINITIAL.HD_TICKET_ID = HD_TICKET.ID

and TICKETINITIAL.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.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 = TICKETCHANGE.USER_ID

left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_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

TICKETCHANGE.DESCRIPTION like '%Ticket Created%' and

HD_TICKET.HD_QUEUE_ID = <1> and

HD_STATUS.NAME != 'Closed'
and (HD_TICKET.ID = 3791) ")
--------------------------------------------------------------------------------------------------------------- - gmercedesbenz 10 years ago
- Remove <1> and change it to 1 - Jbr32 10 years ago
  - Boom. That did it. Thanks so much for your help with this! - gmercedesbenz 10 years ago
  - Great glad to hear! - Jbr32 10 years ago
This worked for me when the dell support article did not. THANK YOU SO MUCH!!! - ComicMatthew 8 years ago
- Great glad to hear - Jbr32 8 years ago
  - I appreciate you taking the time to lay this all out but I'm not able to get it work properly. Notification emails are not going out when tickets are created via email. Here's what I have:
    
    select HD_TICKET.ID,
    
    HD_TICKET.ID as TICKNUM,
    
    'mycompany@mycompany.com' as EMAILCC,
    
    HD_TICKET.TITLE,
    
    U1.USER_NAME as OWNER_NAME,
    
    U3.USER_NAME as LASTINPUTNAME,
    
    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,
    
    STATE,
    
    U1.FULL_NAME as OWNER_FULLNAME,
    
    U1.EMAIL as OWNER_EMAIL,
    
    U2.USER_NAME as SUBMITTER_NAME,
    
    U2.FULL_NAME as SUBMITTER_FULLNAME,
    
    U2.EMAIL as SUBMITTER_EMAIL,
    
    U3.EMAIL as UPDATEREMAIL,
    
    U3.FULL_NAME as UPDATERNAME,
    
    UNIX_TIMESTAMP(TICKETCHANGE.TIMESTAMP),
    
    TICKETCHANGE.COMMENT as COMMENT,
    
    TICKETINITIAL.COMMENT as INITIAL_COMMENT,
    
    TICKETCHANGE.DESCRIPTION as CHANGE_DESCRIPTION,
    
    HD_CATEGORY.CC_LIST AS CATEGORYCC,
    
    HD_CATEGORY.NAME AS CATEGORY_NAME,
    
    U2.LOCATION AS SUBMITTER_LOCATION,
    
    U2.WORK_PHONE AS SUBMITTER_WORK_PHONE,
    
    HD_PRIORITY.NAME AS TICKET_PRIORITY,
    
    HD_QUEUE.NAME AS QUEUE_NAME
    
    from ( HD_TICKET,
    
    HD_PRIORITY,
    
    HD_STATUS,
    
    HD_IMPACT,
    
    HD_CATEGORY)
    
    JOIN HD_TICKET_CHANGE TICKETCHANGE ON TICKETCHANGE.HD_TICKET_ID = HD_TICKET.ID
    
    and TICKETCHANGE.ID=<CHANGE_ID>
    
    JOIN HD_TICKET_CHANGE TICKETINITIAL ON TICKETINITIAL.HD_TICKET_ID = HD_TICKET.ID
    
    and TICKETINITIAL.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.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 = TICKETCHANGE.USER_ID
    
    left join HD_QUEUE on HD_QUEUE.ID = HD_TICKET.HD_QUEUE_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
    
    TICKETCHANGE.DESCRIPTION like '%Ticket Created%' and
    
    HD_TICKET.HD_QUEUE_ID = 8 and
    
    HD_STATUS.NAME != 'Closed' - evenflow 8 years ago
  - Evenflow - I would like run your query in mysql workbench, you will have to edit it a bit (specifically remove the line and TICKETCHANGE.ID=<CHANGE_ID>) and see if you can get results. I looked at your query briefly and thought perhaps it was the queue ID number not configured for your environment. - Jbr32 8 years ago

Posted by: grayematter 10 years ago

Are the tickets unassigned or are they assigned to an individual? If they are assigned, the user should be receiving email notifications. If they are unassigned, take a look at http://www.itninja.com/question/email-ticket-owners-label-on-new-unassigned-ticket for some ideas.

Comments:

All tickets that come in are unassigned, until they are picked up by someone. Right now, all tickets are up for grabs, meaning any rep can pickup any ticket, regardless of the ticket content. If they can't solve the issue, then it's re-assigned to another person. - gmercedesbenz 10 years ago

Answer this question

Posted by:

View more:

KACE Product Support Questions

question