How to join 2 tables thru Query Builder and show all records with or without relationship

[Daisy Licup]

Hello Experts,

Need help from you. I am creating report thru CMDB Query Builder. I am connecting 2 CMDB tables but not all records are showing. For example, I want to show all records in Table A "with" or "without: mapping/relationship with Table B.

When I select "Any relationship" from query builder, the result will only show rows 1 and 3.

But my goal is to shall all 3 records.

Sample Records

#	TableA.Field1	TableB.Field1
1	Value ABC	A123
2	Value XYZ
3	Value XXX	B987

Appreciate your expert advise on this. Thank you.

[Anshu_Anand_]

Have you tried exact filters on cmdb_rel_ci table to see the records.

"Any relationship" will show all the combination relationship of TableA.Field1 X TableB.Field1 defined in relationship type table.

When you link two tables via "any relationship", it shows all the records available on cmdb_rel_ci table.

The number of records between 2 CIs on cmdb_rel_ci == The number of records between 2 CIs on query builder "any relationship"

If its helpful, please mark answer as correct

[Daisy Licup]

hi Anshu. I tried to extract records in cmdb_rel_ci for Windows Servers. But the 2 servers I created which has no relationship to other tables doesn't show. My goal is to show all  existing servers "with or without" relationship to other tables. Refer attached screenshots of my DEV instance.

Kindly advise how to attain this? Thank you.

[Harshal Gawali]

Hi @Daisy Licup ,

You can combine these two tables by using database view. Database view is used for joining two or more tables for reporting purpose.

Both are cmdb tables & they are inherited from the "cmdb_ci" table. so you can create record in database view. 

Navigate to System definition -> Database view

You can open any record for your reference then create new record for combining two cmdb tables.

Regards,

Harshal.

[Daisy Licup]

Hi @Harshal Gawali 

Thanks for your response. But I believe that requires admin role which I don't have. The highest role I have is report_admin only. Is there any alternative in joining 2 tables in reporting or query builder?