How does JupiterOne handle date comparisons?

john chavez

"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

[Deleted User]

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.