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,
LONGTODATE(OPERATIONTIME) OPERATIONTIME,
std1.STATUSNAME "Changed From",
std2.STATUSNAME"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 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 sdef ON wo.siteid=sdef.siteid
left join accountsitemapping asm ON sdef.siteid=asm.siteid
left join accountdefinition ON accountdefinition.org_id=asm.accountid
LEFT JOIN StatusDefinition std1 ON cast(cast(wohd.prev_value AS varchar) AS int) =std1.STATUSID
LEFT JOIN StatusDefinition std2 ON cast(cast(wohd.current_value AS varchar) AS int) =std2.STATUSID
WHERE wohd.COLUMNNAME IN ('STATUSID')
AND wo.CREATEDTIME >= <from_thisweek>
AND wo.CREATEDTIME <= <to_thisweek>
AND accountdefinition.org_id in ($Account)
ORDER BY 1,
OPERATIONTIME DESC
aau.FIRST_NAME "Requester",
std.STATUSNAME "Request Status",
wo.TITLE "Subject",
woh.OPERATION "Operation",
LONGTODATE(wo.CREATEDTIME) CREATEDTIME,
aau1.FIRST_NAME PERFORMEDBY,
LONGTODATE(OPERATIONTIME) OPERATIONTIME,
std1.STATUSNAME "Changed From",
std2.STATUSNAME"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 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 sdef ON wo.siteid=sdef.siteid
left join accountsitemapping asm ON sdef.siteid=asm.siteid
left join accountdefinition ON accountdefinition.org_id=asm.accountid
LEFT JOIN StatusDefinition std1 ON cast(cast(wohd.prev_value AS varchar) AS int) =std1.STATUSID
LEFT JOIN StatusDefinition std2 ON cast(cast(wohd.current_value AS varchar) AS int) =std2.STATUSID
WHERE wohd.COLUMNNAME IN ('STATUSID')
AND wo.CREATEDTIME >= <from_thisweek>
AND wo.CREATEDTIME <= <to_thisweek>
AND accountdefinition.org_id in ($Account)
ORDER BY 1,
OPERATIONTIME DESC
New to ADSelfService Plus?
Related Articles
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 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 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 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 time elapsed in a ticket with SLA associated (PGSQL)
Tested in PGSQL build (14300) PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) "Created Time", ...