How does JupiterOne handle date comparisons?

"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 > date.now - 90 days return count(k) as value parses, but is find aws_iam_access_key as k where date.now - 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

Answers

  • Response from Akash, Solutions Architecture

    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 date.now - k.createdOn on the LHS.

    This similar query is valid:

    find aws_iam_access_key as k where date.now - 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.now - 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

    View the original post on the JupiterOne blog