Query to show Problems, its associated incidents and change_ (MSSQL)
Tested in Build MSSQL (14306)
SELECT woproblem.PROBLEMID AS "Problem ID", woproblem.TITLE AS "Problem Title", "priodef"."PRIORITYNAME" AS "Problem Priority", "urgdef"."NAME" AS "Problem Urgency", "statdef"."STATUSNAME" AS "Problem Status", "impactdef"."NAME" AS "Problem Impact", "probdesc"."FULL_DESCRIPTION" AS "Problem Description", LONGTODATE("woproblem"."REPORTEDTIME") AS "Problem Reported Date", LONGTODATE("woproblem"."UPDATEDTIME") AS "Problem Updated Date", LONGTODATE("woproblem"."DUEBYTIME") AS "Problem DueBy Date", LONGTODATE("woproblem"."CLOSEDTIME") AS "Problem Closed Date","solwork"."DESCRIPTION" AS "Workaround","solres"."DESCRIPTION" AS "Solution", wo.WORKORDERID AS "Associated Request IDs", wo.TITLE AS "Request Subject", cd.CATEGORYNAME AS "Request Category", scd.NAME AS "Request Subcategory",icd.NAME AS "Request Item", ti.FIRST_NAME AS "Request Technician", "sdo"."NAME" AS "Site","sdu"."ISVIPUSER" AS "VIP User","ad"."ORG_NAME" AS "Account", chd.changeid "Associated Change ID", chd.title "Change Subject" FROM WorkOrder wo LEFT JOIN WorkOrderToDescription wotodesc ON wo.WORKORDERID=wotodesc.WORKORDERID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN ItemDefinition icd ON wos.ITEMID=icd.ITEMID LEFT JOIN CategoryDefinition cd ON wos.CATEGORYID=cd.CATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID LEFT JOIN WorkOrder_Fields wof ON wo.WORKORDERID=wof.WORKORDERID LEFT JOIN ProblemToIncidentMapping wotoproblemdet ON wo.WORKORDERID=wotoproblemdet.WORKORDERID LEFT JOIN Problem woproblem ON wotoproblemdet.PROBLEMID=woproblem.PROBLEMID LEFT JOIN Problemtochangemapping ptc ON woproblem.PROBLEMID=ptc.problemid LEFT JOIN Changedetails chd ON ptc.changeid=chd.changeid LEFT JOIN "PriorityDefinition" "priodef" ON "woproblem"."PRIORITYID"="priodef"."PRIORITYID" LEFT JOIN "UrgencyDefinition" "urgdef" ON "woproblem"."URGENCYID"="urgdef"."URGENCYID" LEFT JOIN "CategoryDefinition" "catadef" ON "woproblem"."CATEGORYID"="catadef"."CATEGORYID" LEFT JOIN "ProblemResolution" "probResol" ON "woproblem"."PROBLEMID"="probResol"."PROBLEMID" LEFT JOIN "StatusDefinition" "statdef" ON "woproblem"."STATUSID"="statdef"."STATUSID" LEFT JOIN SDUSER sdu ON woproblem.originatorid=sdu.userid
LEFT JOIN "ImpactDefinition" "impactdef" ON "probResol"."IMPACTID"="impactdef"."IMPACTID"
LEFT JOIN "ProblemToDescription" "probdesc" ON "woproblem"."PROBLEMID"="probdesc"."PROBLEMID"
LEFT JOIN "SolutionToWorkAround" "probwork" ON "woproblem"."PROBLEMID"="probwork"."PROBLEMID"
LEFT JOIN "Solution" "solwork" ON "probwork"."SOLUTIONID"="solwork"."SOLUTIONID"
LEFT JOIN "SolutionToResolution" "probres" ON "woproblem"."PROBLEMID"="probres"."PROBLEMID"
LEFT JOIN "Solution" "solres" ON "probres"."SOLUTIONID"="solres"."SOLUTIONID"
LEFT JOIN "SiteDefinition" "siteDef" ON "woproblem"."SITEID"="siteDef"."SITEID"
LEFT JOIN "SDOrganization" "sdo" ON "siteDef"."SITEID"="sdo"."ORG_ID"
INNER JOIN "AccountSiteMapping" "asm" ON "woproblem"."SITEID"="asm"."SITEID"
INNER JOIN "AccountDefinition" "ad" ON "asm"."ACCOUNTID"="ad"."ORG_ID" WHERE (wo.ISPARENT='1') AND woproblem.PROBLEMID=1 ORDER BY 1
New to ADSelfService Plus?
Related Articles
Query to show technicians associated accounts, sites and Support groups - (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) 1.Technicians and associated Accounts/Sites: select aau.User_id, aau.first_name "First Name", sdu.lastname "Last Name", aal.name "Login Name", AaaContactInfo.EMAILID "Email Address",sdu.jobtitle "Job ...Query to get all the requests associated to a change (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Postgres Query: select chd.ChangeID "Change ID", chd.title "Change name", wo.workorderid "Request ID", wo.title "Request Subject" from incidentcausedbychange incach left join ChangeDetails chd on ...Query to show requests and its associations with Problems, Changes and Projects (MSSQL)
Working on Builds: 14500 and above Database: MSSQL SELECT wo.WORKORDERID AS "Request ID", pim.problemid "Associated Problem ID", icm.changeid "Associated Change ID", wtp.projectid "Associated Project ID", CASE WHEN (wo.is_catalog_template) = 'false' ...Query to show Problem details, timespent and its associated request IDs (MSSQL)
Tested in MSSQL build (14306) SELECT "prob"."PROBLEMID" AS "Problem ID", "prob"."TITLE" AS "Title", "statdef"."STATUSNAME" AS "Status", "orgaaa"."FIRST_NAME" AS "Reported by", Cast((((sum(ct.timespent))/1000)/3600) as varchar(20)) +'Hrs ...Query to show Asset state history ( MSSQL)
Tested in MSSQL build (14306) SELECT res.resourcename 'Asset Name', res.assettag 'Asset Tag',res.serialno 'Asset Serial No.', rs2.statedesc 'Previous State',rs1.statedesc 'Current State',DATEADD(s,DATEDIFF(s,GETUTCDATE() ,getdate()) + ...