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 wo.workorderid=woa.workorderid
inner join WO_TECH_INFO wti on woa.assessmentid=wti.assessmentid
LEFT JOIN AaaUser ti1 ON wti.technicianid=ti1.USER_ID
LEFT JOIN AaaUser ti2 ON wti.nexttechnicianid=ti2.USER_ID order by 1
Group
select woa.workorderid "RequestID",
qd1.QUEUENAME "From Group",
qd2.QUEUENAME "To Group",
longtodate(wog.timespent) "Time Spent" from workorder wo
left join wo_assessment woa on wo.workorderid=woa.workorderid
inner join WO_GROUP_INFO wog on woa.assessmentid=wog.assessmentid
LEFT JOIN QueueDefinition qd1 ON wog.groupid=qd1.QUEUEID
LEFT JOIN QueueDefinition qd2 ON wog.groupid=qd2.QUEUEID order by 1
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 wo.workorderid=woa.workorderid
inner join WO_TECH_INFO wti on woa.assessmentid=wti.assessmentid
LEFT JOIN AaaUser ti1 ON wti.technicianid=ti1.USER_ID
LEFT JOIN AaaUser ti2 ON wti.nexttechnicianid=ti2.USER_ID order by 1
Group
select woa.workorderid "RequestID",
qd1.QUEUENAME "From Group",
qd2.QUEUENAME "To Group",
longtodate(wog.timespent) "Time Spent" from workorder wo
left join wo_assessment woa on wo.workorderid=woa.workorderid
inner join WO_GROUP_INFO wog on woa.assessmentid=wog.assessmentid
LEFT JOIN QueueDefinition qd1 ON wog.groupid=qd1.QUEUEID
LEFT JOIN QueueDefinition qd2 ON wog.groupid=qd2.QUEUEID order by 1
Status
select woa.workorderid "RequestID",
sd1.statusname "From status",
sd2.statusname "To status",
longtodate(wsi.timespent) "Time Spent" from workorder wo
left join wo_assessment woa on wo.workorderid=woa.workorderid
inner join wo_status_info wsi on woa.assessmentid=wsi.assessmentid
left join statusdefinition sd1 on wsi.statusid=sd1.statusid
left join statusdefinition sd2 on wsi.nextstatusid=sd2.statusid order by 1
New to ADSelfService Plus?
Related Articles
Request Time Analysis Reports (Status, Group, Technician Fields changes and its timespent) (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) This report contains the time analysis for each status/group/technician changes in a request, kindly execute this under Reports->New Query Report. Status Changes: SELECT wo.WORKORDERID AS "Request ID", ...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 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 show technician hop count (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) 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", ...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 ...