For our queue we have it set up if a conference room needs technical support we create a ticket for it. Sometimes we have a lot of these tickets as they book the conference rooms in advance and we are looking for a way to create a custom ticket rule in order to hide these tickets until they are coming due. We would create a custom view for only the certain tickets status to do so.

So basically I want to have a rule that says when this ticket has a status of Future Conference Room Support, and a due date of today, the wizard will change the Status to Conference Room Support. That way our view that we created will show only the Conference Room Support tickets plus all other ones we have open.  I am having troubles with have the wizard finding tickets with only today's due date as I created a test ticket with these stipulations to test the rule. When searching it finds all the tickets with due dates, not just the one with today's specific date. 

This is the SQL i have now: 

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() - unix_timestamp(HD_TICKET.TIME_OPENED)
                        else unix_timestamp() - 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(HD_TICKET.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 != 'Closed') AND (HD_TICKET.DUE_DATE = now() )) and HD_TICKET.HD_QUEUE_ID = 8 )


and the updated SQL: 
update HD_TICKET, HD_STATUS as T5
    set HD_TICKET.HD_STATUS_ID = T5.ID, 
HD_TICKET.TIME_OPENED  = IF(T5.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED), 
HD_TICKET.TIME_CLOSED  = IF(T5.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED), 
HD_TICKET.TIME_STALLED = IF(T5.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED), 
HD_TICKET.SATISFACTION_RATING = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_RATING), 
HD_TICKET.SATISFACTION_COMMENT = IF(T5.STATE = 'closed', NULL, HD_TICKET.SATISFACTION_COMMENT)
  where T5.NAME = 'Conference Room Support' and 
HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and 
        (HD_TICKET.ID in (<TICKET_IDS>))


I guess what looks wrong or do you have any suggestions with how I could set this up better?

Thanks in advance. 
Answer Summary:
Cancel
2 Comments   [ + ] Show Comments

Comments

  • I get a syntax error when I change it to that. Any other ideas?
    • Can you post the error? The email notification that I received from ITNinja looks like you tried, but I'm not seeing it.
      • There were syntax errors in your query.

        mysqli error: [1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 42] in EXECUTE("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() - unix_timestamp(HD_TICKET.TIME_OPENED) else unix_timestamp() - 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(HD_TICKET.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 != 'Closed') AND DATE(HD_TICKET.DUE_DATE) = DATE(now()) and HD_TICKET.HD_QUEUE_ID = 8 ) and HD_TICKET.HD_QUEUE_ID = '8' ")
    • You have an extra quote at the end of your query.
      • So the last line. after the 8 I don't need the ) ?
      • I cleaned up the paranthesis to make it more clear:
        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() - unix_timestamp(HD_TICKET.TIME_OPENED) else unix_timestamp() - 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(HD_TICKET.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 != 'Closed'
        AND DATE(HD_TICKET.DUE_DATE) = DATE(now())
        and HD_TICKET.HD_QUEUE_ID = 8
  • Thank you!! It finally worked for what we wanted it to do. I appreciate the time you took to help me.
Please log in to comment

Answer Chosen by the Author

1
The rules wizard doesn't handle dates very well. Find this:
HD_TICKET.DUE_DATE = now()
and replace it with this:
DATE(HD_TICKET.DUE_DATE) = DATE(now())


Answered 02/13/2018 by: chucksteel
Red Belt

Please log in to comment

Answers

Answer this question or Comment on this question for clarity