Query to show last updated field (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
Kindly go to Reports-New Query Report and execute the below query.
PGSQL:
SELECT wo.WORKORDERID AS "Request ID",
max(wo.TITLE) AS "Subject",
max(ti.FIRST_NAME) AS "Assigned Technician",
max(aau.FIRST_NAME) AS "Requester",
max(wotodesc.FULLDESCRIPTION) AS "Description",
longtodate(max(wo.CREATEDTIME)) AS "Created Time",
max(std.STATUSNAME) AS "Request Status",
max(wohd.Columnname) "Updated Field name",
longtodate(max(woh.operationtime)) "Last updatedtime",
max(au.first_name) "Updated by " FROM WorkOrder wo 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 WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID left join workorderhistory woh on woh.workorderid=wo.workorderid
LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID
left join aaauser au on au.user_id=woh.operationownerid
where woh.operation ='update' and woh.historyid = (select max(historyid) from workorderhistory woh1 where woh1.workorderid=wo.workorderid and woh1.operation='update') and (wo.ISPARENT='1') and wo.createdtime >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2019-01-01 00:00:00') * 1000 AS BIGINT) AND wo.createdtime <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2019-03-30 00:00:00') * 1000 AS BIGINT) group by wo.WORKORDERID order by 1
MSSQL:
SELECT wo.WORKORDERID AS "Request ID",
max(wo.TITLE) AS "Subject",
max(ti.FIRST_NAME) AS "Assigned Technician",
max(aau.FIRST_NAME) AS "Requester",
max(wotodesc.FULLDESCRIPTION) AS "Description",
longtodate(max(wo.CREATEDTIME)) AS "Created Time",
max(std.STATUSNAME) AS "Request Status",
max(wohd.Columnname) "Updated Field name",
longtodate(max(woh.operationtime)) "Last updatedtime",
max(au.first_name) "Updated by " FROM WorkOrder wo 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 WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID left join workorderhistory woh on woh.workorderid=wo.workorderid
LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID
left join aaauser au on au.user_id=woh.operationownerid
where woh.operation ='update' and woh.historyid = (select max(historyid) from workorderhistory woh1 where woh1.workorderid=wo.workorderid and woh1.operation='update') AND wo.createdtime>=DATETOLONG('2020-12-01 00:00:00') and wo.createdtime<=DATETOLONG('2020-12-31 23:59:59') group by wo.WORKORDERID order by 1
New to ADSelfService Plus?
Related Articles
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 ...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 Last Status Changed Time and Time spent in previous status ( PGSQL )
Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", wo.TITLE "Subject", ti.FIRST_NAME "Technician", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS "Group", std.STATUSNAME "Request Status", ...Query to show Last added worklog of a ticket ( MSSQL )
Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", ...Query to show Total Onhold time ( PGSQL )
Tested in build PGSQL (14300) PGSQL: SELECT LONGTODATE(wo.CREATEDTIME) AS "Created Time", wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", std.STATUSNAME AS "Request Status", ...