How to auto-create request for contract expiry.

How to auto-create request for contract expiry.

This post describes the use of a python script to auto-create requests for contract expiry using Custom Schedules.

Use case:
If the contract going to expire in next 7 days, we can create a ticket with the details for the contract in ServiceDesk Plus, and this ticket is assigned to a person who is responsible for managing contracts, extending them etc. So all work related to this subject is carried out in ServiceDesk Plus environment. From this, we can manage workflow for this kind of situation and have all the trackable history in one ServiceDesk plus tool.
 

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

MSSQL

SELECT max(mcdt.helpdeskid) "portalid",mcdt.Customcontractid "contractid", max(mcdt.CONTRACTNAME) "name", max(contractcategory.Categoryname) "type", max(mcdt.TOTALPRICE) "cost", max(aao.NAME) "vendor", max(cbyaau.FIRST_NAME) "requester", max(cst.STATUSNAME) "Contract Status", max(Resource.RESOURCENAME) "asset",max(mcdt.FROMDATE) "fromdate",max(mcdt.TODATE) "todate" FROM MaintenanceContract mcdt LEFT JOIN VendorDefinition vdn ON mcdt.MAINTENANCEVENDOR = vdn.VENDORID LEFT JOIN SDOrganization aao ON vdn.VENDORID = aao.ORG_ID LEFT JOIN SDUser cby ON mcdt.CREATEDBY = cby.USERID LEFT JOIN AaaUser cbyaau ON cby.USERID = cbyaau.USER_ID LEFT JOIN ContractStatus cst ON mcdt.STATUSID = cst.STATUSID LEFT JOIN ContractDetails ContractDetails ON mcdt.CONTRACTID = ContractDetails.CONTRACTID LEFT JOIN Resources resource ON ContractDetails.RESOURCEID = Resource.RESOURCEID LEFT JOIN contractcategory contractcategory ON mcdt.categoryid = contractcategory.categoryid WHERE 
((mcdt.todate/1000)-DATEDIFF(s,'19700101',GETDATE()))/60/60/24 >=1   and  
((mcdt.todate/1000)-DATEDIFF(s,'19700101',GETDATE()))/60/60/24 <=7  
GROUP BY mcdt.Customcontractid 


PGSQL

SELECT max(mcdt.helpdeskid) "portalid",mcdt.Customcontractid "contractid", max(mcdt.CONTRACTNAME) "name", max(contractcategory.Categoryname) "type", max(mcdt.TOTALPRICE) "cost", max(aao.NAME) "vendor", max(cbyaau.FIRST_NAME) "requester", max(cst.STATUSNAME) "Contract Status", max(Resource.RESOURCENAME) "asset",max(mcdt.FROMDATE) "fromdate",max(mcdt.TODATE) "todate" FROM MaintenanceContract mcdt LEFT JOIN VendorDefinition vdn ON mcdt.MAINTENANCEVENDOR = vdn.VENDORID LEFT JOIN SDOrganization aao ON vdn.VENDORID = aao.ORG_ID LEFT JOIN SDUser cby ON mcdt.CREATEDBY = cby.USERID LEFT JOIN AaaUser cbyaau ON cby.USERID = cbyaau.USER_ID LEFT JOIN ContractStatus cst ON mcdt.STATUSID = cst.STATUSID LEFT JOIN ContractDetails ContractDetails ON mcdt.CONTRACTID = ContractDetails.CONTRACTID LEFT JOIN Resources resource ON ContractDetails.RESOURCEID = Resource.RESOURCEID LEFT JOIN contractcategory contractcategory ON mcdt.categoryid = contractcategory.categoryid WHERE extract(epoch FROM (to_timestamp(mcdt.todate / 1000) :: TIMESTAMP -now() :: TIMESTAMP)) / 3600 / 24 >= 1 AND extract(epoch FROM (to_timestamp(mcdt.todate / 1000) :: TIMESTAMP -now() :: TIMESTAMP)) / 3600 / 24 <= 7 GROUP BY mcdt.Customcontractid 
order by 1

2. Download and place the script file in the ManageEngine/ ServiceDesk/ integration/custom_scripts folder.
3.  Configure the schedule under Admin--> Custom Schedule like shown below.
4.Edit configuration.json file and update the application URL and the Technician Key

I've attached a screenshot showing a sample implementation below.





A short guide on setting up python is available here
Refer to the link below in order to setup the schedule,



                  New to ADSelfService Plus?

                    • Related Articles

                    • Auto-create problem request based on certain criteria.

                      This is a sample script that triggers the creation of a Problem Request in ServiceDesk Plus using the Problem API.  Use Case:  ​ All incident requests with Priority = High will create a problem ticket with the request's default field values. ...
                    • How to auto-create change request(s) for unplanned changes on Assets

                      This post describes the use of a python script to auto-create change requests for unplanned modifications on Assets using Custom Schedules. This script is applicable only for builds prior to 11138. UseCase: Multiple assets are inventoried in ...
                    • Create New request using Python Script

                      Script to raise a new request using Python Script. Pre-requisite - Python should be installed in the server and configured. You can review the instructions here - Requests module should be installed with the python Steps 1. Download the zip file from ...
                    • How to create a request using an existing template through powershell.

                      This is a Powershell script that calls the ServiceDesk Plus API to create a new request based on an existing Incident or Service Request template name. UseCase: We have Considered a Service Request for 'New Hire' in this script.The Answer that is ...
                    • Close the request upon adding a note.

                      Kindly follow the steps mentioned below: 1) Go to Admin >> Developer Space >> Custom Function >> Global Functions >> Copy & Paste the attached GF_Content.txt and save it with a name. Update the URL and technician key as per your instance. Refer to ...