AMA: How to work with date comparisons


J1_Ambassador Hunter @2x

"Are 'WHERE' and 'WITH' clauses always supposed to be of the syntactic form (PROPERTY) (OP) (EXPRESSION)? 

Clearly find aws_iam_access_key as k where k.createdOn > - 90 days return count(k) as value parses, but is find aws_iam_access_key as k where - k.createdOn < 90 days return count(k) as value 'illegal' in J1QL because the LHS of the 'WHERE' clause syntactically illegal?  To my eye it seems they should yield the same result"

--Question from Ralph on the J1 Community Slack


Answer from Akash, Director, Solutions Architecture

Hi Ralph.

The primary reason is because we don’t allow for general datetime or mathematical expressions in J1QL queries today, only specific ones. We cannot perform the - k.createdOn on the LHS.

This similar query is valid:

find aws_iam_access_key as k where - 90 days < k.createdOn return count(k) as value
This similar query is invalid
find aws_iam_access_key as k where k.createdOn > - date(2021-10-5) return count(k) as value
And this query is valid:
find aws_iam_access_key as k where k.createdOn > date(2021-10-05) return count(k) as value
This is because we can specifically only perform relative date (now minus a number of hours, days, months, etc.) and static date comparisons per these docs.
Hope this helps! Mathematically what you suggested is equivalent, generally speaking.
-- Akash
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 

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
  19.  AMA: What permissions are needed for AWS
  20.  AMA: How to disable a policy
  21.  AMA: AWS security groups that have ingress rules of
  22.  AMA: Add custom tags to saved queries
  23.  AMA: J1 Query showing 2FA not Enabled
  24.  AMA: Query that can pull all "High" or "Critical" findings
  25.  AMA: How to work with date comparisons

Resources for this AMA


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.


cyber-security 1

Ad Title Placeholder

Lorem ipsum dolor sit amet, consectetur adipiscing elit.