Skip to main content

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.

note

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:

  1. FIND Vulnerability AS v RETURN:

    • Retrieves all Vulnerability records using the alias v.
  2. CASE

    • Uses a CASE block to group records by compliance status.
  3. WHEN v.complianceStatus IN ["Met SLA", "Within SLA"] THEN "Compliant"

    • WHEN checks if v.complianceStatus is either "Met SLA" or "Within SLA" and returns "Compliant" if true.
  4. WHEN v.complianceStatus IN ["Out of SLA", "Exceeded SLA"] THEN "Not Compliant"

    • WHEN checks if v.complianceStatus is either "Out of SLA" or "Exceeded SLA" and returns "Not Compliant" if true.
  5. ELSE "No Value"

    • ELSE defines the default result and returns "No Value" if none of the WHEN conditions are met.
  6. END AS "Compliance Status"

    • END marks the conclusion of the CASE block and assigns the alias "Compliance Status" to the result.
  7. 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."

BQL CASE statement example