List of Services Template Report
Tthe below Query to list all service catalog in the system
Database : PostgreSQL Server - Version: 10.5
SELECT serd.Name "Service Category",
reqtl.templatename "Service catalog",
sla.slaname "SLA" ,
sdo.name "Site",
array_to_string(array_agg(CASE
WHEN EscalateToN.orgroleid ='1' THEN 'DEPHEAD'
WHEN EscalateToN.orgroleid ='2' THEN 'REPORTINGTO'
ELSE (aaa1.FIRST_NAME)
END), ',') "Approver" FROM RequestTemplate_list reqtl
LEFT JOIN ServiceDefinition serd ON reqtl.PARENT_SERVICE=serd.SERVICEID
LEFT JOIN SC_TemplateSLAAssociation sct ON sct.template=reqtl.templateid
LEFT JOIN sladefinition sla ON sla.slaid=sct.sla
LEFT JOIN sdorganization sdo ON sdo.org_id=sla.siteid
LEFT JOIN srapproverdetails ON srapproverdetails.id=serd.serviceid
LEFT JOIN aaauser ON aaauser.user_id = srapproverdetails.approver
LEFT JOIN aaacontactinfo ON aaauser.user_id = aaacontactinfo.contactinfo_id
LEFT JOIN sduser sd ON aaauser.user_id=sd.userid
LEFT JOIN userdepartment ud ON aaauser.user_id=ud.userid
LEFT JOIN DepartmentDefinition dpt ON ud.DEPTID=dpt.DEPTID
LEFT JOIN SDOrganization sdoa ON dpt.SITEID=sdoa.ORG_ID
LEFT JOIN ServiceApproverMapping ON reqtl.TEMPLATEID=ServiceApproverMapping.SERVICETEMPLATEID
LEFT JOIN EscalateToMediator ON ServiceApproverMapping.ESCALATETOID=EscalateToMediator.ESCALATETOID
LEFT JOIN EscalateToN ON EscalateToMediator.ESCALATETOID=EscalateToN.ESCALATETOID
LEFT JOIN orgroles org ON EscalateToN.orgroleid=org.orgroleid
LEFT JOIN AaaUser aaa1 ON EscalateToN.USERID=aaa1.USER_ID
WHERE reqtl.isdeleted='0'
group by 1,2,3,4
order by 1
reqtl.templatename "Service catalog",
sla.slaname "SLA" ,
sdo.name "Site",
array_to_string(array_agg(CASE
WHEN EscalateToN.orgroleid ='1' THEN 'DEPHEAD'
WHEN EscalateToN.orgroleid ='2' THEN 'REPORTINGTO'
ELSE (aaa1.FIRST_NAME)
END), ',') "Approver" FROM RequestTemplate_list reqtl
LEFT JOIN ServiceDefinition serd ON reqtl.PARENT_SERVICE=serd.SERVICEID
LEFT JOIN SC_TemplateSLAAssociation sct ON sct.template=reqtl.templateid
LEFT JOIN sladefinition sla ON sla.slaid=sct.sla
LEFT JOIN sdorganization sdo ON sdo.org_id=sla.siteid
LEFT JOIN srapproverdetails ON srapproverdetails.id=serd.serviceid
LEFT JOIN aaauser ON aaauser.user_id = srapproverdetails.approver
LEFT JOIN aaacontactinfo ON aaauser.user_id = aaacontactinfo.contactinfo_id
LEFT JOIN sduser sd ON aaauser.user_id=sd.userid
LEFT JOIN userdepartment ud ON aaauser.user_id=ud.userid
LEFT JOIN DepartmentDefinition dpt ON ud.DEPTID=dpt.DEPTID
LEFT JOIN SDOrganization sdoa ON dpt.SITEID=sdoa.ORG_ID
LEFT JOIN ServiceApproverMapping ON reqtl.TEMPLATEID=ServiceApproverMapping.SERVICETEMPLATEID
LEFT JOIN EscalateToMediator ON ServiceApproverMapping.ESCALATETOID=EscalateToMediator.ESCALATETOID
LEFT JOIN EscalateToN ON EscalateToMediator.ESCALATETOID=EscalateToN.ESCALATETOID
LEFT JOIN orgroles org ON EscalateToN.orgroleid=org.orgroleid
LEFT JOIN AaaUser aaa1 ON EscalateToN.USERID=aaa1.USER_ID
WHERE reqtl.isdeleted='0'
group by 1,2,3,4
order by 1
Related Articles
Change Implementation Query Report
Purpose This report extracts Change, Task, and Worklog details specifically for changes in the Implementation stage. It provides a consolidated view to track: Change progress Task execution Ownership and priorities Worklog activity The report ...Project Details (Milestone and Task Tracking) Query Report
Purpose This report extracts project-level details to provide a comprehensive overview of project performance and tracking. It helps monitor: Project progress Schedule adherence Cost performance Effort estimation vs actuals The report includes the ...Time Elapsed not avavilble on the Matrix report || Known issue at 15130 build || SD-136533
Dear Team, Our client GCA is encountering an issue regarding the matrix reports, where the Time Elapsed column is not fetching and is not available as a selection when you try to create, so we referred to the support team, and after investigation, ...Service Desk services stopped on its own.
The reason for the crash is becauss the SQL database connectivity was lost. It's looks like this while checking the log file : [15:54:58:066]|[10-11-2018]|[SYSERR]|[INFO]|[72]: 101603933 [ServiceDeskCommon-2] WARN tm.TransactionImpl - XAException: ...Error while saving the email server details : Duplicate entry for key
This Problem may happen when the application is holding to different mail server details. 1- So go to Reports > New query report and execute the following query : select * from IncomingMailServer 2- If you see multiple mail server configured in this, ...