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 for Support Rep list in DB
Please run this query to find the list of Support Reps available with login in the system select * from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid inner join aaalogin aaal on ...Login Frequency Query Report
The below report would help us find the last logged in time of the users in SCP 11.0 SELECT AaaUser.FIRST_NAME "Technician", MAX(AaaLogin.NAME) "LoginName", MAX(AaaContactInfo.EMAILID) "Email", MAX(AaaAccSession.USER_HOST) "IP ...Query to show list of contacts that are assigned to an account but not have a login
The below query shows the list of contacts that are assigned to an account, but does not have a login. SELECT org.NAME AS "Account Name" ,aaauser.FIRST_NAME AS " Contact with no login" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ...Query to get the Request Approval details
Compatible builds : 14000 to 14200 DB : MSSQL /PGSQL OUTPUT: SELECT wo.WORKORDERID AS "Request ID", wo.title AS "Subject", wo.REQUESTERID AS "Requester ID", LONGTODATE(wo.CREATEDTIME) AS "Requested Date", LONGTODATE(apdet.sent_time) AS "Action ...Phone number need to be mandatory in 8.1 for contacts
Kindly follow the steps below to create the FAFR, 1. Access admin Module 2. Select the request template option and click the edit option the required request template the script needs to be applied on 3. Select the Fields and Forms Rule tab and paste ...