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.