Query to show ticket audit report

Query to show ticket audit report

MSSQL:

Report 1:
Requester, Request ID, Updated by (Technician), Updated Time :

select a.first_name as "Requester",wh.workorderid as "Request ID",aa.first_name as "Updated by",longtodate(wh.OPERATIONTIME) as Updated Time from workorderhistory wh LEFT JOIN workorder wo on wh.workorderid = wo.workorderid LEFT JOIN aaauser a on wo.requesterid = a.USER_ID LEFT JOIN aaauser aa on wh.OPERATIONOWNERID = aa.USER_ID order by 2

Report 2:
Request ID, Requester, Updated By (Technician), Updated Time, Updated From, Updated To, Value Updated For :

select wo.workorderid as "Request ID",a.first_name as "Requester",aa.first_name as "Updated by",longtodate(wh.OPERATIONTIME) as "Updated Time",whd.PREV_VALUE as "Updated From",whd.CURRENT_VALUE as "Updated To",whd.columnname as "Value Updated For" from workorder wo LEFT JOIN AaaUser a on wo.requesterid = a.user_id LEFT JOIN WorkOrderHistory wh on wo.workorderid = wh.workorderid LEFT JOIN WorkorderHistorydiff whd on wh.historyid = whd.historyid LEFT JOIN AaaUser aa on wh.OPERATIONOWNERID = aa.USER_ID where wo.workorderid = 1 (REQUEST ID )
      • Related Articles

      • Query to show Last added worklog of a ticket _MSSQL

        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", wotodesc.FULLDESCRIPTION AS ...
      • Query report to show Open requests without open tasks

        PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", wotodesc.FULLDESCRIPTION AS "Description", std.STATUSNAME AS "Request ...
      • Query to show tickets older than 30 days_MSSQL

        MSSQL: SELECT wo.WORKORDERID "Request ID",ti.FIRST_NAME "Technician",std.STATUSNAME "Request Status",wo.TITLE "Subject",aau.FIRST_NAME AS "Requester Name", pd.PRIORITYNAME as "Priority", adef.ORG_NAME as "Account", sdo.NAME as "Site", ...
      • 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 ticket aging - PGSQL

        PGSQL: select wo.workorderid as "RequestID", modedefinition.modename "Request Mode", queuedefinition.queuename "Group", cd.CategoryName as "Category", scd.name as "SubCategory", itemdefinition.name "Item",  cri.FIRST_NAME AS "Created By", rtd.name ...