how to copy data for related items for all business services?

Amit_Sehgal

I am trying to create a view to see the related work items for CIs (Business services in this case related to Incidents), I have tried couple of combination class and that didn't work, also tried the service map advanced combination class,

then I tried to get this off related items from the console, its shows the data but I can't copy. any idea on how to copy?

David_Morris1

I would suggest a script targeting the Data warehouse would be the easiest way to get this info on mass,

Sample below showing all services and any related workitems, you may also want to use the "Workitemaboutconfigitemfactvw" as a join to see any services that have been linked as "Affected" rather than "Related"

SELECT BService.displayname as "Service"

,WI.Id as "Workitem"

  FROM [DWDataMart].[dbo].[ServiceDimvw] BService

  left join [DWDataMart].[dbo].[ConfigItemDimvw] CI on BService.EntityDimKey = CI.EntityDimKey

  left join [DWDataMart].[dbo].[WorkItemRelatesToConfigItemFactvw] CI2WI on CI.ConfigItemDimKey = CI2WI.WorkItemRelatesToConfigItem_ConfigItemDimKey

  Left Join [DWDataMart].[dbo].[WorkItemDimvw] WI on CI2WI.WorkItemDimKey = WI.WorkItemDimKey

  where WI.Id is not null

David_Morris1

I would suggest a script targeting the Data warehouse would be the easiest way to get this info on mass,

Sample below showing all services and any related workitems, you may also want to use the "Workitemaboutconfigitemfactvw" as a join to see any services that have been linked as "Affected" rather than "Related"

SELECT BService.displayname as "Service"

,WI.Id as "Workitem"

  FROM [DWDataMart].[dbo].[ServiceDimvw] BService

  left join [DWDataMart].[dbo].[ConfigItemDimvw] CI on BService.EntityDimKey = CI.EntityDimKey

  left join [DWDataMart].[dbo].[WorkItemRelatesToConfigItemFactvw] CI2WI on CI.ConfigItemDimKey = CI2WI.WorkItemRelatesToConfigItem_ConfigItemDimKey

  Left Join [DWDataMart].[dbo].[WorkItemDimvw] WI on CI2WI.WorkItemDimKey = WI.WorkItemDimKey

  where WI.Id is not null

Amit_Sehgal

thanks for your help Davis this is very helpful.