Template configuration

Template configuration

This report will give you 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





          • Related Articles

          • Incident and Service Catalog Template Frequency

            1 . This report is used to find the most used template in the application.   To make any changes to a query, refer to the KB article below. https://pitstop.manageengine.com/support/manageengine/ShowHomePage.do#Solutions/dv/24000633501275 SELECT ...
          • Change template count

            SELECT    ct.NAME "Template",            Count(chdt.changeid) "count" FROM      changedetails chdt  LEFT JOIN changetemplate ct  ON        chdt.templateid=ct.templateid  WHERE     chdt.createdtime >= <from_lastmonth>  ...
          • How to Automatically Associate a Ticket to a Project using PrjID based on Ticket Template

            This post describes the use of a sample python script you to associate a Project automatically to a ticket during its creation/edit based on conditions, such as template matching. This script can be configured under Custom Triggers and you will find ...
          • Request based on Incident and Service Catalog Templates

            This report is to get the name of the Template applied to the request.  To make any changes to a query, refer to the KB article below. https://pitstop.manageengine.com/support/manageengine/ShowHomePage.do#Solutions/dv/24000633501275 SELECT ...
          • Debug jar for request template meta info

            Debug jar for request template meta info