How to email pending requests list to technician periodically

How to email pending requests list to technician periodically

This post describes the use of a python script to email pending requests to each technician in a periodic interval using Custom Schedules.This showcases the use of a feature called 'Custom Schedules' that was released in the build 9300 of ServiceDesk Plus.You can find more information about what custom schedules are in the link here.

Use Case: In ServiceDesk Plus, we may have to schedule many reports to send notification about their pending request. This script simplifies it and emails the report to every technician without having to create separate reports.

Execution Steps :
1. Go to Reports--> New Query Report and run the below query and save this report with the name "Schedulereport" under any of the available report folders.
The below report will give the list of pending request more than 10 days

PGSQL

SELECT wo.WORKORDERID AS "Request ID", ti.FIRST_NAME AS "Technician", std.STATUSNAME AS "Request Status",AaaContactInfo.EMAILID "emailid" FROM WorkOrder wo 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 StatusDefinition std ON wos.STATUSID=std.STATUSID 
LEFT JOIN AaaUserContactInfo ON ti.USER_ID=AaaUserContactInfo.USER_ID 
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID  WHERE (wo.ISPARENT='1') and std.ispending='1'  and AaaContactInfo.EMAILID  != ''  and  extract(epoch from(now()::TIMESTAMP -  to_timestamp(wo.createdtime/1000)::TIMESTAMP))/3600/24 > 10
MSSQL

SELECT wo.WORKORDERID AS "Request ID", ti.FIRST_NAME AS "Technician", std.STATUSNAME AS "Request Status",AaaContactInfo.EMAILID "emailid" FROM WorkOrder wo 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 StatusDefinition std ON wos.STATUSID=std.STATUSID 
LEFT JOIN AaaUserContactInfo ON ti.USER_ID=AaaUserContactInfo.USER_ID 
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID  WHERE (wo.ISPARENT='1') and std.ispending='1'   and  AaaContactInfo.EMAILID  != ''  and DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wo.createdtime/1000),'1970-01-01 00:00:00'),GETDATE()) > 10

2. Download and place the script file in the ManageEngine/ ServiceDesk/ integration/custom_scripts folder.
3. Edit the Configuration.json file and update your server details, Url and technician key.
4. Go to Admin--> Custom Schedules and set up the schedule as shown below.





A short guide on setting up python is available here.

          • Related Articles

          • How to send periodic notifications to requester(s) about their pending requests and accordingly update their status.

            This post describes the use of a python script to notify requester(s) about their requests through email and move those to a specific status. Use Case: Let us assume a scenario where the technicians reply to a request and put it in a custom status ...
          • Report on requests pending approval

            DB Compatibility : PGSQL & MSSQL Build Compatibility : Builds above 10000 SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",dpt.DEPTNAME "Department",wo.TITLE "Subject",ti.FIRST_NAME "Technician", std.STATUSNAME "Request Status", ...
          • How to proactively handle problem management in your IT environment.

            This post describes the use of a python script to analyze the nature of the requests and alarms the technicians about frequently raised requests' category, and sub-category. This may help the technicians to proactively start their investigation in ...
          • Pending Request for more than 10 days

            This report provides a quick view of all Pending incidents which are not closed for more than 10 days. When the incidents start backlogging faster than they can be resolved, the tendency of long incidents pending time can be exposed in this report. ...
          • Close change requests using custom schedules.

            This post describes the use of a python script to close change that are in a specific stage using Custom Schedules.   Use Case: There may be a scenario where number of old Change Requests that have gotten lost in the shuffle over the years. Most of ...