Query to list out the notes added to the tickets
QUERY: To list of the notes added to the tickets while generating report report
SELECT ti.FIRST_NAME AS "Support Rep",
wo.WORKORDERID AS "Request ID",
longtodate(wo.CREATEDTIME) AS "Created Time",
wo.TITLE AS "Subject",
aau.FIRST_NAME AS "Contact",
pd.PRIORITYNAME AS "Priority",
std.STATUSNAME AS "Request Status",
longtodate(wos.LAST_TECH_UPDATE) AS "Last Update Time",
(note.NOTESTEXT) "Notes",
longtodate(note.notesdate) "Note Added date" FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID = sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID = aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID = td.USERID LEFT JOIN AaaUser ti ON td.USERID = ti.USER_ID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID = pd.PRIORITYID LEFT JOIN StatusDefinition std ON wos.STATUSID = std.STATUSID LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID = wof.WORKORDERID
LEFT JOIN (
select ns.WORKORDERID,ns.NOTESTEXT,ns.notesdate from Notes ns WHERE ns.NOTESID = (SELECT MAX(nts.NOTESID) FROM Notes nts WHERE nts.WORKORDERID=ns.WORKORDERID))note ON wo.WORKORDERID=note.WORKORDERID
New to ADSelfService Plus?
Related Articles
Query report to show Problem fields along with last added notes (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT prob.PROBLEMID AS "Problem ID", LONGTODATE(prob.REPORTEDTIME) AS "Reported Date", orgaaa.FIRST_NAME AS "Reported by", LONGTODATE(prob.DUEBYTIME) AS "DueBy Date", ...Report on Notes
This report gives an overview of all Notes added by the Technician. This report helps to find a specific word in the Notes, specific technician name etc.. To make any changes to a query, refer to the KB article below. ...Query to show last notes added in request (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) select wo.workorderid "request id", max(wo.title) "subject", max(qd.queuename) "group", max(std.statusname) "request status", max(pd.priorityname) "priority", max(sdo.name) "site", max(wo.createdtime) ...Send email notification to requester when a note is added to a request
How it works? A report will be scheduled to run at a periodic interval which will fetch he details of the request. Once the schedules runs, the script will fetch the values from the report and sends an notification through email to the requester of ...Query to show Last added worklog of a ticket ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", ...