Template configuration

Template configuration

This report will give you the complete list of templates configured in the application. 

MSSQL

SELECT serd.Name AS "Service Category",
list.Templatename AS "Template Name",
(SELECT STUFF(
  (
        SELECT ',' + tgm.QUEUENAME FROM TemplateToGroupMapping tgm 
        LEFT JOIN RequestTemplate_List rtl1 ON rtl1.templateid = tgm.templateid 
        WHERE rtl1.templateid = rtl2.templateid    FOR XML PATH('')
  ), 1, 1, '')  from RequestTemplate_List rtl2
where rtl2.templateid = list.templateid) AS "Associated Support group",
(SELECT STUFF(
  (
        SELECT ',' + ug.name FROM usergroups ug
        LEFT JOIN templateugmapping tugm on tugm.usergroupid = ug.id
        LEFT JOIN RequestTemplate_List rtl1 ON rtl1.templateid = tugm.templateid 
        WHERE rtl1.templateid = rtl2.templateid    FOR XML PATH('')
  ), 1, 1, '')  from RequestTemplate_List rtl2
where rtl2.templateid = list.templateid) AS "Associated User group",
slad.slaname "Sla name",
(SELECT STUFF(
  ( SELECT ',' + case when etn.orgroleid ='1' then 'DEPHEAD' when  etn.orgroleid ='2' then 'REPORTINGTO' else
(aaa1.FIRST_NAME) end FROM EscalateToN etn
LEFT JOIN AaaUser aaa1 ON etn.USERID=aaa1.USER_ID 
LEFT JOIN EscalateToMediator etm ON etm.ESCALATETOID=etn.ESCALATETOID
LEFT JOIN ServiceApproverMapping sam ON  sam.ESCALATETOID=etm.ESCALATETOID
LEFT JOIN RequestTemplate_List rtl1 ON rtl1.TEMPLATEID=sam.SERVICETEMPLATEID
WHERE rtl1.templateid = rtl2.templateid    FOR XML PATH('')
  ), 1, 1, '')  from RequestTemplate_List rtl2
where rtl2.templateid = list.templateid) AS "Approver",
max(slad.DUEBYDAYS) "Duedate" FROM RequestTemplate_list list
left join SC_TEMPLATESLAASSOCIATION sla1 on list.templateid=sla1.template
left join sladefinition slad on sla1.sla=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.Templatename ,slad.slaname ,list.templateid 
ORDER BY 1

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 SC_TEMPLATESLAASSOCIATION sla1 on list.templateid=sla1.template
left join sladefinition slad on sla1.sla=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 AaaUser aaa1 ON EscalateToN.USERID=aaa1.USER_ID 
group by serd.Name,list.Templatename ,slad.slaname
ORDER BY 1
        New to M365 Manager Plus?
        New to M365 Manager Plus?
          New to RecoveryManager Plus?
          New to RecoveryManager Plus?
            New to Exchange Reporter Plus?
            New to Exchange Reporter Plus?
              New to SharePoint Manager Plus?
              New to SharePoint Manager Plus?
                  See all integrations
                  New to ADManager Plus?
                  See all integrations
                  New to ADManager Plus?

                    New to ADSelfService Plus?

                    Start your free trial
                    Start your free trial

                      • Related Articles

                      • Account specific request template: Redirection to requester's account based template from default template using FAFR

                        Tested in builds 14000, 14200 and 14201 (Global FAFR): Step 1: In Global FAFR, on form load, configure an FAFR and execute the below script under Action as shown below, try{ $req.form.destroyForm(undefined, false); }catch(err){} ...

                      • 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 ...

                      • Customize the category list/selection based on template.

                        The Category applies to all the templates in SDP-MSP. You can hide them using the field and form rules to make them unavailable for users. Under Admin >> Incident Template >> Edit the template in questions >> Field and Form Rules tab. You can create ...

                      • 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>  ...

                      • Disable Default Template - Using FAFR

                        1. Navigate to Admin>Incident Template>> Edit Default Template  2. Select Field & Form Rules Tab 3. Select On form Load  4. Make sure to select "Applies to Technicians" and set the action as Execute Script and paste the below script  ...

                        Related Products