Custom Ticket Rule using current date.
Wanting to kreate a kustom ticket rule in a specific queue that uses the kurrent date as the "trigger" to change the status to something higher than the default. In the define rule first line what could I use for the "date"? Is this a value that would have to come from the database? Just looking for something simple to get the wheels moving.
Jim
0 Comments
[ + ] Show comments
Answers (4)
Answer Summary:
Please log in to answer
Posted by:
nshah
11 years ago
You would want to use the "created date" as the means to make changes. What are you trying to do?
Posted by:
gargone
11 years ago
Ultimately, I want a ticket that changes the priority from medium to high based upon it being entered on the current day. I have a custom field for the start day and time and wish to use it. Kinda stumped on the 3rd field where I need to enter text..not sure what to enter.
Comments:
-
Sorry wrong field. - gargone 11 years ago
-
I'm still not getting it. If today is Monday and a ticket is opened it gets the default values. If you want tickets that are opened to get high then you just need to change the default values in the queue to high (default is medium) . that way when tickets are opened on Tuesday, they will get a high priority because that is the default value for any ticket opened, regardless of date. - nshah 11 years ago
-
I understand that portion, I am trying to account for the "idiot factor" since most of our "technicians" are students. these tickets are for "meetings" that occur everyday, some in the future and some for the same day. Does this help? - gargone 11 years ago
-
I see, you're using a service desk queue to inform people about meetings, correct? And if a ticket is entered today for something happening today then it needs to have a high priority.
You'll need to have some custom SQL to do this. Do you have a rule that you're working on? It's normally easier if you create most of the rule with the wizard and post the SQL code for others to add what you need. - chucksteel 11 years ago -
I did create a dummy rule with most of whats needed. I'll get it posted here in a few minutes. - gargone 11 years ago
Posted by:
gargone
11 years ago
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.CUSTOM_FIELD_VALUE1 like '%%') and HD_TICKET.HD_QUEUE_ID = 3 )
Comments:
-
here it is - gargone 11 years ago
-
Do you have a custom field that has the date of the meeting? We'll need something for the SQL to check against to know if the meeting is happening today. Is that your CUSTOM_FIELD_VALUE1? FYI, CUSTOM_FIELD_VALUE1 actually refers to the custom field 2 (the columns start with 0 in the database). - chucksteel 11 years ago
-
Yes it is custom field 2 which is a timestamp option. - gargone 11 years ago
-
In that case, then you should be able to change
HD_TICKET.CUSTOM_FIELD_VALUE1 like '%%'
to
DATE(HD_TICKET.CUSTOM_FIELD_VALUE1 ) =DATE(NOW())
This should match tickets that have a date of today in that field.
Is the update portion of the rule already setup to change the priority of the ticket? - chucksteel 11 years ago
-
Guess I should have included it. I believe that it is yes.
update HD_TICKET, HD_PRIORITY as T5
set HD_TICKET.HD_PRIORITY_ID = T5.ID
where T5.NAME = 'High' and
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and
(HD_TICKET.ID in (<TICKET_IDS>)) - gargone 11 years ago-
Good. Making the change above should do the trick. Try it out and let us know if it worked. - chucksteel 11 years ago
-
OK got it working. But I had to replace DATE(NOW() with CURDATE()......viola. Thank you gentlemen. - gargone 11 years ago