Query to find replies sent to a requester excluding the system notifications (MSSQL)
Tested in build MSSQL (14306)
Query
select wo.workorderid as "Ticket ID",
convert(date, DATEADD(s,(wo.CREATEDTIME)/1000,'01-01-1970')) AS "Creatd Date",
format (convert(time, DATEADD(s,(wo.CREATEDTIME)/1000,'01-01-1970')), 'hh') + ':' +
format (convert(time, DATEADD(s,(wo.CREATEDTIME)/1000,'01-01-1970')), 'mm') + ':' +
format (convert(time, DATEADD(s,(wo.CREATEDTIME)/1000,'01-01-1970')), 'mm') AS "Create Time",
n.NOTIFICATIONID,
convert(date, DATEADD(s,(n.NOTIFICATIONDATE)/1000,'01-01-1970')) AS "Reply Date",
format (convert(time, DATEADD(s,(n.NOTIFICATIONDATE)/1000,'01-01-1970')), 'hh') + ':' +
format (convert(time, DATEADD(s,(n.NOTIFICATIONDATE)/1000,'01-01-1970')), 'mm') + ':' +
format (convert(time, DATEADD(s,(n.NOTIFICATIONDATE)/1000,'01-01-1970')), 'mm') AS "Reply Time",
n.NOTIFICATIONTITLE as "title",
aau.FIRST_NAME as "sender",
au.FIRST_NAME as "Recipient",
nr.RECIPIENT_EMAIL "Email",
au.USER_ID from notify_workorder nw
left join Notification n on n.NOTIFICATIONID = nw.NOTIFICATIONID
LEFT JOIN notification_recipients nr ON nr.NOTIFICATIONID=n.NOTIFICATIONID
left join WorkOrder wo on wo.WORKORDERID = nw.WORKORDERID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
LEFT JOIN accountsitemapping asm on asm.siteid=sitedef.siteid
LEFT JOIN accountdefinition adef on adef.org_id=asm.accountid
LEFT JOIN WorkOrder_Fields wof ON wof.workorderid=wo.workorderid
LEFT JOIN AaaUser aau ON n.SENDERID=aau.USER_ID
LEFT JOIN AaaUser au ON n.RECIPIENTID=au.USER_ID
where nr.RECIPIENT_EMAIL='req4@abc.com'
and Notificationtype = 'REQREPLY'
and n.SENDERID != '1'
and (wo.createdtime/1000between datediff(s,'1970-01-01 00:00','2022-07-01 00:00') and datediff(s,'1970-01-01 00:00','2022-12-31 23:59'))
Notes
- You can toggle n.SENDERID != '1' value to 1 or 0. 1=response, 0=automated notification. You can remove and n.SENDERID != '1' to get both the data at once.
- You can replace the dates to your own time frame.
New to ADSelfService Plus?
Related Articles
Query to find out who created Accounts (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) Execute the below queries under Reports->New Query Report. select org_id, org_name from accountdefinition ad where ad.org_name in('Requester Name', 'Palanivel Palras','Muhammad Nadeem Khan') Note down ...Query to list request IDs with Requester and Technician Employee ID (MSSQL)
Tested in build MSSQL (14306) Sample Table Database : MSSQL Query SELECT "wo"."WORKORDERID" AS "Request ID", ti.FIRST_NAME AS "Technician", td.employeeid "Tech Employee ID", ti1.FIRST_NAME AS "Requester", td1.employeeid "Req Employee ID" FROM ...Query to find the list of Notifications high in number (outofoffice and microsoft emails) with a limit of 10 requests
MSSQL: Select top 10 WorkorderId, (Select Count(*) from Notify_WorkOrder where Notify_WorkOrder.WorkorderId = WorkOrder.WorkorderId) AS NotificationCount from Workorder Order by (Select Count(*) from Notify_WorkOrder where ...Notifications Sent with HTTP URL, despite of application being Secured
If the URL in notifications are sent with http protocol instead of https even though the application is secured. Then try the below solution. 1. Stop the application 2. Go to <Drive>:\ManageEngine\ServiceDeskPlus-MSP\Conf 3. Keep a backup of the file ...Update Request Status upon Receiving a New Reply from Technician
Use case: When a technician works on a request, there might be incidents where he might need additional information from the requester. In such cases, when the technician replies to the customer the ticket status should be changed to "On-Hold". Steps ...