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.