Query to show conversation description (MSSQL & PGSQL)
Database: MSSQL and PGSQL
Tested in build PGSQL (14300) and MSSQL (14306)
It is not recommended to use the query often in business hours as it may cause performance issues since it has to pull huge data.
Execute the below query under Reports->New Query Report and let us know if that helps.
Please note, incoming and outgoing conversations are shown separately, cannot be merged together.
Incoming:
select wo.workorderid "Request ID", convd.description "Conversation Description" FROM workorder wo LEFT JOIN conversation conv ON wo.workorderid=conv.workorderid LEFT JOIN conversationdescription convd ON conv.conversationid=convd.conversationid LEFT JOIN sduser sdu ON conv.requesterid=sdu.userid LEFT JOIN aaauser aaau ON sdu.userid=aaau.user_id WHERE conv.workorderid=wo.workorderid AND wo.createdtime>=<from_thismonth>and wo.createdtime<=<to_thismonth> group by wo.workorderid, convd.description
order by 1
Outgoing:
select wo.workorderid "Request ID", ntd.description "Notification Description" from workorder wo left join notify_workorder nw on wo.workorderid=nw.workorderid LEFT JOIN notification n on nw.notificationid=n.notificationid LEFT JOIN notificationtodesc ntd ON n.notificationid=ntd.notificationid left join aaauser nau on n.senderid=nau.user_id where nau.first_name!='system' AND wo.createdtime>=<from_thismonth>and wo.createdtime<=<to_thismonth> GROUP BY wo.workorderid, ntd.description
NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.
How to compare date column with auto filled date templates?
- Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
- <from_thisweek> - Starting date of this week
- <to_thisweek> - Ending date of this week
- Available Date Templates
- 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>
Last tested on Build 14500 and above
New to ADSelfService Plus?
Related Articles
Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...Query to show the last worklog added in a ticket (PGSQL)
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(wo.TITLE) "Subject", max(qd.QUEUENAME) "Group", max(ti.FIRST_NAME) "Assigned Technician", CAST(ct.TIMESPENT AS FLOAT)/1000/3600 AS "Time ...Query to show last conversation and last notification in a request ( MSSQL )
Tested in Build MSSQL (14306) Query SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", wo.TITLE "Subject", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", (select conversationdescription.description from ...Query to show last updated field (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Kindly go to Reports-New Query Report and execute the below query. PGSQL: SELECT wo.WORKORDERID AS "Request ID", max(wo.TITLE) AS "Subject", max(ti.FIRST_NAME) AS "Assigned Technician", ...Query to show resolved by value (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wos.assignedtime) "Assigned Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Time", ...