Last reply by from technician or requester
This report gives the last Conversation of Technician and requester.
To make any changes to a query, refer to the KB article below.
SELECT wo.WORKORDERID "Request ID",
max(aau.FIRST_NAME) "Requester",
max(wo.TITLE) "Subject",
max(ti.FIRST_NAME) "Technician",
longtodate(max(wo.CREATEDTIME)) "Created Time",
CASE
WHEN max(wos.notificationstatus) = 'REQ_REPLY' THEN max(c.description)
WHEN max(wos.notificationstatus) = 'TECH_REPLY' THEN max(n.description)
ELSE NULL
END "Last conversation" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
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 StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN
(SELECT con.workorderid,
cond.description FROM conversation con
LEFT JOIN aaauser au1 ON con.requesterid=au1.user_id
LEFT JOIN conversationdescription cond ON con.CONVERSATIONID= cond.CONVERSATIONID) c ON c.workorderid=wo.workorderid
LEFT JOIN
(SELECT nw.workorderid,
nod.description FROM notify_workorder nw
LEFT JOIN notification NO ON nw.notificationid=no.notificationid
LEFT JOIN aaauser au2 ON no.senderid=au2.user_id
LEFT JOIN notificationtodesc nod ON no.NOTIFICATIONID=nod.NOTIFICATIONID
WHERE no.senderid !=1) n ON n.workorderid=wo.workorderid
WHERE std.ispending='1'
GROUP BY wo.WORKORDERID
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 ADSelfService Plus?
Related Articles
Last work log added
This report is used to get the latest worklog added in 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 wo.WORKORDERID ...
How to send periodic notifications to requester(s) about their pending requests and accordingly update their status.
This post describes the use of a python script to notify requester(s) about their requests through email and move those to a specific status. Use Case: Let us assume a scenario where the technicians reply to a request and put it in a custom status ...
Technician change from history
This report is used to find who changed the Technician and how many technician handled the request. To make any changes to a query, refer to the KB article below. ...
Technician
This report is used to find the Technician complete details. SELECT AaaUser.USER_ID, AaaUser.FIRST_NAME "FullName", AaaLogin.NAME "LoginName", AaaLogin.DOMAINNAME "Domain", AaaContactInfo.EMAILID "Email", DepartmentDefinition.DEPTNAME "Department", ...
Last scanned time
SELECT resource.resourcename "Asset Name", Max(net.ipaddress) "IP Address", Longtodate(Max(LASTSUCCESSAUDIT.audittime)) "Last success Scan Date", ...