Hey all,

 

I have a rule here. I'm trying to force the escalations from my student staff to only be allowed to go to myself, then I and anyone in the deptartment can reassign from there. I have a rule that in mysql wb seems to be selecting appropriately but in kace seems to be selecting erroneously. In kace it seems to be grabbing updaters who are staff whereas in mysqlwb those updaters are not selected.

Any thoughts? I think this would be a cool rule to share once it works..

Best, Tim

select HD_TICKET.*,
      -- ticket fields
      HD_TICKET.HD_STATUS_ID AS STATUS_NAME,
      HD_TICKET.ID, -- $id
      HD_TICKET.ID AS TICKNUM, -- $ticknum
      HD_TICKET.TITLE, -- $title
      DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
      DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
      -- change fields
      C.COMMENT, -- $comment
      C.DESCRIPTION, -- $description
      GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
       H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
       ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
      -- about the updater
      UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
      UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
      UPDATER.EMAIL AS UPDATER_EMAIL,     -- $updater_email
      IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
      -- about the owner
      OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
      OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
      SUBSTRING_INDEX(OWNER.FULL_NAME,', ',-1) AS OFIRSTNAME,
      SUBSTRING_INDEX(OWNER.FULL_NAME,',',1) AS OLASTNAME,
      OWNER.EMAIL AS OWNER_EMAIL,     -- $owner_email
      IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
      -- about the submitter
      SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
      SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
      SUBSTRING_INDEX(SUBMITTER.FULL_NAME,', ',-1) AS FIRSTNAME,
      SUBSTRING_INDEX(SUBMITTER.FULL_NAME,',',1) AS LASTNAME,
      SUBMITTER.EMAIL AS SUBMITTER_EMAIL,     -- $submitter_email
      -- about priority
      P.NAME AS PRIORITY, -- $priority
      -- about status
      S.NAME AS STATUS,   -- $status
      -- about impact
      I.NAME AS IMPACT,   -- $impact
      -- about category
      CAT.NAME AS CATEGORY -- $category
      -- other fields
      -- Finds if owner was changed
    FROM HD_TICKET
     /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
     /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
JOIN HD_TICKET_CHANGE INITIAL_CHANGE ON INITIAL_CHANGE.HD_TICKET_ID = HD_TICKET.ID
 and INITIAL_CHANGE.ID=(select MIN(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
     /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
     /* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
     /* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
     /* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
     /* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
     /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
     /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
    WHERE
C.DESCRIPTION like '%Changed ticket Owner%'
AND C.DESCRIPTION not like '%MYNAME%'
AND UPDATER.EMAIL != 'staffname@lasell.edu'
AND UPDATER.EMAIL != 'staffname@lasell.edu'
AND UPDATER.EMAIL != 'staffname@lasell.edu'
AND UPDATER.EMAIL != 'staffname@lasell.edu'
AND UPDATER.EMAIL != 'staffname@lasell.edu'
AND UPDATER.EMAIL != 'staffname@lasell.edu'
AND UPDATER.EMAIL != 'staffname@lasell.edu'
AND UPDATER.EMAIL != 'staffname@lasell.edu'
AND UPDATER.EMAIL != 'staffname@lasell.edu'
AND UPDATER.EMAIL != 'staffname@lasell.edu'
AND UPDATER.EMAIL != 'staffname@lasell.edu'
AND UPDATER.EMAIL != 'staffname@lasell.edu'
AND HD_TICKET.HD_QUEUE_ID = 21  
AND HD_TICKET.HD_STATUS_ID != 2
AND HD_TICKET.HD_STATUS_ID != 51
AND C.DESCRIPTION LIKE '%Changed ticket Owner%'
AND HD_TICKET.TITLE NOT LIKE '%Reminder:%'
AND HD_TICKET.TITLE NOT LIKE '%Notice:%'
AND HD_TICKET.SUBMITTER_ID NOT LIKE '%reporter%'
AND C.DESCRIPTION NOT LIKE '%Ticket Created%'
GROUP BY HD_TICKET.ID
    HAVING 1=1

3 Comments   [ + ] Show Comments

Comments

  • I wouldn't recommend hard coding all of your staff emails into the rule, instead it would be better to join to the label table and check to see if the label matches what you're looking for (assuming you also assign appropriate labels to either staff or students (or both)).

    With that being said, when you say that you want to escalate a ticket are you trying to create a rule that will change the owner of the ticket from any of your students to a particular user (yourself)?
  • Heya,

    thanks, in other words, i want to make a rule where select users can only reassign to a single person from the group of owners. I.e. a rule where only the students can assign tickets to me.

    Tim
    • Unless you're the only technician in the queue then they'll be able to assign it to anyone, but you can have a rule that would re-assign it to you. I just want to be clear that you can't prevent them from assigning to any available technician in the queue. If you're emailing ticket owners on save then you'll need to make sure the rule to re-assign tickets has a lower priority, otherwise owners will receive emails for tickets that are then re-assigned to you.

      We have a situation like this for our student help desk and we made the decision to setup a separate queue for their requests and there is only one potential owner in that queue (their supervisor). In the end it's a lot less work to manage two queues, in my opinion. In our case it also allows us to tailor the language of the emails being sent to students differently than those we send to faculty and staff in some cases.
  • yup, thats what i have in the above rule... we have a separate student queue but have all of our day to day for the entire dept in a single queue, just trying to get better QC

    the above rule works, grabs just what i want in mysqlwb but when i paste into kace, grabs everything just not sure what i'm missing...
Please log in to comment

Answers

0

For anyone interested: Root of the problem was the updater email logic needed to be a NOT LIKE and have '%XYZ%' format, working now

Tim

Answered 09/26/2013 by: tholmes
Green Belt

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