Query to find status change, its time and comments (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
Execute this query under Reports->New Query Report. Date filter is highlighted, you can modify it as per the need.
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",
WOH.DESCRIPTION "COMMENTS" 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 find Time elapsed by each status in requests( MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) MSSQL SELECT wo.WORKORDERID AS "Request ID", mdd.MODENAME AS "Request Mode", qd.QUEUENAME AS "Group", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", scd.NAME ...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 both task comments and worklog comments ( MSSQL )
Tested in MSSQL build (14306) SELECT "taskdet"."TASKID" AS "Task ID", "taskdet"."TASKID" AS "Task ID", "wotask"."WORKORDERID" AS "RequestID", cd.CATEGORYNAME AS "Request Category", "taskgroup"."QUEUENAME" AS "Group", "taskowner"."FIRST_NAME" AS ...Query to show on hold status reason/comments
Working on Builds: 14500 and above Databases: PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", CASE WHEN (wo.is_catalog_template) = 'false' THEN 'Incident' ELSE 'Service Request' END "Request Type",dpt.DEPTNAME AS "Department",pd.PRIORITYNAME AS ...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", ...