Query to show Approved tickets per Approver (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306)
PGSQL & MSSQL:
SELECT wo.WORKORDERID "Request ID",
wo.TITLE "Subject",
std.STATUSNAME "Request Status",
ti.FIRST_NAME "Technician",
longtodate(wo.CREATEDTIME) "Created Time",
ApprovalDetails.EMAIL "Approver Email",
asd.stagename "Stage",
ApprovalStatusDefinition.STATUSNAME "Approval Status",
LONGTODATE(ApprovalStage.SENT_DATE) "Approval Sent date",
LONGTODATE(ApprovalDetails.ACTION_DATE) "Approved Date" FROM WorkOrder wo
LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID
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
JOIN ApprovalStageMapping ON wo.WORKORDERID=ApprovalStageMapping.WORKORDERID
LEFT JOIN ApprovalDetails ON ApprovalStageMapping.APPROVAL_STAGEID=ApprovalDetails.APPROVAL_STAGEID
LEFT JOIN ApprovalStage ON ApprovalDetails.APPROVAL_STAGEID=ApprovalStage.APPROVAL_STAGEID
LEFT JOIN approvalstatusdefinition ON ApprovalDetails.STATUSID=ApprovalStatusDefinition.STATUSID
LEFT JOIN approvalstagedefinition asd ON asd.STAGEID=ApprovalDetails.STAGEID
LEFT JOIN aaauser createduser on createduser.user_id=wo.createdbyid
left join workorder_queue ON workorder_queue.workorderid=wo.workorderid
left join queuedefinition ON workorder_queue.queueid=queuedefinition.queueid
LEFT JOIN ServiceCatalog_Fields scf on scf.workorderid=wo.workorderid
LEFT JOIN requesttemplate_list reqlist on reqlist.templateid=wo.templateid
WHERE ApprovalStatusDefinition.STATUSNAME='Approved' AND ApprovalDetails.EMAIL IN('abc@zoho.com','hari@manageengine.com') AND wo.CREATEDTIME >= DATETOLONG('2020-04-01 07:00:00') AND wo.CREATEDTIME <= DATETOLONG('2020-04-30 23:59:59')
Note: Approver email ID and Date filter needs to be modified as per the need.
New to ADSelfService Plus?
Related Articles
Query that lists pending approval of a particular service approver (PGSQL)
Doesnt work in PGSQL (14300) ERROR: relation "workorder_threaded" does not exist SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",wo.TITLE "Subject",ti.FIRST_NAME "Technician", longtodate(wo.CREATEDTIME) "Created Time",appsau.first_name ...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", ...Query to show number of tickets created per template with their status (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT reqtl.TEMPLATENAME "Request Template", count(wo.WORKORDERID) "Request Count", count(case when std.ispending='0' THEN 1 ELSE NULL END) "Completed", count(case when std.ispending='1' THEN 1 ELSE ...Query to show tasks created per template along with status (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Since Task template is not stored in Task details, we cannot show count based on task templates. However, we have tried to form a query with task subject considering that, when task templates are ...Query to show active and archived tickets (MSSQL)
Tested in build MSSQL (14306) SELECT wo.WORKORDERID "Request ID",qd.QUEUENAME "Group",ti.FIRST_NAME "Technician",serdef.NAME "Service Category",wo.TITLE "Subject",sdo.NAME "Site",accountdefinition.org_name "Account",aau.FIRST_NAME ...