Querying for Relationships

You can use Brinqa Query Language (BQL) to query for relationships between two data models or among multiple data models. For information on how to find out the relationship between data models, see the data models article.

The following examples demonstrate how you can use relationships in your BQL queries.

Shared relationships

In the first example, consider three data models—A, B, and C—where A is related to both B and C, but B and C are not related to each other. In other words, B and C share a common relationship with A. You can use BQL to return A that is related to both B and C.

Figure 1. A diagram illustrating a shared relationship.

In the following query, A is the Host data model, B is the ServiceNow Host data model, and C is the Qualys Vulnerability Management (VM) Host data model. There are hosts running ServiceNow and there are hosts running Qualys VM. (The relationship is SOURCED_FROM.) The query attempts to find hosts that are running both ServiceNow and Qualys VM:

FIND Host AS h
THAT SOURCED_FROM ServicenowHost AS s 
AND h that SOURCED_FROM QualysVmHost AS q 
RETURN h.name, s.name, q.name limit 10

Selective relationships

Following the same scenario in the previous example, you can also query for A that is related to B but not related to C.

The following query returns hosts that are running Qualys VM but not ServiceNow:

FIND Host AS h
THAT SOURCED_FROM QualysVmHost AS q  
AND h that NOT SOURCED_FROM ServicenowHost  
RETURN h.name, q.name

While the first query focuses on the need for both relationships to exist, the second query searches for and retrieves hosts that have the first, but not the second relationship.

Transitive relationships

The third example also involves three data models—A, B, and C—where A is related to B, and B is related to C, but A is not directly related to C. This is often referred to as a transitive relationship.

Figure 2. A diagram illustrating a transitive relationship.

In the following query, A is the Person data model, B is the Host data model, and C is the Finding data model. The Person data model does not have a relationship with the Finding data model. This query illustrates how you can establish a connection between A and C by tethering the relationships from A to B and then from B to C. Specifically, the query returns a person’s last name and the number of findings that are related to this person:

FIND Person AS p
THAT OWNS Host AS h 
AND h THAT HAS Finding AS f 
RETURN p.lastName, count(distinct f) AS Findings

Optional relationships

You can use the OPTIONALLY or OPTIONAL keyword to include optional relationships in your query. This ensures that results from the base data model are still returned—even if the specified relationship or related attributes don’t exist.

note

OPTIONAL and OPTIONALLY can be used interchangeably. Always place OPTIONALLY after THAT and before the relationship verb. For example:
THAT OPTIONALLY HAS, THAT OPTIONALLY IS, THAT OPTIONALLY DEPLOYED_FROM, etc.

Suppose you want to query Host records and include related HostImage names—if available. By using OPTIONALLY, hosts that do not have a related image are still returned:

FIND Host AS h
THAT OPTIONALLY DEPLOYED_FROM HostImage AS hi
WHERE h.name CONTAINS "brinqa"

The above query returns all hosts whose name contains "brinqa" and includes HostImage data when a relationship exists.

Let's break it down further:

• OPTIONALLY ensures that hosts are returned even if they don’t have a related HostImage.

• If a relationship does exist, HostImage data is included.

• If no relationship exists, the host is still returned—just without the related HostImage data.

The following examples and screenshots show the difference between using the OPTIONALLY keyword and omitting it from a query.

The first query uses OPTIONALLY to include all hosts, even those without a related image:

FIND Host AS h
THAT OPTIONALLY DEPLOYED_FROM HostImage AS hi
WHERE h.name CONTAINS "brinqa"

1,892 hosts returned — includes hosts with and without a related host image.

The second query removes OPTIONALLY, so only hosts with a HostImage are included:

FIND Host AS h
THAT DEPLOYED_FROM HostImage AS hi
WHERE h.name CONTAINS "brinqa"

Here, only 62 hosts are retrieved, as the query excludes any host without a related HostImage.

Relationship against multi-valued attributes

The last examples illustrate how to query against multi-valued attributes or one-to-many relationship type attributes. Since multi-valued and one-to-many relationship type attributes are considered lists, you need to use list operators in your queries. For example, the following query returns all assets with specific software installed on them:

FIND InstalledPackage AS ip
THAT INSTALLED_ON Asset AS a 
WHERE ip.displayName CONTAINS ANY ["Microsoft Windows XP", "Microsoft Windows Server 2019"]
RETURN a.name

And this query retrieves all high severity findings from a specific data integration, where f.dataIntegrationTitles is a multi-valued attribute:

FIND Finding AS f
WHERE f.severity = "High" 
AND f.dataIntegrationTitles CONTAINS ANY ["Veracode"]      

When performing queries on multi-valued or one-to-many relationship type attributes, using the NOT keyword may produce unexpected results. In particular, using operators such as NOT CONTAINS ALL, NOT CONTAINS ANY, NOT IN, NOT LIKE, or != can yield more records than anticipated. This is because when a NOT condition is evaluated against a list, BQL applies the 'not equal to' (!=) condition to each element in the list, and returns 'true' if at least one element matches the condition. Let’s demonstrate with an example.

The following diagram represents two hosts, each having a relationship with multiple applications:

Figure 3. Hosts with multiple applications of different risk ratings.

Suppose you want to search for hosts where no application has a High risk rating. You might write a query like this:

FIND Host AS h
THAT SUPPORTS Application AS a 
WHERE a.riskRating != "High"

What's the outcome? You might be surprised to see that not only Host 2 is returned, but Host 1 as well. Based on the BQL logic, when evaluating a.riskRating != "High"on Host 1, the riskRating != "High" condition is checked on each application individually. Since both Application 2 and Application 3 do not have a High risk rating, they each return 'true', resulting in Host 1 being included in the result.