Email On-Call staff when a new ticket getes created by user
Does anyone have a rule that can email the IT on call when a user email help desk after hours or on weekends ?
3 Comments
[ + ] Show comments
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
JasonEgg
6 years ago
Top Answer
Create a Custom Ticket Rule
Select SQL:
select HD_TICKET.*,
'5555555555@verizon.net' AS ON_CALL
from HD_TICKET
join HD_TICKET_CHANGE on
(HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID
AND HD_TICKET_CHANGE.ID = <CHANGE_ID>)
where
DAYOFWEEK(CREATED) in (1,7)
# 1 = Sunday, 7 = Saturday
AND TIME(CREATED) >= TIME('8:00:00')
# at or after 8am
AND TIME(CREATED) <= TIME('17:00:00')
# before or at 5pm
AND HD_TICKET_CHANGE.DESCRIPTION like 'Ticket Created%'
# Only send email on creation of ticket
Check "Email each recipient in query results" and set "Column containing email addresses" to "ON_CALL". Then customize the email message as desired. You can look at the "IT Service Desk Email Notifications" to copy/paste or get ideas for crafting the email message using the columns in HD_TICKET as variables. Those details are located in Service Desk > Configuration > Queues > [your queue] > Customize Emails.
Finally, for "Frequency" select "on Ticket Save" and you're good to go!
Comments:
-
uummm i set it all up and it's not sending the email to '5555555555@verizon.net' AS ON_CALL
i changed DAYOFWEEK(CREATED) in (1,5,6,7)
so i can do some testing in the weekday.
I added ON_CALL to the "Column containing email addresses:"
only have "Email each recipient in query results " checked
and "Frequency" Set to "on Ticket Save" - armasmike 6 years ago-
What's the status you're getting in "Last Run Log"? - JasonEgg 6 years ago
-
05/05/2017 11:27:29> Starting: 05/05/2017 11:27:29 05/05/2017 11:27:29> Executing Select Query... 05/05/2017 11:27:29> selected 1 rows 05/05/2017 11:27:29> Sending ticket notifications... 05/05/2017 11:27:29> sent mail to 1 of 1 05/05/2017 11:27:29> Ending: 05/05/2017 11:27:29 - armasmike 6 years ago
-
It is saying that it sends an email. Try changing the ON_CALL address in the query to an email address you control and see how/if it comes over. - JasonEgg 6 years ago
-
could i add a case when for the select HD_TICKET.*,
'5555555555@verizon.net' AS ON_CALL
Case when '5555555555@verizon.net' = ON_CALL then '5555555555@verizon.net' AS ON_CALL else '777777777@verizon.net' = GUY2 - armasmike 6 years ago-
I don't quite know what you're asking. As I said before: "You need some way of mapping the time ranges to users, which is doable but could be very complicated depending on your circumstance". We set "ON_CALL" manually; it is not an actual field or value in the database. - JasonEgg 6 years ago
-
Ok thank you. This code was great over the weekend. Everything worked now we can help are customers faster. - armasmike 6 years ago
Posted by:
armasmike
6 years ago
works great thank you
Comments:
-
i want to email IT person or group of members assigned to a label , how can i do it? - rahimpal 6 years ago
-
Instead of hard coding an email address you can use a sub select statement like this:
SELECT GROUP_CONCAT(USER.EMAIL)
FROM USER
JOIN USER_LABEL_JT on USER_LABEL_JT.USER_ID = USER.ID
JOIN LABEL on LABEL.ID = USER_LABEL_JT.LABEL_ID
WHERE LABEL.NAME = "User Services"
GROUP BY LABEL.ID
This will return a comma separated list of email addresses for users in the "User Services" label. In the above query you would replace this line:
'5555555555@verizon.net' AS ON_CALL
with
(SELECT GROUP_CONCAT(USER.EMAIL)
FROM USER
JOIN USER_LABEL_JT on USER_LABEL_JT.USER_ID = USER.ID
JOIN LABEL on LABEL.ID = USER_LABEL_JT.LABEL_ID
WHERE LABEL.NAME = "User Services"
GROUP BY LABEL.ID) as ON_CALL - chucksteel 6 years ago
This is not good customer service So having a custom rule that only runs on weekend from 8am -5pm that will email/text msg the it staff on call for the weekend.
If some time down the road we need to cover weekday it would need to be change to 5pm to 8am on weekday and 8am to 5pm on weekend.
But at this time i just need a better way to help my customers on weekend with out me having to look at the helpdesk every 30 min. - armasmike 6 years ago