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
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
hereRefer to the link below in order to setup the schedule,