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 ADManager Plus?

                  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 ...
                    • 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 ...
                    • Contract expire in next 30 days

                      MSSQL SELECT mcdt.CONTRACTNAME "Contract Name", r.resourcename "Asset", longtodate(mcdt.todate) "Date" FROM MaintenanceContract mcdt LEFT JOIN contractdetails cd ON cd.contractid = mcdt.contractid LEFT JOIN resources r ON r.resourceid = cd.resourceid ...
                    • How to auto-create worklog based on values specified in the request

                      This post will to help you create a worklog automatically when a request is created. This script can be configured under Request custom triggers and you will find more information about this here. This is achieved using an API call to create a ...