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>