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 show tickets older than 30 days ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status",wo.TITLE "Subject",aau.FIRST_NAME AS "Requester Name", pd.PRIORITYNAME as "Priority", adef.ORG_NAME as "Account", ...Query to find deleted survey (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Go to Reports >> New Query Report >> Run the below query to get the data SELECT err.message "System log message", err.errormodule "Module", err.suberrormodule "Sub Module", err.action "Action", ...Query to find status change, its time and comments (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Execute this query under Reports->New Query Report. Date filter is highlighted, you can modify it as per the need. SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", std.STATUSNAME "Request ...