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 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 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 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 retrieve users login and logout history and find their current status
REQUIREMENT: To generate a report that contains the login and logout history of users and to determine the current user status. TESTED IN: Builds 14503 (Postgres) QUERY 1: The following query provides the login and logout history of users. SELECT ...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 ...