Data base view creation between api and api_component table

[keshav77]

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]

HI @keshav77 

To establish the parent-child relationship between cmdb_ci_api and cmdb_ci_api_component using the cmdb_rel_ci table, your WHERE clauses need to correctly link the parent, child, and relationship records.

Here's how you should set up your database view with the correct WHERE clauses:

Let's assume your aliases are:

cmdb_ci_api as api
cmdb_ci_api_component as com
cmdb_rel_ci as rel
Your WHERE clauses should be:

For cmdb_rel_ci joining cmdb_ci_api (Parent):

Table: cmdb_rel_ci
Where Clause: rel.parent = api.sys_id
Explanation: This links the sys_id of the API (your parent CI) to the parent field in the relationship table.
For cmdb_rel_ci joining cmdb_ci_api_component (Child):

Table: cmdb_rel_ci
Where Clause: rel.child = com.sys_id
Explanation: This links the sys_id of the API component (your child CI) to the child field in the relationship table.
Optional but Recommended:

To specify the relationship type (if you need a specific kind of parent-child link):

Table: cmdb_rel_ci
Where Clause: rel.type.name = 'Contains' (or another appropriate relationship type like 'Used by', 'Runs on', etc.)
Explanation: Relationships in ServiceNow have types (e.g., "Contains", "Depends on", "Used by"). Filtering by rel.type.name ensures you only get the specific parent-child relationships you're interested in. You might need to check your instance for the exact relationship type name that defines an API containing its components.
Summary of the correct WHERE clauses:

cmdb_rel_ci:

rel.parent = api.sys_id
rel.child = com.sys_id
(Optional) rel.type.name = 'Contains' (or the relevant relationship type)
Your current clause com.sys_id=api.sys_id is incorrect for establishing a parent-child relationship via cmdb_rel_ci, as it tries to directly match the sys_id of the component with the sys_id of the API, which are distinct CIs. The relationship table (cmdb_rel_ci) is precisely for linking these distinct CIs.

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

[keshav77]

@MackI  so you are saying there is not required to use where clause on cmdb_api and cmdb_api_component  table only on  cmbd_rel_ci is just required 

[keshav77]

I have created this but this not giving any data to me @MackI 

[MackI · Accepted solution]

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

[keshav77]

@MackI all three tables are showing valid data in it. but database view table is coming as empty.