THE FOLLOWING CODE HAS BEEN EDITED april 27th, 2010: It works now, but I need to be able to display 2 or more rows.

 

Hello. I'm starting to learn sql scripting but I'm still a bit new at it. I found another thread here that lead me in the right directon, but nothing there was working for me (believe me, I tried it all). Currently running version 5.3.45496 if that matters.

What I'd like to do is, after 1 hour has passed, if there are tickets that are still unassigned, it sends an email notifiying this fact. (Ideally I'd also like it to ignore the saturday/sunday hours, but I can work that out later)

So far I've come up with the following horrendous amount of code which is probably 90% unneeded. The reason I have so much is because I took it from an existing code that I know works, one that sends out a notification email whenever a new ticket is submitted. SO there's probably a lot I don't need, but I know that info works, so I left it and just tried to change the outcome.  Also, I took out my email for now, but rest assured it's there in the final version:

 SELECT
      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 CREATEDON, -- $createdon
      HD_TICKET.CREATED AS CREATED,
      -- 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
      \email@company' AS NEWTICKETEMAIL -- $newticketemail
 
FROM HD_TICKET
     /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.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 (HD_TICKET.CREATED < SUBDATE(NOW(),INTERVAL 1 HOUR)) AND S.NAME = 'new' */

WHERE HD_TICKET.CREATED < SUBDATE(NOW(), INTERVAL 1 HOUR) AND S.NAME ='new'


ORDER BY HD_TICKET.CREATED ASC

 

 

The email that is sent is:

 

The following ticket(s) are still unassigned in KACE.

Please log into http://k1000/ and assign them: 
- TICKET #$ticknum, a $priority priority ticket, was opened by $submitter_fname was created on $createdon.

Email for result row is checked off and simple enough, and then the update query (which im not sure if it's needed) is:

 update HD_TICKET
    set HD_TICKET.CC_LIST = '1'
  where
        (HD_TICKET.ID in (<TICKET_IDS>))

 

Everything else I've tried (the simpler, shorter looking code) gave me errors on the run log, which is why I'm posting this code, since it's the closest I've seem to come to achieving what I need. I appreciate any and all help with me on this matter! I realize I'm new but hopefully I'm learning as we go!

OTHER INFO THAT MIGHT BE IMPORTANT:
- I have only one queue

Answer Summary:
Cancel
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

2

I think you need to change your

(T.CREATED < INTERVAL 1 HOUR) to

(HD_TICKET.CREATED > DATE_SUB(NOW(),INTERVAL 1 HOUR))

 

Do you have a tool like MySQL WorkBench where you can test your queries before trying the run them as a rule? If not, then I highly recommend installing it. Editing the SQL query becomes much easier and then you can copy and paste it into the rule.

 

Answered 04/26/2012 by: chucksteel
Red Belt

  • Thanks for the mysql idea... it does make it much easier! I've updated the code above.... it now displays an answer like I want, but I can't figure out how to get it to display two or more rows in the email. I've included that as well. Any ideas?
  • If you want an email that will include a list of unassigned tickets, then that needs to be done with a scheduled report, not a ticket rule. A ticket rule would normally only act per ticket. You could possible create a rule that would do it, but it would be much more complicated.
Please log in to comment
Answer this question or Comment on this question for clarity