I have created a ticket rule to assign a ticket to a certain user based on a category being selected.  This part works fine.  However, once it changes the ownership, the new owner is not getting an email stating they have a new ticket.  I was told by Kace support that I need to edit the SQL for this, I have never dealt with SQL so I need someones assistance.

Here is the "Select Query"...

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_CATEGORY.NAME like 'Test%') AND (1  in (select 1 from USER where HD_TICKET.OWNER_ID = USER.ID and USER.USER_NAME = 'GFCAdmin')) ) and HD_TICKET.HD_QUEUE_ID = 1 )

 

 

 

 

And here is the "Update Query"..

 

update HD_TICKET, USER as T5

    set HD_TICKET.OWNER_ID = T5.ID

  where T5.USER_NAME = '*Desktop Support' and 

        (HD_TICKET.ID in (<TICKET_IDS>))
0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Answers

1

Well you need to add the EMAIL COLUMN and email body and such to the ticket rule.

Email column is hardcoded with a $ so you would just need to put OWNER_EMAIL in the email column portion then fill out the rest of the email.

Out of curiosity... why are you writing SQL for this action when you have the ability to set up AUTO-ASSIGN in the queue itself?  Seems like you're writing SQL to do something that the GUI can do.

 

QUEUE CONFIG > CUSTOMIZE FIELDS & LAYOUT > Set categories to auto-assign.  Then back out to the QUEUE CONFIG and click CUSTOMIZE EMAILS down by the email on events.

 

Also - your update query looks like it changes the user but the  way this ticket is designed is that no matter what the owners updates (say you want to change the owner) the ticket rule will force it back to that user as long as the the category is test.

And what do you have that SELECT 1 query within your WHERE filter for?  I understand you said you have little experience but you must have put it there for some reason?  Am i overlooking something?? (It IS monday).

 
(1  in (select 1 from USER where HD_TICKET.OWNER_ID = USER.ID and USER.USER_NAME = 'GFCAdmin')) - See more at: http://www.itninja.com/question/kace-service-desk-ticket-rule-assistance#sthash.W7eZvdVj.dpuf
(1  in (select 1 from USER where HD_TICKET.OWNER_ID = USER.ID and USER.USER_NAME = 'GFCAdmin')) ) - See more at: http://www.itninja.com/question/kace-service-desk-ticket-rule-assistance#sthash.W7eZvdVj.dpuf
(1  in (select 1 from USER where HD_TICKET.OWNER_ID = USER.ID and USER.USER_NAME = 'GFCAdmin')) ) - See more at: http://www.itninja.com/question/kace-service-desk-ticket-rule-assistance#sthash.W7eZvdVj.dpu
(1  in (select 1 from USER where HD_TICKET.OWNER_ID = USER.ID and USER.USER_NAME = 'GFCAdmin')) - See more at: http://www.itninja.com/question/kace-service-desk-ticket-rule-assistance#sthash.W7eZvdVj.dpuf
Answered 10/21/2013 by: Wildwolfay
Red Belt

Please log in to comment
Answer this question or Comment on this question for clarity