Request for a field: => Resolution that include the Date and Time, same as Completed Time

Sent: Monday, February 27, 2012 9:44 AM

To: 'ServiceDeskPlusSupport'

Subject: RE: [Request ID :##6602058##] : [Fwd: ##12989## : IT Special Work-Query for generating reports of resolved tickets. Contact manage engine for a custom query.] Status: New

I just run this query and do not work for our format of the reports. You told to us that Resolved Time field will be include in the customize report, same as the Completed Time field. Also, is not giving the exactly number of the tickets. We have this month 843 tickets for this month and your report only show 545 tickets. Attach is My Report and Your Report is not the same results.

What is the status? We have been waiting since September 2011 for a solution. We have open several tickets to solve this issue. I like to know what happen and why I have to start waiting again between 7 to 10 days for a solution.

* Your version and build number of Service Desk plus. 8.0.0 Build 8020

* Database type (MySql or MsSql): MySql

* The required fields and brief description of the fields: Field: => Resolution that include the Date and Time same as Completed Time

On Feb 27, 2012, at 9:12 PM, "ServiceDeskPlusSupport" <servicedeskplus-support@manageengine.com<mailto:servicedeskplus-support@manageengine.com

>> wrote:

Regards

Hi Wanda,

Please provide us the following details for generating the Query

* Your version and build number of Service Desk plus.

* Database type (MySql or MsSql)

* The required fields and brief description of the fields

Please note that writing query reports involves development team activity and depending on the complexity of your needs it could take somewhere between 7 to 10 days for us to get back to you with the desired query output.

Thanks and Regards,

Krishna Bharat

________________________________

This is the query you are provide.

SELECT wo.WORKORDERID 'RequestID',

(ti.FIRST_NAME) 'Technician Name',

longtodate((wo.createdtime))'Created Time',

longtodate((woh.operationtime)) 'Resolved Time',

longtodate((wo.respondedtime)) 'Responded Time',

longtodate((wo.completedtime)) 'Completed Time',

(wo.TITLE) 'Subject',

(sdo.NAME) 'Site',

(aau.FIRST_NAME) 'Requester',

(qd.QUEUENAME) 'Group',

(case when wos.isoverdue=1 then'violated' else 'Not violated' end)'SLA Violation' FROM WorkOrder wo

LEFT JOIN workorderhistory woh on wo.workorderid = woh.workorderid

LEFT JOIN workorderhistorydiff wohd on wohd.historyid=woh.historyid

LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID

LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID

LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID

LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID

LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID

LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID

LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID

LEFT JOIN SDUser td ON wos.OWNERID=td.USERID

LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID

LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID

WHERE (wohd.columnname like '%status%') and

(wohd.current_value=4) AND (wo.ISPARENT = '1')

and (wo.createdtime >= (UNIX_TIMESTAMP(DATE('2012-02-01 00:00:00')) * 1000) and wo.createdtime <= (UNIX_TIMESTAMP(DATE('2012-02-29 23:59:59')) * 1000)) group by wo.workorderid ORDER BY 1

<ATT00001.bin>

From: ServiceDeskPlusSupport [mailto:servicedeskplus-support@manageengine.com]

Sent: Monday, February 27, 2012 5:25 AM

Hi,

Refer to the below link regarding the query

https://forums.manageengine.com/topic/resolved-time-report

Thanks and Regards,

Krishna Bharat

US : +1 408 916 9300

A ustralia : +1 800 631 268

UK : 0 800 028 6590

Thanks and Regards,