Problem while adding or editing request custom views, request life cycle, request business rules and custom triggers
Issue - If below trace is found when trying to add or edit request life cycle, request custom views, request business rules or request custom trigger, then following procedure can be followed
Reason - Issue occurs if service category and service additional fields related tables like servicemetatable, dynamic tables contains orphan entries for that particular service category
Error traces :
[16:54:33:550]|[04-12-2023]|[com.manageengine.sdpod.v3api.JSONDOConverter]|[SEVERE]|[56]: error while forming dynamic fields..|
java.lang.Exception: No Service table found for the service 4801 ----> Service category ID
at com.manageengine.servicedesk.utils.ServiceCatalogUtil.getServiceTable(ServiceCatalogUtil.java:1688)
at com.manageengine.servicedesk.utils.ServiceCatalogUtil.getServiceTable(ServiceCatalogUtil.java:1634)
at com.manageengine.servicedesk.utils.ServiceCatalogUtil.getAllServiceTablesUsingMDHSettings(ServiceCatalogUtil.java:5814)
at com.manageengine.servicedesk.utils.ServiceCatalogUtil.getAllServiceTablesUsingMDHSettings(ServiceCatalogUtil.java:5798)
at com.manageengine.sdpod.v3api.handlerimpl.WorkOrderHandler.getDynamicFields(WorkOrderHandler.java:1038)
at com.manageengine.servicedesk.v3api.impl.ApiImpl.getDynamicFields(ApiImpl.java:358)
at com.manageengine.sdpod.v3api.handlerimpl.UDFValueHandler.getDynamicFields(UDFValueHandler.java:321)
at com.manageengine.sdpod.v3api.handlerimpl.UDFValueHandler.getDynamicField(UDFValueHandler.java:205)
at com.manageengine.sdpod.v3api.entity.EntityFields.getFieldByName(EntityFields.java:149)
at com.manageengine.sdpod.v3api.entity.EntityLocator.getURIParseResult(EntityLocator.java:159)
at com.manageengine.sdpod.v3api.APIRequest.getInstance(APIRequest.java:210)
at com.manageengine.servicedesk.sdpapi.v2.servlet.SDPAPIV2Servlet.handleCall(SDPAPIV2Servlet.java:269)
at com.manageengine.servicedesk.sdpapi.v2.servlet.SDPAPIV2Servlet.doOperation(SDPAPIV2Servlet.java:228)
at com.manageengine.servicedesk.sdpapi.v2.servlet.SDPAPIV2Servlet.doGet(SDPAPIV2Servlet.java:95)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:655)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:764)
Queries to be executed directly from db:
1. select * from servicemetatable where serviceid =4801;
2. select * from dynamictables where tablename in ('ServiceReq_4801','SServiceReq_4801','ARC_ServiceReq_4801');
3. select * from tabledetails where table_name in ('ServiceReq_4801','SServiceReq_4801','ARC_ServiceReq_4801');
4. select * from columndetails where table_id not in (select table_id from tabledetails);
5. select * from servicereq_4801;
6. select * from sservicereq_4801;
7. select * from arc_servicereq_4801;
8. select * from servicedefinition where serviceid=4801;
Replace '4801' with service category id which is found in error trace
To resolve the issue:
Scenario 1 :
Scenario 1 :
If query 5 to 7, all throws error like no table is present and if query 1 to 4 contains data, then it is safe to delete those orphan entries using below queries
1. delete from servicemetatable where serviceid =4801;
2. delete from dynamictables where tablename in ('ServiceReq_4801','SServiceReq_4801','ARC_ServiceReq_4801');
3. delete from tabledetails where table_name in ('ServiceReq_4801','SServiceReq_4801','ARC_ServiceReq_4801');
4. delete from columndetails where table_id not in (select table_id from tabledetails);
From query 8, we'll get the service category name for which issue occurs. After executing the above delete query, restart the application (In some cases tabledetails mickey cache is not getting dropped) and we can just dummy update this service category to resolve the issue
From query 8, we'll get the service category name for which issue occurs. After executing the above delete query, restart the application (In some cases tabledetails mickey cache is not getting dropped) and we can just dummy update this service category to resolve the issue
Scenario 2 :
If query 5 to 7, all throws error like no table is present and if query 1 to 4 also contains no data, then we can just simply dummy update the service category and check if issue is resolved
If any one of the tables (servicereq, sservicereq, arc_servicereq) is present, Kindly move the ticket to G2-Padma team. Do not follow this KB.
If query 5 to 7, all throws error like no table is present and if query 1 to 4 also contains no data, then we can just simply dummy update the service category and check if issue is resolved
If any one of the tables (servicereq, sservicereq, arc_servicereq) is present, Kindly move the ticket to G2-Padma team. Do not follow this KB.
If there are many Service Categories that throw this error, use the following query to return all the Service Categories where the data is inconsistent.
PGSQL query
select a.serviceid AS "Service ID", a.servicename AS "Service Name", a.dytablename AS "Dynamic Table Name", case when a.tableinfo is null then 'Table Not present' else 'Table Present' end AS "Physical Table exists" from (select sd.serviceid as "serviceid",sd.name as "servicename", dt.tablename as "dytablename", (SELECT 1 FROM information_schema.tables infos WHERE infos.table_name ilike dt.tablename ) as "tableinfo" from servicedefinition sd left join servicemetatable smt on sd.serviceid = smt.serviceid left join dynamictables dt on smt.table_id = dt.dynamic_table_id ) a where a.tableinfo is null;
MSSQL query
select a.serviceid AS "Service ID", a.servicename AS "Service Name", a.dytablename AS "Dynamic Table Name", case when a.tableinfo is null then 'Table Not present' else 'Table Present' end AS "Physical Table exists" from (select sd.serviceid as "serviceid",sd.name as "servicename", dt.tablename as "dytablename", (SELECT 1 FROM information_schema.tables infos WHERE infos.table_name like dt.tablename ) as "tableinfo" from servicedefinition sd left join servicemetatable smt on sd.serviceid = smt.serviceid left join dynamictables dt on smt.table_id = dt.dynamic_table_id ) a where a.tableinfo is null;
New to ADSelfService Plus?
Related Articles
How to update Request Additional fields during request create/update using Business Rules
This is a sample python script to update the Request Additional Field Values during request create/update based on certain criteria using Business Rules Execution Steps: 1. Download the attached zip 2. Move the files to ...Which rule is executed first - Business Rule or Custom Trigger ?
When both Business Rule and a Custom Trigger is applied in an application, its the Business Rule that will be executed first before the Custom Trigger execution. Business rules will be executed - Before the ticket is created. Custom triggers will be ...Document for Request Custom Menu and Custom Triggers
Attached a PDF file explaining with screenshots on how Request Custom Menu & Custom Triggers work.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. ...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 ...