Skip to main content

Sample AWS EC2 RQL Queries

note

The following guide will walk you through AWS EC2 RQL Query Examples

List all AWS Security Groups that have Internet Access and exclude certain ports.

config from cloud.resource where cloud.type = 'aws' AND api.name = 'aws-ec2-describe-security-groups' AND json.rule = (ipPermissions[*].ipv4Ranges[*].cidrIp contains 0.0.0.0/0 or ipPermissions[*].ipv6Ranges[*].cidrIpv6 contains ::/0) and ipPermissions[*].fromPort does not intersect (443, 500, 4500, 9021, 9092, 8083, 8088, 8090, 8082, 8081, 2181, 2888, 3888, 3780, 3781, 40815, 40814) and ipPermissions[*].toPort does not intersect (443, 500, 4500, 9021, 9092, 8083, 8088, 8090, 8082, 8081, 2181, 2888, 3888, 3780, 3781, 40815, 40814)

List all EC2 instances that have a connection open for RDP (public or specific) and that has a public ip address.

config from cloud.resource where api.name = 'aws-ec2-describe-security-groups' AND json.rule = (((ipPermissions[?(@.toPort > 3389 && @.fromPort < 3389)].ipRanges[*] contains 0.0.0.0/0) or (ipPermissions[?(@.toPort == 3389 || @.fromPort == 3389)].ipRanges[*] contains 0.0.0.0/0)) or ((ipPermissions[?(@.toPort > 3389 && @.fromPort < 3389)].ipv6Ranges[*].cidrIpv6 contains ::/0) or (ipPermissions[?(@.toPort == 3389 || @.fromPort == 3389)].ipv6Ranges[*].cidrIpv6 contains ::/0))) and isShared is false as X; config from cloud.resource where api.name = 'aws-ec2-describe-instances' AND json.rule = publicIpAddress exists as Y; filter '$.Y.securityGroups[*].groupId contains $.X.groupId'; show X;

List all instances that have a Public IP assigned, and are associated to an NSG that is open to the public.

config from cloud.resource where api.name = 'aws-ec2-describe-instances' AND json.rule = publicIpAddress exists and publicIpAddress is not empty as X; config from cloud.resource where api.name = 'aws-ec2-describe-security-groups' AND json.rule = ipPermissions[*].ipRanges[*] contains 0.0.0.0/0 or ipPermissions[*].ipv6Ranges[*].cidrIpv6 contains ::/0 as Y; filter '$.X.securityGroups[*].groupName == $.Y.groupName' ; show X;

List all security groups that are open to the public on port 3389 that are on a VPC that contains an IGW

config from cloud.resource where api.name = 'aws-ec2-describe-security-groups' AND json.rule = (((ipPermissions[?(@.toPort > 3389 && @.fromPort < 3389)].ipRanges[*] contains 0.0.0.0/0) or (ipPermissions[?(@.toPort == 3389 || @.fromPort == 3389)].ipRanges[*] contains 0.0.0.0/0)) or ((ipPermissions[?(@.toPort > 3389 && @.fromPort < 3389)].ipv6Ranges[*].cidrIpv6 contains ::/0) or (ipPermissions[?(@.toPort == 3389 || @.fromPort == 3389)].ipv6Ranges[*].cidrIpv6 contains ::/0))) and isShared is false as X; config from cloud.resource where api.name = 'aws-ec2-describe-internet-gateways' as Y; filter '$.Y.attachments[*].vpcId contains $.X.vpcId'; show X;

List all security groups that are open to the public on port 22 that are on a VPC that contains an IGW with an EC2 instance attached.

config from cloud.resource where api.name = 'aws-ec2-describe-security-groups' AND json.rule = (((ipPermissions[?(@.toPort > 22 && @.fromPort < 22)].ipRanges[*] contains 0.0.0.0/0) or (ipPermissions[?(@.toPort == 22 || @.fromPort == 22)].ipRanges[*] contains 0.0.0.0/0)) or ((ipPermissions[?(@.toPort > 22 && @.fromPort < 22)].ipv6Ranges[*].cidrIpv6 contains ::/0) or (ipPermissions[?(@.toPort == 22 || @.fromPort == 22)].ipv6Ranges[*].cidrIpv6 contains ::/0))) and isShared is false as X; config from cloud.resource where api.name = 'aws-ec2-describe-internet-gateways' as Y; config from cloud.resource where api.name = 'aws-ec2-describe-instances' as Z; filter '$.Z.securityGroups[*].groupId contains $.X.groupId and $.Y.attachments[*].vpcId contains $.X.vpcId'; show X;

Detect EC2 instances that have been existing longer than 30 dates(via the block device attachTime), where the volume isn't encrypted.

config from cloud.resource where api.name = 'aws-ec2-describe-instances' AND json.rule = "blockDeviceMappings[?(@.deviceName=='/dev/xvda'||@.deviceName=='/dev/sda1')].ebs.attachTime exists and (( _DateTime.ageInDays(blockDeviceMappings[?(@.deviceName=='/dev/xvda')].ebs.attachTime) > 30) or ( _DateTime.ageInDays(blockDeviceMappings[?(@.deviceName=='/dev/sda1')].ebs.attachTime) > 30))" as X; config from cloud.resource where api.name = 'aws-ec2-describe-volumes' AND json.rule = encrypted is false as Y; filter ' $.X.instanceId equals $.Y.attachments[*].instanceId '; show X;

Detect AMI images older than 90 days

config from cloud.resource where cloud.type = 'aws' AND cloud.service = 'EC2' AND api.name = 'aws-ec2-describe-images' AND json.rule = "_DateTime.ageInDays(image.creationDate) > 90"

Detect EC2 instances running AMIs older than 30 days

config from cloud.resource where api.name = 'aws-ec2-describe-instances' as X; config from cloud.resource where api.name = 'aws-ec2-describe-images' AND json.rule = "_DateTime.ageInDays(image.creationDate) > 30" as Y; filter ' $.X.imageId==$.Y.image.imageId '; show X;

Detect EC2 instances not using a specified AMI image

config where cloud.type = 'aws' AND api.name = 'aws-ec2-describe-instances' AND json.rule =  imageId does not contain <ami-AMI_ID>

Detect EC2 instances running for more than 1 day but less than 3 days

config from cloud.resource where api.name = 'aws-ec2-describe-instances' and json.rule = "_DateTime.ageInDays(launchTime) > 1 and state.code equals 16 and _DateTime.ageInDays(launchTime) < 3"

Detect CFT's that created public SecurityGroups

config from cloud.resource where api.name = 'aws-cloudformation-describe-stacks' as X; config from cloud.resource where api.name = 'aws-ec2-describe-security-groups' AND json.rule = ipPermissions[*].ipv6Ranges[*].cidrIpv6 contains ::/0 or ipPermissions[*].ipRanges[*] contains 0.0.0.0/0 as Y; filter '$.X.stackResources[*].physicalResourceId == $.Y.groupId'; show X;

Find flow logs of VPCs that have EC2 instances attached to verify if network flowlogs exist

config from cloud.resource where api.name = 'aws-ec2-describe-flow-logs' as X; config from cloud.resource where api.name = 'aws-ec2-describe-instances' as Y; filter '$.X.resourceId==$.Y.vpcId'; show X;

Find VPCs where Flow Logs are NOT enabled

config from cloud.resource where api.name = 'aws-ec2-describe-vpcs' as X; config from cloud.resource where api.name = 'aws-ec2-describe-flow-logs' as Y; filter ' not ($.Y.resourceId equals $.X.vpcId)'; show X;

List unattached security groups (in respect to EC2 instances)

config from cloud.resource where cloud.type = 'aws' AND api.name = 'aws-ec2-describe-security-groups' as X; config from cloud.resource where api.name = 'aws-ec2-describe-instances' as Y; filter ' not ($.Y.securityGroups[*].groupId contains $.X.groupId) '; show X;

Unattached security groups (in respect to network interfaces)

config from cloud.resource where api.name = 'aws-ec2-describe-security-groups' and json.rule = "groupName does not equal default" as X; config from cloud.resource where api.name = 'aws-ec2-describe-network-interfaces' as Y; filter 'not ($.Y.groups[*].groupId contains $.X.groupId) '; show X;

Find EC2 instance with a specific public IP address

config from cloud.resource where cloud.type = 'aws' AND json.rule = (publicIp exists or publicIpAddress exists) and ( publicIp equals x.x.x.x or publicIpAddress equals x.x.x.x) addcolumn publicIp publicIpAddress