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 ...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 ...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 ...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 report to extract Active Technician Login and IP Address (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports- New Query Report and execute this query. select DISTINCT ac.ACCOUNT_ID as "Login Account ID", au.FIRST_NAME as "Technician Name ( First Name )", count(acs.SESSION_ID) as "Number of ...