I want to create a rule that says if a ticked based on (create time) is x days old then then escalate to x and change status to Y.

I can do the 2nd part easily in the wizard.  How do I get the time date thing to work with the wizard?  If I cant, any recommendations.

Thanks in Advance.
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0
Here's what I did to make this happen....

Created a Timestamp Custom Field in the queue config and left it as hidden. Called it Escalation Time (DO NOT DELETE)

Created 4 rules....
1. When a new ticket is created, email the IT team and set the custom escalation field I created to the created time of the ticket.
2. If priority is Low and Escalated field is over 5 days then send an email to specific people and move priority to Medium and set the Escalated field to the current time.
3. If priority is Medium and Escalated field is over 5 days then send an email to specific people and move priority to High and set the Escalated field to the current time.
4. If priority is High and Escalated field is over 1 day then send an email to specific people and move priority to Critical and set the Escalated field to the current time.

I turned off escalation in the queue customization screen for everything but Critical. Critical I set at 4 hours.

I'll post the rules in comments to follow....
Answered 09/23/2014 by: h2opolo25
Red Belt

  • -- Email the email in the NEWTICKETEMAIL line when a new ticket is created
    -- Update a custom field set to timestamp that keeps track of escalation. All escalation rules use this rule.

    -- SELECT SQL:
    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://kacewebsite/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
    'email@email.com' AS NEWTICKETEMAIL -- CHANGE THIS EMAIL FOR EACH QUEUE
    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%'
    GROUP BY HD_TICKET.ID
    HAVING 1=1


    -- UPDATE SQL:
    update HD_TICKET T
    set T.CUSTOM_FIELD_VALUE3 = T.CREATED -- custom field set to timestamp that is used for escalation purposes
    where T.HD_QUEUE_ID = 1 -- select desired queue
    and T.ID in (<TICKET_IDS>)


    /*
    OPTION: Email each recipient in query results
    Subject: [TICK:$ticknum] NEW TICKET: $title
    Column contaning email addresses: NEWTICKETEMAIL
    Message:
    $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
    */


    -- CUSTOM FIELD NUMBERS IN THE GUI ARE ONE NUMBER HIGHER THAN IN MySQL
    -- ex: CUSTOM_FIELD_4 in the GUI is CUSTOM_FIELD_VALUE3 in MySQL
  • -- rule escalates low priority tickets to medium after 5 days. Custom field created as timestamp to track escalation.

    -- SELECT SQL:
    select T.ID, T.TITLE, O.FULL_NAME as owner, SU.FULL_NAME as submitter, 'email@email.com' as SUPPORT from HD_TICKET T -- EMAILS UPDATE TO THESE EMAILS
    left join HD_PRIORITY P on P.ID = T.HD_PRIORITY_ID
    left join HD_QUEUE Q on Q.ID = T.HD_QUEUE_ID
    left join HD_STATUS S on S.ID = T.HD_STATUS_ID
    left join USER O on O.ID = T.OWNER_ID
    left join USER SU on SU.ID = T.SUBMITTER_ID
    where P.NAME = 'Low'
    and S.State = 'Opened'
    and Q.NAME = 'TROUBLE TICKETS' -- change to desired queue
    and T.CUSTOM_FIELD_VALUE3 < subdate(now(), interval 5 day) -- change to desired time frame

    -- UPDATE SQL:
    update HD_TICKET T
    set T.HD_PRIORITY_ID = 1, -- change priority ID according to HD_PRIORITY table
    T.CUSTOM_FIELD_VALUE3 = Now()
    where T.HD_QUEUE_ID = 1 -- change to desired queue
    and (T.ID in (<TICKET_IDS>))

    /*
    OPTION: Append comment to ticket
    Comment: Ticket Escalated from Low to Medium
    Owners Only selected

    OPTION: Email each recipient in query results
    Subject: [TICK: $id] TICKET ESCALATION
    Column containing email addresses: SUPPORT
    Message:
    The ticket with:
    Ticket ID: $id
    Title: $title
    Owner: $owner
    Submitter: $submitter
    has been escalated!

    */

    -- CUSTOM FIELD NUMBERS IN THE GUI ARE ONE NUMBER HIGHER THAN IN MySQL
    -- ex: CUSTOM_FIELD_4 in the GUI is CUSTOM_FIELD_VALUE3 in MySQL
Please log in to comment
0
The date/time functions don't usually work correctly when you use the wizard so I normally have to edit the MySQL query manually to make them work. Do you have experience with MySQL?
Answered 08/18/2014 by: chucksteel
Red Belt

Please log in to comment
0
Here is my ticket rule.
 it sends and email to SUPPORT every 1:30h if nobody has done nothing on the ticket. there are two things that i would like to improve but i dont know how (if sb would help....it would be awesome)
-it sends an email EVERY 1:30h if it still doesnt suffer any change. i would like that it sends just one email after the first 1:30h without changes
-i would like that it works just in working hours from 8:00 to 18:00

->SELECT SQL

select  'somebody@yourcompany.br' as SUPPORT, HD_TICKET.TITLE AS TITLE,HD_TICKET.ID AS ID, 
HD_STATUS.NAME AS STATUS,Q.NAME as QUEUE,
                                                
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,

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.MODIFIED) end as AGE,
    
U2.FULL_NAME as SUBMITTER_FULLNAME,
U2.EMAIL as SUBMITTER_EMAIL,
        U1.FULL_NAME as OWNER_FULLNAME,
U1.EMAIL as OWNER_EMAIL
     
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

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 = HD_TICKET.MODIFIED
and ((unix_timestamp(NOW()) - unix_timestamp(HD_TICKET.MODIFIED) > '5400') and HD_TICKET.HD_QUEUE_ID = 7 )

GROUP BY HD_TICKET.TITLE, HD_TICKET.ID
 

->active the tickbox for sending and email for every destinary of the consult

-Tittle:Escalating
-Email:SUPPORT (here is where you choose the person or people who is gonna receive the alert. you have to write it in the select query)
-Message:
Tickets não atribuidos em 1:30h.

Creado:                   $sort_time_created
Titulo:                      $title
Ticket ID:                 $id
Solicitante:               $submitter_fullname
Corpo do chamado:     http://youraddressforservicedesk/adminui/ticket.php?ID=$id

Answered 11/21/2014 by: Paloma
Purple Belt

  • for the timetable GillySpy has solved it here: http://www.itninja.com/question/use-escalation-timer-with-a-different-field-sla-counter
  • Here it is the full rule with the problems solved.
    http://www.itninja.com/question/k1000-rules-just-one-notification-on-ticket-rule
Please log in to comment
Answer this question or Comment on this question for clarity

Share