All - we get a ton of spam into the service desk. Our spam get's pre-flagged with a subject that starts with "[SPAM:X..." so it is easy to identify them.   Daily we have to go in and close it out (slow process) the tickets or delete (faster).

So I created the following custom service desk rule to mark the tickets as spam so we don't have to and would like it to run daily at 6am.  

 

Does anyone see any issues with the following?

 

 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.TITLE like '%[SPAM:X%') and HD_TICKET.HD_QUEUE_ID = 10 )

 

 

I add in a comment:

"Automatically closing via Spam Ticket rule on Queue 10; ticket rule 32"

 

Update query:

 update HD_TICKET, HD_CATEGORY as T5, HD_STATUS as T6, USER as T8 set HD_TICKET.HD_CATEGORY_ID = T5.ID, HD_TICKET.HD_STATUS_ID = '54', HD_TICKET.HD_CATEGORY_ID = '115', HD_TICKET.TIME_OPENED = IF(T6.STATE = 'opened', NOW(), HD_TICKET.TIME_OPENED), HD_TICKET.TIME_CLOSED = IF(T6.STATE = 'closed', NOW(), HD_TICKET.TIME_CLOSED), HD_TICKET.TIME_STALLED = IF(T6.STATE = 'stalled', NOW(), HD_TICKET.TIME_STALLED), HD_TICKET.CUSTOM_FIELD_VALUE3 = 'Spam', HD_TICKET.CUSTOM_FIELD_VALUE0 = 'Spam', HD_TICKET.CUSTOM_FIELD_VALUE1 = 'Spam', HD_TICKET.SUBMITTER_ID='10', HD_TICKET.OWNER_ID = '13' where HD_TICKET.HD_QUEUE_ID = T5.HD_QUEUE_ID and (HD_TICKET.ID in (<TICKET_IDS>))

 

 

 

 

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

0

Nothing looks terribly wrong; I'd definitely put this into a test queue and verify it works like you want. For example, if a user forwarded a spam notification in to complain or ask squestions, would it get autoclosed?

Answered 02/04/2014 by: cblake
Red Belt

  • Chris - good point, and yes in that case it would get automatically closed. However, we get notification when new tickets come in, so hopefully we will see that request and be able to respond to it.

    I am also looking into creating an "Quick Close" dropdown so we can quickly close tickets to a certain default settings. Any thoughts on this?
    • The ninjas might need more info on it to help create it, but basically a Custom Ticket rule should be able to accomplish anything you might want. Have a status called Quick Close, then have a ticket rule that runs on save that updates the fields you want changed. If you want to make it more elegant you might include some logic that looks for the status changing to quick close recently, so if someone updates or changes the ticket while that status is set it doesn't change everything back at each save.
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