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", ...Query to get the login failed attempt details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports tab > New Query Report, use the below query to get the login failed attempts details, select principal "User Name", LONGTODATE(timestamp), HOSTNAME, OPERATIONNAME from AuditRecord ar LEFT ...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", ...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 ...Query to list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...