EWS Mail configuration - Script to send reminder emails to both requesters and technicians when there is no response for more than a specific time period and auto close the ticket

EWS Mail configuration - Script to send reminder emails to both requesters and technicians when there is no response for more than a specific time period and auto close the ticket

Use case: 
If the requester is not responding for a specified period of time and the ticket stays idle without any action. So, requesters need to to be reminded of the same and also auto close the ticket after specific time period.

How it works:
This can be achieved using a python script. This script will send reminder email notifications to both requesters and technicians and close the ticket automatically.

Reminders would be triggered when the request is moved to a custom status with no response for more than a specific period of time.

Custom status used: waiting for user reply
Time period criteria: Last updated time
Reminder sending frequency: 1,2,3 days since last updated time
Ticket Auto closure: 4th day

So, three reminder emails will be sent to the requesters and technicians in different time intervals after the request status changes to waiting for user reply. If the last updated time is more than a day, First reminder mail will be sent .  Second mail will be sent when its more than 2 days and final reminder will be sent when its more than 3 days since the last updated time. On 4th day, the ticket will be automatically closed.

Prerequisites:
1. Python is a third party software that has to be installed on the server in which MSP is installed. Here is the link for Installation Steps,
2. functions.py :  This package consists of all the functions that are used to handle the standard requirements of the customer.  Most repeated requirements are written as separate functions.  Depending on our usecase, we can include these functions in our script. 
3. configuration.json : This file maintains the configuration details of the installed server.  
4. send_reminder_mail.py: This file is the actual script where the criteria, reminder sending frequency are set.
5. SQL Query: SQL query is required to run at a regular frequency to check the tickets that are with the custom status.

Note: All the above except #1 are available in the attachment.

Execution steps:    

1.  Execute the below query under Reports->New Custom Report and save the report with a title.

select wo.workorderid "workorderid", 
ti.FIRST_NAME AS "technician", 
aci2.EMAILID "technician_email",
aau.FIRST_NAME AS "requester", 
aci1.EMAILID "requester_email",
wos.last_tech_update "last_tech_update" from workorder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID 
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID 
LEFT JOIN AaaUserContactInfo auci1 ON aau.USER_ID=auci1.USER_ID 
LEFT JOIN AaaContactInfo aci1 ON auci1.CONTACTINFO_ID=aci1.CONTACTINFO_ID 
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID 
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID 
LEFT JOIN AaaUserContactInfo auci2 ON ti.USER_ID=auci2.USER_ID 
LEFT JOIN AaaContactInfo aci2 ON auci2.CONTACTINFO_ID=aci2.CONTACTINFO_ID 
inner join statusdefinition on wos.statusid=statusdefinition.statusid
where statusdefinition.statusname='waiting for user reply'


You can edit the above query and update the status name highlighted to any custom status for which you want the reminder to be sent.

2.  Extract the ZIP file under ..\ManageEngine\ServiceDeskPlus-MSP\integration\custom_scripts - folder.



3.  Edit the configuration.json and update the EWS Mail configuration values for sending reminder emails.



4.  Configure a custom schedule to execute everyday and pass this report as a parameter. 
Navigate to Admin->Custom Schedule-> give a schedule name > execute script > py send_reminder.py




Reminder Notification to the end user will look like below, you can also modify this in the script send_reminder_mail.py file.



As mentioned, this script will send reminders if the last updated time is more than 1,2, 3 days and the ticket will be closed automatically on 4th dayHowever, you can also customize the days in the send_reminder_mail.py file.



If you have any issues, You can reach out to us at support@servicedeskplusmsp.com with complete details.


      New to ADSelfService Plus?

        Resources