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 ...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 ...