Community
Questions Library
Docs
Blog
Events
Swag
Github
Slack
JupiterOne
Discussions
Release Notes
Contact Us
Introduction to JupiterOne Query Language (J1QL) - AskJ1 Community
<main> <article class="userContent"> <p>The JupiterOne Query Language (J1QL) is a query language for querying data stored by JupiterOne. The execution of a J1QL query seamlessly queries a full text search, entity-relationship graph, and any other future data stores, as needed. By design, the query language does not intend to make these data store boundaries obvious to query authors.</p> <h2 data-id="language-features">Language Features</h2> <ul><li>Seamlessly blend full-text search and graph queries</li> <li>Language keywords are case-insensitive</li> <li>Inspired by SQL and Cypher and aspires to be as close to natural language as possible</li> <li>Support for variable placeholders</li> <li>Support for parameters: replacement variables stored server-side</li> <li>Support for bidirectional traversal</li> <li>Return <strong>entities</strong>, <strong>relationships</strong>, and/or traversal <strong>tree</strong></li> <li>Support for sorting via <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">ORDER BY</code> clause (currently only applies to the starting entities of traversal)</li> <li>Support for pagination via <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">SKIP</code> and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">LIMIT</code> clauses (currently only applies to the starting entities of traversal)</li> <li>Multi-step graph traversals through relationships via <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">THAT</code> clause</li> <li>Specifying relationship direction can be done with double arrows,<code class="code codeInline code codeInline" spellcheck="false" tabindex="0"><<</code> and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">>></code></li> <li>Aliasing of selectors via <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">AS</code> keyword</li> <li>Pre-traversal filtering using property values via <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WITH</code> clause</li> <li>Post-traversal filtering using property values or union comparison via <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WHERE</code> clause</li> <li>Support aggregates including <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">COUNT</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">MIN</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">MAX</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">AVG</code>, and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">SUM</code></li> <li>Row level scalar functions including <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">CONCAT</code></li> </ul><h2 data-id="basic-keywords">Basic Keywords</h2> <h4 data-id="find">FIND</h4> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND</code> is followed by an <strong>Entity</strong> <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">class</code> or <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">type</code> value.</p> <p>The value is case-sensitive to automatically determine if the query needs to search for assets by the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">class</code> or the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">type</code>, without requiring authors to specifically stating it.</p> <p>Entity <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">class</code> is stored in <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">TitleCase</code> while <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">type</code> is stored in <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">snake_case</code>. A wildcard <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">*</code> can be used to find <em>any entity</em>. For example:</p> <ul><li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND User</code> is equivalent to <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND * with _class='User'</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND aws_iam_user</code> is equivalent to <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND * with _type='aws_iam_user'</code></li> </ul><p>Note that using the wildcard at the beginning of the query without any pre-traversal filtering -- that is, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND * THAT ...</code> without <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WITH</code> (see below) -- may result in long query execution time.</p> <h4 data-id="with">WITH</h4> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WITH</code> is followed by <strong>property name and values</strong> to filter entities.</p> <p>Supported operators include:</p> <ul><li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">=</code> or <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">!=</code> for <strong>String</strong> value, <strong>Boolean</strong>, <strong>Number</strong>, or <strong>Date</strong> comparison.</li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">></code> or <code class="code codeInline code codeInline" spellcheck="false" tabindex="0"><</code> for <strong>Number</strong> or <strong>Date</strong> comparison.</li> </ul><p>Note:</p> <ul><li>The property names and values are <em>case sensitive</em>.</li> <li><strong>String</strong> values must be wrapped in either single or double quotes - <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">"value"</code> or <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">'value'</code>.</li> <li><strong>Boolean</strong>, <strong>Number</strong>, and <strong>Date</strong> values must <em>not</em> be wrapped in quotes.</li> <li><p>The <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">undefined</code> keyword can be used to filter on the absence of a property. <br> For example: <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND DataStore with encrypted=undefined</code></p></li> <li><p>If a property name contains special characters (e.g. <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">-</code> or <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">:</code>), you can wrap the property name in <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">[]</code>. For example: <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">[tag.special-name]='something'</code></p></li> </ul><h4 data-id="and-or">AND/OR</h4> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">AND</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">OR</code> for multiple property comparisons are supported.</p> <p>For example:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">FIND DataStore WITH encrypted = false AND (tag.Production = true and classification = 'critical') FIND user_endpoint WITH platform = 'darwin' OR platform = 'linux' </pre> <ul><li>You can filter multiple property values like this (similar to <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">IN</code> in SQL):</li> </ul><pre class="code codeBlock" spellcheck="false" tabindex="0">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') </pre> <ul><li>Property filters are evaluated according the following <strong>order of operations</strong>:</li> </ul><p>Parenthesis first, comparisons (<code class="code codeInline code codeInline" spellcheck="false" tabindex="0">=</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">>=</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0"><=</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">!=</code>) after, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">AND</code> and then <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">OR</code>.</p> <p>Filtering multiple property values is often called "shorthand" filtering, because it allows you to filter a single property by multiple values.</p> <p>Below is a table to help illustrate how "shorthand" filters are evaluated:</p> <table><thead><tr><th><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">_type</code></th> <th align="center"><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">_type = "fruit"</code></th> <th align="center"><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">_type = "nut-filled"</code></th> <th align="center"><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">_type = ("fruit" AND "nut-filled")</code></th> <th align="center"><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">_type = ("fruit" OR "nut-filled")</code></th> </tr></thead><tbody><tr><td>"fruit"</td> <td align="center">true</td> <td align="center">false</td> <td align="center">false</td> <td align="center">true</td> </tr><tr><td>"nut-filled"</td> <td align="center">false</td> <td align="center">true</td> <td align="center">false</td> <td align="center">true</td> </tr><tr><td>"fruit", "nut-filled"</td> <td align="center">true</td> <td align="center">true</td> <td align="center">true</td> <td align="center">true</td> </tr><tr><td>"non-fruit"</td> <td align="center">false</td> <td align="center">false</td> <td align="center">false</td> <td align="center">false</td> </tr><tr><td>"non-fruit", "plain"</td> <td align="center">false</td> <td align="center">false</td> <td align="center">false</td> <td align="center">false</td> </tr><tr><td>undefined</td> <td align="center">false</td> <td align="center">false</td> <td align="center">false</td> <td align="center">false</td> </tr></tbody></table><p>When using a <em>negated</em> "shorthand" filter, such as with the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">!=</code> comparison, you can expect J1QL to evaluate values in the following manner:</p> <table><thead><tr><th><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">_type</code></th> <th align="center"><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">_type != "fruit"</code></th> <th align="center"><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">type != "nut-filled"</code></th> <th align="center"><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">_type != ("fruit" AND "nut-filled")</code></th> <th align="center"><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">_type != ("fruit" OR "nut-filled")</code></th> </tr></thead><tbody><tr><td>"fruit"</td> <td align="center">false</td> <td align="center">true</td> <td align="center">true</td> <td align="center">false</td> </tr><tr><td>"nut-filled"</td> <td align="center">true</td> <td align="center">false</td> <td align="center">true</td> <td align="center">false</td> </tr><tr><td>"fruit", "nut-filled"</td> <td align="center">false</td> <td align="center">false</td> <td align="center">false</td> <td align="center">false</td> </tr><tr><td>"non-fruit"</td> <td align="center">true</td> <td align="center">true</td> <td align="center">true</td> <td align="center">true</td> </tr><tr><td>"non-fruit", "plain"</td> <td align="center">true</td> <td align="center">true</td> <td align="center">true</td> <td align="center">true</td> </tr><tr><td>undefined</td> <td align="center">true</td> <td align="center">true</td> <td align="center">true</td> <td align="center">true</td> </tr></tbody></table><h4 data-id="that">THAT</h4> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">THAT</code> is followed by a <strong>Relationship verb</strong>.</p> <p>The verb is the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">class</code> value of a <strong>Relationship</strong> -- that is, the edge between two connected entity nodes in the graph. This relationship verb/class value is stored in <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">ALLCAPS</code>, however, it is <em>case insensitive</em> in the query, as the query language will automatically convert it. The predefined keyword <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">RELATES TO</code> can be used to find <em>any</em> relationship between two nodes. For example:</p> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND Service THAT RELATES TO Account</code></p> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">( | )</code> can be used to select entities or relationships of different class/type.</p> <p>For example, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND (Host|Device) WITH ipAddress='10.50.2.17'</code> is equivalent to and much simpler than the following:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">FIND * WITH(_class='Host' OR _class='Device') AND ipAddress='10.50.2.17' </pre> <p>It is fine to mix entity class and type values together. For example:</p> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND (Database|aws_s3_bucket)</code></p> <p>It can be used on Relationship verbs as well. For example:</p> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND HostAgent THAT (MONITORS|PROTECTS) Host</code></p> <p>Or both Entity and Relationships together. For example:</p> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND * THAT (ALLOWS|PERMITS) (Internet|Everyone)</code></p> <h4 data-id="bidirectional-verbs-by-default">Bidirectional verbs by default</h4> <p><strong>Relationship verbs</strong> are bidirectional by default</p> <p>Both queries yield the same results:</p> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND User THAT HAS Device</code></p> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND Device THAT HAS User</code></p> <h4 data-id="relationship-direction-operators">Relationship direction operators</h4> <p><strong>Relationship direction</strong> can be specified with double arrows ( <code class="code codeInline code codeInline" spellcheck="false" tabindex="0"><<</code> or <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">>></code>) <em>after</em> the verb</p> <p>Finds Entities with a <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">HAS</code> relationship from User to Device:</p> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND User THAT HAS >> Device</code></p> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Find Device THAT HAS << User</code></p> <p>Finds Entities with a <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">HAS</code> relationship from Device to User:</p> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND User THAT HAS << Device</code></p> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Find Device THAT HAS >> User</code></p> <h4 data-id="negating-relationships">Negating relationships</h4> <p>It's useful to know if entities do not have a relationship with another entity. To achieve this, relationships can be negated by prefixing a relationship with an exclamation point: <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">!</code>.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find User that !IS Person </pre> <p><strong>This also applies to any relationships</strong></p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find User that !RELATES TO Person </pre> <p>This finds EBS volumes that are not in use. The query finds relationships regardless of the edge direction, therefore the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">!USES</code> in the below query translates more directly as <strong>"is not used by"</strong>.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find aws_ebs_volume that !USES aws_instance </pre> <p>It is important to note that the above query returns <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">aws_ebs_volume</code> entities. If the query were constructed the other way around --</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find aws_instance that !USES aws_ebs_volume </pre> <p>-- it would return a list of <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">aws_instances</code>, if it does not have an EBS volume attached.</p> <h4 data-id="as">AS</h4> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">AS</code> defines an aliased selector.</p> <p>Defines an aliased selector to use in the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WHERE</code> or <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">RETURN</code> portion of a query. For example:</p> <ul><li><strong>Without</strong> selectors: <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND Firewall THAT ALLOWS *</code></li> <li><strong>With</strong> selectors: <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND Firewall AS fw THAT ALLOWS * AS n</code></li> </ul><p>Selectors can also be defined on a relationship:</p> <ul><li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND Firewall AS fw THAT ALLOWS AS rule * AS n</code></li> </ul><h4 data-id="where">WHERE</h4> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WHERE</code> is used for post-traversal filtering (requires selector)</p> <p>From the example above:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">FIND Firewall as fw that ALLOWS as rule * as n WHERE rule.ingress=true AND (rule.fromPort=22 or rule.toPort=22) </pre> <h4 data-id="return">RETURN</h4> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">RETURN</code> is used to return specific entities, relationships, or properties</p> <p>By default, the entities and their properties found from the start of the traversal is returned. For example, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Find User that IS Person</code> returns all matching <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">User</code> entities and their properties, but not the related <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Person</code> entities.</p> <p>To return properties from both the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">User</code> and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Person</code> entities, define a selector for each and use them in the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">RETURN</code> clause:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">FIND User as u that IS Person as p RETURN u.username, p.firstName, p.lastName, p.email </pre> <p>If a property name contains special characters (e.g. <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">-</code> or <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">:</code>), you can wrap the property name in <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">[]</code>.</p> <p>For example: <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">RETURN p.[special-name]='something'</code></p> <p>Wildcard can be used to return all properties. For example:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">FIND User as u that IS Person as p RETURN u.*, p.* </pre> <p>Using a wildcard to return all properties also returns all metadata properties associated with the selected entities. This feature is useful when you want to perform an analysis that involves metadata.</p> <h4 data-id="to">TO</h4> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">TO</code> is used after a relationship verb, and with the exception of <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">RELATES TO</code>,is considered a 'filler' word that is ignored by the interpreter.</p> <p>The keyword <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">TO</code> is supported in J1QL so that the query can be read as a natural language question. Although <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">TO</code> can be used in a query, if omitted, the returned result will be the same.</p> <p>The following are some example relationship verbs where <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">TO</code> could be used:</p> <ul><li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">DEPLOYED TO</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">CONTRIBUTES TO</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">CONNECTS TO</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">ASSIGNED TO</code></li> </ul><p>The following queries return the same result:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">FIND User THAT CONTRIBUTES TO CodeRepo FIND User THAT CONTRIBUTES CodeRepo </pre> <p><strong>Note</strong>: J1QL keywords are not case-sensitive.</p> <h4 data-id="commenting">Commenting</h4> <p>J1QL supports commenting in queries anywhere in J1 using the format <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">/* insert comment here */</code>.<br> For example:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">FIND aws_security_group WITH displayName ~='elb' /*ELB Security Group*/ OR displayName ~='lambda' /*Lambda Security Group*/ </pre> <h2 data-id="mathematical-expressions">Mathematical Expressions</h2> <p>J1QL supports some mathematical expressions as functions.</p> <table><thead><tr><th>Function</th> <th>Description</th> <th>Example Query</th> </tr></thead><tbody><tr><td>exponents</td> <td>A quantity representing the power to which a given number or expression is to be raised, usually expressed as a raised symbol beside the number or expression (e.g. 3 in 23 = 2 × 2 × 2).</td> <td><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND Risk as r RETURN r.probability ^ 2</code></td> </tr><tr><td>abs</td> <td>Absolute value, the magnitude of a real number without regard to its sign.</td> <td><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND Risk as r RETURN ABS(r.impact - 5) / r.probability</code></td> </tr><tr><td>sqrt</td> <td>Square root, a number which produces a specified quantity when multiplied by itself.</td> <td><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND * as any THAT HAS Risk as r RETURN any.displayName, any._class, sqrt((5 - r.impact)^2 + (5 - r.probability)^2)) as score ORDER BY score ASC</code></td> </tr><tr><td>ceil</td> <td>Round up to the next closest whole number.</td> <td><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND DataStore with allocatedStorage > 0 as d RETURN d.displayName, CEIL(d.allocatedStorage/1000) as allocatedMb</code></td> </tr><tr><td>floor</td> <td>Round down to next closes whole number.</td> <td><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND Risk as r RETURN FLOOR(ABS(r.impact - 5) / r.probability)</code></td> </tr><tr><td>round</td> <td>Round up or down to the next closes whole number.</td> <td><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND DataStore with backupSizeBytes > 0 as d RETURN d.displayName, ROUND(d.backupSizeBytes / d.backupsCount) as averageBackupSize</code></td> </tr><tr><td>coalesce</td> <td>Use the first found value. Provide a list of values and the first value to not be undefined/null will be used.</td> <td><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND (aws_s3_bucket|aws_dynamodb_table) as store RETURN store._type, store.displayName, coalesce(store.backupSizeBytes, store.bucketSizeBytes, 0)/1000 as bytes</code></td> </tr><tr><td>concat</td> <td>Allows math expressions.</td> <td><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND (aws_s3_bucket |aws_dynamodb_table) as store RETURN store._type, store.displayName, concat(coalesce(store.backupSizeBytes, store.bucketSizeBytes, 0)/1000, 'mb') as size</code></td> </tr></tbody></table><h2 data-id="downloading-results">Downloading Results</h2> <p>Click the download icon <img src="https://us.v-cdn.net/6035534/uploads/CTOCK9E5V5XB/download.png" alt="" class="embedImage-img importedEmbed-img"></img> to download all assets or a selected asset as a CSV file. You are notified in the Notifications <img src="https://us.v-cdn.net/6035534/uploads/5BHNDWYYN52D/bell.png" alt="" class="embedImage-img importedEmbed-img"></img> panel in the top-right of the top navigation bar when your download is complete and ready for you to download.</p> <p><img src="https://us.v-cdn.net/6035534/uploads/MU9E4IJA2YEE/query-download.png" alt="" class="embedImage-img importedEmbed-img"></img></p> <h2 data-id="filtering-behavior">Filtering Behavior</h2> <p>JupiterOne aligns its query language with De Morgan's Law. This standard mathematical theory is two sets of rules or laws developed from Boolean expressions for AND, OR, and NOT gates, using two input variables, A and B. These two rules or theorems allow the input variables to be negated and converted from one form of a Boolean function into an opposite form. J1QL uses this law in filtering the results of queries.</p> <p>When you use a <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">!=</code> followed by a set of arguments offset by parentheses, such as <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">!= (A or B or C)</code>, it is equivalent to the expression <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">!= A and != B and != C</code>.</p> <p><strong>Example:</strong></p> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND jira_user WITH accountType != ('atlassian' OR 'app' OR 'customer')</code></p> <p>This query is the equivalent of:</p> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND jira_user WITH accountType != 'atlassian' AND accountType != 'app' AND accountType != 'customer'</code></p> <p>J1QL interprets the query to return all <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">jira_user</code> entities, excluding those that have an <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">accountType</code> value of 'atlassian' or 'app' or customer'.</p> <p>The following table shows the resulting truth values of a complex statement that are all possible for a simple statement.</p> <p><strong>Truth Table</strong></p> <table><thead><tr><th>Entity</th> <th align="center">"fruit"</th> <th align="center">"nut-filled"</th> <th align="center">=("fruit" AND "nut-filled")</th> <th align="center">=("fruit" OR "nut-filled")</th> </tr></thead><tbody><tr><td>"fruit"</td> <td align="center">true</td> <td align="center">false</td> <td align="center">false</td> <td align="center">true</td> </tr><tr><td>"nut-filled"</td> <td align="center">false</td> <td align="center">true</td> <td align="center">false</td> <td align="center">true</td> </tr><tr><td>"fruit", "nut-filled"</td> <td align="center">true</td> <td align="center">true</td> <td align="center">true</td> <td align="center">true</td> </tr><tr><td>"non-fruit"</td> <td align="center">false</td> <td align="center">false</td> <td align="center">false</td> <td align="center">false</td> </tr><tr><td>"non-fruit", "plain"</td> <td align="center">false</td> <td align="center">false</td> <td align="center">false</td> <td align="center">false</td> </tr><tr><td>undefined</td> <td align="center">false</td> <td align="center">false</td> <td align="center">false</td> <td align="center">false</td> </tr></tbody></table><p><strong>Negated Queries</strong></p> <table><thead><tr><th>Entity</th> <th align="center">"fruit"</th> <th align="center">"nut-filled"</th> <th align="center">!=("fruit" AND "nut-filled")</th> <th align="center">!=("fruit" OR "nut-filled")</th> </tr></thead><tbody><tr><td>"fruit"</td> <td align="center">true</td> <td align="center">false</td> <td align="center">true</td> <td align="center">false</td> </tr><tr><td>"nut-filled"</td> <td align="center">false</td> <td align="center">true</td> <td align="center">true</td> <td align="center">false</td> </tr><tr><td>"fruit", "nut-filled"</td> <td align="center">true</td> <td align="center">true</td> <td align="center">false</td> <td align="center">false</td> </tr><tr><td>"non-fruit"</td> <td align="center">false</td> <td align="center">false</td> <td align="center">true</td> <td align="center">true</td> </tr><tr><td>"non-fruit", "plain"</td> <td align="center">false</td> <td align="center">false</td> <td align="center">true</td> <td align="center">true</td> </tr><tr><td>undefined</td> <td align="center">false</td> <td align="center">false</td> <td align="center">true</td> <td align="center">true</td> </tr></tbody></table><h3 data-id="property-filtering">Property Filtering</h3> <p>You can filter multiple property values like this (similar to <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">IN</code> in SQL):</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">FIND user_endpoint WITH platform = ('darwin' OR 'linux') Find Host WITH tag.Environment = ('A' or 'B' or 'C') Find DataStore WITH classification != ('critical' and 'restricted') </pre> <p>Property filters are evaluated according the following <strong>order of operations</strong>:</p> <p>Parenthesis first, comparisons (<code class="code codeInline code codeInline" spellcheck="false" tabindex="0">=</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">>=</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0"><=</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">!=</code>) after, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">AND</code> and then <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">OR</code>.</p> <h2 data-id="string-comparisons">String Comparisons</h2> <p>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 <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">=</code> and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">!=</code>:</p> <ul><li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">~=</code> : contains</li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">^=</code> : starts with</li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">$=</code> : ends with</li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">!~=</code> : does not contain</li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">!^=</code> : does not start with</li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">!$=</code> : does not end with</li> </ul><p>These operators only work for comparisons of strings or multi-value fields.</p> <p><strong>Examples:</strong></p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find Person with firstName^='J' </pre> <p>The above query returns all entities of the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Person</code> class that have a <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">firstName</code> beginning with the character 'J'.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find Person with email='a@b.com' </pre> <p>The above query returns all assets of the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Person</code> class that have a `Person.email' of 'a@b.com' or ['a@b.com', 'x@y.com'].</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find Person with email~='.com' </pre> <p>The above query checks if a substring matches for either a single string or a multi-value field.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find Host with tag.AccountName~='demo' </pre> <p>The above query returns entities of the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Host</code> class with any of the following examples of <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">tag.AccountName</code>: <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">xyz_demo</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">demo_xyz</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">abc_demo_xyz</code>.</p> <p><strong>Note:</strong> These string evaluations are case-sensitive. So <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">'Demo'</code> and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">'demo'</code>yield distinct sets of results.</p> <h2 data-id="parameters">Parameters</h2> <p>The query language supports <a rel="nofollow" href="https://jupiterone.vanillacommunities.com/kb/articles/850-jupiterone-parameter-service">parameters</a> for referencing values stored on the server side. Parameter expressions are allowed in places that could otherwise include a literal value.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">FIND Application WITH loginUrl = ${ param.loginUrl } </pre> <p>There is support for referencing <a rel="nofollow" href="https://jupiterone.vanillacommunities.com/kb/articles/850-jupiterone-parameter-service">parameters</a> that contain arrays.</p> <h2 data-id="date-comparisons">Date Comparisons</h2> <p>The query language supports both relative and static data comparisons on any timestamp property. The timestamp property used for date comparison must be stored as an epoch number in milliseconds.</p> <h3 data-id="relative-date-comparison">Relative Date Comparison</h3> <p>Relative date comparison allows filtering based on the current datetime.</p> <p>For example:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find DataStore with createdOn > date.now - 1 day </pre> <p>The following units are supported:</p> <ul><li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">hour</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">hr</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">hours</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">hrs</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">day</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">days</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">month</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">mo</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">months</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">mos</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">year</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">yr</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">years</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">yrs</code></li> </ul><h3 data-id="static-date-comparison">Static Date Comparison</h3> <p>Static date comparison allows filtering based on a specified datetime.</p> <p>For example:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find DataStore with createdOn > date(2019-10-30) </pre> <p>The static date must be specified in ISO 8601 format:</p> <ul><li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">date(YYYY)</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">date(YYYY-MM)</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">date(YYYY-MM-DD)</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">date(YYYY-MM-DDThhTZD)</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">date(YYYY-MM-DDThh:mmTZD)</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">date(YYYY-MM-DDThh:mm:ssTZD)</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">date(YYYY-MM-DDThh:mm:ss.sTZD)</code></li> </ul><p>If your query with a date comparison does not return anything, refer to the <a rel="nofollow" href="https://jupiterone.vanillacommunities.com/kb/articles/827-troubleshooting-and-reporting-common-data-issues">Troubleshooting and Reporting Common Data Issues</a> guide for help.</p> <h2 data-id="sorting-and-pagination-via-order-by-skip-and-limit">Sorting and Pagination via <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">ORDER BY</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">SKIP</code>, and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">LIMIT</code></h2> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">ORDER BY</code> is followed by a <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">selector.field</code> to indicate what to sort.<br><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">SKIP</code> is followed by a number to indicate how many results to skip.<br><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">LIMIT</code> is followed by a number to indicate how many results to return.</p> <p>In the example below, the query sorts users by their username, and returns the 11th-15th users from the sorted list.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">FIND Person WITH manager = undefined as u ORDER BY u.username SKIP 10 LIMIT 5 </pre> <p><strong>Note:</strong> Query returns up to 250 results by default if <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">LIMIT</code> is not set.</p> <h2 data-id="aggregation-functions-count-min-max-avg-and-sum">Aggregation Functions: <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">COUNT</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">MIN</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">MAX</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">AVG</code> and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">SUM</code></h2> <p>It is useful to be able to perform calculations on data that have been returned from the graph. Being able to perform queries to retrieve a count, min, max or perform other calculations can be quite valuable and gives users more ways to understand their data.</p> <p>The ability to perform aggregations are exposed as <strong>Aggregating Functions</strong>. These are functions that can be applied to a given set of data that was requested via the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">RETURN</code> clause.</p> <p>The following aggregating functions are supported:</p> <ul><li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">count(selector)</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">count(selector.field)</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">min(selector.field)</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">max(selector.field)</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">avg(selector.field)</code></li> <li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">sum(selector.field)</code></li> </ul><p>The keywords are <em>case insensitive</em>.</p> <p>A few examples:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">find bitbucket_team as team that relates to bitbucket_user as user return team.name, count(user) </pre> <pre class="code codeBlock" spellcheck="false" tabindex="0">find bitbucket_team as team that relates to bitbucket_user as user return count(user), avg(user.age) </pre> <p>See more details and examples <a rel="nofollow" href="#How-aggregations-are-applied">below</a>.</p> <p><em>Future development:</em></p> <p>There are plans to support the following aggregations:</p> <ul><li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">count(*)</code> - for determining the count of all other entities related to a given entity.</li> </ul><h2 data-id="scalar-functions">Scalar Functions</h2> <p>The ability to format and/or to perform calculations on row level columns can be accomplished through <strong>Scalar Functions</strong>.</p> <h3 data-id="concat"><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">CONCAT</code></h3> <p>The scalar function <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">CONCAT()</code> empowers users to concatenate or join one or more values into a single string. Currently, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">CONCAT</code> can be used in the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">RETURN</code> to clause of your function, will future development planned for use in the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WHERE</code> clause.</p> <p>Note: If this function receives a number or boolean value, the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">concat</code> intuitively converts these values to strings. Additionally, if <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">concat</code> processes an empty selector field, it evaluates that field as an empty string.</p> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">CONCAT</code> supports the following parameters, separated by comma:</p> <ul><li>Selector Fields: e.g. <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">selector.field</code></li> <li>String values: e.g. <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">'your string'</code> or <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">"your string"</code></li> <li>Number values: e.g. <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">123</code></li> <li>Boolean values: e.g. <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">true</code></li> </ul><p>Example:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">FIND aws_s3_bucket as s3 RETURN CONCAT(s3.bucketSizeBytes, ' bytes') as size </pre> <h3 data-id="merge"><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">MERGE</code></h3> <p>The scalar function <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">MERGE()</code> 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.</p> <p>Example:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">FIND UNIQUE User as u THAT IS Person as p RETURN p.displayName, merge(p.email, u.email, u.publicEmail) as “Email List” (edited) </pre> <h2 data-id="de-duplicate-results-with-unique-return-and-merge">De-duplicate results with <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">UNIQUE</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">RETURN</code>, and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">MERGE</code></h2> <p>Sometimes a query may generate duplicate results. This duplication occurs if there are multiple paths of traversals (such as relationships) between the vertices (such as assets) referenced in a specific query.</p> <p>In this example:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find aws_eni with publicIpAddress != undefined as nic that relates to aws_instance that relates to aws_security_group as sg that allows Internet where nic.securityGroupIds = sg.groupId </pre> <p>This query attempts to find network interfaces that are associated with a security group that allows public facing AWS EC2 instances. In this case, there could be multiple security group rules allowing access to/from the Internet, which may result in duplicate data in the query result because each individual traversal is a successful match to the query.</p> <h3 data-id="unique-and-return"><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">UNIQUE</code> and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">RETURN</code></h3> <p>You can use a combination of <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">UNIQUE</code> and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">RETURN</code> keywords to filter out the duplicates. The query above can be modified as:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find UNIQUE aws_eni with publicIpAddress != undefined as nic that relates to aws_instance that relates to aws_security_group as sg that allows Internet where nic.securityGroupIds = sg.groupId RETURN nic.id, nic.subnetId, nic.attachmentId, nic.active, nic.privateIp, nic.publicIp, nic.vpcId, nic.securityGroupIds, nic.securityGroupNames, nic.tag.AccountName, nic.webLink </pre> <p><em>Limitation: <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">UNIQUE</code> keyword <strong>must</strong> be used together with <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">RETURN</code>.</em></p> <h3 data-id="merge-1"><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">MERGE</code></h3> <p>The function <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">MERGE()</code> 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.</p> <p>Example:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">FIND UNIQUE User as u THAT IS Person as p RETURN p.displayName, merge(p.email, u.email, u.publicEmail) as “Email List” (edited) </pre> <h2 data-id="math-operations">Math Operations</h2> <p>J1QL supports basic math operations on the return values.</p> <ul><li><p>Supported operations include <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">+</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">-</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">/</code>, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">*</code> and parenthesis</p></li> <li><p>It will evaluate with normal order of operations:</p></li> </ul><p>parenthesis -> multiplication or division -> addition or subtraction</p> <ul><li>The operation only works against number values. It does not work against strings or strings that represent numbers:</li> </ul><p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">'1'</code> does not work, it has to be <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">1</code></p> <p>Example query:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find (aws_db_cluster_snapshot|aws_db_snapshot) as snapshot Return snapshot.displayName, snapshot.allocatedStorage * 0.02 as Cost </pre> <p>This can be combined with aggregation functions. For example:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find (aws_db_cluster_snapshot|aws_db_snapshot) as snapshot Return snapshot.tag.AccountName as Account, sum(snapshot.allocatedStorage) * 0.02 as EstimatedCost </pre> <h2 data-id="optional-traversals">Optional traversals</h2> <p>In situations where it is useful to optionally find related entities and include them in the results, J1QL allows for portions of a query to be wrapped with a <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">(</code> and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">)?</code> to mark that section of the query as an optional<br> traversal. This allows for related entities from a graph traversal to be combined and for additional constraints to be applied to the set of entities.</p> <p>Example query:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find User (that IS Person)? </pre> <p>In the above example, we search for <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">User</code> entities and optionally traverse an <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">IS</code> relationship to a <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Person</code> entity. If the relationship exists, the related <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Person</code> entities are added to the list of results.</p> <p><strong>Relationships can still be chained within an optional traversal.</strong> The query below will return a list of <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Device</code> entities owned by a <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Person</code> that is a <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">User</code> and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">User</code> entities that do not have the indirect relationship to the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Device</code>.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find User (that IS Person that OWNS Device)? </pre> <p><strong>Relationships that come after an optional traversal are processed on the combined results.</strong> This query searches for Users or UserGroups that directly assigned an AccessPolicy granting admin permissions to certain resources, or via an AccessRole assigned to the User/UserGroup.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find (User | UserGroup) (that assigned AccessRole)? that assigned AccessPolicy that allows as permission * where permission.admin=true return TREE </pre> <p><strong>Optional traversals can also be chained.</strong> The combined results from each previous optional traversal will be used in the next optional traversal.</p> <p>The below query will find <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">User</code> entities, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Person</code> entities that have an <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">IS</code> relationship to the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">User</code> and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Device</code> entities that are owned by <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Person</code> and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">User</code> entities from the previous optional traversal.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find User (that is Person)? (that owns Device)? Return User, Person, Device </pre> <p><strong>The optional traversals can also be aliased.</strong> This allows the union set of entities to be used when returning results and when applying constraints.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find User (that is Person)? as userOrPerson that owns Device Where userOrPerson.email = 'test@jupiterone.com' Return userOrPerson, Device </pre> <p>Traversals performed within the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">(</code> and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">)?</code> function as normal graph traversals, so <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WITH</code> filters can still be applied to assist with narrowing results.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find User with name = 'test' (that is Person with email = 'test@jupiterone.com')? as userOrPerson that owns Device return userOrPerson, Device </pre> <h2 data-id="smart-classes-beta">Smart classes (beta)</h2> <p>Smart classes are a mechanism for applying a set of entity filters with a shorthand syntax. There are two categories of smart classes:</p> <ol><li><p>JupiterOne application classes<br> Currently, the only supported instance is <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">#CriticalAsset</code>, which maps to the configured definition of critical assets in the Assets app.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">FIND #CriticalAsset that has Finding </pre> <p>The default definition of a critical asset is an entity with one of the following classes:</p></li> </ol><ul><li>Application</li> <li>CodeRepo</li> <li>DataStore</li> <li>Function</li> <li>Host</li> <li><p>Logs</p> <p>and the following attributes:</p></li> <li><p>tag.Production = 'true'</p></li> <li>classification = 'critical'</li> </ul><p>Administrators define critical assets in the Assets app by clicking the gear icon in the Assets title bar.</p> <ol start="2"><li><p>Tag-derived values<br> These values match entities where the tags of an entity contain the provided smart class (case-sensitive).</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">FIND #Production Application </pre> <p>Tags are populated via integrations, and can also be added directly to an entity via J1 as enriched data. Note that, for key-value pair tags, the tag value must be <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">true</code> to match the smart class.</p></li> </ol><p>Assuming you have defined a critical asset as per the above default, here are some example smart class queries and their equivalencies.</p> <table><thead><tr><th>Smart class Query</th> <th>Equivalent Expanded Query</th> </tr></thead><tbody><tr><td><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND #CriticalAsset</code></td> <td><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND * WITH ((_class = ('Application' or 'CodeRepo' or 'DataStore' or 'Function' or 'Host' or 'Logs') and (tag.Production = true and classification = 'critical')) or tags = 'CriticalAsset'</code>)</td> </tr><tr><td><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND #CriticalAsset THAT HAS Finding</code></td> <td><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND * WITH ((_class = ('Application' or 'CodeRepo' or 'DataStore' or 'Function' or 'Host' or 'Logs') and (tag.Production = true and classification = 'critical')) or tags = 'CriticalAsset') THAT HAS Finding</code></td> </tr><tr><td><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND Finding THAT RELATES TO #CriticalAsset</code></td> <td><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND Finding THAT HAS * WITH ((_class = ('Application' or 'CodeRepo' or 'DataStore' or 'Function' or 'Host' or 'Logs') and (tag.Production = true and classification = 'critical')) or tags = 'CriticalAsset')</code></td> </tr><tr><td><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND #Production Application</code></td> <td><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">FIND Application WITH tags = 'Production'</code></td> </tr></tbody></table><p>Returned entities reflect their underlying classes, not the queried smart class.</p> <h2 data-id="examples">Examples</h2> <p>More example queries are shown below.</p> <p>These examples, and same with all packaged queries provided in the JupiterOne web apps, are constructed in a way to de-emphasize the query keywords (they are <em>case insensitive</em>) but rather to highlight the relationships -- the operational context and significance of each query.</p> <h3 data-id="simple-examples">Simple Examples</h3> <p><strong>Find any entity that is unencrypted</strong></p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find * with encrypted = false </pre> <p><strong>Find all entities of class DataStore that are unencrypted</strong></p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find DataStore with encrypted = false </pre> <p><strong>Find all entities of type aws_ebs_volume that are unencrypted</strong></p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find aws_ebs_volume with encrypted = false </pre> <h3 data-id="query-with-relationships">Query with relationships</h3> <p><strong>Return just the Firewall entities that protects public-facing hosts</strong></p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find Firewall that PROTECTS Host with public = true </pre> <p><strong>Return Firewall and Host entities that matched query</strong></p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find Firewall as f that PROTECTS Host with public = true as h RETURN f, h </pre> <p><strong>Return all the entities and relationships that were traversed as a tree</strong></p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find Firewall that PROTECTS Host with public = true RETURN tree </pre> <h3 data-id="full-text-search">Full-text search</h3> <p><strong>Find any and all entities with "127.0.0.1" in some property value</strong></p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find "127.0.0.1" </pre> <p><strong>The FIND keyword is optional</strong></p> <pre class="code codeBlock" spellcheck="false" tabindex="0">"127.0.0.1" </pre> <p><strong>Find all hosts that have "127.0.0.1" in some property value</strong></p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find "127.0.0.1" with _class='Host' </pre> <h3 data-id="more-complex-queries">More complex queries</h3> <p>Find critical data stored outside of production environments.</p> <p>This assumes you have the appropriate tags (Classification and Production) on your entities.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find DataStore with tag.Classification='critical' that HAS * with tag.Production='false' </pre> <p>Find all users and their devices without the required endpoint protection agent installed:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find Person that has Device that !protects HostAgent </pre> <p>Find incorrectly tagged resources in AWS:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find * as r that RELATES TO Service that RELATES TO aws_account where r.tag.AccountName != r.tag.Environment </pre> <p>If your users sign on to AWS via single sign on, you can find out who has access<br> to those AWS accounts via SSO:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find User as U that ASSIGNED Application as App that CONNECTS aws_account as AWS RETURN U.displayName as User, App.tag.AccountName as IdP, App.displayName as ssoApplication, App.signOnMode as signOnMode, AWS.name as awsAccount </pre> <h3 data-id="using-metadata">Using Metadata</h3> <p>Filtering on metadata can often be useful in performing security analysis. The example below is used to find network or host entities that did <em>not</em> get ingested by an integration instance. In other words, these are entities that are likely "external" or "foreign" to the environment.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find (Network|Host) with _IntegrationInstanceId = undefined </pre> <p>The following example finds all brand new code repos created within the last 48 hours:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find CodeRepo with _beginOn > date.now-24hr and _version=1 </pre> <p>For more details on metadata properties, see the JupiterOne <a rel="nofollow" href="https://jupiterone.vanillacommunities.com/kb/articles/846-jupiterone-data-model">data model documentation</a>.</p> <h2 data-id="advanced-notes-and-use-cases">Advanced Notes and Use Cases</h2> <h3 data-id="how-aggregations-are-applied">How aggregations are applied</h3> <p>There are three different ways for aggregations to be applied</p> <ul><li>on the customer's subgraph (determined by the traversal that is run)</li> <li>on a portion of the customer's subgraph relative to a set of entities (groupings)</li> <li>on data for a single entity</li> </ul><p>The way aggregations happen are determined by what is requested via the query language's <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">return</code> clause.</p> <h4 data-id="aggregations-relative-to-a-subgraph">Aggregations relative to a subgraph</h4> <p>If all selectors are aggregations, then all aggregations will be scoped to the entire traversal that the user has requested and not tied to individual entities.</p> <p>Ex. <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">return count(acct), count(user)</code>:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find Account as acct that has User as user return count(acct), count(user) </pre> <h4 data-id="aggregations-relative-to-a-grouping-by-entity-attribute">Aggregations relative to a grouping by entity attribute</h4> <p>If selectors are provided that do not use an aggregation function, they will be used as a <em>grouping key</em>. This key will be used to apply the aggregations relative to the data chosen.</p> <p>Ex. <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">return acct._type, count(user)</code>:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find Account as acct that has User as user return acct._type, count(user) </pre> <h4 data-id="aggregations-relative-to-a-grouping-by-multiple-attributes">Aggregations relative to a grouping by multiple attributes</h4> <p>If multiple attributes of a selector are included the return function, the last one before the aggregation will be used as the <em>grouping key</em>.</p> <p>Ex. <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">return acct._type, acct.displayName, count(user)</code>:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find Account as acct that has User as user return acct._type, acct.displayName, count(user) </pre> <h4 data-id="aggregations-examples">Aggregations Examples</h4> <h5 data-id="the-simple-case">The Simple Case</h5> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">count</code> always returns the number of distinct entities or atrributes requested.</p> <p>With this data:<br></p> <table><thead><tr><th>id</th> <th>class</th> <th>name</th> <th>lead</th> </tr></thead><tbody><tr><td>1</td> <td>bitbucket_team</td> <td>team1</td> <td>alice</td> </tr><tr><td>2</td> <td>bitbucket_team</td> <td>team2</td> <td>bob</td> </tr><tr><td>3</td> <td>bitbucket_team</td> <td>team3</td> <td>alice</td> </tr></tbody></table><p>and this query:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">find bitbucket_team as team return count(team.lead) </pre> <p>the result will be:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">{ "type": "table", "data": [ { "count(team.lead)": 2 }, ] } </pre> <h5 data-id="single-grouping-key">Single grouping key</h5> <p>For example, with the following query,</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">find bitbucket_team as team that relates to bitbucket_user as user return team.name, count(user) </pre> <p>the result will be:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">{ "type": "table", "data": [ { "team.name": "team1", "count(user)": 25 }, { "team.name": "team2", "count(user)": 5 } ] } </pre> <p>In this case, the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">team.name</code> acts as the key that groups aggregations together. So <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">count(user)</code> finds the count of users relative to each team.</p> <h5 data-id="multiple-grouping-keys">Multiple grouping keys</h5> <p>When there are return selectors that are not aggregating functions, the aggregating functions will be performed relative to the identifier that it is closer to in the traversal.</p> <p>Example:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">find bitbucket_project as project that relates to bitbucket_team as team that relates to bitbucket_user as user return project.name, team.name, count(user) </pre> <p>The <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">count(user)</code> aggregation will be performed relative to the team, because the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">team</code> traversal is closer to the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">user</code> traversal in the query.</p> <p>Example result:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">{ "type": "table", "data": [ { "project.name": "JupiterOne", "team.name": "team1", "count(user)": 25 }, { "project.name": "JupiterOne", "team.name": "team2", "count(user)": 5 }, { "project.name": "Windbreaker", "team.name": "team2", "count(user)": 5 } ] } </pre> <p>If the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">return</code> statement is changed to this:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">return project.name, count(user) </pre> <p>The <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">count(user)</code> aggregation will be performed relative to the project.</p> <p>Example result:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">{ "type": "table", "data": [ { "project.name": "JupiterOne", "count(user)": 50 }, { "project.name": "Windbreaker", "count(user)": 5 } ] } </pre> <h5 data-id="examples-relative-to-a-single-entity">Examples relative to a single entity</h5> <p><em>Future development:</em></p> <p>If a selector is specified and an aggregating function is applied to that selector's source identifier in some way, aggregations will happen locally to the element.</p> <p>Example:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">find bitbucket_project as project that relates to bitbucket_team as team that relates to bitbucket_user as user return project.name, count(project.aliases), team.name, count(user) </pre> <p>Example result:</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">{ "type": "table", "data": [ { "project.name": "JupiterOne", "count(project.aliases)": 1, "team.name": "team1", "count(user)": 25 }, { "project.name": "JupiterOne", "count(project.aliases)": 1, "team.name": "team2", "count(user)": 5 }, { "project.name": "Windbreaker", "count(project.aliases)": 5, "team.name": "team2", "count(user)": 5 } ] } </pre> </article> </main>
.png)