AMA: Sub-Queries in J1QL

circle
circle

AMA is an ongoing series published each Thursday, highlighting questions the community has asked in our support and how-to channels on Slack. If you haven't already done so, please join us on Slack 

"How can I do sub-queries in J1QL like this (trying to find all instances that have an image id from a sub-query that returns all EKS node image IDs):"

FIND aws_instance 
WITH ImageID = (FIND aws_ami WITH displayName ^= "amazon-eks-node-"
RETURN aws_ami.id)

--Question from David on the J1 Community Slack

 

Answer from J1 Champion, Adam

Chained / nested queries are not supported yet. This is a commonly requested feature.There are often relationships that support this kind of thing without needing this feature. For example, based on the query shared, you can maybe do:

FIND UNIQUE aws_ami
WITH
displayName ^= "amazon-eks-node-"
AS img
THAT USES aws_instance AS inst
RETURN
img.id AS ImageId

The above gives a list of unique AMI IDs in use by your instances, which is what I think you want. For fun, you can modify this a bit to get a list of your instances using the target AMIs themselves (different than what was in your original query, but maybe still useful to think about):

FIND aws_ami
WITH
displayName ^= "amazon-eks-node-"
AS img
THAT USES aws_instance AS inst
RETURN
inst.tag.AccountName AS AWSAccount,
inst.displayName AS InstanceName,
inst.id AS InstanceId,
inst.webLink AS InstanceLink,
img.id AS ImageId,
img.webLink AS ImageLink
ORDER BY
AWSAccount, InstanceName

This example also gives links to the instances and the AMI images as well. I hope these two are helpful for what you're trying to accomplish.Update: Re-reading your original post, the second example here may be more what you are looking for. Either way, hopefully you got what you need between them.
 
Response from David: Yep second example is what I need, thank you!

Articles in this Series

  1. AMA: JupiterOne and PagerDuty WebHooks
  2. AMA: Export YAML Files for Vendors Pulled from SSO Providers
  3. AMA: Map Controls and Frameworks Relationships
  4. AMA: How to Track Professional Associations, Security Forums, and Threat Intel Sources
  5. AMA: Find AWS Instances by their Private IP Address
  6. AMA: SSO Integrated Authentication, Move to Another Role
  7. AMA: send-mail action to map two properties
  8. AMA: Setup the Policy Accept Button
  9. AMA: Match a blank field in J1QL
  10. AMA: Track and Alert Using Firewall Rules Matrix
  11. AMA: AWS Roles not used for 90 days, and date related queries
  12. AMA: Manage Alerts for Jira through J1 Terraform Provider
  13. AMA: Filter Results from Specific AWS Accounts
  14. AMA: Iterate and return total count of iam users per account
  15. AMA: Search for security groups by tag
  16. AMA: Identify S3 Buckets Open to Cross-Account Attacks
  17. AMA: Resources for J1 DevOps Use Cases
  18. AMA: Sub-Queries in J1QL

Resources for this AMA

avatar

Posted By JupiterOne Team

The JupiterOne Team is a diverse set of engineers and developers who are working on the next generation of cyber asset visibility and monitoring.

To hear more from the JupiterOne Team, get our newsletter. No spam, just the good stuff once or twice a month. Sign up below.

PREVIOUS ARTICLE

cyber-security 1

Ad Title Placeholder

Lorem ipsum dolor sit amet, consectetur adipiscing elit.