I'm sorry if this has been asked, but I can't find the answers anywhere in the archive for current versions of the K1000 appliance. Everything I can find points back to documents located on the kace.com domain that no longer exists.

We are just setting up the Service Desk component of the K1000 appliance and would like all technicians to get an e-mail whenever a new ticket is created. Ideally it would run within a few minutes of the ticket being received. Maybe I'm missing something, but this should be a core component of any service desk system. 

Thanks for anything you can provide!
2 Comments   [ + ] Show Comments

Comments

  • When I copy the query you listed, all I get is "There were syntax errors in your query." But I don't see any indication of how to fix them. I changed the URL to my KBox, and I changed the email to my address. Is there anything else that should be changed? This is all in a SELECT query, nothing in an UPDATE query, right?
    • That's it as far as changes. Make sure you get the first word "select" as well all the way to "having 1=1". ITNinja does this copy append thing so once you paste look at the end and delete the part automatically inserted by ITNinja
  • Have you seen this blog? Might break it down for you as well

    http://www.itninja.com/blog/view/new-ticket-notify-all-owners
    • This worked for me. Thank you both for your help!
Please log in to comment

Answers

1
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://servicedesk.mxholdingsus.com/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@domain.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




Check the Email each recipient in query results
Subject: [TICK:$ticknum] NEW TICKET: $title
Column containin email address: NEWTICKETEMAIL
Body:
$submitter_fname has opened a ticket.  
The submission was:
Ticket: $ticknum
From: $submitter_fname ($submitter_email)
Category: $category
Priority: $priority
Status: $status
Severity: $impact
Opening Comment: $comment

For more details go to:
http://servicedesk.mxholdingsus.com/userui/ticket?ID=$id
Answered 01/17/2015 by: h2opolo25
Red Belt

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