Check AS organization country against a list of countries from IPinfo’s ASN database on Snowflake

Docs Integrations & Platforms
,
1

For compliance or restriction reasons, you may want to evaluate if IP addresses belong to organizations of particular countries. Creating a blocklist/safelist based on AS-origin countries can help with designing sophisticated access policies.

:link: IPinfo ASN Database— Snowflake Marketplace

Here we will be looking up if the IP addresses belong to ASNs of certain countries. We pass an array to the query containing the ISO 3166-1 alpha-2 code of certain countries.

SELECT
  l.ip,
  asn.country,
  array_contains(country::variant, array_construct('IE','AT','LT','LU','LV','DE','DK','SE','SI')) as "ip_filter"
FROM logs l
JOIN TABLE(ipinfo.public.ip_asn(ip)) asn;

image
image1054×448 33.3 KB

:link: Documentation and Database Schema: ASN Database

1 Like