I'm trying to create a query that every 15 minutes scans the Help Desk queue and sets all New tickets to Open. I have what I think is the right queries but they wont update using <TICKET_IDS>. It's a dissection of the wizard generated query.

 

Freq: 15 Minutes

Select Query:

SELECT *
FROM HD_TICKET 
JOIN HD_STATUS ON HD_TICKET.HD_STATUS_ID = HD_STATUS.ID
WHERE CREATED <= DATE_SUB(NOW(), INTERVAL 15 MINUTE) -- TICKETS OLDER THAN 15 MINUTES
AND HD_TICKET.HD_QUEUE_ID = 1 -- SERVICE DESK QUEUE
AND OWNER_ID = 0 -- UNASSIGNED TECHNICIAN
AND HD_STATUS.NAME = "NEW" -- NEW TICKETS

 

Update Query:

UPDATE HD_TICKET, HD_STATUS AS T5
SET HD_TICKET.HD_STATUS_ID = T5.ID,
HD_TICKET.TIME_OPENED = IF (T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED)
WHERE T5.NAME = 'Opened'
AND HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID
AND HD_TICKET.ID IN (<TICKET_IDS>)
1 Comment   [ + ] Show Comment

Comments

  • You have HD_TICKET.TIME_OPENED = to an IF statement in your update query, that's probably causing an issue.
Please log in to comment

Answers

1

I mean... you can simplify it further because if the rule is going to say ALL unassigned NEW tickets then you can set it to say simply that and just have the rule scheduled to run every 15 minutes.

 

So You can set it up SUPER simple.

 

FIRST OFF:  It's not working because you're using "select * from HD_TICKET" and TICKET_IDS is supposed to grab the first numeral value and when you run a select * that is NOT the first numeral, because it is not properly aliased.  Whenever making custom rules, it's been best practice for me to use the template given by KACE in an FAQ and adjust that. 

 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 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,
S.NAME AS STATUS,
I.NAME AS IMPACT,
CAT.NAME AS CATEGORY
FROM HD_TICKET
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
WHERE

I almost ALWAYS use this template (and adjust what fields I'm selecting) and it ensures that it works during a RUN NOW.  I would suggest the same.

 

For you, to keep this simple, you would just need to add "WHERE HD_TICKET.HD_STATUS_ID = "##" (Enter the status id, which you can get a list from SELECT * FROM HD_STATUS inside your MySQL workbench.)

and whatever else you want to the WHERE statement.  Then set the ticket rule to run every 15 minutes and you won't need the AGE modifier.

 

As far as your update statement, you can probably use the same one, but remember using SELECT * will NOT let you properly use TICKET_IDS.  I made this mistake too, and changed a couple hundred tickets at once.

 

 

 

Answered 10/16/2013 by: Wildwolfay
Red Belt

  • TL;DR: Don't use SELECT * with TICKET_IDS.

    At least from my experience.
Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share