Skip to main content

Comparisons

J1QL’s comparison capabilities accommodate effective filtering and analysis of data. String comparisons offer operators for evaluating specific substrings, prefixes, and suffixes—allowing precise filtering of string values. Regular expressions (regex) can also be applied to properties for more advanced filtering including case-insensitive matching. Date comparisons provide flexibility for filtering based on relative or static timestamps, empowering you to perform time-based analysis and gain valuable insights from your data.

String comparisons​

J1QL supports the use of the following operators for comparisons of strings stored either as a single string or multi-value field. In addition to = (equals) and != (does not equal):

  • ~= : Contains

  • ^= : Starts with

  • $= : Ends with

  • !~= : Does not contain

  • !^= : Does not start with

  • !$= : Does not end with

note

These operators only work for comparisons of strings or multi-value fields.

Example queries using basic string comparisons​

  1. The following query returns all entities of the Person class that have a firstName beginning with the character 'J':

    FIND Person WITH firstName ^= "J"

  2. This query returns all assets of the Person class that have a Person.email of 'a@b.com' or ['a@b.com', 'x@y.com']:

    FIND Person WITH email = "a@b.com"

  3. The following query checks if a substring matches for either a single string or a multi-value field:

    FIND Person WITH email ~= ".com"

  4. Lastly, this query returns entities of the Host class with any of the following examples of tag.AccountName: xyz_demo, demo_xyz, abc_demo_xyz:

    FIND Host WITH tag.AccountName ~= "demo"
note

These string evaluations are case-sensitive. So 'Demo' and 'demo' yield distinct sets of results.

Example queries using regex comparisons​

  1. The following query returns all entities of the Person class that have a firstName containing the case-insensitive string 'james':

    FIND Person WITH firstName = /james/i

  2. This query returns all assets of the Host class that have a tag classification with a value of Highly Sensitive or similar permutations such as highly-sensitive or highlysensitive:

    FIND Host WITH tag.classification = /highly[ \-]?sensitive/i
note

Due to limitations in some of the data stores the regex search options are limited. For more informaiton please see REGEX Implementation.

Date comparisons​

The query language also supports both relative and static date comparisons on any timestamp property. The timestamp property used for date comparison must be stored as an epoch number in milliseconds.

To verify that the date value is stored in JupiterOne correctly, use the following query as an example way to find out:

Verifying the date value
FIND {Entity class or type} AS x 
RETURN x.createdOn, x.createdOn AS storedValue

The results for this query should contain the following:

  1. The first column(x.createdOn) shows the datetime value formatted for the UI. If the value is returned following this syntax: yyyy-mm-ddThh:mm:ss.sssZ, then we know that the stored value is an epoch number.

  2. The second column (storedValue) will show the value as stored in the database. This value should be returned as a 13-digit number.

If either of the above conditions is not true, then the values uploaded to the JupiterOne platform should be updated to include the epoch number prior to executing date comparison queries.

Relative date comparisons​

Relative date comparison allows filtering based on the current datetime. For example:

Relative date comparison
FIND DataStore WITH createdOn > DATE.now - 1 day

The following units are supported for relative date comparisons in J1QL:

  • hour, hr, hours, hrs
  • day, days
  • month, mo, months, mos
  • year, yr, years, yrs

Static date comparisons​

In contrast to relative date comparisons, static date comparison allow to filter based on a specified datetime. For example:

Static date comparison
FIND DataStore WITH createdOn > DATE(2019-10-30)

The static date must be specified in ISO 8601 format:

  • DATE(YYYY)
  • DATE(YYYY-MM)
  • DATE(YYYY-MM-DD)
  • DATE(YYYY-MM-DDThhTZD)
  • DATE(YYYY-MM-DDThh:mmTZD)
  • DATE(YYYY-MM-DDThh:mm:ssTZD)
  • DATE(YYYY-MM-DDThh:mm:ss.sTZD)

Contents