Configuration Management - complete list of request templates
This report returns the complete list of templates configured in the application.
MSSQL:
SELECT serd.Name "Service Category",
max(list.Templatename) "Template Name",
STUFF(
(SELECT ',' + tgm.QUEUENAME
FROM TemplateToGroupMapping tgm
WHERE list.TemplateID=tgm.TemplateID
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') "Group",
STUFF(
(SELECT ',' + ug.NAME
FROM UserGroups ug
LEFT JOIN TemplateUGMapping tugm ON ug.ID=tugm.USERGROUPID
WHERE list.TemplateID=tugm.TemplateID
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') "User Group",
(slad.slaname) "Sla name",
max(slad.DUEBYDAYS) "DUEBYDAYS",
max(slad.duebyhours) "duebyhours",
STUFF(
(SELECT ',' + (CASE
WHEN EscalateToN.orgroleid ='1' THEN 'DEPHEAD'
WHEN EscalateToN.orgroleid ='2' THEN 'REPORTINGTO'
ELSE aaa1.FIRST_NAME
END)
FROM EscalateToN
LEFT JOIN aaauser aaa1 ON EscalateToN.USERID=aaa1.USER_ID
LEFT JOIN orgroles org ON EscalateToN.orgroleid=org.orgroleid
LEFT JOIN EscalateToMediator ON EscalateToN.ESCALATETOID=EscalateToMediator.ESCALATETOID
LEFT JOIN ServiceApproverMapping ON EscalateToMediator.ESCALATETOID=ServiceApproverMapping.ESCALATETOID
WHERE list.TEMPLATEID=ServiceApproverMapping.SERVICETEMPLATEID
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 1, '') "Approver" FROM RequestTemplate_list list
LEFT JOIN sladefinition slad ON list.slaid=slad.slaid
LEFT JOIN RequestTemplate_fields field ON list.TEMPLATEID=field.TEMPLATEID
LEFT JOIN Aaauser aaa ON list.Createdby=aaa.USER_ID
LEFT JOIN sduser sd ON field.TechnicianID=sd.userid
LEFT JOIN AaaUser aau ON sd.userid=aau.user_id
LEFT JOIN ServiceDefinition serd ON list.PARENT_SERVICE=serd.SERVICEID
GROUP BY serd.Name,
list.Templateid,
slad.slaname
ORDER BY 2
PGSQL:
SELECT serd.Name "Service Category",
list.Templatename "Template Name",
array_to_string(array_agg(tgm.QUEUENAME), ',') "Group",
array_to_string(array_agg(ug.NAME), ',') "User Group",
slad.slaname "Sla name",
max(slad.DUEBYDAYS) "Duedate",
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 list
LEFT JOIN sladefinition slad ON list.slaid=slad.slaid
LEFT JOIN RequestTemplate_fields field ON list.TEMPLATEID=field.TEMPLATEID
LEFT JOIN Aaauser aaa ON list.Createdby=aaa.USER_ID
LEFT JOIN sduser sd ON field.TechnicianID=sd.userid
LEFT JOIN AaaUser aau ON sd.userid=aau.user_id
LEFT JOIN ServiceDefinition serd ON list.PARENT_SERVICE=serd.SERVICEID
LEFT JOIN TemplateToGroupMapping tgm ON list.TemplateID=tgm.TemplateID
LEFT JOIN TemplateUGMapping tugm ON list.TemplateID=tugm.TemplateID
LEFT JOIN UserGroups ug ON tugm.USERGROUPID=ug.ID
LEFT JOIN ServiceApproverMapping ON list.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
GROUP BY serd.Name,
list.Templatename,
slad.slaname
ORDER BY 1
Click this link to navigate to the next report.