Incident and Service Catalog Template Frequency

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. 


SELECT serd.Name "Service Category", reqtl.templatename "Template name", count(wo.WORKORDERID) "Count of Request" FROM WorkOrder wo
LEFT JOIN RequestTemplate_list reqtl ON wo.TEMPLATEID=reqtl.TEMPLATEID
LEFT JOIN ServiceDefinition serd ON reqtl.PARENT_SERVICE=serd.SERVICEID
WHERE reqtl.isdeleted='0'
GROUP BY serd.Name,
         reqtl.templatename
ORDER BY 1
 
2.  This report is used to find the most used template in the application in particular month in the current year

SELECT serd.Name "Service Category",  reqtl.templatename "Template name",  count(wo.WORKORDERID) "Count of Request" FROM WorkOrder wo
LEFT JOIN RequestTemplate_list reqtl ON wo.TEMPLATEID=reqtl.TEMPLATEID
LEFT JOIN ServiceDefinition serd ON reqtl.PARENT_SERVICE=serd.SERVICEID
WHERE reqtl.isdeleted='0' and  wo.CREATEDTIME >= <from_thisyear> AND wo.CREATEDTIME <= <to_thisyear> and
MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.CREATEDTIME/1000), '1970-01-01 00:00:00')) = 1

GROUP BY serd.Name,
         reqtl.templatename
ORDER BY 1
Note : The '1' highlighted above indicates the order of the month - January


3.  This report is used to find the most used template in the application in particular month in the last year

SELECT serd.Name "Service Category",  reqtl.templatename "Template name",  count(wo.WORKORDERID) "Count of Request" FROM WorkOrder wo
LEFT JOIN RequestTemplate_list reqtl ON wo.TEMPLATEID=reqtl.TEMPLATEID
LEFT JOIN ServiceDefinition serd ON reqtl.PARENT_SERVICE=serd.SERVICEID
WHERE reqtl.isdeleted='0' and  wo.CREATEDTIME >= <from_lastyear> AND wo.CREATEDTIME <= <to_lastyear> and
MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + (wo.CREATEDTIME/1000), '1970-01-01 00:00:00')) = 10
GROUP BY serd.Name,
         reqtl.templatename
ORDER BY 1
​Note : The '10' highlighted above indicates the order of the month - October


4.  This report is used to find the most used template in the application by month

MSSQL : 

SELECT serd.Name "Service Category",reqtl.TEMPLATENAME "Template",count(wo.WORKORDERID) "Count of Request",YEAR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ( wo.CREATEDTIME/1000), '1970-01-01 00:00:00')) 'year',CASE

WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ( wo.CREATEDTIME/1000), '1970-01-01 00:00:00'))=1 THEN ' JAN'

WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ( wo.CREATEDTIME/1000), '1970-01-01 00:00:00'))=2 THEN '2. FEB' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ( wo.CREATEDTIME/1000), '1970-01-01 00:00:00'))=3 THEN ' MAR' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ( wo.CREATEDTIME/1000), '1970-01-01 00:00:00'))=4 THEN ' APR' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ( wo.CREATEDTIME/1000), '1970-01-01 00:00:00'))=5 THEN ' MAY' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ( wo.CREATEDTIME/1000), '1970-01-01 00:00:00'))=6 THEN ' JUN' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ( wo.CREATEDTIME/1000), '1970-01-01 00:00:00'))=7 THEN ' JUL' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ( wo.CREATEDTIME/1000), '1970-01-01 00:00:00'))=8 THEN ' AUG' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ( wo.CREATEDTIME/1000), '1970-01-01 00:00:00'))=9 THEN ' SEP' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ( wo.CREATEDTIME/1000), '1970-01-01 00:00:00'))=10 THEN ' OCT' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ( wo.CREATEDTIME/1000), '1970-01-01 00:00:00'))=11 THEN ' NOV' WHEN MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ( wo.CREATEDTIME/1000), '1970-01-01 00:00:00'))=12 THEN ' DEC' ELSE NULL END 'MONTH' FROM workorder wo 

LEFT JOIN RequestTemplate_list reqtl ON wo.TEMPLATEID=reqtl.TEMPLATEID

LEFT JOIN ServiceDefinition serd ON wo.SERVICEID=serd.SERVICEID 

where wo.CREATEDTIME >= <from_thisyear> AND wo.CREATEDTIME <= <to_thisyear> group by serd.Name,reqtl.TEMPLATENAME,MONTH(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ( wo.CREATEDTIME/1000), '1970-01-01 00:00:00')), YEAR(dateadd(s, datediff(s, GETUTCDATE(), getdate()) + ( wo.CREATEDTIME/1000), '1970-01-01 00:00:00'))  order by 5,1 desc

PGSQL : 



SELECT serd.Name "Service Category",reqtl.TEMPLATENAME "Template", count(wo.WORKORDERID) "Count of Request",EXTRACT(YEAR FROM (to_timestamp( wo.createdtime/1000)::TIMESTAMP)) "YEAR", 
CASE WHEN 
EXTRACT(MONTH FROM (to_timestamp( wo.createdtime/1000)::TIMESTAMP))=1  THEN ' JAN' WHEN 
EXTRACT(MONTH FROM (to_timestamp( wo.createdtime/1000)::TIMESTAMP)) =2 THEN ' FEB' WHEN 
EXTRACT(MONTH FROM (to_timestamp( wo.createdtime/1000)::TIMESTAMP)) =3 THEN ' MAR' WHEN 
EXTRACT(MONTH FROM (to_timestamp( wo.createdtime/1000)::TIMESTAMP)) =4 THEN ' APR' WHEN 
EXTRACT(MONTH FROM (to_timestamp( wo.createdtime/1000)::TIMESTAMP)) =5 THEN ' MAY' WHEN 
EXTRACT(MONTH FROM (to_timestamp( wo.createdtime/1000)::TIMESTAMP)) =6 THEN ' JUN' WHEN 
EXTRACT(MONTH FROM (to_timestamp( wo.createdtime/1000)::TIMESTAMP)) =7 THEN ' JUL' WHEN 
EXTRACT(MONTH FROM (to_timestamp( wo.createdtime/1000)::TIMESTAMP)) =8 THEN ' AUG' WHEN 
EXTRACT(MONTH FROM (to_timestamp( wo.createdtime/1000)::TIMESTAMP)) =9 THEN ' SEP' WHEN 
EXTRACT(MONTH FROM (to_timestamp( wo.createdtime/1000)::TIMESTAMP))= 10 THEN ' OCT' WHEN 
EXTRACT(MONTH FROM (to_timestamp( wo.createdtime/1000)::TIMESTAMP))= 11 THEN ' NOV' WHEN 
EXTRACT(MONTH FROM (to_timestamp( wo.createdtime/1000)::TIMESTAMP))= 12 THEN ' DEC'  ELSE NULL END "MONTH"
 from workorder wo LEFT JOIN RequestTemplate_list reqtl ON wo.TEMPLATEID=reqtl.TEMPLATEID
LEFT JOIN ServiceDefinition serd ON wo.serviceid=serd.SERVICEID where wo.CREATEDTIME >= <from_thisyear> AND wo.CREATEDTIME <= <to_thisyear> group by serd.Name,reqtl.TEMPLATENAME,EXTRACT(MONTH FROM (to_timestamp( wo.createdtime/1000)::TIMESTAMP)) , 
EXTRACT(YEAR FROM (to_timestamp( wo.createdtime/1000)::TIMESTAMP)) order by 5,1 desc



Note : Login to ServiceDesk Plus, go to Reports tab > New Query Report > Copy the query to the query editor and run the report. 



      New to ADManager Plus?

        New to ADSelfService Plus?

        Start your free trial

          • Related Articles

          • Request based on Incident and Service Catalog Templates

            This report is to get the name of the Template applied to the request.  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 ...

          • Template configuration

            This report will give you the complete list of templates configured in the application.  MSSQL SELECT serd.Name "Service Category", max(list.Templatename) "Template Name", STUFF( (SELECT ',' + tgm.QUEUENAME FROM TemplateToGroupMapping tgm WHERE ...

          • How to create service requests through email

            This post describes the use of a python script to create a service request through email , based on keywords in the subject.When a request is created with specific keywords in the subject of a incoming mail , the custom trigger will be invoked.The ...

          • How to create service requests through email using Business Rule

            Note: Template can be modified directly using BR from build 11.3 This post describes the use of a python script to create a service request through email based on keywords in the subject. When a request is created with specific keywords in the ...

          • Create an incident request in place of a service request depending on a resource question value.

            Requirement: Create an incident request in place of a service request depending on a resource question value. Usecase: In many companies, same service template is used for both requesting for a "New Laptop" and "Replacement for the existing laptop", ...

            Related Products