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
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 ...Query to show workstation's hard disk details (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT Max(workstation.workstationname) "Workstation", Max(workstation.servicetag) "Service Tag", Max(workstation.model) "Model", Max(osinfo.osname) "OS", MAX(memInfo.TOTALMEMORY/(1024*1024*1024)) AS ...