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
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, ...Unable to restore data after changing DBserver to SQL
After changing the DB server to MSSQL, We tried to restore the data from the backup file, but the restore didn't take place successfully so we followed those steps and everything worked perfectly. Please follow the below steps and re-initiate the ...Delete Large System error logs records
Problem: for large Log files (over 1 million records) When we select the "delete all" button in the system log viewer in the support tab the system Crashed ! So How can we delete these large log files? Solution: we can delete them from database ...Database Warning : Your database transaction log file size has crossed the threshold
You are getting this email as a result of an enhancement that was made in the 11 version to notify administrators if the SQL Log File Size crossed the threshold. In Service Desk Plus, under Performance Settings in Admin/ESM Directory you have the ...