A report will be scheduled to run at a periodic interval which will fetch he details of the request. Once the schedules runs, the script will fetch the values from the report and sends an notification through email to the requester of the request where the note is added.
Custom Schedules are actions scheduled on a specific date and time with or without repeat frequency. The actions are specified in a customized script file. Any data from ServiceDesk Plus can be sent to the script through Query Reports.
Create a new custom schedule with a unique Schedule Name. Mention the command to execute the script in the Executor field. Associate the required Query Reports as Arguments. Mention the Start Date & Time at when the action should take place. Schedule the action by specifying the Repeat interval. Enable/Disable a custom schedule as preferred.
1. Extract the attached files under ManageEngine\ServiceDeskPlus-MSP\integration\custom_scripts
2. Edit the configuration.json with the mail server details.
3. Navigate to Reports>New Query Reports>> Run the below query
SELECT wo.WORKORDERID AS "workorderid",
max(wo.TITLE) AS "subject",
max(aau.FIRST_NAME) AS "Requester",
max(AaaContactInfo.EMAILID) AS "Email" 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 ON aau.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID LEFT JOIN Notes note ON wo.WORKORDERID=note.WORKORDERID where
note.NOTESID = (SELECT MAX(nts.NOTESID) FROM Notes nts WHERE nts.WORKORDERID=note.WORKORDERID and extract(epoch from(now()::TIMESTAMP - to_timestamp(nts.notesdate/1000)::TIMESTAMP))/3600 < 1) and (wo.ISPARENT='1') group by wo.WORKORDERID
The below query is for 10.6:
SELECT wo.WORKORDERID AS "workorderid",
max(wo.TITLE) AS "subject",
max(aau.FIRST_NAME) AS "Requester",
max(AaaContactInfo.EMAILID) AS "Email" 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 ON aau.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT JOIN workorderNotes note ON wo.WORKORDERID=note.WORKORDERID where note.NOTESID = (SELECT MAX(nts.NOTESID) FROM workorderNotes nts LEFT JOIN notes nos ON nts.NOTESID=nos.NOTESID WHERE nts.WORKORDERID=note.WORKORDERID) and
(wo.ISPARENT='1') group by wo.WORKORDERID
4. Save the report.
5 . Navigate to Admin>Custom Schedule>>> Fill the details
The Executor field should be
py sendMailRecentNotes.py
Select the report as argument
Whenever the schedule runs, email notifications will be sent to the requester of the request where notes are added.