Does anyone have a report on affected configuration items?

Alan_Foster

Does anyone have a report on affected configuration items from the service management database?  Looking for a top 10 configuration items from Incidents.  I know there is one in the Cireson analytics but for some reason it is not working in my portal.

Brian_Wiest

What version of the portal are you on? Some of the older had sync issues in SQL for analytics. Upgrading resolves those issues.

For your question here is a small sample that should put you on the path. This returns all the related CI specific to business services.

SELECT CItem.DisplayName, wi.WorkItemId FROM

WorkItem wi

INNER JOIN Affected_CI$WorkItem_ConfigurationItem AffCI ON wi.Id = AffCI.WorkItemId

INNER JOIN ConfigurationItem CItem ON AffCI.ConfigurationItemId = CItem.Id

WHERE wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' 

AND CItem.ObjectClassName = 'Microsoft.SystemCenter.BusinessService'

ORDER BY CItem.DisplayName

Brian_Wiest

If you look at the release notes v8.3.1.X is where Cireson resolves issues with the analytics jobs. 
https://support.cireson.com/Problem/Edit/PR71203

Brian_Wiest

What version of the portal are you on? Some of the older had sync issues in SQL for analytics. Upgrading resolves those issues.

For your question here is a small sample that should put you on the path. This returns all the related CI specific to business services.

SELECT CItem.DisplayName, wi.WorkItemId FROM

WorkItem wi

INNER JOIN Affected_CI$WorkItem_ConfigurationItem AffCI ON wi.Id = AffCI.WorkItemId

INNER JOIN ConfigurationItem CItem ON AffCI.ConfigurationItemId = CItem.Id

WHERE wi.ClassId = 'A604B942-4C7B-2FB2-28DC-61DC6F465C68' 

AND CItem.ObjectClassName = 'Microsoft.SystemCenter.BusinessService'

ORDER BY CItem.DisplayName

Alan_Foster

@Brian_Wiest
Current Portal Version: 8.2.0.2016

Management Pack Version: 7.7.2016.185 
I will take a look at the query.  Thanks

Brian_Wiest

If you look at the release notes v8.3.1.X is where Cireson resolves issues with the analytics jobs. 
https://support.cireson.com/Problem/Edit/PR71203