Query to show who closed the request
PGSQL & MSSQL:
select wo.workorderid "Request ID", aau.first_name "Request Closed By", cit.typename "Role", queuedefinition.queuename "Group" from WorkOrder wo LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID LEFT JOIN WorkOrderHistoryDiff wohd ON woh.HISTORYID = wohd.HISTORYID LEFT JOIN aaauser aau ON woh.operationownerid=aau.user_id LEFT JOIN sduser sdu ON aau.user_id=sdu.userid LEFT JOIN ci ci ON sdu.ciid=ci.ciid LEFT JOIN citype cit ON cit.typeid=ci.citypeid left join workorder_queue woq
on wo.workorderid=woq.workorderid left join queuedefinition on woq.queueid=queuedefinition.queueid WHERE woh.operation='CLOSE' AND wo.createdtime>=<from_thismonth>and wo.createdtime<=<to_thismonth>
ORDER BY 1 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>
- 1
NOT WOrking on Latest
New to ADSelfService Plus?
Related Articles
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 time elapsed in a ticket with SLA associated (PGSQL)
Tested in PGSQL build (14300) PGSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status", LONGTODATE(wo.CREATEDTIME) "Created Time", ...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 hours and days since last update (MSSQL)
Tested in Build MSSQL (14306) Go to Reports-New Query Report and execute this query. select wos.WORKORDERID "Request ID", max(accountdefinition.org_name) "Account",MAX(aau.FIRST_NAME) "Requester",MAX(wo.TITLE) "Subject", MAX(sd.statusname) "Ticket ...Request closed by and Resolved by from history
This report is used to find the closed by and resolved by technician from the history. In some cases the request may be assigned to some technician and it is closed by a different technician. This information is captured in the history. To make any ...