/build/static/layout/Breadcrumb_cap_w.png

Blog Posts tagged with Kace Ticket rule

Ask a question

5.4 Ticket Rule for auto assigning Computer Asset

First make sure that you have an end user attached under computer asset info.

To check go to  Computer-> Inventory. Scroll to the bottom and click asset then click Edit this asset. You should see somethign like the following.  

If you have this then the following rule will auto attach this computer when a ticket is opened by this user.

 

SETTING* Frequency: On Save

*********************SELECT QUERY********************************

select HD_TICKET.*,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
HD_TICKET.ID as TICKET_ID,
STATE,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved',
if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
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 = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_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 (( (1 in (select 1 from MACHINE where HD_TICKET.MACHINE_ID = 0)) ) and HD_TICKET.HD_QUEUE_ID = 1 )

**************************END SELECT***************************************

SETTING* Run an update query, using the results from the one above

*******************UPDATE QUERY********************************************
UPDATE
HD_TICKET Inner Join
USER On HD_TICKET.SUBMITTER_ID = USER.ID Inner Join
MACHINE On USER.USER_NAME = MACHINE.USER SET HD_TICKET.MACHINE_ID = MACHINE.ID WHERE HD_TICKET.ID IN (<TICKET_IDS>)

*************************END UPDATE****************************************

View comments (3)

Ticket Rule: Notify on New Ticket

If you find you cant get a notify rule to work in 5.4 try the following.

To implement a new rule Go to 

Service Desk --> Configuration --> Queues --> Click on your queue name Scroll to the bottom to custom rules and click [Customize]

Select Choose action -> Add new

Simply click next and save through the wizard to get to the input screen and then change your setup as follows.

Give it a name

Run on Ticket Save

***************************SELECT QUERY**********************************

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

***************************END SELECT**********************************

*************************EMAIL CONTENT*********************************

$submitter_fname,
Your ticket '$title'
Was recieved at $created
TICKET DETAILS
********************
$history
Thanks for your business,
The Help desk Team

**************************END EMAIL************************************

 

The below pic is the setup of your email fields.

 

*NOTE

If you wish to use this as a distribution list then change the line in the SQL select query that reads 

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

You will want to resplace with your email address to send to

Then replace the Email Column pictured above from SUBMITTER_EMAIL to NEWTICKETEMAIL

 

View comments (6)

K1000: Unassigned Aging ticket alert

This is an update to the Aging ticket rule. It will alert you on any ticket open more than 15 minutes.

 

ISSUE: You want to be alerted to tickets in queue that have been unassigned for longer than 15 minutes.

RESOLUTION: Apply the following ticket rule as follows.

 

Your ticket will be setup as follows;

 

Your SQL Select

 

****************************BEGIN SQL******************************

select HD_TICKET.*,
HD_STATUS.NAME AS STATUS_NAME,
date_format(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME,
'Dornan@dellkace.local' as QUEUEADMIN
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
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 = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_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 TIME_TO_SEC(TIMEDIFF(NOW(),HD_TICKET.CREATED)) > 900
and HD_TICKET.OWNER_ID = 0
and HD_STATUS.STATE != 'closed'
and HD_TICKET.HD_QUEUE_ID = 1

 

************************************END SQL******************************

To change the TO address edit the following Line

 'Dornan@dellkace.local' as QUEUEADMIN
Be the first to comment

K1000: Prevent Email/Ticket Reopen on Computer/Asset deletion

ISSUE: When you delete a computer from Inventory all tickets in helpdesk send notification emails

 

RESOLUTION: Apply the following Custom Ticket Rule.

 

Setup as below.

 

**********************SELECT QUERY************************

select -- ticket fields
HD_TICKET.ID, -- $id
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID AND C.ID=<CHANGE_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 = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_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
( C.COMMENT LIKE '%Machine % was deleted%' OR C.COMMENT LIKE 'User % was deleted%')
and HD_STATUS.STATE = 'Closed'

***********************END SELECT*************************

**********************UPDATE QUERY************************

update HD_TICKET_CHANGE
set
HD_TICKET_CHANGE.NOTIFY_USERS='', /*remove all users from notification list */
OWNERS_ONLY_DESCRIPTION=
TRIM(BOTH '\n' FROM(REPLACE (CONCAT(TRIM(BOTH '\n' FROM
OWNERS_ONLY_DESCRIPTION), '\n',
'Ticket Rule: Suppressing email notification for user/machine deletion'
,'\n') ,'\n\n','\n')))
where
(HD_TICKET_CHANGE.ID = (<CHANGE_ID>))

************************END QUERY*************************

View comments (7)

K1000: Auto Overdue aging tickets

This ticket rule will take any ticket that is in an opened state for more than 7 days and move the priority to a csutom made one of "Overdue"

First you will need to have made a custom Priority state and know the ID of it in the HD_TICKET table. You can find how to do that here.

 

Now we will make our rule. First the select query 

 

*************************************************

select HD_TICKET.*,
HD_STATUS.NAME AS STATUS_NAME,
HD_STATUS.ORDINAL as STATUS_ORDINAL,
HD_IMPACT.ORDINAL as IMPACT_ORDINAL,
HD_CATEGORY.ORDINAL as CATEGORY_ORDINAL,
HD_PRIORITY.ORDINAL as PRIORITY_NUMBER,
STATE,
if((datediff(DUE_DATE, now()) = 0), 2, if((datediff(DUE_DATE, now())<0), 1, 3)) as SORT_OVERDUE_STATUS,
if(unix_timestamp(TIME_OPENED) > 0, TIME_OPENED, 1<<62) as SORT_TIME_OPENED,
if(unix_timestamp(TIME_STALLED) > 0, TIME_STALLED, 1<<62) as SORT_TIME_STALLED,
if(unix_timestamp(TIME_CLOSED) > 0, TIME_CLOSED, 1<<62) as SORT_TIME_CLOSED,
if(unix_timestamp(ESCALATED) > 0, ESCALATED, 1<<62) as SORT_ESCALATED,
if(unix_timestamp(HD_TICKET.CREATED) > 0, HD_TICKET.CREATED, 1<<62) as SORT_TIME_CREATED,
if(unix_timestamp(HD_TICKET.MODIFIED) > 0, HD_TICKET.MODIFIED, 1<<62) as SORT_MODIFIED,
if(unix_timestamp(HD_TICKET.DUE_DATE) > 0, HD_TICKET.DUE_DATE, 1<<62) as SORT_DUE_DATE,
case upper(STATE)
when 'CLOSED' then unix_timestamp(HD_TICKET.TIME_CLOSED) - unix_timestamp(HD_TICKET.TIME_OPENED)
when 'OPENED' then unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.TIME_OPENED)
else unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.CREATED) end as AGE,
if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME) as OWNER_NAME,
U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL,
if (U1.ID is null, 'z', concat('a', if ((LENGTH(U1.FULL_NAME) = 0), U1.USER_NAME, U1.FULL_NAME))) as SORT_OWNER_NAME,
if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME) as SUBMITTER_NAME,
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
if (U2.ID is null, 'z', concat('a', if ((LENGTH(U2.FULL_NAME) = 0), U2.USER_NAME, U2.FULL_NAME))) as SORT_SUBMITTER_NAME,
if (U3.ID is null, 'z', concat('a', if ((LENGTH(U3.FULL_NAME) = 0), U3.USER_NAME, U3.FULL_NAME))) as SORT_APPROVER_NAME,
if(APPROVAL='rejected', 'Rejected', if(APPROVAL='info', 'More Info Needed', if(APPROVAL='approved', 'Approved', if(APPROVER_ID>0, 'Pending', '')))) as APPROVAL_STATUS,
Q.NAME as QUEUE_NAME
from (HD_TICKET, HD_PRIORITY, HD_STATUS, HD_IMPACT, HD_CATEGORY)
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 = HD_TICKET.APPROVER_ID
LEFT JOIN HD_QUEUE Q on Q.ID = HD_TICKET.HD_QUEUE_ID
LEFT JOIN MACHINE M1 on M1.ID = HD_TICKET.MACHINE_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 HD_TICKET.CREATED > DATE_ADD(NOW(), INTERVAL -7 DAY)
AND (HD_STATUS.STATE != 'closed')
AND HD_PRIORITY_ID != 2
AND HD_TICKET.HD_QUEUE_ID = 1

*************************************************

And the update query.

*************************************************

update HD_TICKET
set HD_TICKET.HD_PRIORITY_ID = 10
where HD_TICKET.HD_QUEUE_ID = 1 and
(HD_TICKET.ID in (<TICKET_IDS>))

*************************************************

Several things to note...

1. This rule is hard coded to run on the primary queue 

where HD_TICKET.HD_QUEUE_ID = 1

I like to do this with update queuries to make sure there are no unexpected surprises occuring in other queues behind the scenes. You should be extremely careful if using this on a queue other than 1 for the same reason. MAKE SURE you change the queue id before running.

2. I also hard coded the priority in 

set HD_TICKET.HD_PRIORITY_ID = 10
Make sure the id in the update query matches the one you got in the link above.
I always recommend having a test queue for testing update queries and hard coding the queue number to avoid mistakes that can cost data, time, and money, maybe even your job.
NEVER TEST A QUERY ON A LIVE QUEUE!
Be the first to comment
Showing 1 - 5 of 25 results

Top Contributors

Talk About None