Query to fetch additional fields configured in request templates
Postgres:
SELECT ca.aliasname AS "UDF field Name",
ca.columnname AS "UDF Column Name",
ca.field_type AS "UDF Field Type",
sd.name AS "Service Category",
rtl.templatename AS "Template Name" FROM columnaliases ca
LEFT JOIN form_customizer fc ON ca.columnaliasesid::text = substring(fc.field_name, position('_' IN fc.field_name)+1+ position('_' IN substring(fc.field_name,position('_' IN fc.field_name)+1,length(fc.field_name))) , length(fc.field_name))
LEFT JOIN requesttemplate_list rtl ON rtl.templatename = substring(fc.form_name,position('_' IN fc.form_name)+1,length(fc.form_name))
LEFT JOIN servicedefinition sd ON rtl.parent_service = sd.serviceid ORDER BY 1
MSSQL:
SELECT ca.aliasname "UDF field Name",
rtl.templateid as "Template ID",
rtl.templatename as "Template Name",
aau.first_name as "Template Owner",
rtl.is_catalog_template as "Service OR Incident template",
rtl.status as "template is active OR inactive?" FROM RequestTemplate_List rtl
LEFT JOIN AaaUser aau ON rtl.createdby = aau.user_id
LEFT JOIN form_customizer fc on rtl.templatename = substring(fc.form_name, CHARINDEX('_', fc.form_name)+1 , len(fc.form_name) )
INNER JOIN columnaliases ca on cast(ca.columnaliasesid as varchar) = substring(fc.field_name, CHARINDEX('_', fc.field_name, CHARINDEX('_', fc.field_name)+1)+1 , len(fc.field_name)) ORDER BY 1
New to ADSelfService Plus?
Related Articles
Query to display additional field associations with templates (MSSQL)
Tested in build MSSQL (14306) Use case The reports shows in which templates the created additional fields are associated Query select sd.name "Service Catalog Name", rt.templatename "Template name", fc.field_name "Fields" from requesttemplate_list rt ...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", ...Request with ServiceCatalog Additional fields
Service catalog additional fields are category specific fields. These fields are stored in a dynamic table, so we need to join the specific template to get the resultant. To make any changes to a query, refer to the KB article below. ...Query to extract the “Software-License Additional Fields” along with the existing additional fields (PGSQL)
(Applicable for builds 14300 & above) Below query has been modified to add a LEFT JOIN between the additional fields table and the table from which you extracted the softwares: QUERY: SELECT SUM(scd.ALLOCATED) AS "Allocated", ...Query to retrieve Account details along with additional attributes, postal address and additional fields data
TESTED IN BUILDS: 14700 (Postgres) QUERY 1 - Without Account Additional Fields: select ad.ORG_NAME As "Account", org.description AS "DESCRIPTION", ad.LOGIN_WEBURI AS "LOGIN WEB URL", ad.LOGIN_URI AS "LOGIN URI", ad.SUPPORT_EMAIL AS "SUPPORT EMAIL", ...