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 "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", wo.TITLE AS "Subject", wotodesc.FULLDESCRIPTION AS "Description", ad.ORG_NAME AS "Account", serdef.NAME AS "Service Category",reqtemp.templatename "Template Name",qd1.QUEUENAME "From Group", qd2.QUEUENAME "To Group", count(wohd.columnname) "Ticket Hop Count", cast((wog.timespent/60) as varchar(20)) +':'+cast((wog.timespent % 60) as varchar(20)) "Time Elapsed" FROM WorkOrder wo
LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID
LEFT JOIN ServiceDefinition serdef ON wo.SERVICEID=serdef.SERVICEID
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 CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID
LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID
LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID
LEFT JOIN SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid
LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id
LEFT JOIN requesttemplate_list reqtemp ON reqtemp.templateid=wo.templateid
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.nextgroupid=qd2.QUEUEID
inner join workorderhistory woh on wo.workorderid = woh.workorderid inner join workorderhistorydiff wohd on woh.historyid = wohd.historyid
WHERE (wo.ISPARENT='1')
group by wo.WORKORDERID , mdd.MODENAME , qd.QUEUENAME , aau.FIRST_NAME , ti.FIRST_NAME , cd.CATEGORYNAME , scd.NAME , icd.NAME , wo.TITLE, wotodesc.FULLDESCRIPTION , ad.ORG_NAME , serdef.NAME,reqtemp.templatename,qd1.QUEUENAME , qd2.QUEUENAME, wog.TIMESPENT, wohd.COLUMNNAME having wohd.columnname = 'QUEUEID' order by wo.WORKORDERID
PGSQL:
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 "Category", scd.NAME AS "Subcategory", icd.NAME AS "Item", wo.TITLE AS "Subject", wotodesc.FULLDESCRIPTION AS "Description", ad.ORG_NAME AS "Account", serdef.NAME AS "Service Category",reqtemp.templatename "Template Name",qd1.QUEUENAME "From Group", qd2.QUEUENAME "To Group", count(wohd.columnname) "Ticket Hop Count", (wog.TIMESPENT/60)||'HRS :MINS '||((wog.TIMESPENT))%60 "Time Elapsed" FROM WorkOrder wo LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID LEFT JOIN ServiceDefinition serdef ON wo.SERVICEID=serdef.SERVICEID 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 CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN AccountSiteMapping asm ON wo.siteid=asm.siteid LEFT JOIN AccountDefinition ad ON asm.accountid=ad.org_id LEFT JOIN requesttemplate_list reqtemp ON reqtemp.templateid=wo.templateid 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.nextgroupid=qd2.QUEUEID inner join workorderhistory woh on wo.workorderid = woh.workorderid inner join workorderhistorydiff wohd on woh.historyid = wohd.historyid WHERE (wo.ISPARENT='1') group by wo.WORKORDERID , mdd.MODENAME , qd.QUEUENAME , aau.FIRST_NAME , ti.FIRST_NAME , cd.CATEGORYNAME , scd.NAME , icd.NAME , wo.TITLE, wotodesc.FULLDESCRIPTION , ad.ORG_NAME , serdef.NAME,reqtemp.templatename,qd1.QUEUENAME , qd2.QUEUENAME, wog.TIMESPENT, wohd.COLUMNNAME having wohd.columnname = 'QUEUEID' order by wo.WORKORDERID
New to ADSelfService Plus?
Related Articles
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 support groups and its individual custom attributes (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT ci.CINAME AS "Support Group Name", su.first_name "Owned By", ci.DESCRIPTION AS "Description",ia.attributename "Additional Attributes Name",ia.attributevalue "Additional Attributes ...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 to show ticket aging - PGSQL
Last tested on 14500 Database: PGSQL: select wo.workorderid as "RequestID", modedefinition.modename "Request Mode", queuedefinition.queuename "Group", cd.CategoryName as "Category", scd.name as "SubCategory", itemdefinition.name "Item", ...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 ...