Script to Auto close Request when the status is Resolved for a particular number of days

Script to Auto close Request when the status is Resolved for a particular number of days

For version 11 and above:

-> In version 11, we only have the option to set the request Auto-close for a max of 10 days

-> The below script will help you to extend the auto-closure time


Execution steps:

1. Go to Reports--> New Query Report and run the below query and save this report.


MSSQL

SELECT wo.workorderid AS "Request ID", ad.org_name AS "Account", wo.helpdeskid AS "portalID" FROM workorder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID
LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID 
LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID
LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID
WHERE (wo.ISPARENT='1') and  std.statusname='Resolved'  
and wohd.COLUMNNAME in ('STATUSID')  
 group by wo.WORKORDERID,ad.org_name having DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(max(woh.OPERATIONTIME)/1000),'1970-01-01 00:00:00'),GETDATE()) > 30



PGSQL

SELECT wo.workorderid AS "Request ID", ad.org_name AS "Account", wo.helpdeskid AS "portalID" FROM workorder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID
LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID 
LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID
LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID
WHERE (wo.ISPARENT='1') and  std.statusname='Resolved'  
and wohd.COLUMNNAME in ('STATUSID')  
 group by wo.WORKORDERID,ad.org_name having extract(epoch from(now()::TIMESTAMP -  to_timestamp(max(woh.OPERATIONTIME)/1000)::TIMESTAMP))/3600/24 > 30

**This will give you the ID of requests under Resolved status which are more than 30 days. Save the Report as Resolved Request**

Note: You can change the number of days from the above query 

2. Downloaded and place the script files in the ManageEngine/ ServiceDeskPlus-MSP/ integration/custom_scripts folder.

3. Update the  application URL and the Technician Key  in the script with the help of the comments provided within.

4. Configure the schedule under Admin--> Custom Schedule





A short guide on setting up python is available here.

                New to ADManager Plus?

                  New to ADSelfService Plus?

                    • Related Articles

                    • Changing the RequestID to start from a particular number

                      To change the RequestID to an number of you choice follow steps below. 1. Connect to SupportCenterPluss MySql database  eg) C:\\SupportCenter\\mysql\\bin>mysql.exe -u root -P 33356 supportcenter 2. Insert a dummy request with requestid and ...
                    • Set Request Id to start from a particular number - Version 11.0.

                      Regarding the First Portal (General), you can use the below steps to change the Request Id to start from 136000. 1.The below Select Query will give the last active ticket ID. Select * from SeqGenState where seqname='WorkOrder.WORKORDERID'; 2.Then, ...
                    • Update status using Python script in 11.0

                      Using a Python script via custom trigger, we can update the status invoking an API call. A short guide on setting up python is available here : https://pitstop.manageengine.com/portal/kb/articles/how-to-setup-python-on-the-server Find the attached ...
                    • Scripts to update request status when a new reply is received.

                      Please do refer to the below steps to change the status when a Contact sends a new reply to the ticket. 1. Download the script files from the below link and extract them and place it under..\ManageEngine\SupportCenter\integration\custom_scripts - ...
                    • Phone number need to be mandatory in 8.1 for contacts

                      Kindly follow the steps below to create the FAFR, 1. Access admin Module 2. Select the request template option and click the edit option the required request template the script needs to be applied on 3. Select the Fields and Forms Rule tab and paste ...