Investigating cloud metadata with JPQL (JSON Path Query Language)

Query Template

Use this base pattern for all TotalCloud queries:

Basic structure:

SELECT <alias_or_fields> 
FROM cloud.resource 
WHERE (<filters>) AS <alias>

Each query must include FROM cloud.resource, a $.streamName filter in the WHERE clause, and an alias defined with AS.

Example:

SELECT r 
FROM cloud.resource 
WHERE ($.streamName = "DescribeStacksStream" AND $.data.StackName = "my-stack") 
AS r

Boolean Logic

Use AND, OR, and NOT to combine predicates. The query engine processes them as follows:

AND - Both sides are classified independently. Predicates that can be pushed to Elasticsearch are evaluated there; any remaining predicates are evaluated in DuckDB.

OR - If any branch of an OR expression is residual (cannot be pushed to Elasticsearch), the entire OR expression is evaluated in DuckDB.

NOT - Can be pushed to Elasticsearch only when the inner expression is fully pushable.

Examples:

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeEC2InstancesStream"
      AND $.data.state = "running"
      AND NOT $.data.region = "us-east-1") AS r

SELECT r FROM cloud.resource
    WHERE ($.streamName = "GetElbV2ListenersStream"
      AND ($.data.Protocol CONTAINS "HTTP" OR $.data.Protocol CONTAINS "HTTPS")) AS r

Comparison Operators

Use comparison operators to match field values exactly or by range. Numeric and date fields support range operators (GT, GTE, LT, LTE, BETWEEN). The EQ and NEQ operators are supported on text, numeric, boolean, and date fields.

Operator	Description	Syntax
`EQ` or `=`	Exact equality. Case-sensitive for text; numeric equality for numbers and dates.	`$.path = "value"` or `$.path EQ "value"`
`NEQ` or `!=`	Not equal. Inverse of EQ.	`$.path != "value"` or `$.path NEQ "value"`
`GT`	Greater than Numeric and date fields only.	`$.path GT value`
`GTE`	Greater than or equal. Numeric and date fields only.	`$.path GTE value`
`LT`	Less than. Numeric and date fields only.	`$.path LT value`
`LTE`	Less than or equal. Numeric and date fields only.	`$.path LTE value`
`BETWEEN`	Inclusive range check. Numeric and date fields only.	`$.path BETWEEN lo AND hi`

Find a CloudFormation stack by exact name:

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeStacksStream" AND $.data.StackName = "my-stack") AS r

Find security groups with ports in a specific range using GT and LTE:

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeEC2SecurityGroupsStream"
      AND $.data.ipPermissions[*].fromPort GT 1023
      AND $.data.ipPermissions[*].toPort LTE 65535) AS r

Find security groups with ports in a well-known range using BETWEEN:

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeEC2SecurityGroupsStream"
      AND $.data.ipPermissions[*].fromPort BETWEEN 8000 AND 9000) AS r

Membership Operators

Use membership operators to test whether a field value belongs to a set of values. Array-aware operators such as ANYOF, NONEOF, SUBSETOF, and INTERSECTS are designed for array-typed fields.

Operator	Description	Syntax
`IN`	Value is one of the listed values. Works like SQL IN.	`$.path IN ("v1", "v2")`
`ANYOF`	Field value matches any of the given values. Array-aware: returns true if any array element matches.	`$.path ANYOF ("v1", "v2")`
`NONEOF`	Field value matches none of the given values. Inverse of ANYOF.	`$.path NONEOF ("v1", "v2")`
`MEMBER_OF`	Checks if the field value is a member of the given set. Best suited for array-typed fields.	`$.path MEMBER_OF ("v1", "v2")`
`SUBSETOF`	All elements of the field's array are contained within the given set. Evaluated in DuckDB.	`$.path SUBSETOF ("v1", "v2")`
`INTERSECTS`	The field's array has at least one element in common with the given set. Evaluated in DuckDB.	`$.path INTERSECTS ("v1", "v2")`

Find load balancer listeners using HTTP or HTTPS (ANYOF):

SELECT r FROM cloud.resource
    WHERE ($.streamName = "GetElbV2ListenersStream"
      AND $.data.Protocol ANYOF ("HTTP", "HTTPS")) AS r

Find listeners not using TCP or UDP (NONEOF):

SELECT r FROM cloud.resource
    WHERE ($.streamName = "GetElbV2ListenersStream"
      AND $.data.Protocol NONEOF ("TCP", "UDP")) AS r

Find roles whose permissions are fully within a safe set (SUBSETOF):

SELECT r FROM cloud.resource
    WHERE ($.streamName = "AccountAuthDetailRoleStream"
      AND $.data.permissions SUBSETOF ("read", "list")) AS r

Find EC2 instances attached to specific security groups (INTERSECTS):

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeEC2InstancesStream"
      AND $.data.securityGroups INTERSECTS ("sg-00001", "sg-00002")) AS r

Text / Pattern Operators

Use text operators to search string fields by substring, prefix, suffix, wildcard, or regular expression. These operators are not valid on numeric or date fields. CONTAINS, SUFFIX, and LIKE are always evaluated in DuckDB. PREFIX and MATCHES_GLOB may be pushed to Elasticsearch for structured keyword fields.

Operator	Description	Syntax
`CONTAINS`	Substring match. Case-sensitive. Matches any field value that contains the given string.	`$.path CONTAINS "substring"`
`PREFIX`	Value starts with the given prefix.	`$.path PREFIX "prefix"`
`SUFFIX`	Value ends with the given suffix. Always evaluated in DuckDB.	`$.path SUFFIX "suffix"`
`MATCHES`	Regular expression match.	`$.path MATCHES "regex"`
`LIKE`	SQL-style wildcard match. Use `%` for any sequence of characters and `_` for a single character. Always evaluated in DuckDB.	`$.path LIKE "pattern%"`
`MATCHES_GLOB`	Glob-style wildcard match. Use `*` for any sequence of characters and `?` for a single character.	`$.path MATCHES_GLOB "glob*"`

Find stacks whose name contains a specific substring (CONTAINS on a scalar field):

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeStacksStream"
      AND $.data.StackName CONTAINS "StackSet-qualys-cloudtrail") AS r

Find roles where a nested array field contains a specific policy name (CONTAINS on an array field):

SELECT a FROM cloud.resource
    WHERE ($.streamName = "AccountAuthDetailRoleStream"
      AND $.data.AttachedManagedPolicies[*].PolicyName CONTAINS "116"
      AND $.data.AttachedManagedPolicies[*].PolicyArn CONTAINS "Amazon_EventBridge_Invoke_Api_Destination_1162039806") AS a

Find stacks whose name starts with a specific prefix (PREFIX):

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeStacksStream"
      AND $.data.StackName PREFIX "StackSet-qualys") AS r

Find stacks using a SQL-style wildcard (LIKE):

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeStacksStream"
      AND $.data.StackName LIKE "StackSet-%") AS r

Find stacks using a glob wildcard (MATCHES_GLOB):

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeStacksStream"
      AND $.data.StackName MATCHES_GLOB "StackSet-qualys-*") AS r

Find stacks using a regular expression (MATCHES):

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeStacksStream"
      AND $.data.StackName MATCHES "StackSet-.*-cross-region") AS r

Null and Presence Operators

Use these operators to check whether a field exists, is null, or is an empty array or string. EXISTS is pushable to Elasticsearch for most field types. IS_NULL and IS_EMPTY are always evaluated in DuckDB.

Operator	Description	Syntax
`EXISTS`	Field is present and not null.	`$.path EXISTS`
`IS NULL`	Field is null or missing.	`$.path IS NULL`
`IS EMPTY`	Field is an empty array or empty string.	`$.path IS EMPTY`

Find instances that have a public IP address assigned (EXISTS):

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeEC2InstancesStream"
      AND $.data.publicIpAddress EXISTS) AS r

Find SNS topics with no access policy set (IS NULL):

SELECT r FROM cloud.resource
    WHERE ($.streamName = "GetTopicAttributeStream"
      AND $.data.Policy IS NULL) AS r

Find IAM roles with no managed policies attached (IS EMPTY):

SELECT r FROM cloud.resource
    WHERE ($.streamName = "AccountAuthDetailRoleStream"
      AND $.data.AttachedManagedPolicies IS EMPTY) AS r

IP and Network Operators

Use IP operators to filter resources by IP address or port. These operators work on both scalar and array IP fields and are always evaluated in DuckDB using auto-registered macros.

Operator	Description	Syntax
`IN_CIDR`	IP address falls within the given CIDR block.	`$.path IN_CIDR "10.0.0.0/8"`
`IN_IP_RANGE`	IP address falls within an inclusive range defined by a lower and upper bound.	`$.path IN_IP_RANGE ("lo", "hi")`
`IN_PORT_RANGE`	Port number falls within a numeric range.	`$.path IN_PORT_RANGE (lo, hi)`

Find EC2 instances with a private IP in the corporate network range (IN_CIDR):

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeEC2InstancesStream"
      AND $.data.privateIpAddress IN_CIDR "10.0.0.0/8") AS r

Find EC2 instances with a private IP within a specific host range (IN_IP_RANGE):

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeEC2InstancesStream"
      AND $.data.privateIpAddress IN_IP_RANGE ("10.0.0.1", "10.0.0.255")) AS r

Find security group rules with ports in the non-privileged range (IN_PORT_RANGE):

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeEC2SecurityGroupsStream"
      AND $.data.ipPermissions[*].fromPort IN_PORT_RANGE (1024, 65535)) AS r

Special Operators

These operators apply computed values or namespace-qualified functions as predicates. They are always evaluated in DuckDB.

Operator	Description	Syntax
`NUMBER OF WORDS`	Counts whitespace-delimited words in the field value and compares to a number.	`NUMBER OF WORDS($.path) GT n`
`AGE_IN_DAYS`	Computes age of a date field in days from today. Valid on date fields only. Use via the `_DateTime` namespace.	`_DateTime.ageInDays($.path) GT n`
Namespace function predicate	A namespace-qualified function call used as a predicate. See Namespace Functions for all available functions.	`_Namespace.fn($.path) = value`

Find EC2 instances launched more than 90 days ago:

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeEC2InstancesStream"
      AND _DateTime.ageInDays($.data.launchTime) GT 90) AS r

Find resources whose description has more than 5 words:

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeEC2InstancesStream"
      AND NUMBER OF WORDS($.data.description) GT 5) AS r

Find SNS topics with a safe access policy:

SELECT r FROM cloud.resource
    WHERE ($.streamName = "GetTopicAttributeStream"
      AND _AWSSNS.isSnsAccessPolicySafe($.data.Policy) = true) AS r

Array Modifiers

Array modifiers prefix an operator to control how it applies across the elements of an array field. Use ANY and NONE for Elasticsearch-pushable checks; use ALL and SIZE for DuckDB-evaluated checks.

Modifier	Engine	Description	Example
`ANY`	ES	True if any array element satisfies the predicate.	`ANY $.data.ports EQ 443`
`NONE`	ES	True if no array element satisfies the predicate.	`NONE $.data.tags EQ "public"`
`ALL`	DuckDB	True if all array elements satisfy the predicate.	`ALL $.data.protocols EQ "HTTPS"`
`SIZE`	DuckDB	Applies a numeric comparison to the count of array elements.	`SIZE $.data.attachedPolicies GT 2`

Namespace Functions

Namespace functions are called using the syntax _Namespace.functionName(args) and are always evaluated in DuckDB. Use them as predicates in your WHERE clause.

_DateTime - Date and Time Functions

Use _DateTime functions to filter resources by the age or relative date of a date field.

Function	Description
`_DateTime.ageInDays($.path)`	Returns the number of days elapsed since the date field value.
`_DateTime.ageInMonths($.path)`	Returns the number of months elapsed since the date field value.
`_DateTime.ageInYears($.path)`	Returns the number of years elapsed since the date field value.
`_DateTime.daysBetween($.path1, $.path2)`	Returns the number of days between two date fields.
`_DateTime.today()`	Returns the current date as a literal value for use in comparisons.

Find EC2 instances launched more than 90 days ago:

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeEC2InstancesStream"
      AND _DateTime.ageInDays($.data.launchTime) GT 90) AS r

Find resources created more than 6 months ago:

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeEC2InstancesStream"
      AND _DateTime.ageInMonths($.data.launchTime) GT 6) AS r

Find certificates that expire before today:

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeCertificatesStream"
      AND $.data.notAfter LT _DateTime.today()) AS r

_IPAddress - IP Address Functions

Use _IPAddress functions to filter resources by whether an IP address or array of IP addresses falls within a CIDR block or a defined range.

Function	Description
`_IPAddress.inCIDRRange($.path, "cidr")`	True if the IP address is within the given CIDR block.
`_IPAddress.areAllInCIDRRange($.path, "cidr")`	True if all IP addresses in an array field are within the CIDR block.
`_IPAddress.areAnyOutsideCIDRRange($.path, "cidr")`	True if any IP address in an array field falls outside the CIDR block.
`_IPAddress.inRange($.path, "lo", "hi")`	True if the IP address falls within the inclusive range defined by low and high bounds.

Find instances with a private IP in the corporate network:

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeEC2InstancesStream"
      AND _IPAddress.inCIDRRange($.data.privateIpAddress, "10.0.0.0/8") = true) AS r

Find security groups with any IP rule outside the allowed CIDR range:

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeEC2SecurityGroupsStream"
      AND _IPAddress.areAnyOutsideCIDRRange($.data.ipPermissions[*].cidrIp, "10.0.0.0/8") = true) AS r

_Port - Port Functions

Use _Port functions to filter resources by whether a port number falls within a defined range.

Find security group rules with high-number ports open:

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeEC2SecurityGroupsStream"
      AND _Port.inRange($.data.ipPermissions[*].fromPort, 1024, 65535) = true) AS r

_AWSSNS - AWS SNS Functions

Use _AWSSNS functions to evaluate the safety of SNS topic access policies. The function returns true for null policies and false when a wildcard principal is present without conditions.

Find SNS topics with a safe access policy:

SELECT r FROM cloud.resource
    WHERE ($.streamName = "GetTopicAttributeStream"
      AND _AWSSNS.isSnsAccessPolicySafe($.data.Policy) = true) AS r

Find SNS topics whose access policy is publicly exposed:

SELECT r FROM cloud.resource
    WHERE ($.streamName = "GetTopicAttributeStream"
      AND _AWSSNS.isSnsAccessPolicySafe($.data.Policy) = false) AS r

_AWSVPC - AWS VPC Functions

Use _AWSVPC functions to evaluate the safety of VPC Gateway Endpoint policies.

Find VPC Gateway Endpoints with a safe policy:

SELECT r FROM cloud.resource
    WHERE ($.streamName = "DescribeVpcEndpointsStream"
      AND _AWSVPC.isGatewayEndpointPolicySafe($.data.PolicyDocument) = true) AS r

Join Queries

Use a JOIN to correlate resources across two streams. Specify a join key in the ON clause using EQ. Use a HAVING clause to filter join results by the count of matching related records.

Basic join syntax:

SELECT <aliases>
    FROM cloud.resource WHERE (<filters_a>) AS a
    JOIN cloud.resource WHERE (<filters_b>) AS b
    ON (a.$.path EQ b.$.path)

Find application load balancers that have at least one HTTP listener:

SELECT alb, lsn
    FROM cloud.resource WHERE ($.streamName = "GetELBV2ListStream"
      AND $.data.type = "application") AS alb
    JOIN cloud.resource WHERE ($.streamName = "GetElbV2ListenersStream"
      AND $.data.Protocol ANYOF ("HTTP")) AS lsn
    ON (alb.$.data.loadBalancerArn EQ lsn.$.data.loadBalancerArn)

Find EC2 instances in a specific VPC CIDR range:

SELECT i FROM cloud.resource WHERE ($.streamName = "DescribeEC2InstancesStream") AS i
    JOIN cloud.resource WHERE ($.streamName = "DescribeEC2VpcsStream"
      AND $.data.cidrBlock = "172.31.0.0/16") AS v
    ON (i.$.data.instancesSet.item.vpcId EQ v.$.data.vpcId)

Use HAVING COUNT to filter ALBs with two or more HTTP or HTTPS listeners:

SELECT alb
    FROM cloud.resource WHERE ($.streamName = "GetELBV2ListStream"
      AND $.data.type = "application") AS alb
    JOIN cloud.resource WHERE ($.streamName = "GetElbV2ListenersStream"
      AND $.data.Protocol ANYOF ("HTTP", "HTTPS")) AS lsn
    ON (alb.$.data.loadBalancerArn EQ lsn.$.data.loadBalancerArn)
    HAVING COUNT(lsn) >= 2

Last updated: July, 2026