Problem Relates to Config Item

Brian_Wiest

Need some assistance finding the correct table.
Managment is looking for the SQL widgets to report back problems as releates to Config items. 
Almost most are Business Service. 
I can find the Items On the table [SC2012_ServiceManager].[dbo].[MTV_Microsoft$SystemCenter$BusinessService]

Here is my current query 

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]

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

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

Thanks

Geoff_Ross

Brian,

Here's an example to bring in all IRs with the affected Business Service.
Hopefully you can add this into the query above.

SELECT c.DisplayName, a.WorkItemId FROM
WorkItem a
INNER JOIN Affected_CI$WorkItem_ConfigurationItem b
ON a.Id = b.WorkItemId
INNER JOIN ConfigurationItem c
ON b.ConfigurationItemId = c.Id
WHERE a.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' 
AND c.ObjectClassName = 'Microsoft.SystemCenter.BusinessService'
ORDER BY c.DisplayName

Geoff

Geoff_Ross

Brian,

Here's an example to bring in all IRs with the affected Business Service.
Hopefully you can add this into the query above.

SELECT c.DisplayName, a.WorkItemId FROM
WorkItem a
INNER JOIN Affected_CI$WorkItem_ConfigurationItem b
ON a.Id = b.WorkItemId
INNER JOIN ConfigurationItem c
ON b.ConfigurationItemId = c.Id
WHERE a.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' 
AND c.ObjectClassName = 'Microsoft.SystemCenter.BusinessService'
ORDER BY c.DisplayName

Geoff

Brian_Wiest

Woohoo Got it to work 
Final code if anyone else is interested-Works well in the SQL widgets

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
INNER JOIN Affected_CI$WorkItem_ConfigurationItem AffCI ON wi.Id = AffCI.WorkItemId
INNER 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 = 0
Order by ID DESC

Geoff_Ross

Excellent work and very nice pasting of your T-SQL query.