CASE Statements
CASE statements in Brinqa Query Language (BQL) evaluate conditions in order and return a result based on the first condition that matches. If no conditions match, the ELSE
clause defines the default result.
CASE
in BQL follows the same logic as SQL and Neo4j. For reference, see the SQL CASE documentation and Neo4j CASE documentation.
CASE
statements can only be used with the RETURN
clause. This limits their use to reports, automations, and Explorer. They are ignored in List views for assets, findings, or tickets.
The following query uses a CASE
statement to categorize vulnerabilities based on their complianceStatus
and counts how many fall into each category:
FIND Vulnerability AS v
RETURN
CASE
WHEN v.complianceStatus IN ["Met SLA", "Within SLA"] THEN "Compliant"
WHEN v.complianceStatus IN ["Out of SLA", "Exceeded SLA"] THEN "Not Compliant"
ELSE "No Value"
END AS "Compliance Status",
count(*) AS Count
Let's break down the components of the above query to better understand how the CASE
statement is constructed and interpret the results:
-
FIND Vulnerability AS v RETURN
:- Retrieves all
Vulnerability
records using the aliasv
.
- Retrieves all
-
CASE
- Uses a
CASE
block to group records by compliance status.
- Uses a
-
WHEN v.complianceStatus IN ["Met SLA", "Within SLA"] THEN "Compliant"
WHEN
checks ifv.complianceStatus
is either "Met SLA" or "Within SLA" and returns "Compliant" if true.
-
WHEN v.complianceStatus IN ["Out of SLA", "Exceeded SLA"] THEN "Not Compliant"
WHEN
checks ifv.complianceStatus
is either "Out of SLA" or "Exceeded SLA" and returns "Not Compliant" if true.
-
ELSE "No Value"
ELSE
defines the default result and returns "No Value" if none of theWHEN
conditions are met.
-
END AS "Compliance Status"
END
marks the conclusion of theCASE
block and assigns the alias "Compliance Status" to the result.
-
count(*) AS Count
- Counts the number of matching records and assigns the alias "Count".
The following screenshot illustrates the results of the BQL query in a tabular report section using the advanced mode. Each row represents a compliance status category, showing the total count of vulnerabilities based on the CASE
statement WHEN
conditions, grouped as either "Compliant" or "Not Compliant."