/build/static/layout/Breadcrumb_cap_w.png

Using a ticket rule to email on ticket save for portal-created tickets

Hello:

One of our resolutions for 2021 is to clean up and better use our K1000 Service Desk.  Using some tips found here on ITNinja, I've created an OTS rule that emails the submitter when a technician creates a new ticket from the SMA GUI.  It works correctly, passing selected fields to the user via email, confirming their issue has been reported and a ticket created.  That Select SQL syntax is below.

As we continue to refine our processes, a new use case has been identified.  Some issues -- especially walk-up customer concerns -- are solved in under 5 minutes, with no follow-up required.  For completeness, we still want to log these as tickets, and we'd still like the customer to get a ticket closed confirmation (to eventually include a satisfaction survey).  However, we *don't* need these customers to also get a "ticket opened" email.  Because the ticket is opened, resolved, and closed in quick succession, there is no need for the end user to get two emails within a 5 minute span.  In this case, we don't need to tell them their ticket was opened -- they already know that.

So.  How can I  modify the syntax below to send emails OTS for all new tickets, EXCEPT those where the status is "Closed"?  (Alternately, it could instead be modified to send on all tickets where the status = "New" -- our workflow could accommodate either solution.)  I obviously need something in the "WHERE" section, but I can't quite figure out the correct structure or fields.

Thanks!!


 SELECT

      -- ticket fields

      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-2020.comm.virginia.edu/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, -- $priority

      -- about status

      /* S.NAME AS STATUS,   -- $status */

      -- about impact

      I.NAME AS IMPACT,   -- $impact

      -- about category

      CAT.NAME AS CATEGORY, -- $category

      -- other fields

      -- -- example of static distribution list

      'helpdesk@mycompany.com' AS NEWTICKETEMAIL -- $newticketemail

    FROM HD_TICKET

     /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID

                            AND C.ID=<CHANGE_ID>

     /* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID

     /* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_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 '%CREATED%'

      /* this is necessary when using group by functions */

GROUP BY HD_TICKET.ID

    HAVING 1=1


2 Comments   [ + ] Show comments
  • So I've made a little progress on this:

    Including << HD_STATUS.NAME as STATUS >> as a SELECT field, and adding HD_STATUS as a FROM option does correctly obtain the status name from the ticket.

    I then thought I could just add << and HD_STATUS.NAME != 'Closed' >> to my WHERE routine and I'd be all set.

    However, this does not work. And it does not work because on ticket save, the appliance considers the status of the new ticket to be "Opened," even if the end technician has set the status to "Closed" as he/she is creating it.

    I've confirmed this by mapping status to a variable and including that variable in the generated email. It shows the Status as "Opened," even if the KACE GUI correctly shows the ticket as Closed. This of course then causes a "ticket has been opened" email to be generated, immediately followed by a "your ticket is now closed" email, which is exactly what I don't want.

    Questions:
    -- Does the SMA briefly consider the status of all new tickets to be "Opened," even for a millisecond, and even if the user interacting with the GUI ticket form chooses a status of "Closed"? That seems to be what is happening.

    -- If so, anything that can be done about this? One thought I had is to not run this rule OTS, but instead run it hourly. We have a fairly small volume of tickets, so it wouldn't be a bandwidth issue. But I think end users will be frustrated -- if a technician tells them on the phone "I'm creating a ticket for you now," and then that ticket confirmation email doesn't show up for 50 minutes, there's going to be some confusion.

    -- I suppose I could create a custom field, something called "Don't send email" and then filter out tickets that have that box checked. Clunky, but I guess it would work.

    Would welcome any other suggestions to get to my ultimate goal of not sending "ticket opened" emails for "immediately closed" tickets. Thanks!! - erzeszut 8 months ago
    • You don't have a join to the HD_STATUS table, so I don't think your statement is working correctly. I'm actually surprised you didn't get an error when the rule ran due to an unknown column being selected. - chucksteel 8 months ago
      • Joining to the HD_STATUS table makes no difference, unfortunately. Tickets still seem to have this status of "Opened" for at least a brief moment on ticket save, even if the status is set to "Closed" in the GUI. - erzeszut 8 months ago
  • Can you post your statement with the join to the status table? - chucksteel 8 months ago

Answers (1)

Posted by: jleitsch 7 months ago
Purple Belt
0

We use a template for the walk-ins like this.

In the where statement we leverage "HD_TICKET.TITLE NOT LIKE '%Template Title%'"

 
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