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.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

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. 
Answered 03/05/2015 by: chucksteel
Red Belt

  • 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?
    • 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.
      • 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...
      • Can you post your select statement?
      • 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.
      • I used the wizard and set created = to today or I tried < 5 minutes, neother worked
      • 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%'
      • for future reference, what did you change? Was it something I could have done in the wizard?
      • 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".
      • well I thank you sir and appreciate your time
      • 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
      • Rules should not have any effect on the app.
      • 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
      • 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.
      • I actually spoke to dell and they said its something on them, and they are aware of it. Thanks for the response, Chuck
Please log in to comment
This content is currently hidden from public view.
Reason: Removed by member request
For more information, visit our FAQ's.

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
Answered 03/16/2015 by: Hobbsy
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity
Admin Script Editor
Admin Script Editor is an integrated scripting environment available free here at ITNinja

Share