Query to show service catalog template details (MSSQL & PGSQL)

Query to show service catalog template details (MSSQL & PGSQL)


Tested in Build PGSQL (14300) or MSSQL (14306)


select sd.name "Service Name", rtl.templatename "Service Template Name", rtl.comments "Template description", rtf.description "Description field" from requesttemplate_list rtl LEFT JOIN requesttemplate_fields rtf ON rtl.templateid=rtf.templateid LEFT JOIN Categorydefinition cd ON rtf.categoryid=cd.categoryid LEFT JOIN subcategorydefinition scd ON rtf.subcategoryid=scd.subcategoryid LEFT JOIN itemdefinition id ON rtf.itemid=id.itemid LEFT JOIN Servicedefinition sd ON rtl.parent_Service=sd.serviceid where is_catalog_template='true' AND rtl.isdeleted='false' ORDER BY 1

                  New to ADSelfService Plus?

                    • 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 ...
                    • Contract and Service Plans details - Query Report (MSSQL & PGSQL)

                      Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: select ac.contractno "Contract Number", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type", sp.timeperiod "Bill ...
                    • Query that shows Service catalog SLA (MSSQL & PGSQL)

                      Tested in Build PGSQL (14300) or MSSQL (14306) select slad.slaname "SLA NAME", sdo.name "SITE", slad.duebydays "Resolved within Days", slad.duebyhours "Resolved within Hours", slad.duebyminutes "Resolved within Mins", slad.fr_duebydays "Responded ...
                    • Query to fetch the fields in the templates (MSSQL & PGSQL)

                      Tested in builds from PGSQL (14300) or MSSQL (14306) The query fetches all the fields with display name used across Request templates PQSQL: select Distinct (CASE when fc.field_name like 'udf_%' then ca.aliasname else fc.field_name END) "Fields", ...
                    • Query to show Average response time for Category (MSSQL & PGSQL)

                      Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...