Query to fetch the templates
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", rt.templatename "Template name" , sd.name "Service Catalog Name", (CASE when rt.is_catalog_template='true' then 'Service Template' else 'Incident Template' END) "Template Type" from requesttemplate_list rt
left join servicedefinition sd on sd.serviceid = rt.parent_service
left join form_customizer fc on fc.form_name = ('WorkOrder_' || rt.templatename)
left join columnaliases ca on fc.field_name like ('udf_%_' || ca.columnaliasesid)
where rt.templatename not like 'Default Service Item'
order by 2
MSSQL:
select Distinct (CASE when fc.field_name like 'udf_%' then ca.aliasname else fc.field_name END) "Fields", rt.templatename "Template name" , sd.name "Service Catalog Name", (CASE when rt.is_catalog_template='true' then 'Service Template' else 'Incident Template' END) "Template Type" from requesttemplate_list rt
left join servicedefinition sd on sd.serviceid = rt.parent_service
left join form_customizer fc on fc.form_name = ('WorkOrder_' + rt.templatename)
left join columnaliases ca on fc.field_name like ('udf_%_' + cast(ca.columnaliasesid as varchar))
where rt.templatename not like 'Default Service Item'
order by 2
New to ADSelfService Plus?
Related Articles
Query to fetch Checklist Information
DB Compatibility : PGSQL & MSSQL Build Compatibility : Builds above 11107 PGSQL : SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", icd.NAME AS ...Query Executor Tool for PostGres
This tool is designed to execute queries in the customer environment by connecting the database by reading the database configuration file. We need to enter the query that we require to execute in queryToExecute.txt file. We can enter multiple ...Query to fetch Task Worklog and related entity ID
DB Compatibility : PGSQL & MSSQL Build Compatibility : Builds above 9400 SELECT taskdet.TASKID AS "Task ID", taskdet.TITLE AS "Title", taskowner.FIRST_NAME AS "Owner", taskdet.MODULE AS "Module", wotask.WORKORDERID AS "Request ID", taskprob.PROBLEMID ...1. Query Basics
Basic Query: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status" FROM WorkOrder wo LEFT JOIN SDUser sdu ON ...Query to Pull out Login Hours
Purpose To generate the Total Login Hours per Technician. Query : select ai as "Account ID", tn as "Technician Name ( First Name )", lh as "Login hours", LONGTODATE(llt) as "Last Login Time", ln as "Login Name", domain as "Domain", email as ...