Custom Reports for ServiceDesk Plus – Assigned Verses Closing Technicians

9 May 2014
by Set3 Solutions Technical Services

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:

Analytics Service Desk

Custom Reports for ServiceDesk Plus – Assigned Verses Closing Technicians

This article is relevant to:

Other recent articles in the same category

Migrating to ServiceDesk Plus Cloud: What Moves, What Doesn’t

Endpoint Central Disk Space Reports: Getting the Data You Actually Need

Automatically Creating Notes from Worklogs in ServiceDesk Plus Cloud

Latest Updates for ManageEngine ServiceDesk Plus Cloud

Latest Updates for ManageEngine ServiceDesk Plus On-Premise

Analytics Plus, edition by edition: which one is right for your team?

You may be interested in these other recent articles

Migrating to ServiceDesk Plus Cloud: What Moves, What Doesn’t

Protected: Test: Quote Form

Endpoint Central Disk Space Reports: Getting the Data You Actually Need

Latest Updates for ManageEngine Endpoint Central

Automatically Creating Notes from Worklogs in ServiceDesk Plus Cloud

This article is relevant to:

Other recent articles in the same category

You may be interested in these other recent articles

Search the site