Deluge Custom Function - Custom Schedules
Sample Scripts for custom schedule.
Steps to configure:
1. Copy the respective query and save the query report with a name.
2. Update the query report name in the custom schedule function script.
3. Configure a custom schedule with the custom function and the respective query report as the parameter.
4. Query column names need to be updated in the script.
Configuration Steps:
1. Save the query report with a name.
2. Enable the Custom Schedule and update the report name in the script.
3. Configure the Custom Schedule with the report as the parameter.
Usecase - 1:
Send notification mail to the technician if the requester does not reply to the pending request for more than 10days.
Handled using the last updated time of the request
PSSQL Query:
select workorder.workorderid "workorderid", (aau.FIRST_NAME) AS "technician" ,(AaaContactInfo.EMAILID) "emailid", LONGTODATE(workorderstates.last_tech_update) "last_tech_update" from workorder
LEFT join workorderstates on workorder.workorderid=workorderstates.workorderid
LEFT JOIN SDUser sdu ON workorderstates.ownerid=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN AaaUserContactInfo ON aau.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT join statusdefinition on workorderstates.statusid=statusdefinition.statusid
where AaaContactInfo.EMAILID != '' and statusdefinition.statusname = 'open' and
extract(epoch from(now()::TIMESTAMP - to_timestamp(workorderstates.last_tech_update/1000)::TIMESTAMP))/3600/24 > 10
MSSQL Query:
select workorder.workorderid "workorderid", (aau.FIRST_NAME) AS "technician" ,(AaaContactInfo.EMAILID) "emailid", LONGTODATE(workorderstates.last_tech_update) "last_tech_update" from workorder
LEFT join workorderstates on workorder.workorderid=workorderstates.workorderid
LEFT JOIN SDUser sdu ON workorderstates.ownerid=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN AaaUserContactInfo ON aau.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT join statusdefinition on workorderstates.statusid=statusdefinition.statusid
where AaaContactInfo.EMAILID != '' and statusdefinition.statusname = 'open' and DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(workorderstates.last_tech_update/1000),'1970-01-01 00:00:00'),GETDATE()) > 10
Usecase - 2:
Send notification mail to HR 3days before the termination date(user defined date fields)
PSSQL Query:
select workorder.workorderid "workorderid", (aau.FIRST_NAME) AS "technician" ,(AaaContactInfo.EMAILID) "emailid", LONGTODATE(wof.udf_date1) "termination_date", extract(epoch from( to_timestamp(wof.udf_date1/1000)::TIMESTAMP - now()::TIMESTAMP))/3600/24 "date_diff" from workorder
LEFT join workorderstates on workorder.workorderid=workorderstates.workorderid
LEFT JOIN SDUser sdu ON workorderstates.ownerid=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN AaaUserContactInfo ON aau.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT join statusdefinition on workorderstates.statusid=statusdefinition.statusid
left join workorder_fields wof on workorder.workorderid=wof.workorderid
where AaaContactInfo.EMAILID != '' and statusdefinition.statusname = 'open' and
(extract(epoch from( to_timestamp(wof.udf_date1/1000)::TIMESTAMP - now()::TIMESTAMP))/3600/24 >=1 and
extract(epoch from( to_timestamp(wof.udf_date1/1000)::TIMESTAMP - now()::TIMESTAMP))/3600/24 <=3 )
MSSQL Query:
select workorder.workorderid "workorderid", (aau.FIRST_NAME) AS "technician" ,(AaaContactInfo.EMAILID) "emailid", LONGTODATE(wof.udf_date1) "termination_date", (wof.udf_date1/1000)-DATEDIFF(s,'19700101',GETDATE()))/60/60/24 "date_diff" from workorder
LEFT join workorderstates on workorder.workorderid=workorderstates.workorderid
LEFT JOIN SDUser sdu ON workorderstates.ownerid=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN AaaUserContactInfo ON aau.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT join statusdefinition on workorderstates.statusid=statusdefinition.statusid
left join workorder_fields wof on workorder.workorderid=wof.workorderid
where AaaContactInfo.EMAILID != '' and statusdefinition.statusname = 'open' and
((wof.udf_date1/1000)-DATEDIFF(s,'19700101',GETDATE()))/60/60/24 >=1 and ((wof.udf_date1/1000)-DATEDIFF(s,'19700101',GETDATE()))/60/60/24 <=3
Usecase - 3:
Closing a request with default resolution if there is no update for 10 days. Handled using "Last Updated Time" of the request.
PSSQL Query:
select workorder.helpdeskid "portalid", workorder.workorderid "workorderid", LONGTODATE(workorderstates.last_tech_update) "last_tech_update" from workorder
LEFT join workorderstates on workorder.workorderid=workorderstates.workorderid
LEFT join statusdefinition on workorderstates.statusid=statusdefinition.statusid
where statusdefinition.statusname= 'Onhold' and extract(epoch from(now()::TIMESTAMP - to_timestamp(workorderstates.last_tech_update/1000)::TIMESTAMP))/3600/24 > 10
MSSQL Query:
select workorder.helpdeskid "portalid",workorder.workorderid "workorderid", LONGTODATE(workorderstates.last_tech_update) "last_tech_update" from workorder
LEFT join workorderstates on workorder.workorderid=workorderstates.workorderid
LEFT join statusdefinition on workorderstates.statusid=statusdefinition.statusid
where statusdefinition.statusname= 'Waiting on user' and DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(workorderstates.last_tech_update/1000),'1970-01-01 00:00:00'),GETDATE()) > 10
Usecase - 4:
Send notification mail to technician after violating a date field. (eg: Investigation Date)
PSSQL Query:
select workorder.workorderid "workorderid", (aau.FIRST_NAME) AS "technician" ,(AaaContactInfo.EMAILID) "emailid", LONGTODATE(wof.udf_date1) "investigation_date" from workorder
LEFT join workorderstates on workorder.workorderid=workorderstates.workorderid
LEFT JOIN SDUser sdu ON workorderstates.ownerid=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN AaaUserContactInfo ON aau.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT join statusdefinition on workorderstates.statusid=statusdefinition.statusid
left join workorder_fields wof on workorder.workorderid=wof.workorderid
where AaaContactInfo.EMAILID != '' and statusdefinition.statusname = 'open' and extract(epoch from(now()::TIMESTAMP - to_timestamp(wof.udf_date1/1000)::TIMESTAMP))/3600/24 >=1
MSSQL Query:
select workorder.workorderid "workorderid", (aau.FIRST_NAME) AS "technician" ,(AaaContactInfo.EMAILID) "emailid", LONGTODATE(wof.udf_date1) "investigation_date" from workorder
LEFT join workorderstates on workorder.workorderid=workorderstates.workorderid
LEFT JOIN SDUser sdu ON workorderstates.ownerid=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN AaaUserContactInfo ON aau.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT join statusdefinition on workorderstates.statusid=statusdefinition.statusid
left join workorder_fields wof on workorder.workorderid=wof.workorderid
where AaaContactInfo.EMAILID != '' and statusdefinition.statusname = 'open' and DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(wof.udf_date1/1000),'1970-01-01 00:00:00'),GETDATE()) >= 1
Usecase - 5:
Send 3 reminder notifications(1 notification per day) to technicians when the request is pending for last 3 days. After the 3rd notification ticket will be assigned to some other user
PSSQL Query:
select workorder.helpdeskid "portalid", workorder.workorderid "workorderid", (aau.FIRST_NAME) AS "technician", (AaaContactInfo.EMAILID) "emailid", LONGTODATE(workorder.createdtime) "createdtime", ROUND(extract(epoch from(now()::TIMESTAMP - to_timestamp(workorder.createdtime/1000)::TIMESTAMP))/3600/24) "date_diff" from workorder
LEFT join workorderstates on workorder.workorderid=workorderstates.workorderid
LEFT JOIN SDUser sdu ON workorderstates.ownerid=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN AaaUserContactInfo ON aau.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT join statusdefinition on workorderstates.statusid=statusdefinition.statusid where AaaContactInfo.EMAILID != '' and statusdefinition.statusname = 'open' and extract(epoch from(now()::TIMESTAMP - to_timestamp(workorder.createdtime/1000)::TIMESTAMP))/3600/24 > 1 and extract(epoch from(now()::TIMESTAMP - to_timestamp(workorder.createdtime/1000)::TIMESTAMP))/3600/24 >3
MSSQL Query:
select workorder.helpdeskid "portalid", workorder.workorderid "workorderid", (aau.FIRST_NAME) AS "technician" ,(AaaContactInfo.EMAILID) "emailid", LONGTODATE(workorder.createdtime) "createdtime" from workorder
LEFT join workorderstates on workorder.workorderid=workorderstates.workorderid
LEFT JOIN SDUser sdu ON workorderstates.ownerid=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN AaaUserContactInfo ON aau.USER_ID=AaaUserContactInfo.USER_ID
LEFT JOIN AaaContactInfo ON AaaUserContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT join statusdefinition on workorderstates.statusid=statusdefinition.statusid where AaaContactInfo.EMAILID != '' and statusdefinition.statusname = 'open' and
DATEDIFF(day, dateadd(s,datediff(s,GETUTCDATE() ,getdate()) +(workorder.createdtime/1000),'1970-01-01 00:00:00'),GETDATE()) >3
New to ADSelfService Plus?
Related Articles
Update Request Additional fields during request create/update using Custom Trigger - Deluge
This is a sample custom function script to update the Request Additional Field Values during request create/update based on certain criteria using Custom Triggers. Refer to this link for Python ...How to implement dynamic request approval using FAFR and custom triggers - Deluge
This is a sample script written in Deluge to handle Conditional Approvals for Incident \ Service Requests, through Custom Triggers using Field and Form Rules. One of the advantages of using this script that no modification in the script is required ...Update a request based on certain values - Deluge
This post describes the use of a deluge script to update requests based on values given a global function, namely "request_properties". This action can be performed using Custom Triggers. Refer to this link for Python ...Create multiple Child Requests - Deluge Script
This is a sample deluge script to automatically trigger the creation of multiple child requests from a parent request, based on the request template chosen. The request ID of the child requests will be added as a Note in the parent request. This is ...Create service requests through email using Business Rule - Deluge
This post describes the use of custom function scripts to create a service request through email based on keywords in the subject. When a request is created with specific keywords in the subject of a incoming mail , the Business Rule will be ...