How can I report on data from multiple tables?

[ServiceNowSteve]

Good Afternoon Everyone,

I have a request to build a report and I am having a hard time figuring out how to accomplish it.

The report needs to show any company in our CMDB that has an entry for a Server as well as a WAN Circuit.

My tables are cmdb_ci_server and u_cmdb_ci_wan_circuits

I tried to do a database view but I guess I don't understand how to construct it.

Can anybody think of a way to show any company that has BOTH entries for at least one server and at least one WAN circuit while filtering out any companies that don't have both?

[Allen Andreas]

Hi,

If you can review both of those tables and see that they're extended from the same table, then you can adjust your report at that higher level table. This will then allow you to report on both. Check the dictionary record for both of these tables and see the "extends" field to see if you can do this (I suspect so). Your custom table may be extended from server already?

Please mark reply as Helpful/Correct, if applicable. Thanks!

[ServiceNowSteve]

They both extend the cmdb_ci table, technically the server table extends Computer which extends Hardware which extends the cmdb_ci table

There is a complication doing it based on the cmdb_ci table but I don't want to muddy the water here so let's assume I use the base CMDB_CI table.

How could I report on only items with both because what I am trying now is

Class = Server OR Class = WAN Circuit which returns any company with either one of those entries.

If I try to use AND I will get nothing because you can't have a class that's both.

[Thomas_Davis]

You may be able to leverage CMDB Query Builder to accomplish this.  Here is a Performance Analytics Academy session that discusses it.

Intro into CMDB Query Builder with Reporting

There is also a Now Learning K21 CreatorCon session that discussed it as well.

Leveraging CMDB Performance Analytics with CMDB Query Builder

Hope this helps,