Report on Affected CI table & Change Request

[Naveen87]

Hello Developers,

On a CHG record, We have a related list call Affected CI.

I'm creating a report on task_ci table to show the CHG record where these Affected CI are mentioned.

I have 1000's of CI for which report needs to be fetched. I don't want to add 1000's OR conditions.

Is there a way/condition which is written on Parent of CI & all downstream CI's are shown?

Ex:- 

SW_License_Key_PRD is a Parent CI(cmdb_ci_appl). It has 22 downstream CI.

Weblogic instance_SW_License_Key_PRD_fieslgp02_wl-clics-clics-2 is 1 of them.

On CHG0325591, Weblogic instance_SW_License_Key_PRD_fieslgp02_wl-clics-clics-2 is affected CI. 

I don't wish to add rest 21 CI in the report condition to fetch it.

I wish to add the Parent "SW_License_Key_PRD is a Parent CI(cmdb_ci_appl)" which will show all CHG's where 22 downstream/child CI's are used

Please suggest a way out.

[Mark Manders]

The CI's don't have a parent/child relationship like that. That's done through the cmdb_rel_ci table. I guess you need to create a database view with the cmdb_rel_ci and task_ci table for this, so you can put the parent in the query.

[Naveen87]

Can you please help me with the steps to create Database view & connect 2 tables & build the report.

[Mark Manders]

https://www.youtube.com/watch?v=_JY462TmXxU

[Naveen87]

Thank you. I have created a Database view.

Tried to create a report on this & fetch the data.

I have a change record CHG0271533 which is using the CI = SW_License_Key_PRD but nothing is shown in the report. 

This was simple condition & no data pop - up. 

Next condition would be ci.parent = SW_License_Key_PRD.

I doubt this will show 22 child CI's CHG.

[Mark Manders]

Is the data in your database view? What does it show when you have no conditions? What fields have you added to the DB view to show?