Query to get list of service request templates created with Tasks associated to it. (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306)
Execute the given query below from Reports-->New query report
select sd.name "Service Name", rtl.templatename "Service Template Name", rtl.comments "Template description", rtf.description "Description field" , ttd.templatename "Task Name",ttd.title "Task Title",std .statusname "Task Status"
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
left join requesttasktemplate rtt on rtl.templateid=rtt.reqtemplateid
left join tasktemplatedetails ttd on ttd.templateid=rtt.templateid
left join statusdefinition std on std.statusid=ttd.statusid
where is_catalog_template='true' AND rtl.isdeleted='false' ORDER BY 2
New to ADSelfService Plus?
Related Articles
Query to show tasks created per template along with status (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Since Task template is not stored in Task details, we cannot show count based on task templates. However, we have tried to form a query with task subject considering that, when task templates are ...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 list Service Request Approver and Purchase Request Approver (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Select aau.first_name "Name", po.approverstatus "Purchase Request Approver", sr.approverstatus "Service Request Approver", adef.org_name "Account" from aaauser aau left join POApproverDetails po on ...Report to get list of Requesters with Requester created time and created by (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Kindly execute the report under Report -> New Query Report. SELECT AaaUser.FIRST_NAME "FullName", AaaLogin.NAME "LoginName", longtodate(aaauser.createdtime) "Created time", AaaContactInfo.EMAILID ...Query to show number of tickets created per template with their status (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT reqtl.TEMPLATENAME "Request Template", count(wo.WORKORDERID) "Request Count", count(case when std.ispending='0' THEN 1 ELSE NULL END) "Completed", count(case when std.ispending='1' THEN 1 ELSE ...