Sorry it’s been quiet on the Blog front but I’ve been extremely busy – that’s a good thing!
 
Thought this custom report might be useful for a few people. 
 
I’m currently working with a Service Manager for an existing ManageEngine ServiceDesk Plus installation. The product has been in place for some time but, unfortunately, the desk has become saturated with open requests. As such the new Service Manager is on a mission to close aged requests, where appropriate. Obviously, there are a few occasions where the local teams need some of the tickets re-opened that the Service Manager has force closed.
 
As such the following report was very useful as it allowed the Service Manager to find view a list of requests with details of the assigned technician verses closing technician. The report should work with MySQL, MS SQL or PostGresSQL
 
SELECT wo.WORKORDERID “Request ID”,
cd.CATEGORYNAME “Category”,
scd.NAME “Subcategory”,
tia.FIRST_NAME “Assigned Technician”,
ti.FIRST_NAME “Closing Technician” FROM WorkOrder wo 
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID 
LEFT JOIN Workorderhistory wh ON wo.WORKORDERID=wh.WORKORDERID 
LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID 
LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID 
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
— Join the closing technician details based on Workorder History details
LEFT JOIN SDUser td ON wh.OPERATIONOWNERID=td.USERID 
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
— Join the assigned technician details based on the Workorder details
LEFT JOIN SDUser tda ON wos.OWNERID=tda.USERID 
LEFT JOIN AaaUser tia ON tda.USERID=tia.USER_ID
WHERE wh.OPERATION=’CLOSE’ AND wo.COMPLETEDTIME >= <from_thisweek> AND wo.COMPLETEDTIME <= <to_thisweek>
For different timescales use the following options in the WHERE statement:
 
  • Today – <from_today> – <to_today>
  • This week – <from_thisweek> – <to_thisweek>
  • Last week – <from_lastweek> – <to_lastweek>
  • This month – <from_thismonth> – <to_thismonth>
  • Last month – <from_lastmonth> – <to_lastmonth>
  • This quarter – <from_thisquarter> – <to_thisquarter>
  • Last quarter – <from_lastquarter> – <to_lastquarter>
  • Yesterday – <from_yesterday> – <to_yesterday>
 
The report is currently looking at the COMPLETEDTIME but this can be changed to CREATEDTIME if you want to work with ticket creation date instead.
 

Hope it works for you.

Enjoy!

This article is relevant to:
AnalyticsService Desk

You may be interested in these other recent articles

Extend the Capabilities of ServiceDesk Plus With Deluge Scripting

17 September 2020

ServiceDesk Plus now offers a new means of extending application capabilities with Deluge, the easy to learn on-line scripting language. The ability to add additional…

Read more

Advanced Analytics for ServiceNow

4 August 2020

ManageEngine have released an official app in the ServiceNow marketplace. ServiceNow users can benefit from ManageEngine’s lightweight, plug-and-play analytics solution Analytics Plus, all directly from…

Read more

Benefit From an OpManager/ServiceDesk Plus Integration

2 June 2020

OpManager can easily be integrated with ServiceDesk Plus to create an efficient network fault management system. Read on to discover some of the benefits of…

Read more

Set3 Solutions and ManageEngine

12 May 2020

Set3 Solutions, a ManageEngine Technology Partner of Zoho Corporation ManageEngine is a division of Zoho Corporation. Set3 Solutions Ltd. are not just a reseller but…

Read more

What Is the Best Way to Update ServiceDesk Plus?

6 May 2020

How to Safely Update ServiceDesk Plus With Service Packs The ServiceDesk Plus team at ManageEngine frequently release new features and fixes, which we post on…

Read more