Query to show support group escalations (PGSQL & MSSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
PGSQL & MSSQL:
SELECT wo.WORKORDERID "Request ID",
aau.FIRST_NAME "Requester",
dpt.DEPTNAME "Department",
std.STATUSNAME "Request Status",
wo.TITLE "Subject",
rtdef.NAME "Request Type",
sdo.NAME "Site",
LONGTODATE(wo.CREATEDTIME) CREATEDTIME,
aau1.FIRST_NAME PERFORMEDBY,
LONGTODATE(woh.OPERATIONTIME) OPERATIONTIME,
qd1.queuename "Changed From",
qd2.queuename "Changed To"
from WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID
LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID
LEFT JOIN AaaUser aau1 ON aau1.USER_ID = woh.OPERATIONOWNERID
LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID
left join QueueDefinition qd1 on cast(cast(wohd.prev_value as varchar) as int) =qd1.QUEUEID
left join QueueDefinition qd2 on cast(cast(wohd.current_value as varchar) as int) =qd2.QUEUEID
where wohd.COLUMNNAME in ('QUEUEID') order by 1,woh.OPERATIONTIME desc
New to ADSelfService Plus?
Related Articles
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 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 technicians associated accounts, sites and Support groups - (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 1.Technicians and associated Accounts/Sites: select aau.User_id, aau.first_name "First Name", sdu.lastname "Last Name", aal.name "Login Name", AaaContactInfo.EMAILID "Email Address",sdu.jobtitle "Job ...Query to show Problems, its associated incidents and change_ (MSSQL)
Tested in Build MSSQL (14306) SELECT woproblem.PROBLEMID AS "Problem ID", woproblem.TITLE AS "Problem Title", "priodef"."PRIORITYNAME" AS "Problem Priority", "urgdef"."NAME" AS "Problem Urgency", "statdef"."STATUSNAME" AS "Problem Status", ...Query to show Average response time for Category (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...