Hi:

We are delving into some more advanced service desk use cases for our K1000 (have used very simple ticketing up to this point).  Trying to accomplish the following with a new ticket queue:

  • All incoming tickets will be unassigned, and evaluated by a Help Desk staff member (who will take ownership of the ticket)
  • If it's a Tier 1 issue, the HD staffer will resolve and close the ticket; done.
  • If it's a Tier 2 issue, the ticket needs to be categorized; the ticket status will be changed to one of four specific Tier 2 statuses
  • A specific person or people should be cc'd, based on the status; for example, if the status is "Tier 2: Financial," our financial person should be notified; if it's "Tier 2: Enrollment," our group of two enrollment people should be emailed, etc., etc.
  • Ideally, the ticket would revert to "unassigned" ownership, though I can deal with assigning a specific owner for each status as needed

Is there a way to do this?  I've been over the on ticket save options trying to see how this can be accomplished.  Thanks in advance.        

Answer Summary:
Cancel
1 Comment   [ + ] Show Comment

Comments

  • Thanks, this is what I have so far, from the Wizard:

    Select SQL:
    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_STATUS.NAME like '%Enrollment%') and HD_TICKET.HD_QUEUE_ID = 15 )


    And then, the actual query (with email obfuscated):

    update HD_TICKET, USER as T5
    set HD_TICKET.OWNER_ID = T5.ID,
    HD_TICKET.CC_LIST = 'XXXX@YYY.ZZZ'
    where T5.FULL_NAME = 'Unassigned' and
    (HD_TICKET.ID in (<TICKET_IDS>))


    So ideally, this rule would find any ticket with a status containing the word "Enrollment," set the owner to Unassigned, and CC the desired email address. It's set to "on ticket save," but so far is not performing any tasks.
Please log in to comment

Answer Chosen by the Author

0
You can definitely do this with ticket rules. Start with the wizard and see how close you can get. Once you get stuck you can post the SQL select statement here and folks can help make the necessary changes.

Answered 02/19/2016 by: chucksteel
Red Belt

Please log in to comment

Answers

Answer this question or Comment on this question for clarity