Query to show shared requests

Query to show shared requests

Shared to Tech:

SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", LONGTODATE(wo.CREATEDTIME) AS "Created Time",  LONGTODATE(wo.COMPLETEDTIME) AS "Completed Time",  LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Time", wos.ISOVERDUE AS "Overdue Status", sa.first_name "Shared to technician"  FROM WorkOrder wo 
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID 
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID 
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 WO_SHARE_LIST wsl ON wo.workorderid=wsl.workorderid
LEFT JOIN TECHNICIAN_SHARE_LIST tsl ON wsl.shareid=tsl.shareid
LEFT JOIN aaauser sa ON tsl.techid=sa.user_id
WHERE (wo.ISPARENT='1') AND sa.first_name='Technician1' ORDER BY 1

Shared to Group:

SELECT wo.WORKORDERID AS "Request ID",
       wo.TITLE AS "Subject",
       aau.FIRST_NAME AS "Requester",
       qd.QUEUENAME AS "Group",
       ti.FIRST_NAME AS "Technician",
       std.STATUSNAME AS "Request Status",
       qa.queuename "Shared to group" 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 StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN WO_SHARE_LIST wsl ON wo.workorderid=wsl.workorderid
INNER JOIN GROUP_SHARE_LIST gsl ON wsl.shareid=gsl.shareid
LEFT JOIN queuedefinition qa ON gsl.queueid=qa.queueid
WHERE (wo.ISPARENT='1') AND qa.queuename='Group1' ORDER BY 1

Note: In both the queries, shared to tech name and shared to group names are highlighted. Please replace it with necessary values. Ensure that the exact name in the application is used in the query.

          • Related Articles

          • Query to track technician activity on tickets

            Use case: This query will help you find what are all activities that the technicians have done on requests other than the assigned ones SELECT au.first_name "Technician" ,wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",wo.TITLE ...
          • Query report to show Open requests without open tasks

            PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request ...
          • Query to show technician hop count

            MSSQL: SELECT wo.WORKORDERID "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", qd.QUEUENAME "Current Group",aau.FIRST_NAME AS "Requester",  ti.FIRST_NAME "Technician", ad.ORG_NAME AS "Account", ti1.FIRST_NAME "From technician", ...
          • Query to know the technician reassignment history

            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 show time spent by each technician in a ticket.

            Navigate to Reports->New Query Report and execute this report. Under Help->About, check the database you are using and use the appropriate query. MSSQL: SELECT wo.WORKORDERID "Request ID",wo.TITLE "Subject",cd.CATEGORYNAME "Category",scd.NAME ...