Query to retrieve pending approvals from a technician account specific (MSSQL)
Tested in build MSSQL (14306)
SELECT wo.WORKORDERID AS "Request ID",
ad.org_name as "Account",
aaa .first_name as "Technician",
ApprovalStatusDefinition.STATUSNAME AS "Approval Status" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
Left Join AAAUser srep ON wos.OwnerID=srep.User_ID
LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID
LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID
LEFT JOIN AccountSiteMapping asm ON wo.SITEID=asm.SITEID
LEFT JOIN AccountDefinition ad ON asm.ACCOUNTID=ad.ORG_ID
left 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 sduser sd on sd.userid=ApprovalDetails.approverid
left join aaauser aaa on aaa.user_id=sd.userid
left join workorderaccountmapping wam on wam.workorderid=wo.workorderid
WHERE ApprovalDetails.ISDELETED!=1 and ApprovalStatusDefinition.STATUSNAME like '%Pending%' and ad.org_name like '%My Org Inc%'
Group by wo.WORKORDERID,aaa .first_name,ad.org_name,ApprovalStatusDefinition.STATUSNAME ORDER BY 1
Replace the account name as per your account based on your requirement and execute the query.
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 ...How to email pending requests list to technician periodically
This post describes the use of a python script to email pending requests to each technician in a periodic interval using Custom Schedules.This showcases the use of a feature called 'Custom Schedules' that was released in the build 9300 of ServiceDesk ...Pending Request for more than 10 days
This report provides a quick view of all Pending incidents which are not closed for more than 10 days. When the incidents start backlogging faster than they can be resolved, the tendency of long incidents pending time can be exposed in this report. ...Query to show technician hop count (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) SELECT wo.WORKORDERID "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", qd.QUEUENAME "Current Group",aau.FIRST_NAME AS "Requester", ti.FIRST_NAME "Technician", ad.ORG_NAME AS "Account", ...Query to get approval status per request with approvers by account (MSSQL)
Tested in build MSSQL (14306) SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", wo.DESCRIPTION As "DESCRIPTION", aau.FIRST_NAME AS "Requester", dpt1.DEPTNAME AS " Requester Department", srep.First_Name As "Suprep", qd.QUEUENAME AS ...