Cireson Support Portal "Open Issues" solution

Best Answer

• Options

  Brian_Wiest Customer Super IT Monkey ✭✭✭✭✭

  May 2017 Answer ✓

  Use the dashboard builder to create the page and table. 
  SQL Table query someone along the lines of ...
  
  

  SELECT wi.WorkItemId as [Id], 

  wi.Title,

  dsstatus.DisplayString as [Status], 

  dssupport.DisplayString as [Support Group], 

  wi.PriorityId as [Priority],

  dscategory.DisplayString as [Category],

  dssource.DisplayString as [Source],

  Problem.KnownError_4CE16CF0_5098_3CBD_3F72_72C2C5201FDB as [Known Issue],

  Case When wi.AssignedUser IS Null Then 'UnAssigned'

  Else wi.AssignedUser

  End as [AssignedTo], 

  CONVERT(varchar,DATEADD(hh,-5,wi.Created),100) [Created], 

  CONVERT(varchar,DATEADD(hh,-5,wi.LastModified),100) [Last Modified],

  CItem.DisplayName

  FROM [WorkItem] wi

  LEFT JOIN [Enumeration] enum ON enum.EnumerationID = wi.TierId 

  LEFT JOIN [DisplayString] dssupport ON dssupport.ElementID=enum.EnumerationID 

  LEFT JOIN [DisplayString] dsstatus ON dsstatus.ElementID=wi.StatusId

  LEFT JOIN [DisplayString] dscategory ON dscategory.ElementID=wi.CategoryId

  LEFT JOIN [DisplayString] dssource ON dssource.ElementID=wi.SourceId

  LEFT OUTER JOIN Affected_CI$WorkItem_ConfigurationItem AffCI ON wi.Id = AffCI.WorkItemId

  LEFT OUTER JOIN ConfigurationItem CItem ON AffCI.ConfigurationItemId = CItem.Id

  JOIN [SC2012_ServiceManager].[dbo].[MTV_System$WorkItem$Problem] [Problem] ON Problem.BaseManagedEntityId = WI.Id

  WHERE wi.ClassId = '422afc88-5eff-f4c5-f8f6-e01038cde67f'

  AND wi.StatusId NOT IN('25EAC210-E091-8AE8-A713-FEA2472F32FF', '7FF92B06-1694-41E5-2DF7-B4D5970D2D2B')

  AND Problem.KnownError_4CE16CF0_5098_3CBD_3F72_72C2C5201FDB = 1

  Order by ID DESC
  
  This example includes Known issues as I separate them onto different tables.

  6