How to Query ipinfo.io Data with CloudQuery

CREATE TABLE ipinfo_data (
  ip          TEXT PRIMARY KEY,
  hostname    TEXT,
  city        TEXT,
  region      TEXT,
  country     TEXT,
  org         TEXT,   -- "AS15169 Google LLC"
  timezone    TEXT,
  -- privacy detection fields (paid tier)
  vpn         BOOLEAN,
  proxy       BOOLEAN,
  tor         BOOLEAN,
  hosting     BOOLEAN,
  enriched_at TIMESTAMPTZ DEFAULT NOW()
);

-- Join EC2 instances with ipinfo geolocation data
SELECT
  i.instance_id,
  i.instance_type,
  i.region AS aws_region,
  i.public_ip_address,
  ip.country,
  ip.city,
  ip.org AS asn_org,
  ip.hostname
FROM aws_ec2_instances i
LEFT JOIN ipinfo_data ip
  ON i.public_ip_address = ip.ip
WHERE i.public_ip_address IS NOT NULL
ORDER BY ip.country, i.region;

-- Flag EC2 instances where geolocation country doesn't match expected region
SELECT
  i.instance_id,
  i.region AS aws_region,
  i.public_ip_address,
  ip.country AS geolocated_country,
  ip.city,
  ip.org AS network_org,
  i.tags->>'Environment' AS environment,
  i.tags->>'Owner' AS owner
FROM aws_ec2_instances i
JOIN ipinfo_data ip
  ON i.public_ip_address = ip.ip
WHERE
  i.public_ip_address IS NOT NULL
  AND (
    -- Flag non-US IPs for resources in US regions
    (i.region LIKE 'us-%' AND ip.country != 'US')
    -- Flag non-European IPs for resources in EU regions
    OR (i.region LIKE 'eu-%' AND ip.country NOT IN ('DE', 'IE', 'SE', 'FR', 'IT', 'ES', 'GB'))
  )
ORDER BY i.region, ip.country;

-- Find public IPs resolving to unexpected hosting providers
SELECT
  i.instance_id,
  i.public_ip_address,
  ip.org,
  ip.hosting AS is_hosting_ip,
  ip.country
FROM aws_ec2_instances i
JOIN ipinfo_data ip ON i.public_ip_address = ip.ip
WHERE
  ip.hosting = true
  AND ip.org NOT ILIKE '%amazon%'
  AND ip.org NOT ILIKE '%google%'
  AND ip.org NOT ILIKE '%microsoft%'
  AND i.public_ip_address IS NOT NULL;

-- Flag resources that should be EU-only but geolocate outside the EU
SELECT
  i.instance_id,
  i.region AS aws_region,
  i.public_ip_address,
  ip.country AS geolocated_country,
  ip.city,
  ip.org,
  i.tags->>'DataClassification' AS data_classification
FROM aws_ec2_instances i
JOIN ipinfo_data ip ON i.public_ip_address = ip.ip
WHERE
  i.public_ip_address IS NOT NULL
  AND i.tags->>'DataClassification' IN ('PII', 'PHI', 'Confidential')
  AND ip.country NOT IN ('DE', 'IE', 'SE', 'FR', 'NL', 'BE', 'AT', 'DK', 'FI')
ORDER BY ip.country, i.region;