Skip to main content

Scalar functions

The ability to format and/or perform calculations on row-level columns can be accomplished through Scalar Functions. These scalar functions enhance the versatility and convenience of J1QL by providing powerful tools for transforming and combining data in queries.

CONCAT​

The scalar function CONCAT() empowers users to concatenate or join one or more values into a single string. Currently, CONCAT can be used in the RETURN to the clause of your function, will future development is planned for use in the WHERE clause.

If this function receives a number or boolean value, the CONCAT intuitively converts these values to strings. Additionally, if CONCAT processes an empty selector field, it evaluates that field as an empty string.

CONCAT supports the following parameters, separated by commas:

  • Selector Fields: selector.field
  • String values: 'your string' or "your string"
  • Number values: 123
  • Boolean values: true
Example using CONCAT
FIND
  aws_s3_bucket as s3
RETURN
  CONCAT(s3.bucketSizeBytes, ' bytes') as size

MERGE​

The scalar function MERGE() allows you to merge multiple properties into a single property list. You can now combine multiple (defined) properties into a single property without having to choose to return one property or another.

Example using MERGE
FIND UNIQUE User AS u
THAT IS Person AS p
RETURN p.displayName, merge(p.email, u.email, u.publicEmail) AS "Email List"

DATETIME​

The scalar function DATETIME() allows you to format timestamp values into human-readable date and time strings using custom format patterns. This function requires two arguments: a timestamp field and a formatting string.

note

The timestamp field must be a defined property on the entity. If the timestamp value is undefined when passed to the DATETIME() function, the function will not work properly and may throw an error or return unexpected results.

The DATETIME function accepts:

  • Timestamp field: A property containing an epoch timestamp in milliseconds
  • Format string: A string specifying the desired date/time format pattern (required)
Example using DATETIME
FIND DataStore AS ds
RETURN
  ds.displayName,
  DATETIME(ds._createdOn, "yyyy-MM-dd HH:mm:ss.SSSSZ") AS "Created Date",
  DATETIME(ds._beginOn, "hh:mm") AS "Time Modified"

You can also combine DATETIME with other scalar functions:

Combining DATETIME with CONCAT
FIND * AS s
RETURN CONCAT("Created at ", DATETIME(s._createdOn, "hh:mm")) AS "Creation Time"
LIMIT 10

For more information about the format string patterns, see the Java DateTimeFormatter documentation.

J1.RISKSCORE​

The scalar function j1.riskScore() returns the UVM risk score of one vulnerability on one device, computed with your account's Risk configuration. Use it to filter, sort, and aggregate on risk score without writing the scoring formula yourself.

j1.riskScore takes exactly two arguments, in this order:

  • The alias of a UnifiedVulnerability
  • The alias of a UnifiedDevice

Each alias must select exactly that class. Passing the aliases in the other order, an alias of another class, or * returns a validation error.

The score is on a 0–100 scale and is not rounded. It matches the risk score in the entity inspector. The Prioritized table shows the same score on a 0–10 scale, so a score of 39.16 appears there as 3.9.

Example using J1.RISKSCORE
FIND UnifiedVulnerability AS uv
  THAT IS Vulnerability
  THAT HAS (Host|Device)
  THAT IS UnifiedDevice AS ud
RETURN uv.cveId, ud.displayName, j1.riskScore(uv, ud) AS score

You can use j1.riskScore in RETURN, WHERE, ORDER BY, and HAVING, inside aggregate functions, and in math expressions:

Risk burden per device
FIND UnifiedVulnerability AS uv
  THAT IS Vulnerability
  THAT HAS (Host|Device)
  THAT IS UnifiedDevice AS ud
RETURN
  ud.displayName,
  SUM(j1.riskScore(uv, ud)) AS burden,
  ROUND(AVG(j1.riskScore(uv, ud))) / 10 AS "Average (0–10)"
note

SUM, AVG, COUNT, and COLLECT over j1.riskScore count each vulnerability and device pair once, even when the query reaches the pair through several paths, such as two scanners reporting the same CVE on one device. SUM and AVG over other values run once per matched path.

When an optional traversal finds no device for a vulnerability, j1.riskScore returns null for that row.

Contents