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 M365 Manager Plus?
        New to M365 Manager Plus?
          New to RecoveryManager Plus?
          New to RecoveryManager Plus?
            New to Exchange Reporter Plus?
            New to Exchange Reporter Plus?
              New to SharePoint Manager Plus?
              New to SharePoint Manager Plus?
                  See all integrations
                  New to ADManager Plus?
                  See all integrations
                  New to ADManager Plus?

                    New to ADSelfService Plus?

                    Start your free trial
                    Start your free trial

                      • Related Articles

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

                      • Convert Incident to Service Request

                        For builds before 10536 The option will be available at: open the respective request > Actions > Convert incident to service request For builds after 10600. Edit the respective request > click on the Template dropdown [top-right corner] > Choose the ...

                      • How to use URL ( external link ) in the service catelog - service templates in self service portal

                        The application has an option to add link in the self service portal customization. But if the requirement is to add the URL inside the below templates The requirement is that , to have a specific template that can redirect to an external URL and not ...

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

                      • Unable to save/edit Incident Templates

                        If you happen to see issue while editing an existing incident template, or error while saving new incident template created with the below error, (INVALID_CSRF_TOKEN) Then get the server.xml from the MSP\conf folder and make the below changes, ...

                        Related Products