Task related reports (MSSQL)

Task related reports (MSSQL)

Tested in MSSQL build (14306)


Sample Output and headers present in this report


Goto reports -> New query report and executes the following 

SELECT 
taskdet.TASKID AS "Task ID", 
taskdet.MODULE AS "Module", 
taskprior.PRIORITYNAME AS "Priority", 
taskdesc.DESCRIPTION AS "Description", 
tc.COMMENT As "Task comments",
taskgroup.QUEUENAME AS "Group", 
taskowner.FIRST_NAME AS "Owner", 
taskcreatedby.FIRST_NAME AS "Requester", 
LONGTODATE(taskdet.CREATEDDATE) AS "Created Date", 
taskstatus.STATUSNAME AS "Task Status", 
taskprob.PROBLEMID AS "Problem ID", 
taskchange.CHANGEID AS "Change ID", 
taskproject.PROJECTID AS "Project Id",
wotask.WORKORDERID AS "RequestID",
LONGTODATE(TasDetHistory.OPERATIONTIME) As "Task Updated time"
FROM TaskDetails taskdet 
LEFT JOIN AaaUser taskcreatedby ON taskdet.CREATEDBY=taskcreatedby.USER_ID 
LEFT JOIN SDUser taskownersdu ON taskdet.OWNERID=taskownersdu.USERID 
LEFT JOIN AaaUser taskowner ON taskownersdu.USERID=taskowner.USER_ID 
LEFT JOIN PriorityDefinition taskprior ON taskdet.PRIORITYID=taskprior.PRIORITYID 
LEFT JOIN StatusDefinition taskstatus ON taskdet.STATUSID=taskstatus.STATUSID 
LEFT JOIN QueueDefinition taskgroup ON taskdet.GROUPID=taskgroup.QUEUEID 
LEFT JOIN WorkOrderToTaskDetails wototaskdet ON taskdet.TASKID=wototaskdet.TASKID 
LEFT Join TaskToComment ttc ON taskdet.TASKID=ttc.TASKID
LEFT JOIN Comments TC ON ttc.TASKID=tc.COMMENTID
LEFT JOIN TaskDescription taskdesc ON taskdet.TASKID=taskdesc.TASKID
LEFT JOIN WorkOrder wotask ON wototaskdet.WORKORDERID=wotask.WORKORDERID 
LEFT JOIN ProblemToTaskDetails probtotaskdet ON taskdet.TASKID=probtotaskdet.TASKID 
LEFT JOIN Problem taskprob ON probtotaskdet.PROBLEMID=taskprob.PROBLEMID 
LEFT JOIN ChangeToTaskDetails changetotaskdet ON taskdet.TASKID=changetotaskdet.TASKID 
LEFT JOIN ChangeDetails taskchange ON changetotaskdet.CHANGEID=taskchange.CHANGEID  
 LEFT JOIN TaskToProjects projtotaskdet ON "taskdet"."TASKID"="projtotaskdet"."TASKID" 
LEFT JOIN ProjectDetails taskproject ON "projtotaskdet"."PROJECTID"="taskproject"."PROJECTID"
 LEFT JOIN TaskToProjects ON "taskdet"."TASKID"="TaskToProjects"."TASKID" 
LEFT JOIN ProjectAccMapping ON "TaskToProjects"."PROJECTID"="ProjectAccMapping"."PROJECTID" 
LEFT JOIN AccountSiteMapping ON taskdet.SITEID=AccountSiteMapping.SITEID 
LEFT JOIN (select max(tdhistory.OPERATIONTIME) AS OPERATIONTIME,tdhistory.TASKID AS TaskID from TaskDetailsHistory tdhistory group by tdhistory.TASKID) TasDetHistory ON taskdet.TASKID=TasDetHistory.TASKID



==============
Tested in SQL / 10524 build
==============

                  New to ADSelfService Plus?

                    • Related Articles

                    • Query to show Site and its related settings(MSSQL & PGSQL)

                      Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports-New Query Report and execute the query. select adef.org_name "Account Name",sdef.siteid "Site ID", sdo.name "Site Name", siteconf.settingdetail "Related Settings", CASE WHEN ...
                    • 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 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 fetch Task Worklog and related entity ID

                      DB Compatibility : PGSQL & MSSQL Build Compatibility : Builds above 9400 SELECT taskdet.TASKID AS "Task ID", taskdet.TITLE AS "Title", taskowner.FIRST_NAME AS "Owner", taskdet.MODULE AS "Module", wotask.WORKORDERID AS "Request ID", taskprob.PROBLEMID ...
                    • Task Comments and Description

                      This report is used to find the task comments and Description. To make any changes to a query, refer to the KB article below. https://pitstop.manageengine.com/support/manageengine/ShowHomePage.do#Solutions/dv/24000633501275 SELECT taskdet.TASKID AS ...