Ticket Rule: notify submitters based on inactivity
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
Answers (2)
Please log in to answer
Posted by:
Wildwolfay
10 years ago
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>))
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>))
Comments:
-
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. - Wildwolfay 10 years ago
-
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! - jabadm 10 years ago-
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. - Wildwolfay 10 years ago
-
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 - jabadm 10 years ago-
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) - Wildwolfay 10 years ago-
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. - jabadm 10 years ago
-
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. - Wildwolfay 10 years ago
Posted by:
Wildwolfay
10 years ago
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!)
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!)
