Data base view creation between api and api_component table

keshav77 · ‎07-06-2025

Hi All,

I am trying to create a database view by joining cmdb_ci_api & cmdb_ci_api_component table

Purpose of this join is to find out which to find parent child relation ship between them.

I created a database view as but I am little bit confuse what where clause I need to use in this case.

Currently I am using in cmdb_ci_api_component -- com.sys_id=api.sys_id

and

cmdb_rel_ci -- rel.parent=api.sys_id

But this not working currently.. kindly assist me in this

MackI · ‎07-07-2025

hi @keshav77

Common Reasons for No Data:

No Actual Relationships: You might have cmdb_ci_api and cmdb_ci_api_component records, but no actual relationship records (cmdb_rel_ci) have been created between them.
Incorrect Relationship Type: The most frequent culprit. The type.name you're using ('Used by') does not match the actual relationship type defined in your CMDB for these CIs.
No Data for One of the Tables: If any of the three tables (cmdb_ci_api, cmdb_ci_api_component, cmdb_rel_ci) are empty or lack records that meet the join criteria, the view will be empty.

Test the cmdb_rel_ci Query in Isolation:
This helps isolate if the issue is with the relationships themselves.

If this query returns no records, then your database view will also return no records, because the underlying relationships don't exist or don't match your WHERE clause.

A small request from my end, If you like this opinion and your problem is resolved after reviewing and applying it. Please kindly mark this your best answer‌🌠‌ OR mark it Helpful ‌⛑‌ if you think that you get some insight from this content relevant to your problem and help me to contribute more to this community

View solution in original post

keshav77 · ‎07-07-2025

@Jaspal Singh do you have any suggestion on this