Filters & Queries

A brief introduction to searches and queries

Search & Filters

The CloudQuery Asset Inventory is designed to help you find assets and anything related fast. All categorized resources can be searched by simply typing your search query in the Asset Inventory filter.

Try searching for an IP address or instance ID like this:

192.168.1.*

i-02f488a70bb73b315

The search index covers all categorized resources and their columns (including JSON columns). This means searching an instance ID may lead to listing attached interfaces or volumes as well.

You can additionally filter the Asset Inventory using a special 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, and used via the API.

For more details on how to use filters, see Search & 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.