I am trying to come up with a ticket rule to send service desk personnel an e-mail if a ticket that has been created is in a status of New - Not Started for 2 hours or more.

This is in order to keep a ticket from being dormant for too long if the technician assigned to it is not available for a while so service desk personnel can assign it to someone else or at least check with the currently assigned tech.


Does anyone have some pointers as to the best way to accomplish this?

Thanks in advance

1 Comment   [ + ] Show Comment

Comments

  • Thanks for the suggestion Chucksteel.... I am a little bit confused though.
    Are these 2 separate ways of accomplishing what I am looking for? I can either create a custom ticket rule either using SQL or using the wizard right?
    I tried the SQL code you sent on a SQL rule but the result was selected 0 rows and I know I have plenty of tickets opened for several days (still testing the system) I also tried to use the wizard instead but I don't think I understand it very well, I could not find the HD.STATUS.CREATED that you mentioned below. The drop down does not show anything remotely closed to that (keep in mind this is my first time creating ticket rules so I don't really know what I am doing)
    Any further explanation would be greatly appreciated.

    Thanks again
Please log in to comment

Answers

0
I don't have a full rule that does this, but this SQL query will find tickets with a Status of "New" that have been open for two hours:
SELECT * FROM ORG1.HD_TICKET
JOIN HD_STATUS on HD_STATUS.ID = HD_TICKET.HD_STATUS_ID
WHERE HD_STATUS.NAME = "New"
AND HD_TICKET.CREATED < NOW() - INTERVAL 2 HOUR
You should be able to use the rules wizard to create a rule that finds tickets with a status of "New - Not Started" and then add the "AND HD_TICKET.CREATED < NOW() - INTERVAL 2 HOUR" portion to the WHERE clause to limit it to tickets that have been open for more than two hours.
Answered 05/19/2015 by: chucksteel
Red Belt

Please log in to comment
0

Thanks for the example Chucksteel... after a lot of research I am starting to understand a little bit how this SQL stuff works :-)

I had trouble with your query and I am not sure why but I used it as a reference and ended up using the wizard to create a rule that more or less works although still not quite the way I want it.

This is the query the wizard built for me:

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(M1.ID is null, 'z', concat('a', M1.NAME)) as sort_MACHINE_NAME,
                        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_STATUS.NAME like '%New%') AND HD_TICKET.MODIFIED > '15') and HD_TICKET.HD_QUEUE_ID = 5 )



Basically it runs every 15 minutes and it checks if the status of the ticket contains "New" and if the ticket has been opened for X amount of minutes or more and it will send an e-mail alerting that the status has not changed for X amount of minutes. The issue I am having now is with the e-mail.

The way our system works is by using different e-mails based on categories so when the alert is sent for a ticket with a status of new and hasn't changed in 2 hours the e-mail is going to the category e-mail but I want it to go to our helpdesk e-mail instead..... Does anyone have any ideas on how to specify the e-mail I want to send it to?

I tried checking the option for send e-mail results but the e-mail that is received from that looks terrible as far as formatting and does not contain the actual e-mail text that I configured for that rule.


Thanks


Answered 05/27/2015 by: raul102801
Orange Belt

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