/build/static/layout/Breadcrumb_cap_w.png

Systems Management Question


Is there a custom rule that will allow the service desk to notify a submitter that their ticket has been inactive for 1 day?

02/17/2017 582 views
We are wanting to create a rule that notifies a submitter that their ticket has been inactive, not because it has not been worked, but more so that the owner of the ticket has not heard back from the submitter.

I have read a few articles on here and I have found an SQL script, but I can't get it to work properly.  I have changed everything that I can see, although I'm not very good with SQL.
1 Comment   [ + ] Show comment

Comments

  • Please post the SQL statement that you are trying to use and someone should be able to help.
    • SELECT
      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://k1000.plainscommerce.local/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
      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
      SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
      -- about priority
      P.NAME AS PRIORITY,
      S.NAME AS STATUS,
      I.NAME AS IMPACT,
      CAT.NAME AS CATEGORY
      FROM (HD_TICKET, ASSET_DATA_20)
      JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
      JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
      JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
      JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
      JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
      JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
      LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
      LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
      LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
      where ((S.NAME = 'Waiting for User'))
      and DATE_SUB(NOW(), INTERVAL 1 DAY) > DATE(HD_TICKET.MODIFIED)
      and (HD_TICKET.HD_QUEUE_ID = 5
      GROUP BY HD_TICKET.ID

All Answers

0

There's an open paren on the second to last line in your query you posted in comments:

and (HD_TICKET.HD_QUEUE_ID = 5 

It does not require "(" so delete it

A few questions for clarification:
1. Are you only applying this to one queue?
2. Are you sure the only "inactive" status is 'Waiting for User'? (in our environment we have "Need More Info", "New, and "Waiting on Customer")
3. Do you have any idea why the table "ASSET_DATA_20" is included in this query?


Answered 02/21/2017 by: JasonEgg
Red Belt

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ