Query to show last updated field.

Query to show last updated field.

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
          • Related Articles

          • Query to show Time spent in each status - Time Analysis

            MSSQL: SELECT wo.WORKORDERID "Request ID", wo.title "Subject",queuedefinition.queuename "Support Group", "aau"."FIRST_NAME" AS "Requester", "dpt"."DEPTNAME" AS "Department", "cd"."CATEGORYNAME" AS "Category", "scd"."NAME" AS "Subcategory", ...
          • Query to show Last Status Changed Time and Time spent in previous status

            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", LONGTODATE(MAX(OPERATIONTIME)) "Last ...
          • Query to show the last worklog added in a ticket

            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",        MAX(cast((ct.TIMESPENT)/1000 * interval '1 ...
          • Query to show Last added worklog of a ticket

            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",        MAX(cast((ct.TIMESPENT)/1000 * interval '1 ...
          • Query to show problem time spent along with last activity on a problem

            MSSQL: SELECT "prob"."PROBLEMID" AS "Problem ID",  "prob"."TITLE" AS "Title", "statdef"."STATUSNAME" AS "Status",  "orgaaa"."FIRST_NAME" AS "Reported by", Cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs ...