Problem Relates to Config Item

Brian_WiestBrian_Wiest Customer Ninja IT Monkey ✭✭✭✭
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

Best Answer

Answers

  • Brian_WiestBrian_Wiest Customer Ninja IT Monkey ✭✭✭✭
    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_RossGeoff_Ross Cireson Consultant Super IT Monkey ✭✭✭✭✭
    Excellent work and very nice pasting of your T-SQL query.
Sign In or Register to comment.