Query to list Merged Requests (PGSQL)
Tested in Build : 14506 , PGSQL
SELECT wo.WORKORDERID AS "Request Id", wo.TITLE AS "Subject", from_unixtime(wo.CREATEDTIME/1000) AS "Created Time", from_unixtime(wo.DUEBYTIME/1000) AS "Due Time", from_unixtime(wos.LAST_TECH_UPDATE/1000) AS "LastUPDATE Time", STRING_AGG(nw.CHILD_WOID :: TEXT,',') AS "Merged Requests", au.FIRST_NAME "Merged By" FROM WorkOrder wo INNER JOIN WorkOrderStates wos ON wo.WORKORDERID = wos.WORKORDERID INNER JOIN Notify_WorkOrder nw ON wo.WORKORDERID = nw.WORKORDERID LEFT JOIN WorkOrderHistory woh ON wo.WORKORDERID = woh.WORKORDERID LEFT JOIN AaaUser au ON woh.OPERATIONOWNERID=au.USER_ID WHERE nw.CHILD_WOID is NOT NULL AND woh.OPERATION='MERGEWITH' AND (from_unixtime(wos.LAST_TECH_UPDATE/1000) >= ?) GROUP BY wo.WORKORDERID, wo.TITLE, wo.CREATEDTIME, wo.DUEBYTIME, wos.LAST_TECH_UPDATE, au.FIRST_NAME
New to ADSelfService Plus?
Related Articles
Query to list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...Query to show shared requests (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) Shared to Tech: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", LONGTODATE(wo.CREATEDTIME) AS "Created Time", ...Query to show parent and child requests (Linked Request) and its details.(MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Please go to Reports-New Query Report and execute this query. select woparent.WORKORDERID "Parent request ID", aaauparentreq.first_name "Parent Requester", woparent.title "Parent Subject", ...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 to fetch the fields in the templates (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) The query fetches all the fields with display name used across Request templates PQSQL: select Distinct (CASE when fc.field_name like 'udf_%' then ca.aliasname else fc.field_name END) "Fields", ...