Sample Queries

Basic Filter

Data
{
    "userIdentity": {
        "accessKeyId": "123456789"
    },
    "eventSource": "ec2.amazonaws.com"
}

Query
select eventSource from aws_cloudtrail where userIdentity.accessKeyId = 'EXAMPLE_KEY_ID'

Result
| Time | eventSource       |
|------|-------------------|
| ...  | ec2.amazonaws.com |

Order by

Data
{
    "eventSource": "ec2.amazonaws.com",
    "eventName": "AuthorizeSecurityGroupIngress",
}
{
    "eventSource": "ec2.amazonaws.com",
    "eventName": "StartInstances",
}

Query
select eventSource, eventName from aws_cloudtrail order by eventName desc

Result
| Time | eventSource       | eventName                     |
|------|-------------------|-------------------------------|
| ...  | ec2.amazonaws.com | StartInstances                |
| ...  | ec2.amazonaws.com | AuthorizeSecurityGroupIngress |

Count + In

Data
{
    "eventSource": "ec2.amazonaws.com",
    "eventName": "AuthorizeSecurityGroupIngress",
}
{
    "eventSource": "s3.amazonaws.com",
    "eventName": "PutObject",
}
{
    "eventSource": "sts.amazonaws.com",
    "eventName": "AssumeRole",
}

Query
select count(*) AS c from aws_cloudtrail where eventSource in ('ec2.amazonaws.com', 'iam.amazonaws.com')

Result
| c   |
|-----|
| 2   |

Group By + Distinct Count

Data
{
    "userIdentity": {
        "userName": "Kaushik",
    },
    "eventSource": "ec2.amazonaws.com",
}
{
    "userIdentity": {
        "userName": "Kaushik",
    },
    "eventSource": "ec2.amazonaws.com",
}
{
    "userIdentity": {
        "userName": "Jun",
    },
    "eventSource": "ec2.amazonaws.com",
}
{
    "userIdentity": {
        "userName": "Jun",
    },
    "eventSource": "s3.amazonaws.com",
}

Query
select userIdentity.userName, count(distinct eventSource) as c from aws_cloudtrail ct1 group by userIdentity.userName

Result
| userIdentity.userName | c |
|-----------------------|---|
| Kaushik               | 1 |
| Jun                   | 2 |

Subquery Filter

Data in aws_cloudtrail
{
    "userIdentity": {
        "accessKeyId": "EXAMPLE_KEY_ID"
    },
    "eventName": "GetObject",
}
{
    "userIdentity": {
        "accessKeyId": "DIFFERENT_KEY_ID"
    },
    "eventName": "RunInstances",
}

Data in other_data
{
    "userIdentity": {
        "accessKeyId": "EXAMPLE_KEY_ID",
        "userName": "Milo"
    }
}
{
    "userIdentity": {
        "accessKeyId": "DIFFERENT_KEY_ID",
        "userName": "Ollie"
    }
}

Query
select eventName
from aws_cloudtrail
where userIdentity.accessKeyId in (select userIdentity.accessKeyId from other_data where userName = 'Milo')

Result
| Time | eventName |
|------|-----------|
| ...  | GetObject |

Select JSON Nested Array Path

Data
{
    "requestParameters": {
        "ipPermissions": {
            "items": [
                {
                    "ipRanges": {
                        "items": [
                            {
                                "cidrIp": "0.0.0.0/0"
                            }
                        ]
                    },
                    "toPort": 500
                },
                {
                    "ipRanges": {
                        "items": [
                            {
                                "cidrIp": "0.0.0.0/0"
                            }
                        ]
                    },
                    "toPort": 800
                }
            ]
        }
    },
    "eventName": "AuthorizeSecurityGroupIngress"
}

Query
select JSON_QUERY($,'$.requestParameters.ipPermissions.items[*].toPort') as toPorts,
JSON_QUERY($,'$.requestParameters.ipPermissions.items[*].ipRanges.items[*].cidrIp') as cidrIPs
from aws_cloudtrail
where eventName = 'AuthorizeSecurityGroupIngress'

Result
| Time | toPorts    | cidrIps                    |
|------|------------|----------------------------|
| ...  | [500, 800] | ["0.0.0.0/0", "0.0.0.0/0"] |

Filter JSON Array

Data
{
    "requestParameters": {
        "groupId": "sg-0b128b58ba5cfd7fd",
        "ipPermissions": {
            "items": [
                {
                    "ipRanges": {
                        "items": [
                            {
                                "cidrIp": "0.0.0.0/0"
                            }
                        ]
                    }
                }
            ]
        }
    }
}

Query
select requestParameters.groupId as sg
from aws_cloudtrail
where JSON_VALUE($,'$.requestParameters.ipPermissions.items[*].ipRanges.items[*].cidrIp') = '0.0.0.0/0'

Result
| Time | sg                   |
|------|----------------------|
| ...  | sg-0b128b58ba5cfd7fd |

Filter Array All

In the following examples, we are looking for data that contains an array at the specified path, in which the specified operator returns true when applied to each element of the array.

Data Primitive
{
    "user": "Bob",
    "abc": {
        "items": [4, 5, 6]
    }
}
{
    "user": "Alice",
    "abc": {
        "items": [2, 3, 4]
    }
}

Query Primitive
select user
from test_data
where array abc.items contains all < 3

Result Primitive
| Time | user |
|------|------|
| ...  | Bob  |

Data Object
{
    "user": "Bob",
    "abc": {
        "items": [{"foo": 4}, {"foo": 5}]
    }
}
{
    "user": "Alice",
    "abc": {
        "items": [{"foo": 2}, {"bar": 3}]
    }
}

Query Object
select user
from test_data
where array abc.items contains all (foo > 3)

Result Object
| Time | user |
|------|------|
| ...  | Bob  |

Filter Array Exact

In the following examples, we are looking for data that contains an array at the specified path equivalent to the array specified in the query.

Data
{
    "user": "Bob",
    "abc": {
        "items": [4, 5, 6]
    }
}
{
    "user": "Alice",
    "abc": {
        "items": [2, 3, 4]
    }
}

Query
select user
from test_data
where array abc.items = [2, 3, 4]

Result
| Time | user   |
|------|--------|
| ...  | Alice  |

Filter Array Any

In the following examples, we are looking for data that contains an array at the specified path equivalent to the array specified in the query.

Data Primitive
{
    "user": "Bob",
    "abc": {
        "items": [4, 5, 6]
    }
}
{
    "user": "Alice",
    "abc": {
        "items": [2, 3, 4]
    }
}

Query Primitive
select user
from test_data
where array abc.items contains any < 2

Result Primitive
| Time | user   |
|------|--------|
| ...  | Bob    |
| ...  | Alice  |

Data Object
{
    "user": "Bob",
    "abc": {
        "items": [{"foo": 2}, {"bar": 3}]
    }
}
{
    "user": "Alice",
    "abc": {
        "items": [{"foo": 2}, {"bar": 3}]
    }
}

Query Object
select user
from test_data
where array abc.items contains any (foo > 2)

Result Object
| Time | user   |
|------|--------|
| ...  | Bob    |
| ...  | Alice  |

Filter Array None

Data
{
    "requestParameters": {
        "groupId": "sg-example-1",
        "ipPermissions": {
            "items": [
                {
                    "ipRanges": {
                        "items": [
                            {
                                "cidrIp": "0.0.0.0/0"
                            }
                        ]
                    },
                    "toPort": 8080
                },
                {
                    "ipRanges": {
                        "items": [
                            {
                                "cidrIp": "1.1.1.1/24"
                            }
                        ]
                    },
                    "toPort": 9000
                }
            ]
        }
    },
    "eventName": "AuthorizeSecurityGroupIngress"
}
{
    "requestParameters": {
        "groupId": "sg-example-2",
        "ipPermissions": {
            "items": [
                {
                    "ipRanges": {
                        "items": [
                            {
                                "cidrIp": "8.8.8.8/24"
                            }
                        ]
                    },
                    "toPort": 8080
                },
                {
                    "ipRanges": {
                        "items": [
                            {
                                "cidrIp": "8.8.8.8/24"
                            }
                        ]
                    },
                    "toPort": 8123
                }
            ]
        }
    },
    "eventName": "AuthorizeSecurityGroupIngress"
}

Query
select requestParameters.groupId as sg
from aws_cloudtrail
where array requestParameters.ipPermissions.items contains none (
    toPort = 8080
    and array ipRanges.items contains any (cidrIp = '0.0.0.0/0')
)

Results
| Time | sg                   |
|------|----------------------|
| ...  | sg-example-2         |

Basic Filter​

Order by​

Count + In​

Group By + Distinct Count​

Subquery Filter​

Select JSON Nested Array Path​

Filter JSON Array​

Filter Array All​

Filter Array Exact​

Filter Array Any​

Filter Array None​