/build/static/layout/Breadcrumb_cap_w.png

Issue with SQL code: Kace Service Desk New Ticket Notification - Email

Good Day Everyone,

   How is everyone doing? The issue at hand is dealing with the Kace K1000's Service Desk Email notification. I am currently trying to create a custom ticket rule that emails a group of people whne a new ticket is generated within a queue.

 

A Dell Kace tech point me in the direction of this URL - 

http://www.kace.com/support/resources/kb/solutiondetail?sol=SOL111222

 

This URL contains the SQL code below that is either full of errors or does not work. Can someone assist me the proper SQL code that generates an email to let my IT Department of when a user opens a new ticket via the Kace web interface.

SQL Code Does Not Work:

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://kbox/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

'helpdesk@mycompany.com' 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.DESCRIPTION LIKE 'TICKET CREATED%'

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

GROUP BY HD_TICKET.ID

HAVING 1=1

Posted 10 years ago 8583 views

Comments

  • Hi

    I do not know if the above a direct copy of what you have currently as a new ticket rule.

    If it is you need to change two things still:

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

    The above needs to be changed to the url of your servicedesk.

    'helpdesk@mycompany.com' AS NEWTICKETEMAIL -- $newticketemail

    The above needs to be changed to the distrubution email group of your IT team.

    I still dont know what options you have enabled on the actual ticket rule as if you only added the above code to one section it still will not work.

    You will need to add the sql code in the
    " Send an email for each result row" option box. - GoranK 10 years ago
  • This is the SQL that we use:

    Note you will probably need to change the HD_TICKET.HD_QUEUE_ID to match up with yours

    ______________________________
    select HD_TICKET.ID,
    HD_TICKET.ID as TICKNUM,
    'someone@gmail.com, someone@gmail.com, someone@gmail.com, someone@gmail.com, someone@gmail.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 = 10 and
    HD_STATUS.NAME != 'Closed'






    ______________________________


    Other settings:
    Frequency = on ticket save
    Send an email for each result now (checked)
    Subject = Support Department - [TICK:$ticknum] $title (NEW)
    Email Column = EMAILCC
    Email Body:
    $initial_comment
    ****END OF MESSAGE*****

    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 - Jbr32 10 years ago
    • Finally got it working. Thanks JBR32 - shandy4473 10 years ago
  • Thanks Jbr32 but it's not working. - shandy4473 10 years ago
    • If you are manually executing the code it will not work as it references a variable that only exists during runtime <change_id>

      If you let it just run in ticket save it should work - Jbr32 10 years ago
      • I've been banging my head against the wall all morning trying to figure out what this syntax (<change_id>) means so that I can test modifications to this query. Similar syntax is in many KACE SQL queries on these forums and in the KB. This explanation is too important to be buried in the middle of a thread like this. (And if it is featured somewhere more prominently, it isn't easily searchable... I've not found it anywhere yet.) - MichaelMc 9 years ago
    • Also what was the error? - Jbr32 10 years ago
  • Hi shandy. I work KACE Technical Support and have been vested in this particular KACE article. Is the SQL you provided your actual SQL statement altered for your configuration? If not please provide the SQL you are using so I can review. - KACE_Mary 10 years ago
    • Or if you prefer email me the SQL statement mary_scherich@dell.com - KACE_Mary 10 years ago
  • Right now the above SQL code partially works. The email portion of it doesnt work. If you select the option of "Send query results to someone" - then an email is sent upon opening a new ticket which is the desired outcome.

    However without selecting the option of "Send query results to someonme" - the script doesnt email - shandy4473 10 years ago
  • For a product that costs around 10K - customers should not be experiencing email issues such as this. A ticketing system should out of the box have a built in system for sending email alerts for new tickets.

    I am very surprised by this

    Thanks
    Steve - shandy4473 10 years ago
    • Steve ,

      please use what is currently listed in the FAQ http://www.kace.com/support/resources/kb/solutiondetail?sol=SOL111222

      that has been tested and is fully working. - olgonzo 10 years ago
      • Hi Olgonzo - I actually had a level 1 tech work with me and we could not get this working.

        Anyhow - I am using a script posted by another user which works.

        Thanks
        Steve - shandy4473 10 years ago
      • This does not work for me at all. I would think the same shandy4473 - JSenesap 8 years ago
      • the KB article works , i would use that. - olgonzo 8 years ago
  • For anyone having issue this script works:

    select HD_TICKET.ID,
    HD_TICKET.ID as TICKNUM,
    'your email address' 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 = #number and
    HD_STATUS.NAME != 'Closed'




    Please make sure the HD_TICKET.HD_QUEUE_ID is set to the proper queue number and email is set correctly as shown in top line of script -> ' your email address' as EMAILCC, (please place email in single quotes)

    check line -> Send an email for each result row
    Subject -> [TICK:$ticknum] NEW TICKET: $title
    Email Column -> EMAILCC
    Email Body: Whatever you want

    Thanks
    Steve - shandy4473 10 years ago
    • Hi Shandy,

      I need a little help. I've tried pasting your code into my k1000 and I never get an email. This is what I've got:

      select HD_TICKET.ID, HD_TICKET.ID as TICKNUM, 'name@mydomain.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'


      After i create a new ticket and look under the rules Last Run Log it gives me this:
      01/16/2015 10:42:03> Starting: 01/16/2015 10:42:03 01/16/2015 10:42:03> Executing Select Query... 01/16/2015 10:42:03> selected 1 rows 01/16/2015 10:42:03> Executing Update Query... 01/16/2015 10:42:03> mysql error: [1065: Query was empty] in EXECUTE("") 01/16/2015 10:42:03> Ending: 01/16/2015 10:42:03

      Sorry to bring up an old post, but could use a little help here. - BDEEN 9 years ago
This post is locked
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ