How to Create a Full Outer Join in CMDB Query Builder for Service Instances and Servers

Prathmeshda
Giga Guru

yesterday

This is a step-by-step guide on how to construct a CMDB Query Builder query in ServiceNow to retrieve all Service Instances (cmdb_ci_service_auto) and Servers (Linux and Windows), regardless of whether they are directly related.

Although the CMDB Query Builder does not have a full outer join query similar to those used in SQL databases, it is possible to replicate the same functionality using the “Optional” relationship type. This is if the structure is properly set up to allow you to see all related and unrelated data in your CMDB.

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

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

Steps to Build Query 1:

  1. Navigate to CMDB Query Builder: In your ServiceNow instance, type CMDB Query Builder in the navigation filter and open it.

  2. Create a New Query: Click New to start a fresh query.

  3. Add Service Instance Class: Drag the Service Instance class (cmdb_ci_service_auto) onto the canvas. This will be your starting point.

  4. Add Server Class: Drag the Server class (cmdb_ci_server) onto the canvas.

  5. Establish Relationship: Draw a connection line from Service Instance to Server. The system will automatically suggest common relationships. Select the most appropriate relationship type (e.g., Uses::Used by, Runs on::Runs).

  6. Configure Connection Properties for Optional Relationship:
    • Click on the connection line between Service Instance and Server.
    • In the Connection Properties panel (usually on the right), locate the Relationship Type section.
    • Crucially, check the Optional checkbox for this relationship. This makes it a left outer join from the perspective of the Service Instance.

  7. Filter Servers for Linux and Windows:
    • Click on 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 Linux OR Operating System contains Windows.

  8. Select Display Columns: Choose the relevant columns from both Service Instance and Server that you wish to see in the results.

  9. Save and Run: Save your query with a descriptive name (e.g., "Service Instances with Optional Servers") and run it. The results will show all Service Instances, with server details where a relationship exists, and empty server fields where no relationship is found.

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. Create a New Query: Start another new query in CMDB Query Builder.

  2. Add Server Class: Drag the Server class (cmdb_ci_server) onto the canvas. This will be your starting point.

  3. Filter Servers for Linux and Windows:  Click on the Server node (cmdb_ci_server).
    • In the Conditions tab, add the filter: Operating System contains Linux OR Operating System contains Windows.

  4. Add Service Instance Class: Drag the Service Instance class (cmdb_ci_service_auto) onto the canvas.

  5. 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).

  6. Configure Connection Properties for Optional Relationship:
    • Click on the connection line between Server and Service Instance.
    • 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).

  7. Select Display Columns: Choose the relevant columns from both Server and Service Instance.

  8. 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.

Although the CMDB Query Builder does not combine these into a single table with nulls on both sides in one row, these two queries do provide all the necessary data points to understand the complete relationship landscape between Service Instances and Servers, meeting the essence of a full outer join requirement within the capabilities of the Query Builder.

If you found this article useful, please mark it as Helpful. It helps others find the content more easily 👍🙂

0 Helpfuls
  • 54 Views
0 REPLIES 0