Get a first look at what's coming. The Developer Passport Australia Release Preview kicks off March 12. Dive in! 

CMDB Query Builder Join two queries

Pablo Sanz
Tera Guru

3 weeks ago

I have limited experience using CMDB Query Builder, and I was assigned to create the following query. I would greatly appreciate your assistance.

 

Requirement

  1. Retrieve all Service Instances (cmdb_ci_service_auto) that are related to Linux and Windows Servers.

  2. Also include Service Instances that do not have any relationship with Servers.

  3. Additionally, the main query should include Servers that do NOT have any relationship with a Service Instance, so that we can obtain the complete list of Servers (both related and not related).

Expected Result

The query should allow us to see the following scenarios:

  • Service Instance 1 → Server 1

  • Service Instance 2 → (No related Server)

  • (No related Service Instance) → Server 2

In summary, the goal is to retrieve:

  • All Service Instances (with and without relationships).

  • All Servers (with and without relationships).

Currently, I have separate queries: 

PabloSanz_0-1771561955159.png

 

PabloSanz_1-1771562027533.png

 

 

Any guidance on how to structure this in Query Builder would be greatly appreciated.

0 Helpfuls
  • 395 Views
1 REPLY 1

Prathmeshda
Tera Guru

3 weeks ago

Hello @Pablo Sanz 
Query 1: All Service Instances and their Related Servers (Left Outer Join Equivalent)

This query will begin with Service Instances and may include related Servers. This will cover the following cases: Service Instance 1 → Server 1 and Service Instance 2 → (No related Server).
Steps to Build Query 1:
Add Service Instance Class: Drag the Service Instance class (cmdb_ci_service_auto) onto the canvas. This will be your starting point.
Add Server Class: Drag the Server class (cmdb_ci_server) onto the canvas.
Add Relationship: Draw a connection line from Service Instance to Server. The system will automatically provide suggestions for common relationships. Select the best relationship type (e.g., Uses::Used by, Runs on::Runs).
Set Connection Properties for Optional Relationship:•Select the connection line from Service Instance to Server. •In the Connection Properties panel (usually on the right), find the Relationship Type section.

Filter Servers for Linux and Windows:
•Select the Server node (cmdb_ci_server) on the canvas.
•In the Conditions tab of the Properties panel, add a condition to filter for Linux and Windows servers. For example: •Operating System contains .OR •Operating System holds Windows
Select Display Columns: Select the columns from both Service Instance and Server that you want to display in your results.
Save and Run: Save your query with a descriptive name (such as "Service Instances") and execute it. Your results will list all Service Instances, with server information if a relationship exists, and blank server fields if no relationship can be determined.

Query 2: All Servers and their Related Service Instances (Right Outer Join Equivalent)

This query will start with Servers and optionally include related Service Instances. This addresses the scenarios: Service Instance 1 → Server 1 and (No related Service Instance) → Server 2.

Steps to Build Query 2:

  1. Filter Servers for Linux and Windows:
  2. Click on the Server node (cmdb_ci_server).
  3. In the Conditions tab, add the filter:
  4. Operating System contains Linux •OR •Operating System contains Windows
  5. Add Service Instance Class: Drag the Service Instance class (cmdb_ci_service_auto) onto the canvas.
  6. Establish Relationship: Draw a connection line from Server to Service Instance. Select the most appropriate relationship type (e.g., Used by::Uses, Runs on::Runs).
  7. Configure Connection Properties for Optional Relationship:
  8. Click on the connection line between Server and Service Instance.
  9. In the Connection Properties panel, check the Optional checkbox. This makes it a left outer join from the perspective of the Server (effectively a right outer join when considering the Service Instance).
  10. Select Display Columns: Choose the relevant columns from both Server and Service Instance.
  11. Save and Run: Save this query (e.g., "Servers with Optional Service Instances") and run it. The results will show all Linux and Windows Servers, with Service Instance details where a relationship exists, and empty Service Instance fields where no relationship is found.

Result 

  1. •Query 1 results will show all Service Instances, including those without associated servers, and their linked servers
  2. •Query 2 results will show all Linux and Windows Servers, including those without associated Service Instances, and their linked Service Instances.

By reviewing both result sets, you can identify all three scenarios:

•Service Instance 1 → Server 1: Will appear in both queries.

•Service Instance 2 → (No related Server): Will appear in Query 1 with null values for server fields.

•(No related Service Instance) → Server 2: Will appear in Query 2 with null values for service instance fields.

Although the CMDB Query Builder does not combine these into a single table with nulls on both sides in one row, these two queries contain all the information points needed to comprehend the entire relationship environment between Service Instances and Servers, meeting the essence of the full outer join requirement within the capabilities of the Query Builder.

If this response proves useful, please mark it as Accept as Solution and Helpful.

0 Helpfuls