History/Changes
Query to show request history for technicians, group and status changes (MSSQL & PGSQL)
Last tested on 14500 and above too Technician select woa.workorderid "RequestID", ti1.FIRST_NAME "From technician", ti2.FIRST_NAME "To technician", longtodate(wti.timespent) "Time Spent" from workorder wo left join wo_assessment woa on ...
Query to show priority changes in the ticket (MSSQL)
Tested in MSSQL build (14306) This query shows which tickets were downgraded from P1. MSSQL: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", LONGTODATE(wo.DUEBYTIME) "DueBy ...
Query to show tickets that are in resolved status for more than 1 hour ( MSSQL)
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Request ID",ad.ORG_NAME AS "Account" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID INNER JOIN Accountsitemapping asm ON wo.siteid=asm.siteid INNER JOIN ...
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", ...
Query to show first assigned and re-assigned ticket irrespective of the created date ( MSSQL)
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(dpt.DEPTNAME) "Department", max(wo.TITLE) "Subject", max(qd.QUEUENAME)"Group", max(ti.FIRST_NAME) "Technician", max(std.STATUSNAME) "Request ...
Query to show active and archived tickets (MSSQL)
Tested in build MSSQL (14306) SELECT wo.WORKORDERID "Request ID",qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",serdef.NAME "Service Category",wo.TITLE "Subject",sdo.NAME "Site",accountdefinition.org_name "Account",aau.FIRST_NAME ...
Query to know the technician changes in a ticket
This will show the output only if the technician is assigned/updated in a ticket 1. Login to SDP MSP as administrator 2. Execute this from SDP MSP application -> Reports -> New Query report SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS ...
Query to find the change in Dueby time when the Site is changed (MSSQL)
Tested in build MSSQL (14306) SELECT wo.WORKORDERID AS "Request ID", "rtdef"."NAME" AS "Request Type", "std"."STATUSNAME" AS "Request Status", LONGTODATE(cast(cast(wohd1.prev_value as varchar) as bigint)) AS "Previous Due Date", ...
Query to show how many times a ticket has “hopped” from group to group. (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", qd.QUEUENAME AS "Current Group", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS ...
Query to show the last worklog added in a ticket (PGSQL)
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(wo.TITLE) "Subject", max(qd.QUEUENAME) "Group", max(ti.FIRST_NAME) "Assigned Technician", CAST(ct.TIMESPENT AS FLOAT)/1000/3600 AS "Time ...
Query report on Conversation & Notification in a request. (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Please go to Reports-New Query Report and execute this query. SELECT wo.WORKORDERID "Request ID", (aau.FIRST_NAME) "Requester", (wo.TITLE) "Subject", (ti.FIRST_NAME) "Technician", ...
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", ...
Query to show when Onhold was placed for a request (PGSQL)
Tested in Build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID AS "Request ID", sdo.NAME AS "Site", cd.CATEGORYNAME AS "Category", longtodate(wo.CREATEDTIME) AS "Created Time", std.STATUSNAME AS "Current Request Status", longtodate(wo.COMPLETEDTIME) AS ...
Query to show parent and child requests (Linked Request) and its details.(MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Please go to Reports-New Query Report and execute this query. select woparent.WORKORDERID "Parent request ID", aaauparentreq.first_name "Parent Requester", woparent.title "Parent Subject", ...
Query to show last conversation and last notification in a request ( MSSQL )
Tested in Build MSSQL (14306) Query SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", wo.TITLE "Subject", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", (select conversationdescription.description from ...
Query to show last conversation and last notification in a request ( PGSQL )
Tested in Build PGSQL (14300) SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", wo.TITLE "Subject", ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time", (select conversationdescription.description from ...
Query to show due by date changes in request (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) MSSQL SELECT wo.WORKORDERID "Request ID", max(ti1.FIRST_NAME) "Technician" , max(aau.FIRST_NAME) "Requester", max(wo.TITLE) "Subject", max(ti.first_name) "Changed by", dateadd(s,datediff(s,GETUTCDATE() ...
Query to find Time elapsed by each status in requests( MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) MSSQL SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", qd.QUEUENAME AS "Group", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", scd.NAME ...
Query to show unassigned time of a request and when its first assigned (MSSQL)
Tested in Build MSSQL (14306) Go to Reports-New Query Report and execute this query. MSSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", LONGTODATE(wo.createdtime) "Created Time", LONGTODATE(wo.COMPLETEDTIME) "Completed Time", ...
Query to show number of tickets received, pending, closed along with the count of conversations (MSSQL)
Tested in Build MSSQL (14306) Go to Reports-New Query Report and execute this report. SELECT datename(MONTH,dateadd(s,wo.createdtime/1000, '01-01-1970')) "Month" , Day(dateadd(s,datediff(s,GETUTCDATE() ,getdate()) + (wo.CREATEDTIME/1000),'1970-01-01 ...
Query to show status changes in a ticket_ PGSQL
Tested in Build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", aau.FIRST_NAME "Requester", std.STATUSNAME "Request Status", wo.TITLE "Subject", woh.OPERATION "Operation", LONGTODATE(wo.CREATEDTIME) CREATEDTIME, aau1.FIRST_NAME PERFORMEDBY, ...
Query to find the difference between technician assigned and group assigned time (PGSQL)
Tested in Build PGSQL (14300) Please go to Reports-New Query Report and execute this query. SELECT wo.WORKORDERID "Request id", longtodate(min(ta.OPERATIONTIME)) "Technician assigned time", longtodate(min(gp.OPERATIONTIME)) "Group assigned time", ...
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 ...