Task related reports
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?
Resources
Related Articles
Query to show Site and its related settings
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 (siteconf.configid) = 1 THEN 'Operational hours' WHEN ...Script to send custom reminder notification to notify task owner based on scheduled start and end time.
Prerequisites: 1. Python Installation Steps : https://pitstop.manageengine.com/portal/kb/articles/how-to-setup-python-on-the-server 2. functions.py : This package consists of all the functions that are used to handle the standard requirements of the ...Query to view task details with account
For Postgres and MS SQL SELECT org_name "Account", "taskmilestone"."PROJECTID" AS "Project id", "taskdet"."TASKID" AS "Task ID", "taskdet"."TITLE" AS "Title", "taskcreatedby"."FIRST_NAME" AS "Created By", "taskowner"."FIRST_NAME" AS "Owner", ...Query to show Request and associated task details
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", icd.NAME AS "Item", ti.FIRST_NAME AS "Technician", ...Query to show status changes of a task
PGSQL & MSSQL: SELECT taskdet.taskid "Task ID", taskdet.TITLE "Task Title", std.STATUSNAME "Task Status", aau.first_name "Task Owner", aau1.first_name "Status changed by", LONGTODATE(tdh.operationtime) "Status changed in (Time)", std1.STATUSNAME ...