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!

Answer Summary:
Cancel
2 Comments   [ + ] Show Comments

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.
  • Hi

    I want to email the ticket details to submitter after submitting the tickets.. any rules to do this?
Please log in to comment

Community Chosen Answer

3

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.  

 

 

 

 

 

Answered 01/14/2014 by: Jbr32
Tenth Degree Black Belt

  • 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) ")
    • 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.
      • You should also remove


        and (HD_TICKET.ID = 3786)

        but that is probably there for testing
  • Gotcha. Where would I find the queue ID? I only have one queue and replaced "citizant with "1" but still got the same error.
    • 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.
      • 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
      • 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
  • 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) ")
    ---------------------------------------------------------------------------------------------------------------
    • Remove <1> and change it to 1
      • Boom. That did it. Thanks so much for your help with this!
      • Great glad to hear!
  • This worked for me when the dell support article did not. THANK YOU SO MUCH!!!
    • Great glad to hear
      • 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 - 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.
Please log in to comment

Answers

1

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.

Answered 01/14/2014 by: grayematter
Fourth Degree Black Belt

  • 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.
Please log in to comment
Answer this question or Comment on this question for clarity