Community
Questions Library
Docs
Blog
Events
Swag
Github
Slack
JupiterOne
Discussions
Release Notes
Contact Us
J1QL Tips and Tricks - AskJ1 Community
<main> <article class="userContent"> <p>This guide contains tips and tricks that assist in creating J1QL queries in your account. If you haven't already, check out our <a rel="nofollow" href="https://jupiterone.vanillacommunities.com/kb/articles/844-jupiterone-query-language-tutorial">J1QL Tutorial</a> as well as our <a rel="nofollow" href="https://jupiterone.vanillacommunities.com/kb/articles/980-introduction-to-jupiterone-query-language-j1ql">J1QL Language Specs</a> articles for helpful context.</p> <p>Before creating your own query, we recommend starting with our wide-array of <a rel="nofollow" href="https://ask.us.jupiterone.io/">pre-packaged questions</a>, tweaking them as needed, then creating your own from scratch.</p> <h2 data-id="when-to-use-with-vs-where">When to Use WITH vs WHERE</h2> <p><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WITH</code> and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WHERE</code> are both keywords used to filter the results of your query. The filtering of the results happens either pre-traversal or post-traversal. <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WITH</code> is used for pre-traversal filtering and <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WHERE</code> is used for post-traversal filtering.</p> <p>In a <em>pre-traversal</em> filter, the nodes specified in the query are filtered down before traversing child nodes and finding matching query results.</p> <p>In a <em>post-traversal</em> filter, the entire graph is traversed to find potential matching query results before filtering occurs on the resulting graph to produce the final query result.</p> <p><strong>TIP</strong> Use <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WITH</code> over <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WHERE</code> when possible as the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WITH</code> operation is faster and more efficient.</p> <h3 data-id="with">WITH</h3> <ol><li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WITH</code> is used to filter entity <strong>node</strong> properties <strong>pre-traversal</strong>.</li> </ol><p>Take the following example.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find User with name~='john' that HAS UserGroup that ASSIGNED AccessRole that (ALLOWS|TRUSTS) Account </pre> <p><img src="https://us.v-cdn.net/6035534/uploads/98G4S0EBGSXC/j1ql-custom-query-with-example.png" alt="j1ql-custom-query-with-example" class="embedImage-img importedEmbed-img"></img></p> <p>The query will find a filtered list of users with a name property that contains 'john'. The query will then find and join any connected UserGroup(s), then AccessRole(s), and finally Account(s).</p> <h3 data-id="where">WHERE</h3> <ol><li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WHERE</code> is used to filter entity <strong>node</strong> properties <strong>post-traversal</strong>.</li> </ol><p>Let's continue with the previous <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WITH</code> example.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find User as u1 that HAS UserGroup that ASSIGNED AccessRole that ASSIGNED User as u2 WHERE u1.name=u2.name </pre> <p><img src="https://us.v-cdn.net/6035534/uploads/ZWZTLED9KEDI/j1ql-custom-query-where-example.png" alt="j1ql-custom-query-where-example" class="embedImage-img importedEmbed-img"></img></p> <p>This query will first find every User that is part of a UserGroup that is assigned an AccessRole that is assigned a User. Once the results have been been retrieved, the query will take both Users that were assigned an alias (u1 and u2), and compare the name property on each. If they match, the filtered result<br> will be returned.</p> <p><strong>TIP</strong> Property comparisons can only be done using <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WHERE</code>.</p> <ol start="2"><li><code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WHERE</code> is also used to filter the relationship <strong>edge</strong> properties <strong>post-traversal</strong>.</li> </ol><p>Let's look at a different example with an edge that has properties.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find Training that ASSIGNED User WHERE ASSIGNED.completedOn = undefined </pre> <p><img src="https://us.v-cdn.net/6035534/uploads/CTZTU1KDGNWX/j1ql-custom-query-incomplete-trainings.png" alt="j1ql-custom-query-incomplete-trainings" class="embedImage-img importedEmbed-img"></img></p> <p>This query checks the edge, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">ASSIGNED</code>, and filters on its properties. In this example we find trainings assigned to Users where the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">completedOn</code> date is undefined (or incomplete).</p> <p><strong>TIP</strong> Use <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WITH</code> over <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WHERE</code> when possible as the <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">WITH</code> operation is faster and more efficient.</p> <h2 data-id="variable-placeholders">Variable Placeholders</h2> <p>A variable placeholder(s) is used in the JupiterOne web application to prompt a user for a specific value(s) to be injected into a pre-saved query. Variable placeholders can be leveraged in saved J1QL queries using the double curly bracket syntax <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">{{variable-placeholder-name}}</code>.</p> <p>Take the following example question that is saved to the question library. The query searches for an AWS instance with a specific ID.</p> <p><img src="https://us.v-cdn.net/6035534/uploads/ADPJ2K210FLS/j1ql-custom-query-library-example.png" alt="j1ql-custom-query-library-example" class="embedImage-img importedEmbed-img"></img></p> <p>Running the above query displays a modal that prompts the user to enter a value. The query will not execute until you enter a value and press the submit button.</p> <p><img src="https://us.v-cdn.net/6035534/uploads/SU8UZ08AQJAD/j1ql-custom-query-placeholder.png" alt="j1ql-custom-query-placeholder" class="embedImage-img importedEmbed-img"></img></p> <h3 data-id="create-a-question-that-uses-a-placeholder">Create a Question that uses a Placeholder</h3> <p>Variable placeholder syntax cannot be used directly in the search bar. There are two ways to create a new question and add a variable placeholder.</p> <ol><li>Save a New Question</li> </ol><ul><li>Type a query into the search bar and enter a <em>temporary</em> value</li> <li>Click the star icon to save</li> <li>Type in a Title that describes the query (this is the question name)</li> <li>Edit the query, replacing the temporary value with <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">{{your variable placeholder name here}}</code></li> <li>Click <strong>Save</strong></li> </ul><p><img src="https://us.v-cdn.net/6035534/uploads/R1SWU4M9QCKA/j1ql-custom-query-save.png" alt="j1ql-custom-query-save" class="embedImage-img importedEmbed-img"></img></p> <ol start="2"><li>Duplicate an Existing Question</li> </ol><ul><li>Click on the Library icon</li> <li>Find an existing question with query elements similar to your new query</li> <li>Click on the Duplicate icon</li> <li>Update the Title</li> <li>Update the existing query and add <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">{{your variable placeholder name here}}</code></li> <li>Click <strong>Save</strong></li> </ul><p><img src="https://us.v-cdn.net/6035534/uploads/FPOZ1O8ZYTTC/j1ql-custom-query-library.png" alt="j1ql-custom-query-library" class="embedImage-img importedEmbed-img"></img><br><img src="https://us.v-cdn.net/6035534/uploads/SKDDCTQOMTIP/j1ql-custom-query-duplicate.png" alt="j1ql-custom-query-duplicate" class="embedImage-img importedEmbed-img"></img></p> <p><strong>TIP</strong> Placeholders are only supported in pre-saved queries.</p> <p><strong>TIP</strong> One or more placeholders can be used in a single saved query.</p> <h2 data-id="leveraging-the-graph-for-context">Leveraging the Graph for Context</h2> <p>Leveraging the graph in JupiterOne to gain context on your cyber security environment is one useful way construct a J1QL query. You can start from a basic query or use an existing one to customize a more meaningful query. Check out the <a rel="nofollow" href="https://jupiterone.vanillacommunities.com/kb/articles/844-jupiterone-query-language-tutorial">J1QL Tutorial</a> for an overview of nodes and relationships as they relate to J1QL.</p> <p>Check out the <a rel="nofollow" href="https://jupiterone.vanillacommunities.com/kb/articles/846-jupiterone-data-model">Data Model - Overview</a> document for a comprehensive list of JupiterOne entities, their properties, and the relationships between entities. The graph is a tool that can be used when these details are unknown.</p> <p>When traversing the graph from a starting node, you will be able to see and expand every related node. For a comprehensive list of all entities and relationships for a specific integration, see the corresponding <a rel="nofollow" href="https://jupiterone.vanillacommunities.com/kb/articles/789-integration-and-mapping-related-faqs">Integration Guide</a>.</p> <p><strong>TIP</strong> When you are in doubt about which verb to use to traverse an edge, use the catch-all verb <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">RELATES TO</code>, which graphs out related entities and the relationships between them. However, you should not use <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">RELATES TO</code> in your final/saved query because the query will run slow having to check all nodes as possible children instead of just those connected by a specific edge verb. See the following example.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find User that RELATES TO * </pre> <h3 data-id="in-the-jupiterone-app">In the JupiterOne App</h3> <ol><li>To start, navigate to the Landing page of JupiterOne and type in a basic query, in the example below, replace <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">DataStore</code> with the entity class/type you are interested in.</li> </ol><p><img src="https://us.v-cdn.net/6035534/uploads/DV1AIDN6OQ3D/j1ql-custom-query-landing-bar.png" alt="j1ql-custom-query-landing-bar" class="embedImage-img importedEmbed-img"></img></p> <ol start="2"><li><p>Click on the specific result you are interested in.</p></li> <li><p>Click on the vertical ellipses to open additional actions then click <strong>Open in Graph</strong>.</p></li> </ol><p><img src="https://us.v-cdn.net/6035534/uploads/ZVMEIQU3UOAT/j1ql-custom-query-result.png" alt="j1ql-custom-query-result" class="embedImage-img importedEmbed-img"></img></p> <p>A new browser tab will be opened and will run a query that returns a graph showing everything related to or mapped to the entity via a relationship.</p> <ol start="4"><li>Click the queried entity and you will see the relationship verbs for each mapped entity.</li> </ol><p><img src="https://us.v-cdn.net/6035534/uploads/QXOXJINODKRD/j1ql-custom-query-graph-relationships.png" alt="j1ql-custom-query-graph-relationships" class="embedImage-img importedEmbed-img"></img></p> <ol start="5"><li>The type & class of a node can be determined by by selecting the node, clicking the info icon, and looking under the display name.</li> </ol><p><img src="https://us.v-cdn.net/6035534/uploads/EQELDQPSFW48/j1ql-custom-query-class-and-type.png" alt="j1ql-custom-query-class-and-type" class="embedImage-img importedEmbed-img"></img></p> <p>The <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Developer</code> node from in the example screenshot has a type of <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">aws_iam_role</code> and a class of <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">AccessRole</code>. We know the following relationships exist for a User class.</p> <ul><li>A User is ASSIGNED an AccessRole</li> <li>A User IS a Person</li> <li>A User HAS a UserGroup</li> <li>A User is ASSIGNED an Application</li> </ul><p>These relationships can be used to craft queries.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find User that ASSIGNED AccessRole Find User that IS Person Find User that HAS UserGroup Find User that ASSIGNED Application </pre> <p><strong>TIP</strong> Relationships can be queried bidirectionally. For example, <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Find User that HAS UserGroup</code> can also be queried as <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Find UserGroup that HAS User</code> and will return the same results in the graph. However, the list view will return a list of <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">User</code> for the former or <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">UserGroup</code> for the latter depending on which entity you <code class="code codeInline code codeInline" spellcheck="false" tabindex="0">Find</code>.</p> <ol start="6"><li>Continue to expand the query by clicking on and expanding nodes in the graph. To do this, select any node and click on the ellipses.</li> </ol><p><img src="https://us.v-cdn.net/6035534/uploads/V8LGYK0T4L7G/j1ql-custom-query-ellipses.png" alt="j1ql-custom-query-ellipses" class="embedImage-img importedEmbed-img"></img></p> <p>From here we see several additional nodes. We can select a path that we are interested and continue to expand the graph until we arrive at a query that is meaningful.</p> <p>In the following example we traversed from a User to a UserGroup to an AccessRole to an Account. Written out more specifically using types we have an okta_user to an okta_user_group to an aws_iam_role to an aws_account.</p> <p>Our final query could be something similar to the following example, adding in filters, sorting, pagination, etc. as needed. See <a rel="nofollow" href="https://jupiterone.vanillacommunities.com/kb/articles/980-introduction-to-jupiterone-query-language-j1ql">J1QL Language Specs</a> to understand the query language features.</p> <pre class="code codeBlock" spellcheck="false" tabindex="0">Find User that HAS UserGroup that ASSIGNED AccessRole that TRUSTS Account </pre> <p><img src="https://us.v-cdn.net/6035534/uploads/0XJEAW7E64GV/j1ql-custom-query-full-example.png" alt="j1ql-custom-query-full-example" class="embedImage-img importedEmbed-img"></img></p> </article> </main>
.png)