Database view to get Affected CIs and Impacted Services for Changes

[Serkan Yilmaz]

Hi SNC,

There is a requirement to generate a Report for CAB to show all the Affected CIs and Impacted Services related to Changes.

Use case if we have a Change with 4 Affected CIs and 4 Impacted Services, the report should show 8 records with unique CIs/ Services.

Information that we need in the report:

CHG00001         Affected CI01

CHG00001         Affected CI02

CHG00001         Affected CI03

CHG00001         Affected CI04

CHG00001         Impacted Service01

CHG00001         Impacted Service02

CHG00001         Impacted Service03

CHG00001         Impacted Service04

Instead of the Database view result like:

CHG00001         Affected CI01         Impacted Service01

CHG00001         Affected CI01         Impacted Service02

CHG00001         Affected CI01         Impacted Service03

CHG00001         Affected CI01         Impacted Service04

CHG00001         Affected CI02         Impacted Service01

CHG00001         Affected CI02         Impacted Service02

CHG00001         Affected CI02         Impacted Service03

CHG00001         Affected CI02         Impacted Service04

CHG00001         Affected CI03         Impacted Service01

etc....

Tech. details:

Tables:

- Change request

- CIs Affected

- Impacted CIs

Who has a way to fix this requirement with or without a database view or scripting?

arnoud?

Cheers,

Serkan

[Adam Stout]

Reports won't show different fields in the same column.   If I needed to do this, I would look at creating a new table with CHG Impacted Objects with these columns:

Change # - Reference to change

Table - Table Name field of impacted object table

ID - Document ID field of impacted Sys ID

Or just use two embedded or related lists on the change form.

Or a dashboard with two reports on it.

[Serkan Yilmaz]

Hi Adam,

Thanks for your reaction.

Option 2 (related list) is enabled on the Change form, but this is not the requirement.

Option 3 (two reports) we suggest this approach, but the requester wants one overview with all Changes and the related Impacted/ Affected CIs/ Services.

I was hoping for a miracle instead of solving this requirement with System customization

Cheers!

[Arnoud Kooi]

Business Service extends CMDB CI, so you could consider copying the records from task_cmdb_ci_service to task_ci by a BR...

(Unfortunately, task_cmdb_ci_service does not extend task_ci)

You can then base the report on task_ci, and dotwalk the Change / CI, without using a DB view

If you choose Adams option, a reference to cmdb_ci can be made, don't think you need document_id

[Serkan Yilmaz]

Hi Arnoud,

Your suggestion is one of the System customization options, but I don't know if Change Management wants this.

Cheers.