J1QL: Basic Keywords

In this article, we'll cover the basic commands of J1QL, a query language used for data exploration and retrieval in JupiterOne. By understanding these fundamental commands, you'll gain the necessary skills to query specific entities, filter data, and sort information effectively. Whether you're a beginner or have prior experience in programming languages, this guide will equip you with the essential knowledge to navigate and extract valuable insights from your data using J1QL.

note

J1QL keywords are not case-sensitive

FIND

FIND simplifies the search process for assets or entities by allowing users to specify either the _class or _type value. By making the value case-sensitive, the command automatically determines whether the search should be based on class or type, eliminating the need for explicit input.

An asset class is stored in TitleCase while _type is stored in snake_case. A wildcard * can be used to find any asset. For example:

• FIND User is equivalent to FIND * WITH _class='User'
• FIND aws_iam_user is equivalent to FIND * WITH _type='aws_iam_user'

WITH

WITH offers valuable filtering capabilities by allowing users to specify property names and values to narrow down asset results. WITH is followed by property names and values to filter entities. The WITH command empowers users to precisely filter and retrieve the desired entities, enhancing the efficiency and accuracy of data exploration and retrieval in J1QL.

info

For a list of all compatible comparison operators, see our dedicated guide covering J1QL comparisons.

There are several considerations to note when working with WITH:

• The property names are always case-sensitive, property values are also case-sensitive except when using regex in case insensitive mode.
• String values must be wrapped in either single or double quotes - "value" or 'value'.
• Boolean, Number, and Date values must not be wrapped in quotes.
• The undefined keyword can be used to filter on the absence of a property. For example: FIND DataStore WITH encrypted=undefined.
• If a property name contains special characters (e.g. - or :), you can wrap the property name in []. For example: [tag.special-name]='something'.

AND / OR

Using AND / OR in J1QL provides powerful capabilities for comparing multiple properties in asset filtering. By supporting logical operators like AND and OR, users can construct complex property comparisons to precisely retrieve entities that meet specific criteria. J1QL's shorthand filtering further enhances efficiency by allowing filtering of a single property against multiple values, similar to the IN clause in SQL. With a clear order of operations and intuitive syntax, AND / OR enables advanced filtering operations and extracts targeted data with ease and precision.

Here are some examples illustrating how to utilize AND and OR within a query:

AND example

FIND DataStore WITH encrypted = false AND (tag.Production = true AND classification = 'critical')

OR example

FIND user_endpoint WITH platform = 'darwin' OR platform = 'linux'

Additionally, you can filter multiple property values like this (similar to IN in SQL):

FIND user_endpoint WITH platform = ('darwin' OR 'linux')

FIND Host WITH tag.Environment = ('A' or 'B' or 'C')

FIND DataStore WITH classification != ('critical' or 'restricted')

note

Note that property filters are evaluated according to the following order of operations: Parenthesis first, comparisons (=, >=, <=, !=) after, AND, and then OR.

Shorthand filtering

Filtering multiple property values is often called "shorthand" filtering because it allows you to filter a single property by multiple values. Below is a table to help illustrate how "shorthand" filters are evaluated:

_type	_type = "fruit"	_type = "nut-filled"	_type = ("fruit" AND "nut-filled")	_type = ("fruit" OR "nut-filled")
"fruit"	true	false	false	true
"nut-filled"	false	true	false	true
"fruit", "nut-filled"	true	true	true	true
"non-fruit"	false	false	false	false
"non-fruit", "plain"	false	false	false	false
undefined	false	false	false	false

When using a negated "shorthand" filter, such as with the != comparison, you can expect J1QL to evaluate values in the following manner:

_type	_type != "fruit"	type != "nut-filled"	_type != ("fruit" AND "nut-filled")	_type != ("fruit" OR "nut-filled")
"fruit"	false	true	true	false
"nut-filled"	true	false	true	false
"fruit", "nut-filled"	false	false	false	false
"non-fruit"	true	true	true	true
"non-fruit", "plain"	true	true	true	true
undefined	true	true	true	true

THAT

The THAT command in J1QL provides a versatile way to specify relationship verbs and filter entities based on their connected relationships. By allowing users to define relationship verbs in ALLCAPS, J1QL simplifies the process of finding relationships between different asset nodes. This enables mixing asset class and type values together, and bidirectional relationship verbs are supported by default, ensuring flexibility in querying relationships. Additionally, J1QL provides options to specify relationship direction using double arrows (<< or >>) and allows negation of relationships for querying entities without specific relationships. The inclusion of the AS command allows users to define aliased selectors for convenient use in WHERE or RETURN sections of queries.

note

Using the wildcard at the beginning of the query without any pre-traversal filtering–that is, FIND * THAT ... without WITH may result in a long query execution time.

Example query using THAT:

FIND Service
  THAT RELATES TO Account

You can use ( | ) when performing a THAT query to select entities or relationships of different classes and types. For example, FIND (Host|Device) WITH ipAddress='XX.XX.X.XX' is equivalent to (and much simpler) than the following:

FIND * WITH (_class='Host' OR _class='Device') AND ipAddress='XX.XX.X.XX'

THAT asset class types and relationships

Asset class and type values can be used together while using THAT:

FIND (Database|aws_s3_bucket)

It can also be used on Relationship verbs:

FIND HostAgent
  THAT (MONITORS|PROTECTS) Host

And even allows both Assets and Relationships:

FIND *
  THAT (ALLOWS|PERMITS) (Internet|Everyone)

Relationship verbs

By default Relationship verbs are bi-directional. For example, the following queries would yield the same results:

FIND User
  THAT HAS Device

FIND Device
  THAT HAS User

Relationship direction operators

Aside from operating bi-directionally, a Relationship’s direction can be specified using double arrows after the verb:

Finds assets with a HAS relationship from User to Device:

FIND User
  THAT HAS >> Device

FIND Device
  THAT HAS << User

Finds assets with a HAS relationship from Device to User:

FIND User
  THAT HAS << Device

FIND Device
  THAT HAS >> User

In the above examples, each query can leverage a directional relationship based on the desired target (i.e., User or Device).

Negating relationships

You can also determine if an asset does not have a relationship with another by negating the relationship. This can be achieved by prefixing a relationship with an exclamation point (!).

1. Example of using a negation:

FIND User
  THAT !IS Person

2. Applying a negation to relationships:

FIND User
  THAT !RELATES TO Person

danger

It is not valid to alias the result of a negated relationship. Whilst this may produce results the behaviour is undefined.

The following will produce undefined behaviour:

FIND User
  THAT !RELATES TO Person AS p
RETURN p

Negating a relationship use case

This finds EBS volumes that are not in use. The query finds relationships regardless of the edge direction, therefore the !USES in the below query translates more directly as "is not used by".

FIND aws_ebs_volume
  THAT !USES aws_instance

It is important to note that the above query returns aws_ebs_volume entities. If the query were constructed the other way around, it would return a list of aws_instances, if it does not have an EBS volume attached:

FIND aws_instance
  THAT !USES aws_ebs_volume

AS

The AS command in J1QL allows users to define aliased selectors, providing flexibility in specifying assets or relationships to be used in the WHERE or RETURN sections of a query. By using aliases, you can assign meaningful names to selected entities or relationships, enhancing query readability and making it easier to reference them later in the query.

For example:

Without selectors

FIND Firewall
  THAT ALLOWS *

With selectors

FIND Firewall AS fw
  THAT ALLOWS * AS n

Additionally, selectors can also be defined on a relationship:

FIND Firewall AS fw
  THAT ALLOWS AS rule * AS n

WHERE

Using WHERE facilitates post-traversal filtering by allowing you to apply additional conditions to narrow down the results of a query. By leveraging the selector defined in the query, you can specify filtering criteria based on various properties or relationships. WHERE allows for ease of refinement of queries to retrieve only the results that meet specific conditions, enabling precise data extraction and analysis.

For example:

FIND Firewall AS fw
  THAT ALLOWS AS rule * AS n
WHERE rule.ingress=true AND (rule.fromPort=22 OR rule.toPort=22)

RETURN

RETURN allows you to specify the assets, relationships, or properties you want to retrieve from the query results. By using the RETURN clause, you can selectively choose the desired information to be included in the output, enhancing data relevance and reducing unnecessary data retrieval. The RETURN command provides flexibility in querying and enables targeting specific assets or properties of interest, optimizing data analysis and facilitating efficient information extraction.

By default, the assets and their properties found from the start of the traversal are returned in a query. For example, Find User that IS Personreturns all matching User assets and their properties, but not the related Person entities. To return properties from both the User and Person entities, define a selector for each and use them in the RETURN clause like so:

Defining a selector

FIND User AS u
  THAT IS Person AS p
RETURN u.username, p.firstName, p.lastName, p.email

This means that a Person having multiple IS relationships to User entities will have their p.firstName, p.lastName, and p.email values returned in each path through the graph that leads to that User:

User with multple paths

| u.username | p.firstName | p.lastName | p.email                        |
|------------|-------------|------------|--------------------------------|
| spiderman  | Peter       | Parker     | not.spiderman@example.com      |
| batman     | Bruce       | Wayne      | totally.not.batman@example.com |
| bwayne     | Bruce       | Wayne      | totally.not.batman@example.com |

note

When the RETURN statement is used with specified properties, the J1QL engine will pick out the properties from each path traversed. Sometimes, the path to the end of the query can fork since there are multiple ways assets can relate to each other. This means that a Person having multiple IS relationships to User entities will have their p.firstName, p.lastName, and p.email values returned in each path through the graph that leads to a User.

Using RETURN with special characters

If a property name contains special characters (e.g. - or :), you can wrap the property name in [].

FIND User AS u
  THAT IS Person AS p
RETURN u.username, p.firstName, p.lastName, p.email, p.[special-name]

Results from the query will contain the relevant p.[special-name] entities per User:

| u.username | p.firstName | p.lastName | p.email                        | p.special-name |
|------------|-------------|------------|--------------------------------|----------------|
| spiderman  | Peter       | Parker     | not.spiderman@example.com      | Spiderman      |
| batman     | Bruce       | Wayne      | totally.not.batman@example.com | Batman         |
| bwayne     | Bruce       | Wayne      | totally.not.batman@example.com | Batman         |

Using RETURN with a wildcard

Wildcard can be used to return all properties for multiple assets in a flattened table. For example:

RETURN query with wildcard

FIND User AS u
  THAT IS Person AS p
RETURN u.*, p.*

Using a wildcard to return all properties also returns all metadata properties associated with the selected assets. This feature is useful when you want to perform an analysis that involves metadata.

Asset and Relationship reference

Using the RETURN clause, the J1QL query engine returns back only the requested information.

Results would typically be returned like this via the API:

{
  "type": "table",
  "data": [
    { "User._type": "jupiterone_user", "Person.name": "Mochi" }
  ]
}

When executing queries via the application, additional metadata is returned back for each row with references to the assets and relationships traversed via the paths. This resides under a _meta property that is attached to each row in the query:

{
  "type": "table",
  "data": [
    {
      "User._type": "jupiterone_user",
      "Person.name": "Mochi",
      "_meta": {
        "byAlias": {
          "User": {
            "id": "f4b7cfbb-8532-dbcb-b244-e2864423fccd",
            "entity": {
              "_id": "4147b2bc-3b65-42a8-be50-164a45c4864d"
            }
          },
          "Person": {
            "id": "952f0d8c-19dc-4dde-a3bf-e6ce0cda85a7",
            "entity": {
              "_id": "cdbceb28-e066-4006-9456-225ddb358d16"
            }
          }
        }
      }
    }
  ]
}

note

Note that when row metadata is requested via API, usage of the UNIQUE keyword or aggregations will cause the _meta property to be stripped from rows.

TO

The TO command in J1QL provides a natural language-like syntax for expressing relationships between entities. By using the TO keyword after a relationship verb, you can create queries that closely resemble human language, enhancing query readability and ease of use. While TO is considered a "filler" word that is ignored by the interpreter, its inclusion allows you to construct queries that align with their natural language understanding, making the query-writing process more intuitive.

The following are some example relationship verbs where TO could be used:

• DEPLOYED TO
• CONTRIBUTES TO
• CONNECTS TO
• ASSIGNED TO

The below queries are executed the same way and return the same results:

TO query comparison

FIND User
  THAT CONTRIBUTES TO CodeRepo

FIND User
  THAT CONTRIBUTES CoreRepo

Commenting

J1QL supports comenting in queries anywhere in JupiterOne using the following format: /* {insert comment here} */

This is useful for annotating queries across JupiterOne users in order to communicate intention on variables, findings, and other contextual information related to the query itself.

Using comments in a query

FIND aws_security_group WITH displayName ~='elb' /*ELB Security Group*/
  OR displayName ~='lambda' /*Lambda Security Group*/

Conclusion

In conclusion, understanding the basic J1QL commands is crucial for effective querying in JupiterOne's graph database. By mastering these common J1QL keywords, you can extract valuable insights and perform data analysis within the platform. This proficiency empowers users to uncover connections and make informed decisions in security and compliance efforts.

To build further proficiency in J1QL, continue on to learn more about J1QL’s filtering behavior and understand how to curate your query results by applying filtering.