Query report for both live and archived requests (PGSQL)
Tested in Build 14306
Columns -
- Request ID
- Created Time
- Resolved Time
- Account
- Category
- Subcategory
- Group
Query -
SELECT wo.WORKORDERID AS "Request ID", wo.CREATEDTIME AS "Created Time", wo.RESOLVEDTIME AS "Resolved Time", ad.ORG_NAME AS "Account", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", qd.QUEUENAME AS "Group" FROM WorkOrder wo LEFT JOIN WorkOrderAccountMapping wam ON wo.WORKORDERID=wam.WORKORDERID LEFT JOIN PortalAccounts port_acc ON wam.ACCOUNTID=port_acc.ACCOUNTID LEFT JOIN AccountDefinition ad ON port_acc.ACCOUNTID=ad.ORG_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID WHERE (wo.ISPARENT='1') UNION SELECT arcwo.WORKORDERID AS "Request ID", arcwo.CREATEDTIME AS "Created Time", arcwo.RESOLVEDTIME AS "Resolved Time", ad.ORG_NAME AS "Account", arcwo.CATEGORYNAME AS "Category", arcwo.SUBCATEGORYNAME AS "Subcategory", arcwo.QUEUENAME AS "Group" FROM Arc_WorkOrder arcwo LEFT JOIN Arc_WorkOrderAccountMapping awm ON arcwo.WORKORDERID=awm.WORKORDERID LEFT JOIN PortalAccounts port_acc ON awm.ACCOUNTID=port_acc.ACCOUNTID LEFT JOIN AccountDefinition ad ON awm.ACCOUNTID=ad.ORG_ID
New to ADSelfService Plus?
Related Articles
Query to retrieve both the live and archived requests details
Database: Pgsql Query: SELECT wo.WORKORDERID AS "Request ID", pd.PRIORITYNAME AS "Priority", id.NAME AS "Impact", ud.NAME AS "Urgency", rtd.NAME AS "Request Type", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved ...Query to generate the report based on the total number of requests actioned by the technicians (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) This is a comprehensive report on the total number of requests updated by a technicians. This query provides a holistic view of technician activity like updating resolution process, such as updating or ...Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...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 ...Archived and Active Request in a same report
This Report is used to get the complete list of request both active and archived request in the same report. To make any changes to a query, refer to the KB article below. ...