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 ...

            • Login frequency

              This report is used to find the Technician and Requester Login frequency. SELECT AaaUser.FIRST_NAME "Technician", MAX(AaaLogin.NAME) "LoginName", MAX(AaaContactInfo.EMAILID) "Email", MAX(AaaAccSession.USER_HOST) "IP Address", ...

            • 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 ...

              Related Products