02/11/2013 3445 views

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.


Answer Summary:
0 Comments   [ + ] Show comments


All Answers


You would want to use the "created date" as the means to make changes. What are you trying to do?

Answered 02/11/2013 by: nshah
Red Belt


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.

Answered 02/11/2013 by: gargone
Second Degree Brown Belt

  • Sorry wrong field.
  • 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.
  • 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?
  • 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.
  • I did create a dummy rule with most of whats needed. I'll get it posted here in a few minutes.

To get todays date i KACE (mysql) use CURDATE()

Answered 02/11/2013 by: jdornan
Red Belt

 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,
                        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 )
Answered 02/12/2013 by: gargone
Second Degree Brown Belt

  • here it is
  • 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).
  • Yes it is custom field 2 which is a timestamp option.
    • In that case, then you should be able to change


      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?
  • Guess I should have included it. I believe that it is yes.

    update HD_TICKET, HD_PRIORITY as T5
    where T5.NAME = 'High' and
    • Good. Making the change above should do the trick. Try it out and let us know if it worked.
  • OK got it working. But I had to replace DATE(NOW() with CURDATE()......viola. Thank you gentlemen.