Query to show PM task details with Next schedule and last ran time (MSSQL & PGSQL)

Query to show PM task details with Next schedule and last ran time (MSSQL & PGSQL)

Tested in Build PGSQL (14300) or MSSQL (14306)


Next schedule time :

SELECT swo.WORKORDERID "PM Task ID",
      adef.org_name "Account",
       sdo.name "Site",
       ti.SCHEDULE_TIME "Next Schedule Time",
       aau.FIRST_NAME "Requester",
       dpt.DEPTNAME "Department",
       cd.CATEGORYNAME "Category",
       scd.NAME "Subcategory",
       icd.NAME "Item",
       qd.QUEUENAME "Group",
       ati.FIRST_NAME "Technician",
       swo.TITLE "Subject",
       std.STATUSNAME "Request Status" FROM sworkorder swo
LEFT JOIN SDUser sdu ON swo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
 left join sitedefinition sdef on sdef.siteid=swo.siteid 
left join accountsitemapping asm on asm.siteid=sdef.siteid 
left join accountdefinition adef on adef.org_id=asm.accountid 
left join sdorganization sdo on sdo.org_id=sdef.siteid
LEFT JOIN SWorkOrderStates swos ON swo.WORKORDERID=swos.WORKORDERID
LEFT JOIN QueueDefinition qd ON swos.QUEUEID=qd.QUEUEID
LEFT JOIN CategoryDefinition cd ON swos.CATEGORYID=cd.CATEGORYID
LEFT JOIN SubCategoryDefinition scd ON swos.SUBCATEGORYID=scd.SUBCATEGORYID
LEFT JOIN ItemDefinition icd ON swos.ITEMID=icd.ITEMID
LEFT JOIN SDUser td ON swos.OWNERID=td.USERID
LEFT JOIN AaaUser ati ON td.USERID=ati.USER_ID
LEFT JOIN StatusDefinition std ON swos.STATUSID=std.STATUSID
LEFT JOIN SREQTASK_INPUT si ON si.WORKORDERID=swo.WORKORDERID
LEFT JOIN task_input ti ON ti.INSTANCE_ID = si.INSTANCE_ID
LEFT JOIN schedule s ON ti.SCHEDULE_ID = s.SCHEDULE_ID
LEFT JOIN UserDepartment ud ON aau.USER_ID=ud.USERID
LEFT JOIN DepartmentDefinition dpt ON ud.DEPTID=dpt.DEPTID 
ORDER BY 4

Last Ran time:


SELECT swo.WORKORDERID "PM Task ID",
      adef.org_name "Account",
       sdo.name "Site",
ti.execution_start_time "Last Ran Start time",
ti.execution_finish_time "Last Ran Finish time",
ti.SCHEDULE_TIME "Next Schedule Time",
       aau.FIRST_NAME "Requester",
       dpt.DEPTNAME "Department",
       cd.CATEGORYNAME "Category",
       scd.NAME "Subcategory",
       icd.NAME "Item",
       qd.QUEUENAME "Group",
       ati.FIRST_NAME "Technician",
       swo.TITLE "Subject",
       std.STATUSNAME "Request Status" FROM sworkorder swo
LEFT JOIN SDUser sdu ON swo.REQUESTERID=sdu.USERID
LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID
 left join sitedefinition sdef on sdef.siteid=swo.siteid 
left join accountsitemapping asm on asm.siteid=sdef.siteid 
left join accountdefinition adef on adef.org_id=asm.accountid 
left join sdorganization sdo on sdo.org_id=sdef.siteid
LEFT JOIN SWorkOrderStates swos ON swo.WORKORDERID=swos.WORKORDERID
LEFT JOIN QueueDefinition qd ON swos.QUEUEID=qd.QUEUEID
LEFT JOIN CategoryDefinition cd ON swos.CATEGORYID=cd.CATEGORYID
LEFT JOIN SubCategoryDefinition scd ON swos.SUBCATEGORYID=scd.SUBCATEGORYID
LEFT JOIN ItemDefinition icd ON swos.ITEMID=icd.ITEMID
LEFT JOIN SDUser td ON swos.OWNERID=td.USERID
LEFT JOIN AaaUser ati ON td.USERID=ati.USER_ID
LEFT JOIN StatusDefinition std ON swos.STATUSID=std.STATUSID
LEFT JOIN SREQTASK_INPUT si ON si.WORKORDERID=swo.WORKORDERID
LEFT JOIN task_input ti ON ti.INSTANCE_ID = si.INSTANCE_ID
LEFT JOIN schedule s ON ti.SCHEDULE_ID = s.SCHEDULE_ID
LEFT JOIN UserDepartment ud ON aau.USER_ID=ud.USERID
LEFT JOIN DepartmentDefinition dpt ON ud.DEPTID=dpt.DEPTID 
ORDER BY 4
        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?

                    New to ADSelfService Plus?

                    Start your free trial

                      • Related Articles

                      • Query to show Request and associated task details (PGSQL & MSSQL)

                        Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", wo.TITLE AS "Subject", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", ...

                      • Query to show Last added worklog of a ticket ( MSSQL )

                        Tested in build MSSQL (14306) MSSQL: SELECT wo.WORKORDERID AS "Ticket Number", pd.PRIORITYNAME AS "Priority", cd.CATEGORYNAME AS "Category", qd.QUEUENAME AS "Group", ti.FIRST_NAME AS "Technician", aau.FIRST_NAME AS "Requester", Wo.title "Subject", ...

                      • Query to show Average response time for Category (MSSQL & PGSQL)

                        Tested in build PGSQL (14300) and MSSQL (14306) PGSQL: SELECT accountdefinition.org_name "Account",cd.categoryname "Category", TO_CHAR(((avg(wo.respondedtime)-avg(wo.createdtime))/1000 || ' second')::interval, 'HH24:MI:SS') "Avg Response Time" FROM ...

                      • Query to show last comments added in Projects task_MSSQL

                        SELECT taskdet.TASKID AS "Task ID", taskdet.TITLE AS "Title", taskowner.FIRST_NAME AS "Owner", taskcreatedby.FIRST_NAME AS "Created By", taskstatus.STATUSNAME AS "Task Status", taskdesc.DESCRIPTION AS "Description", ...

                      • Query to show the last worklog added in a ticket (PGSQL)

                        Tested in build PGSQL (14300) PGSQL: SELECT wo.WORKORDERID "Request ID", max(aau.FIRST_NAME) "Requester", max(wo.TITLE) "Subject", max(qd.QUEUENAME) "Group", max(ti.FIRST_NAME) "Assigned Technician", CAST(ct.TIMESPENT AS FLOAT)/1000/3600 AS "Time ...

                        Related Products