Incoming and Outgoing conversations with Attachment name and location (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
PGSQL & MSSQL:
Incoming: (Referred as 'Conversations' in the database)
select conv.conversationid "Conversation ID", conv.workorderid "Request ID", wo.title "Request Subject", aaau.first_name "Sender", LONGTODATE (Conv.createdtime) "Incoming Conversation created time", Conv.title "Incoming Conversation subject", conv.ispublic "Public Conversation", sda.attachmentname "Attachment name", sda.attachmentpath "Attachment Path" 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 LEFT JOIN workordertodescription wtd ON wtd.workorderid = wo.workorderid LEFT JOIN Conversationattachment ca ON conv.conversationid=ca.conversationid LEFT JOIN SDESKATTACHMENT SDA ON ca.attachmentid=sda.attachmentid where conv.ispublic='true' AND wo.createdtime>=<from_lastmonth> and wo.createdtime<=<to_lastmonth> group by conv.workorderid, wo.TITLE, conv.conversationid, aaau.first_name, Conv.createdtime, Conv.title, conv.ispublic, sda.attachmentname, sda.attachmentpath ORDER BY 2
Outgoing: (Referred as 'Notifications' in the database)
select n.notificationid "Notification ID", wo.workorderid "Request ID" , wo.title "Request Subject", longtodate(n.Notificationdate) "Outgoing conversation sent date" ,n.notificationtitle "Conversation subject", nau.first_name "Sender name", N.ISPUBLIC "Public conversation", sda.attachmentname "Attachment Name", sda.attachmentpath "Attachment Path" from workorder wo left join notify_workorder nw on wo.workorderid=nw.workorderid LEFT JOIN notification n on nw.notificationid=n.notificationid left join aaauser nau on n.senderid=nau.user_id left join notificationtodesc nod on n.notificationid=nod.notificationid left join notify_attachments na on n.notificationid=na.notificationid left join sdeskattachment sda on na.attachmentid=sda.attachmentid where n.ispublic='true' AND wo.createdtime>=<from_lastmonth> and wo.createdtime<=<to_lastmonth> AND nau.first_name!='system' group by n.notificationid,wo.workorderid, wo.TITLE, n.Notificationdate, n.notificationtitle, nau.first_name, N.ISPUBLIC, sda.attachmentname, sda.attachmentpath ORDER BY 2
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>
New to ADSelfService Plus?
Related Articles
Query to show total number of conversations, incoming and outgoing conversations (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) Total Number of Conversations: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", aau.FIRST_NAME AS "Requester", ad.ORG_NAME AS "Account", std.STATUSNAME AS ...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 for request attachment details (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Requests with Attachment, its name and path SELECT ad.ORG_NAME AS "Account", wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", ti.FIRST_NAME AS "Technician", sa.ATTACHMENTNAME "Attachment ...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 ...How do I convert the database from PGSQL to MSSQL?
1. Stop ManageEngine ServiceDeskPlus – MSP service. 2. Take a backup of the existing data and configuration under PGSQL database by following the below steps. Open CMD as an administrator and execute command to start the data backup. ...