Storm Athena query examples

storm-athena-query-examples.sql

-- Default VPCs
SELECT *
FROM "stormreports"."ec2_vpcs"
WHERE isDefault = true
        AND date = date_format(current_date, '%Y-%m-%d')
        
        
-- VPCs with no ENIs attached (possibly unused)
WITH e AS 
    (SELECT vpcid,
         count(*) AS eniCount
    FROM "stormreports"."ec2_networkinterfaces"
    WHERE date = date_format(current_date, '%Y-%m-%d')
    GROUP BY  vpcid)
SELECT v.vpcid AS VpcId,
         tag.value AS Name,
         v.account AS Account,
         v.region AS Region,
         v.isdefault AS IsDefault,
         e.eniCount AS EniCount
FROM "stormreports"."ec2_vpcs" AS v
LEFT JOIN e
    ON (v.vpcid = e.vpcid), UNNEST(tags) t(tag)
WHERE e.vpcid IS NULL
        AND v.date = date_format(current_date, '%Y-%m-%d')
        AND tag.key = 'Name'
ORDER BY  v.vpcid

-- Unused EBS volumes by account and region
SELECT v.account AS Account,
         v.region AS Region,
         count(*) AS UnusedVolumeCount,
         sum(v.size) AS UnusedVolumeSize,
         round(sum(v.size * .10)) AS UnusedVolumeEstMoCost
FROM "stormreports"."ec2_volumes" AS v
WHERE cardinality(v.attachments) < 1
        AND v.date = date_format(current_date, '%Y-%m-%d')
GROUP BY  v.account, v.region
ORDER BY  UnusedVolumeEstMoCost DESC;
                  
-- S3 Buckets with AllUsers permission grants
SELECT name AS BucketName,
         account AS AccountId,
         grant.grantee.uri AS GranteeURI,
         grant.permission AS GrantPermission
FROM "stormreports"."s3_buckets", UNNEST(bucketacl.content.grants) t(grant)
WHERE date = date_format(current_date, '%Y-%m-%d')
        AND bucketacl.accessdenied = false
        AND grant.grantee.URI = 'http://acs.amazonaws.com/groups/global/AllUsers'
                   
-- Public IP Security Groups with TCP != 80, 443, 8080
WITH eni_secgroups AS 
    (SELECT n.networkinterfaceid AS networkInterfaceId,
         n.account AS accountId,
         n.availabilityzone AS availabilityZone,
         n.subnetid AS subnetId,
         n.vpcid AS vpcId,
         n.association.publicip AS publicIp,
         g.groupid AS groupId,
         g.groupname AS groupName,
         n.sourcedestcheck AS sourceDestCheck,
         n.association.allocationid AS allocationId,
         n.association.associationid AS associationId,
         n.association.ipownerid AS ipOwnerId,
         n.attachment.instanceid AS instanceId,
         n.tagset AS tags
    FROM "stormreports"."ec2_networkinterfaces" AS n,UNNEST(n.groups) t(g)
    WHERE n.date = date_format(current_date, '%Y-%m-%d')
            AND n.association IS NOT NULL
            AND n.interfacetype != 'nat_gateway'), ipperms AS 
    (SELECT *
    FROM "stormreports"."ec2_securitygroups" AS s, UNNEST (s.ippermissions) t(p)
    WHERE s.date = date_format(current_date, '%Y-%m-%d'))
SELECT accountId AS AccountId,
         networkInterfaceId AS networkInterfaceId,
         publicIp AS PublicIp,
         ipOwnerId AS IpOwnerId,
         rule.cidrip AS CIDR,
         p.ipprotocol AS IpProtocol,
         p.fromport AS FromPort,
         p.toPort AS toPort,
         region AS REGION,
         availabilityZone AS AvailabilityZone
FROM eni_secgroups AS e
JOIN ipperms AS i
    ON (e.groupid = i.groupid), UNNEST (p.ipranges) t(rule)
WHERE p.ipProtocol = 'tcp'
        AND rule.cidrip = '0.0.0.0/0'
        AND NOT (80
    BETWEEN CAST(p.fromPort AS integer)
        AND CAST(p.toPort AS integer))
        AND NOT (443
    BETWEEN CAST(p.fromPort AS integer)
        AND CAST(p.toPort AS integer))
        AND NOT (8080
    BETWEEN CAST(p.fromPort AS integer)
        AND CAST(p.toPort AS integer))
ORDER BY  accountId, networkInterfaceId
                 
                
--- Cross account role principal trusts
WITH x AS 
    (SELECT arn,
         description,
         createdate,
         tags,
         assumerolepolicydocument,
         regexp_extract(json_format(principal),
         'arn:aws:.*:.*:(\d+):.*',1) AS AccountId
    FROM "stormreports"."iam_roles", UNNEST(CAST(json_extract(assumerolepolicydocument, '$.statement') AS ARRAY<JSON>)) t(statement), UNNEST(CAST(json_extract(statement,'$.principal.aws') AS ARRAY<JSON>)) t(principal)
    WHERE date = date_format(current_date, '%Y-%m-%d')
            AND json_array_length(json_extract(statement,'$.principal.aws')) > 0
    UNION
    SELECT arn,
         description,
         createdate,
         tags,
         assumerolepolicydocument,
         regexp_extract(json_extract_scalar(statement,
         '$.principal.aws'),'arn:aws:.*:.*:(\d+):.*',1) AS AccountId
    FROM "stormreports"."iam_roles", UNNEST(CAST(json_extract(assumerolepolicydocument, '$.statement') AS ARRAY<JSON>)) t(statement)
    WHERE date = date_format(current_date, '%Y-%m-%d')
            AND json_extract_scalar(statement, '$.principal.aws') IS NOT NULL
    ORDER BY  arn DESC)
SELECT x.arn AS ARN,
         x.accountId AS TrustedId,
         x.createdate AS CreateDate,
         x.description AS Description,
         x.tags AS Tags,
         x.assumerolepolicydocument AS TrustPolicy
FROM x
LEFT JOIN "stormreports"."organizations_members" AS o
    ON (x.accountid = o.id)
WHERE o.id IS NULL
        AND x.accountid IS NOT NULL
ORDER BY  TrustedId asc
                                            
                                            
                                            
                                            
--- VPC Report with gateway attachments and ENI counts
                                            
WITH igws AS 
    (SELECT attachments[1].vpcid AS VpcId,
         internetgatewayid AS InternetGatewayId
    FROM "stormreports"."ec2_internetgateways"
    WHERE cardinality(attachments) > 0
            AND date = date_format(current_date,'%Y-%m-%d')), vgws AS 
    (SELECT vpcattachments[1].vpcid AS VpcId,
         VpnGatewayId AS VpnGatewayId,
         amazonsideasn AS AmazonSideASN
    FROM "stormreports"."ec2_vpngateways"
    WHERE cardinality(vpcattachments) > 0
            AND date = date_format(current_date, '%Y-%m-%d')), vpcs AS 
    (SELECT *
    FROM "stormreports"."ec2_vpcs" AS v
    JOIN "stormreports"."organizations_members" AS o
        ON (v.date = o.date
            AND v.account = o.id), UNNEST(cidrblockassociationset) t(cidr)
    WHERE v.date = date_format(current_date,'%Y-%m-%d')), enis AS 
    (SELECT vpcid,
         count(*) AS EniCount
    FROM "stormreports"."ec2_networkinterfaces" AS n
    WHERE n.date = date_format(current_date,'%Y-%m-%d')
    GROUP BY  vpcid)
SELECT v.cidr.cidrblock AS CidrBlock,
         v.vpcid AS VpcId,
         COALESCE(e.enicount,0) AS EniCount,
         i.internetgatewayid AS InternetGatewayId,
         g.vpngatewayid AS VpnGatewayId,
         g.amazonsideasn AS AmazonSideASN,
         v.account AS Account,
         v.region AS Region,
         v.dhcpoptionsid AS DhcpOptionsId,
         v.state AS State,
         v.isdefault AS IsDefault,
         v.businessunit AS BusinessUnit,
         v.environment AS Environment,
         v.tags AS Tags
FROM vpcs AS v
LEFT JOIN enis AS e
    ON (v.vpcid = e.vpcid)
LEFT JOIN igws AS i
    ON (v.vpcid = i.vpcid)
LEFT JOIN vgws AS g
    ON (v.vpcid = g.vpcid)
ORDER BY  v.cidr.cidrblock ASC;