Query to show Problem - Task Report with Problem Details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306)
Attached the sample query output.
Query:
SELECT prob.PROBLEMID AS "Problem ID", prob.TITLE AS "Title",catadef.CATEGORYNAME AS "Category",
longtodate(prob.CLOSEDTIME) AS "Closed Date", probdesc.FULL_DESCRIPTION AS "Description",
longtodate(prob.DUEBYTIME) AS "DueBy Date", qDef.QUEUENAME AS "Group", impactdef.NAME AS "Impact",
itemdef.NAME AS "Item", priodef.PRIORITYNAME AS "Priority", orgaaa.FIRST_NAME AS "Reported by",
longtodate(prob.REPORTEDTIME) AS "Reported Date", sdo.NAME AS "Site", solres.DESCRIPTION AS "Solution",
statdef.STATUSNAME AS "Status", subcatadef.NAME AS "Subcategory", ownaaa.FIRST_NAME AS "Technician",
longtodate(prob.UPDATEDTIME) AS "Updated Date", urgdef.NAME AS "Urgency", orgsd.ISVIPUSER AS "VIP User",
solwork.DESCRIPTION AS "Workaround", task.taskid as "Task ID", task.title as "Task Title", taskstat.statusname as "Task Status"
FROM Problem prob
LEFT JOIN SDUser orgsd ON prob.ORIGINATORID=orgsd.USERID
LEFT JOIN AaaUser orgaaa ON orgsd.USERID=orgaaa.USER_ID
LEFT JOIN SDUser ownsd ON prob.OWNERID=ownsd.USERID
LEFT JOIN AaaUser ownaaa ON ownsd.USERID=ownaaa.USER_ID
LEFT JOIN StatusDefinition statdef ON prob.STATUSID=statdef.STATUSID
LEFT JOIN PriorityDefinition priodef ON prob.PRIORITYID=priodef.PRIORITYID
LEFT JOIN UrgencyDefinition urgdef ON prob.URGENCYID=urgdef.URGENCYID
LEFT JOIN CategoryDefinition catadef ON prob.CATEGORYID=catadef.CATEGORYID
LEFT JOIN SubCategoryDefinition subcatadef ON prob.SUBCATEGORYID=subcatadef.SUBCATEGORYID
LEFT JOIN ItemDefinition itemdef ON prob.ITEMID=itemdef.ITEMID
LEFT JOIN ProblemResolution probResol ON prob.PROBLEMID=probResol.PROBLEMID
LEFT JOIN ImpactDefinition impactdef ON probResol.IMPACTID=impactdef.IMPACTID
LEFT JOIN ProblemToDescription probdesc ON prob.PROBLEMID=probdesc.PROBLEMID
LEFT JOIN SolutionToWorkAround probwork ON prob.PROBLEMID=probwork.PROBLEMID
LEFT JOIN Solution solwork ON probwork.SOLUTIONID=solwork.SOLUTIONID
LEFT JOIN SolutionToResolution probres ON prob.PROBLEMID=probres.PROBLEMID
LEFT JOIN Solution solres ON probres.SOLUTIONID=solres.SOLUTIONID
LEFT JOIN SiteDefinition siteDef ON prob.SITEID=siteDef.SITEID
LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID
LEFT JOIN ProblemToQueue ON prob.PROBLEMID=ProblemToQueue.PROBLEMID
LEFT JOIN QueueDefinition qDef ON ProblemToQueue.QUEUEID=qDef.QUEUEID
left join problemtotaskdetails probtotask on probtotask.problemid =prob.problemid
left join taskdetails task on task.taskid = probtotask.taskid
left join statusdefinition taskstat on taskstat.statusid = task.statusid ORDER BY 1,2,10;
New to ADSelfService Plus?
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", ...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", ...Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...Query report to show Problem fields along with last added notes (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306) PGSQL & MSSQL: SELECT prob.PROBLEMID AS "Problem ID", LONGTODATE(prob.REPORTEDTIME) AS "Reported Date", orgaaa.FIRST_NAME AS "Reported by", LONGTODATE(prob.DUEBYTIME) AS "DueBy Date", ...Contract and Service Plans details - Query Report (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: select ac.contractno "Contract Number", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type", sp.timeperiod "Bill ...