/build/static/layout/Breadcrumb_cap_w.png

Setting a custom Due Date in Help Desk Ticket

My question is, Can I have the due date default to 7 days in the future from when the ticket is first open. Currently it is set to None. Would I need to edit a new help desk sql rule? If so what would the syntax be? Please help. I saw this in another post - just don't know if pasting it in the NewSQL rules would do anything:

update HD_TICKET as T
set T.DUE_DATE = DATE_ADD(T.CREATED, INTERVAL 14 DAY)
where
(T.ID in (<TICKET_IDS>))

I would like to have it at 7 days - so obviously would need to change the interval. Any help is appreciated, thanks.

Asked 9 years ago 3032 views
0 Comments   [ + ] Show comments

Answers (2)

Posted by: chucksteel 9 years ago
Red Belt
0
Yes, this update query should work, just change the INTERVAL 14 DAY to INTERVAL 7 DAY. Also make sure that the select statement is only matching tickets on creation, otherwise it will change the due date every time the ticket is updated. 
Comments:
  • so I just put that whole query "update HD_TICKET as T
    set T.DUE_DATE = DATE_ADD(T.CREATED, INTERVAL 14 DAY)
    where
    (T.ID in (<TICKET_IDS>))" into the my rules (New SQL) not the wizard...would it go in the Select SQL box or Update SQL box...or does something need to be in both? - mazostyle 9 years ago
    • This is the update statement so it goes in the update box. If the post that you got the update query from doesn't include the select statement then I would recommend using the wizard to create a rule that will select the tickets you want and then edit the rule afterwards to use this update query. - chucksteel 9 years ago
      • makes sense, do I actually need the word update at the beginning? edit: got the due date to change to 7 days - but what can I do to make it only effect newly created tickets? im running it on old open tickets and its coming up as over due. I want this to only apply to a newly created ticket... - mazostyle 9 years ago
      • Can you post your select statement? - chucksteel 9 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(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_TICKET.TIME_OPENED = 'today') and HD_TICKET.HD_QUEUE_ID = 8 )
        I think the last line needs to have date created something - for that instance I tried time opened, neither works. - mazostyle 9 years ago
      • I used the wizard and set created = to today or I tried < 5 minutes, neother worked - mazostyle 9 years ago
      • Here is a query that uses the description of the change to the ticket. This is my preferred method.

        select HD_TICKET.*, DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') as CREATED, DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') as MODIFIED,
        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 ON HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
        and HD_TICKET_CHANGE.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 HD_TICKET.HD_QUEUE_ID = 8 and HD_TICKET_CHANGE.DESCRIPTION LIKE 'TICKET CREATED%' - chucksteel 9 years ago
      • for future reference, what did you change? Was it something I could have done in the wizard? - mazostyle 9 years ago
      • No, the wizard doesn't support this kind of query, unfortunately. The select statement that I posted makes a join to the HD_TICKET_CHANGE table and only matches if the change description includes "Ticket Created". - chucksteel 9 years ago
      • well I thank you sir and appreciate your time - mazostyle 9 years ago
      • chucksteel, would this new select statement break the ability to create tickets via the k1000 go app? Because now no users can create tickets - it says connect failure - failed to create ticket. Let me know if you can, thanks - mazostyle 9 years ago
      • Rules should not have any effect on the app. - chucksteel 9 years ago
      • chuck!!! ever since we upgraded to 6.3, the due date initially works fine, but if you do any changes to the original ticket, it sets the due date to none. Do you know why this is?? Please let me know if you get a chance, thanks - mazostyle 9 years ago
      • We haven't upgraded to 6.3 yet so I'm not sure what might be happening. There may be a change in the database tables that is affecting how the rule detects which tickets update but until I have a 6.3 appliance I won't know for certain. - chucksteel 9 years ago
      • I actually spoke to dell and they said its something on them, and they are aware of it. Thanks for the response, Chuck - mazostyle 9 years ago
Posted by: Hobbsy 9 years ago
Red Belt
0
I managed to do the following, I first set the Select statement to only run on a calls logged today where a due date had not been set using the following last lines

                        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 (((date(HD_TICKET.CREATED) = curdate() )) and HD_TICKET.HD_QUEUE_ID = 1 )

I then used the following update statement to increment the due date by 14 days

update HD_TICKET, HD_CATEGORY as T5
    set HD_TICKET.DUE_DATE = DATE_ADD(HD_TICKET.CREATED, INTERVAL 14 DAY)
  where HD_TICKET.DUE_DATE = '' and 
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and 
        (HD_TICKET.ID in (<TICKET_IDS>))

Which can then be easily edited to change the time period you wish to add
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ