Greetings! I'm looking to notify submitters (preferably not owners) of their ticket by email when it is in certain statuses and not updated/modified for (3) days. I'd also like to add a stock comment indicating the email was sent.

Relevant statuses are "Waiting" "Waiting on Submitter" Waiting on Supervisor"  (however not all statuses that contain "Waiting" are relevant).

I'd like to do the same thing at (7) days out. Then at (14) days I'd like to close the case for inactivity.

I did some searching throughout the site and realize there are other similar questions here, but none quite to my specifications. I'm fairly inexperienced with SQL so not exactly savvy enough yet to take someone's select/update queries and modify them to my own use.  So any and all assistance will be greatly appreciated.

0 Comments   [ + ] Show Comments

Comments

Please log in to comment

Community Chosen Answer

2
As promised, here is the WAITING FOR USER SQL.
Frequency - 15 minutes
Some things to note:  The first 3 lines and last 3 lines are part of my BUSINESS HOURS work around, so ignore those.  Also, you will need to adjust the QUEUE that it is running against.

Setup the email as you see fit, but the EMAIL_COLUMN should  = SUBMITTER_EMAIL

Below that is the UPDATE query which includes the counter, you do not need the counter unless you are planning on using a second rule to notify managers, which will require you to setup the manager name in the KACE user information or do an import/shave like I have, which is another story for another day (or a blog if I feel like it in a minute).  you DO however need to update the MODIFIED timestamp or the rule will run every 15 minutes because as far as it's concerned, it's still more than 2 days since MODIFIED.

SELECT
FIELD_138 as DAYSTART,
FIELD_140 as DAYEND,
FIELD_139 as DAYOFWEEK,


          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
          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 2 DAY) > DATE(HD_TICKET.MODIFIED)
and (HD_TICKET.HD_QUEUE_ID = 1
or HD_TICKET.HD_QUEUE_ID = 9
or HD_TICKET.HD_QUEUE_ID = 10
or HD_TICKET.HD_QUEUE_ID = 12
or HD_TICKET.HD_QUEUE_ID = 13
or HD_TICKET.HD_QUEUE_ID = 18
or HD_TICKET.HD_QUEUE_ID = 19
or HD_TICKET.HD_QUEUE_ID = 20
or HD_TICKET.HD_QUEUE_ID = 21
or HD_TICKET.HD_QUEUE_ID = 23
or HD_TICKET.HD_QUEUE_ID = 24
or HD_TICKET.HD_QUEUE_ID = 25
or HD_TICKET.HD_QUEUE_ID = 26 )
and TIME(NOW())< ASSET_DATA_20.FIELD_140 and TIME(NOW())> ASSET_DATA_20.FIELD_138
and DAYOFWEEK(NOW()) = ASSET_DATA_20.FIELD_139

        GROUP BY HD_TICKET.ID


UPDATE QUERY:
update HD_TICKET
Set HD_TICKET.MODIFIED = NOW(),
HD_TICKET.CUSTOM_FIELD_VALUE9 = HD_TICKET.CUSTOM_FIELD_VALUE9 + 1
WHERE (HD_TICKET.ID in (<TICKET_IDS>))
Answered 09/05/2014 by: Wildwolfay
Red Belt

  • In order to turn this around and use it for ticket owners, you should only need to change a few key fields like the S.NAME and the EMAIL COLUMN. Anyways, this should give you a good idea of what you need to do, if you have more questions, i'll be available.
  • Wow. Thanks a lot for this. I'm just getting a look at it and I can imagine I'll have a few questions as I work it into our environment. I don't think we'll need the notify manager function so that should save a bit on setup. I do like how you're doing multiple notices to submitters, which is exactly what I was looking for. One difference in your usage and my needs are the notice intervals. I imagine I'll have to setup the rule multiple times each running with it's own trigger of 2days, 3days, 7days, etc.

    But I'll post again when I actually have a question. Thanks again!
    • as far as those steeped notifications, I think the easiest way to do that would be to use a counter, similar to the one in place for the manager submission. Then, using the WHERE parameters you can call upon that counter. first notification doesn't have it, but the second would be an additional WHERE CUSOTM_FIELD_VALUEcounternumber = '1' and the next rule would be = '2' as well as adjusting the modified date and the time check against that.
  • So I've removed your custom stuff, changed the queue ID to match ours, changed the S.NAME to match the first status I'm looking for, and changed the INTERVAL to 3 days. When I test I get a generic error. Here's the SQL so far:

    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\nYour ticket is waiting for a response. Please see your ticket at https://kbox.careprohs.com/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 on Submitter'))
    and DATE_SUB(NOW(), INTERVAL 3 DAY) > DATE(HD_TICKET.MODIFIED)
    and (HD_TICKET.HD_QUEUE_ID = 7)
    GROUP BY HD_TICKET.ID
    • What error did you receive? I copy/pasta'd this into my mysql workbench and it seems to runwithout any trouble....

      Looking at it now... you may need to remove ASSET_DATA_20 from the "FROM" section, as that table probably doesn't even exist for you. (That was where the business hours were)
      • I pulled that out and still receive the same generic error:

        There was an error retrieving the data for this page.
        Please refresh the page. If the error persists, try resetting the default page layout.
    • what program are you using to try and test this rule? That is utterly confusing error statement. If you're using an actual ticket rule to test the notification, and your getting a timing out issue, then that's odd. I would highly suggest using mysql workbench though to test things like this, or maybe a testbox.
Please log in to comment

Answers

1
I have 4 rules around this:
Waiting for Ticket Owner
Waiting for User
Notify Manager of Waiting for User
Notify Manager of Waiting for Ticket owner

Here's the gist:
The parameters for the first two essentially look at the status, if it's waiting for user for a period of time (2 days, in my case) it sends an email to the submitter to notify them.  There is also an UPDATE clause in which I use a custom field that is not being used and add a +1 there.  Once this notification goes out 3 times, the custom field becomes a 3.  That triggers the Notify Manager rule, which goes through, finds the submitter, finds their manager (which I imported into custom 3 using ldap then broke down all of the OU= crap using another rule) and finds their manager's email address and sends THEM an email saying "here's a ticket your user has not responded too"

The Waiting for Ticket Owner works in the exact same fashion, but the update adds +1 to a DIFFERENT custom field (it's own counter) and sends a notification when that hits 3.

If you read this and go "wow, sounds fabulous" then hold unto your hat, cuz I will post the SQL for that when I return in just a few minutes.  (if these notifications are NOT something you are interested, comment so and I will save the trouble!)
Answered 09/05/2014 by: Wildwolfay
Red Belt

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

Share