Custom Reports for ServiceDesk Plus - Assigned Verses Closing Technicians

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