Archived and Active Request in a same report

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. 


SELECT wo.WORKORDERID "Request ID",
       wo.TITLE "Subject",
       aau.FIRST_NAME "Requester",
       dpt.DEPTNAME "Department",
       ti.FIRST_NAME "Technician",
       LONGTODATE(wo.CREATEDTIME) "Created Time",
       LONGTODATE(wo.COMPLETEDTIME) "Completed Time",
       std.STATUSNAME "Request Status" 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
WHERE (wo.ISPARENT=1)
  AND wo.CREATEDTIME >= <from_thismonth>
  AND wo.CREATEDTIME <= <to_thismonth>
UNION
SELECT arcwo.WORKORDERID "Request ID",
       arcwo.TITLE "Subject",
       arcRequester.FIRST_NAME "Requester",
       arcwo.DEPTNAME "Department",
       arcTech.FIRST_NAME "Technician",
       LONGTODATE(arcwo.CREATEDTIME) "Created Time",
       LONGTODATE(arcwo.COMPLETEDTIME) "Completed Time",
       arcwo.STATUSNAME "Status" FROM Arc_WorkOrder arcwo
LEFT JOIN AaaUser arcRequester ON arcwo.REQUESTERID=arcRequester.USER_ID
LEFT JOIN AaaUser arcTech ON arcwo.OWNERID=arcTech.USER_ID
WHERE arcwo.CREATEDTIME >= <from_thismonth>
  AND arcwo.CREATEDTIME <= <to_thismonth> ORDER BY 1

Note : Login to ServiceDesk Plus, go to Reports tab > New Query Report > Copy the query to the query editor and run the report. 







      New to M365 Manager Plus?
      New to M365 Manager Plus?
        New to RecoveryManager Plus?
        New to RecoveryManager Plus?
          New to Exchange Reporter Plus?
          New to Exchange Reporter Plus?
            New to SharePoint Manager Plus?
            New to SharePoint Manager Plus?
                See all integrations
                New to ADManager Plus?
                See all integrations
                New to ADManager Plus?

                  New to ADSelfService Plus?

                  Start your free trial
                  Start your free trial

                    • Related Articles

                    • Request missing

                      Use case: In some cases, requests are missing. We would get the ID number from acknowledgement notification but we cannot find that in the tickets. In such cases, please follow below steps to find out the details. 1. Make sure to check the Request ...

                    • 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 ...

                    • 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 ...

                    • Service request approvers

                       This report helps to find the who all the service request approvers in the application.  SELECT aaauser.first_name "Approver Name", dpt.deptname "Department", sdo.name "Site", sd.jobtitle "Job Title" FROM srapproverdetails LEFT JOIN aaauser ON ...

                    • 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 ...

                      Related Products