Filters & Queries

A brief introduction to searches and queries

Filters

The CloudQuery Asset Inventory can be filtered using a special search syntax that looks like a simplified version of SQL. Filters are great for narrowing down a dataset.

For example, this filter will find an AWS EC2 instance with a particular ARN:

resource_type = "aws_ec2_instances" AND arn = "arn:aws:ec2:us-east-1:586794438123:instance/i-09140f4e77f963973"

The syntax is similar to a SQL WHERE clause. Unlike SQL, however, filters support wildcards using the *symbol. This filter finds all EC2 instances with an instance type starting with t:

resource_type = "aws_ec2_instances" AND instance_type = "t*"

This filter will return instances of type t2.micro, t3.medium, etc.

Filters can be saved, shared, used through the API, and used as the basis in other parts of the platform, such as dashboards and policies (coming soon).

For more details on how to use filters, see Filter Query Syntax.

Queries

Queries are just good ol' SQL. They can be written in the SQL Console. Queries can be saved, shared and used via the API, just like Searches. However, unlike Searches, Queries can perform complex joins and answer questions that are more complex than just finding a needle in a haystack.

For example, here is a query that finds all unassigned EC2 images, by joining the aws_ec2_imagestable with the aws_ec2_instancestable:

SELECT 
    img.image_id,
    inst.image_id as ec2_image, -- should all be NULL to show unassigned
    img.name,
    img.creation_date,
    img.state,
    img.tags
FROM 
    aws_ec2_images img
LEFT JOIN 
    aws_ec2_instances inst ON inst.image_id = img.image_id
WHERE 
    inst.image_id IS NULL -- AMIs not associated with any EC2 instance
    AND img.state = 'available'
    AND img.creation_date <= NOW() - INTERVAL '30 days'
ORDER BY 
    img.creation_date ASC;

Queries use ClickHouse SQL syntax. For more on this, see the ClickHouse documentation.