JupiterOne Query Language Tutorial¶
Querying can be the most challenging, yet the most fun and rewarding part of the JupiterOne experience. Once you become familiar with the query language, we are certain that you will find yourself uncovering all sorts of previously undiscovered insight from your data.
The JupiterOne Query Language (aka “J1QL”) is a query language for finding the entities and relationships within your digital environment. J1QL blends together the capabilities of asking questions, performing full text search, or querying the complex entity-relationship graph.
There are plenty of pre-packaged queries you can easily use in the Landing app or browse in Query Library. This tutorial focuses instead on helping you construct custom queries yourself.
This tutorial builds on the full J1QL documentation using some common use cases.
Part 1 - Simple Root query¶
First, let’s try this query:
Find Account that relates to Root return tree
Please note the noun that immediately follows the verb is case sensitive:
TitleCaseword tells the query to search for entities of that class (e.g.
snake_caseword tells the query to search for entities of that type (e.g.
You should get a result that looks like this (the
return tree part of the query
tells it to show the graph view by default):
The selected node in the above example is the special
Root node, which
represents your organization. Depending on the number of integration
configurations you have, you’ll see different number of accounts connected,
showing that the
See the three sets of controls in the result panel. Starting from top right to bottom left –
The first set of controls (next to the query) allows you to:
- Switch views between Table, Graph, Raw JSON, and Pretty JSON.
- Share the query – shows a popup box with the weblink to copy and share.
- Save the query – give it a title, description, and optionally some tags to save it to your own query library.
- Close / remove this result panel from the page.
The second set of controls (above the selected entity node) allows you to:
- Show the detailed properties, tags and metadata of the selected entity.
- Expand the entity to see more of its connected neighbors - this will bring in additional data that may not have been returned by the original query, allowing you to further the search and analysis.
- Hide the selected entity node from the graph view - once you’ve hidden an entity, an unhide button will show up in the third set of controls at the bottom left of the graph, allowing you to unhide all currently hidden entities.
The last set of controls (at the bottom left corner) allows you to:
- Toggle the full screen mode.
- Opens up the filter panel to show/hide entities in the graph by account or entity type.
- Unhide all currently hidden entities (not shown in the above screenshot – it only shows up when there is at least one hidden entity).
See more details on the graph controls in this doc.
Part 2 - Infrastructure Analysis¶
Examples in this section require at least one AWS integration configuration.
If you’ve configured an AWS integration, you are now ready to try something a lot more interesting. Type in, or copy/paste the following query:
2a - SSH Key Usage Examples¶
Find AccessKey with usage='ssh'
This should find a set of
aws_access_keyentities used for SSH access into your EC2 instances, assuming you have some of those and they are configured to allow SSH access.
You can also query by the entity type instead of its class. The following query will get you the same result - unless you also have SSH Keys you’ve added from other integrations (non-AWS) or from the UI / API.
Now expand the search a little bit with the following:
Find Host as h that uses AccessKey with usage='ssh' as k return h.tag.AccountName, h._type, h.displayName, h.instanceId, h.region, h.availabilityZone, h.publicIpAddress, h.privateIpAddress, h.platform, h.instanceType, h.state, k._type, k.displayName
This finds the
AccessKeyand returns a set of specific properties. You can add or remove properties returned as desired.
Note the keyword
thatis what tells the query to traverse the graph to find connections/relationships between entities, followed by a verb that represents :) the relationship class.
Also keep in mind you can switch to the Graph view to get a more visual result, and continue to drill down interactively.
Again, you can query using the more specific entity types. For example:
Find aws_instance that uses aws_key_pair
Or mix and match them:
Find Host that uses aws_key_pair
Note that the relationship keyword/verb is not case sensitive.
2b - EBS Volume Examples¶
First, let’s see if there are any unencrypted EBS volumes:
Find aws_ebs_volume with encrypted != true
Note in the above query, the
withkeyword binds to the entity noun immediately to its left, and allows you to filter results on that entity’s property values.
If the above query finds some unencrypted EBS volumes, it’ll be interested to see what’s using them:
Find Host that uses aws_ebs_volume with encrypted != true
You can view the
aws_ebs_volumeentities and their relationships in the Graph mode, and further inspect the properties on each entity node or relationship edge. You can also expand to see more connected entities and relationships.
Are these actively in use? And in production?
Find Host with active = true and tag.Production = true that uses aws_ebs_volume with encrypted != true
What subnets are these instances in? Let’s also just return a few key properties from type of entities related in this search:
Find Network as n that has Host as h that uses aws_ebs_volume with encrypted != true and tag.Production = true as e return n.displayName, h._type, h.displayName, e.displayName, e.encrypted
OK. How about any EBS Volumes not actively in use? Perhaps some of them can be removed…
Find aws_ebs_volume that !uses Host
You may notice the above query feels backwards. That’s okay. The query will work the same way regardless of the direction of relationship. Because the query by default returns all properties from the initial set of entities, it is sometimes easier to reverse the query direction so that you get the data you’re looking for more easily.
Find Host that !uses aws_ebs_volume as v return v.*may feel more correct, but it is definitely a bit more to type out.
2c - Unencrypted Data¶
There are many types of data stores you may have in AWS. For example, EBS Volumes, S3 Buckets, RDS Clusters and Instances, DynamoDB Tables, Redshift Clusters, to name a few. You likely want them to be encrypted if they store confidential data.
How do you find out if that’s the case?
Find (aws_s3_bucket|aws_rds_cluster|aws_db_instance|aws_dynamodb_table|aws_redshift_cluster) with encrypted!=true
The above query will certainly do the job, but it’s quite complicated. This is where the abstract class labeling automatically assigned by JupiterOne serves its purpose. Querying by class makes it a whole lot simpler:
Find DataStore with encrypted != true
Now, you can start adding a few property filters to make the results much more focused, to help cut down the noise or to prioritize remediation. For example:
Find DataStore with encrypted != true and tag.Production = true and (classification = 'confidential' or classification = 'restricted')
2d - Tagging Resources¶
As you can see from some of the earlier examples, tagging resources can be very useful operationally. That’s why we highly recommend tagging your resources at the source. These tags will be ingested by JupiterOne and you can use them in your custom queries.
By default, the packaged queries provided by JupiterOne, as seen in the Query Library from the Landing app and used in the Compliance app, rely on the following tags:
booleantags to indicate data type)
All custom tags ingested by JupiterOne integrations are prefixed with
tag.<TagName>. They need to be used as such in the query.
Owner tags are automatically captured as properties
so they can be used directly in the query without the
tag. prefix - in all
classification = '...' or
boolean) tags can be
added by JupiterOne as part of the Advanced Options in each integration
2e - Network Resources and Configurations¶
You may have a number of questions to ask or confirm about your network resources and their configurations. Here are a few examples.
Let’s start with finding a few network resources and their connections:
Find (Gateway|Firewall) with category='network' that relates to * return tree
Keep in mind you can toggle the result back to Table view if you’d like.
How about networks and subnets?
Find Network that contains Network return tree
Or resources in a VPC:
Find Network that has (Host|Cluster|Database) return tree
The result looks like this (you may have a lot more going on than what’s shown here from the demo environment):
Note that the properties panel for the selected
webLinkthat will allow you to quickly get to the source directly in the AWS web console.
In AWS, you most likely have set up CloudFront distributions to distribute traffic to your API Gateways or static websites hosted in S3. What does that look like?
Find aws_cloudfront_distribution that relates to * return tree
Here, the result looks a little busier, from a J1 account with multiple AWS integration configurations and quite a few
aws_cloudfront_distributionentities and relationships.
This graph shows you the origins connected to the distributions: both S3 buckets (for static website/contents) and API Gateways. Additionally, the graph shows you the ACM Certificate being used by them and the WAF ACL, if any, configured to protect them.
Keep in mind you can select any entity node in the graph to inspect its detailed properties, or find a web link to quick get to the source in AWS web console.
If you use AWS Transfer for SFTP, you can find the Transfer Servers, Users, which IAM Roles are assigned to them, and which S3 Buckets the users have access to.
Find aws_account that HAS aws_transfer that HAS Host that HAS User that RELATES TO * return tree
You’ll get a visual that looks like this:
2f - Serverless Functions¶
Are you using serverless (lambda functions)? If you are, here are a few things that may help you see how they are set up.
Let’s start with a listing of your lambda functions:
Simple. Now, what triggers each function?
find aws_lambda_function as function that TRIGGERS * as trigger return trigger._type, trigger.displayName, trigger.arn, trigger.webLink, function.functionName, function.arn, function.webLink
Are there lambda functions with access to resources in a VPC?
Find aws_lambda_function that has aws_vpc return tree
The above query will give you a visual graph of the lambda functions and the VPC they are configured to run inside.
It is actually a best practice to not run lambda functions without access to a VPC unless they need direct access to resources within one – for example, EC2 instances, RDS databases, or ElasticSearch/ElastiCache.
Is inbound SSH allowed directly from an external host or network?
Find Firewall as fw that ALLOWS as rule (Host|Network) with internal=false or internal=undefined as src where rule.ingress=true and (rule.fromPort<=22 and rule.toPort>=22) return fw._type, fw.displayName, rule.fromPort, rule.toPort, src.displayName, src.ipAddress, src.CIDR
Notice the above query uses
whereto filter the property values of the relationship. You can use both
whereto filter property values of entities. See the full J1QL documentation for more details.
Also keep in mind you can toggle to Graph View to see the above results more visually and interactively.
What production resources are directly connected/exposed to the Internet/everyone?
Find (Internet|Everyone) that relates to * with tag.Production=true and _class!='Firewall' and _class!='Gateway' return tree
What are my network layer resources?
Find (Firewall | Gateway) with category='network'
What about Security Group protection?
Find aws_security_group that PROTECTS aws_instance return tree
Pro Tip: selecting an edge in the graph to see the security group rule details (i.e. properties on the edge)
Part 3 - User and Access Analysis¶
Once you have an Okta or OneLogin integration configured, try some of these example queries yourself.
3a - IdP users and access¶
Examples in this section require an identity provider integration (Okta or OneLogin)
Are there system accounts do not belong to an individual employee/user?
Find User that !is Person
Userentities in JupiterOne are automatically mapped to a corresponding
_type: 'employee') entity, when there is at least one Identity Provider (IdP) integration configuration - such as Okta or OneLogin.
Pro Tip 1: Set the
userTypeproperty of the user profile in your IdP account to
'system' or 'generic' or 'bot'will prevent JupiterOne from creating a
Personentity for that user.
Pro Tip 2: Set the
aws_iam_useror other non-IdP users to be the email address of a Person / employee will allow JupiterOne to automatically map that User to its corresponding Person. Alternatively, you can add an
aws_iam_userfor the mapping to work.
Which active user accounts do not have multi-factor authentication enabled?
Find User with active = true and mfaEnabled != true that !(ASSIGNED|USES|HAS) mfa_device
Depending on the specific IdP integration, a
Userentity may have a relationship mapping to an
mfa_deviceinstead of the
mfaEnabledflag directly as a property.
Therefore, the above query finds all
Userentities with the
activeflag but not the
mfaEnabledflag set to true on its properties, and additionally, checks for the existence of an relationship between that
mfa_deviceassigned or in use.
Are there users accessing my ‘AWS’ application without using MFA?
Find User with active = true and mfaEnabled != true that ASSIGNED Application with displayName = 'Amazon Web Services'
Replace the string value of the
displayNameto check for another application.
You can also use
shortName = 'aws', which will check for all AWS application instances, if you have more than one AWS SAML app configured with your IdP.
Find all contractors and external users in the environment.
Find User that IS Person that !EMPLOYS Root
The above query finds user accounts belong to any individual not directly employed by your organization (
Find User as u that IS Person as p where u.userType='contractor' or p.employeeType='contractor'
The above query finds contractor users.
3b - Cloud users and access¶
Examples in this section require at least one AWS integration configuration.
Who has been assigned full Administrator access in AWS?
find (aws_iam_role|aws_iam_user|aws_iam_group) that ASSIGNED AccessPolicy with policyName='AdministratorAccess'
Which IAM roles are assigned which IAM policies?
find aws_iam_role as role that ASSIGNED AccessPolicy as policy return role._type as RoleType, role.roleName as RoleName, policy._type as PolicyType, policy.policyName as PolicyName
3c - Combined users and access across all environments¶
Examples in this section work best when there are both IdP and AWS integration configurations enabled in JupiterOne.
Who has access to what systems/resources?
Find (User|Person) as u that (ASSIGNED|TRUSTS|HAS|OWNS) (Application|AccessPolicy|AccessRole|Account|Device|Host) as a return u.displayName, u._type, u.username, u.email, a._type, a.displayName, a.tag.AccountName order by u.displayName
Part 4 - Cross Account Analysis¶
Many examples in this section requires both Okta and AWS integration configurations in JupiterOne, as well as an AWS SAML app configured in your Okta account. Some queries work best when you have multiple AWS configurations.
Who has access to my AWS accounts via single sign on (SSO)?
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
Are there assume role trusts from one AWS account to other external entities?
Find aws_account that HAS aws_iam that HAS aws_iam_role that TRUSTS (Account|AccessRole|User|UserGroup) with _source='system-mapper' return tree
Note from the above query,
_source='system-mapper'is an indicator that the trusted entity is not one ingested by an integration configuration, rather, mapped and created by JupiterOne during the analysis of Assume Role policies of the IAM roles in your account(s). Therefore, these entities are most likely external.
For example, you will most definitely see the JupiterOne integration IAM role with a
TRUSTSrelationship to the JupiterOne AWS account.
Part 5 - Endpoint Compliance¶
Examples in this section require the activation of at least one JupiterOne Endpoint Compliance Agent - powered by Stethoscope app.
Do I have local firewall enabled on end-user devices?
Find HostAgent as agent that MONITORS user_endpoint as device return device.displayName, device.platform, device.osVersion, device.hardwareModel, device.owner, agent.firewall, agent.compliant, agent._type, agent.displayName
Whose endpoints are non-compliant?
Find Person as person that OWNS (Host|Device) as device that MONITORS HostAgent with compliant!=true as agent return person.displayName, person.email, device.displayName, device.platform, device.osVersion, device.hardwareModel, device.owner, agent.compliant, agent._type, agent.displayName
What applications do those users have access to?
Find HostAgent with compliant!=true that MONITORS (Host|Device) that OWNS Person that IS User that Assigned Application return tree
Out of those above, any of them have access to AWS?
Find HostAgent with compliant!=true that MONITORS (Host|Device) that OWNS Person that IS User that (ASSIGNED|HAS) (aws_iam_role|aws_iam_policy|aws_iam_user_policy|aws_iam_group) return tree
The resulting graph may look like this: