Skip to main content

Query for Relationships in BQL

This article describes how to query your data using the relationships between two data models and how to execute a query with multiple relationships.

You can use BQL to query for relationships between two data models or among multiple data models. For more information on relationships between data models see the data models article.

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

Example I​

In the first example, consider three data models: A, B, and C, where A is related to B and A is related to C, but B is not related to C. You can use BQL to return A that is related to both B and C.

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

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

Example II​

Following the same scenario in example I, 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 Vulnerability Management 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.

Example III​

The third example again uses three data models, A, B, and C, where A is related to B, and B is related to C, but A is not related to C. 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.

The following 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

Example IV​

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:

NOT limitation diagram

Figure 1. 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.

Last updated on