Request aging with recent worklog comments (MSSQL)
Tested in MSSQL build (14306)
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 "Priority", wo.TITLE AS "Subject",wodm.Dependsonid "Depends on", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wos.LAST_TECH_UPDATE) AS "Last Upd Time",std.STATUSNAME AS "Request Status", ct.description "Last Worklog comment", DATEDIFF(DAY, dateadd(s, datediff(s, GETUTCDATE(), getdate()) +(wo.createdtime/1000), '1970-01-01 00:00:00'), GETDATE()) "Days since created",wos.ISOVERDUE AS "Overdue Status" 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 SDUser td ON wos.OWNERID=td.USERID
LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID
LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN WODependency wod ON wo.workorderid=wod.workorderid
LEFT JOIN WODependencyMarking wodm ON wod.Reqdependencyid=wodm.Reqdependencyid
LEFT JOIN ONHOLDSCHEDULER ohs on ohs.workorderid=wo.workorderid
LEFT JOIN WorkOrderToCharge wotoc ON wo.WORKORDERID=wotoc.WORKORDERID
LEFT JOIN ChargesTable ct ON wotoc.CHARGEID=ct.CHARGEID
WHERE (wo.ISPARENT='1') AND std.ispending = 1 AND ct.chargeid=(SELECT max(chargeid) FROM workordertocharge where workorderid=wo.workorderid) AND wo.createdtime>=<from_thismonth> and wo.createdtime<=<to_thismonth>
GROUP BY wo.WORKORDERID, wo.is_catalog_template, dpt.DEPTNAME, pd.PRIORITYNAME, wo.TITLE , wodm.Dependsonid, qd.QUEUENAME, ti.FIRST_NAME , aau.FIRST_NAME , wo.CREATEDTIME, wos.LAST_TECH_UPDATE, std.STATUSNAME, wos.ISOVERDUE, ct.description
order by 1, "Days since created" DESC
NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.
How to compare date column with auto filled date templates?
- Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
- <from_thisweek> - Starting date of this week
- <to_thisweek> - Ending date of this week
- Available Date Templates
- Today - <from_today> - <to_today>
- This week - <from_thisweek> - <to_thisweek>
- Last week - <from_lastweek> - <to_lastweek>
- This month - <from_thismonth> - <to_thismonth>
- Last month - <from_lastmonth> - <to_lastmonth>
- This quarter - <from_thisquarter> - <to_thisquarter>
- Last quarter - <from_lastquarter> - <to_lastquarter>
- Yesterday - <from_yesterday> - <to_yesterday>
New to ADSelfService Plus?
Related Articles
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 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", ...Complete worklog report -MSSQL
Query: SELECT aau.FIRST_NAME AS "Requester", max(wo.WORKORDERID) AS "Request ID", rtdef.NAME AS "Request request type", wo.TITLE AS "Subject", wo.description , std.STATUSNAME AS "Request Status", ti.FIRST_NAME AS "Assigned Technician", ...Query to show tickets older than 30 days ( MSSQL )
Tested in build MSSQL (14306) 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", ...Close comments
This report is used to find the close comment added by requester. To make any changes to a query, refer to the KB article below. https://pitstop.manageengine.com/support/manageengine/ShowHomePage.do#Solutions/dv/24000627781192 SELECT wo.WORKORDERID ...