Bulk Closure of Overdue Changes

Bulk Closure of Overdue Changes

Requirement:  Bulk close changes with different workflows and roles, filtered based on modifiable criteria as needed.

Steps to configure:
  1. Login to SDP Application > Navigate to reports
  2. Create a query report, listing the "ChangeID", "ChangeManager", "ChangeOwner".
    1. Sample Query:  SELECT chdt.CHANGEID AS "ChangeID", chdt.changemanagerid as "ChangeManager", chdt.technicianid as "ChangeOwner" FROM ChangeDetails chdt LEFT JOIN Change_StatusDefinition statusDef ON chdt.WFSTATUSID = statusDef.WFSTATUSID LEFT JOIN ChangeTemplate tempDef ON chdt.TEMPLATEID = tempDef.TEMPLATEID LEFT JOIN Change_StageDefinition stage on stage.wfstageid = chdt.wfstageid WHERE tempDef.NAME = 'Standard Change' AND statusDef.STATUSDISPLAYNAME != 'Completed' or stage.displayname != 'Close' AND chdt.DELETEDTIME IS NULL AND chdt.CREATEDTIME < <from_lastmonth>
    2. This query can be modified to suit your needs.  Current query would list all changes in the "Standard Change" template that are not "Completed" or "Closed," not deleted, and created before the last month. 
    3. Save this report with a name, and in my case i have saved it as "ChangesToBeClosed"
  3. Go to Admin > Developer Space > Custom Schedule Function > New > Copy paste the contents of CloseChange.txt and save it with a name.
    1. Update the hosted url and Technician key as per your instance.
    2. For Integration key/Technician key refer this KB link: https://pitstop.manageengine.com/portal/en/kb/articles/creating-integration-key
    3. Create an integration key with SDChangeManager role.
    4. Update the reportName, stage, status, stageID as per your instance. In my case the report name is "ChangesToBeClosed"
  4. Configure a custom schedule to invoke this newly created custom function, passing the custom query report as a parameter. If needed, this can also be set up on a periodic basis.

                    New to ADSelfService Plus?

                      • Related Articles

                      • Script: Close associated changes when a request is closed

                        Use case:  In many cases, the client never comes back to confirm the change. This means that the change remains opened indefinitely. When the ticket is closed it is logical that the change to be closed. No one is going to close a ticket unless the ...
                      • Query to show priority changes in the ticket (MSSQL)

                        Tested in MSSQL build (14306) This query shows which tickets were downgraded from P1. MSSQL: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", LONGTODATE(wo.DUEBYTIME) "DueBy ...
                      • Query to show overdue tickets with delay by days

                        Working on Build's: 14500 PGSQL: SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", wos.ISOVERDUE AS "Overdue Status", ad.ORG_NAME AS "Account", CAST (DATE_PART('day', now() - FROM_UNIXTIME(wo.DUEBYTIME /1000)) AS varchar)"Days since ...
                      • Query to show requests and its associations with Problems, Changes and Projects (MSSQL)

                        Working on Builds: 14500 and above Database: MSSQL SELECT wo.WORKORDERID AS "Request ID", pim.problemid "Associated Problem ID", icm.changeid "Associated Change ID", wtp.projectid "Associated Project ID", CASE WHEN (wo.is_catalog_template) = 'false' ...
                      • Request Summary (Inbound,completed and overdue)

                        This report provides a quick overview of monthly opened, closed and overdue incidents. It provides you a quick determination of a large number of incidents. This information can then be used by an Incident Management administrator to determine if a ...