IPGeolocation.io CloudQuery Source Plugin

1. ipgeolocation_ip_geolocation

The main table. One row per entry in ips, or a single row for the caller's public IP when ips is empty. Works on the free plan. Columns marked Paid below need a paid plan.

Key columns:

With include_security: true the table also carries every security column listed below, unprefixed. With include_abuse: true it carries every abuse column with an abuse_ prefix.

Example:

SELECT ip, country_name, city, company_name, timezone_name, as_number
FROM ipgeolocation_ip_geolocation
WHERE country_code2 = 'US';

2. ipgeolocation_ip_security

Threat intelligence for each IP in ips. Requires a paid plan and costs 2 credits per lookup.

Example:

SELECT ip, threat_score, is_vpn, is_proxy, is_tor, is_residential_proxy
FROM ipgeolocation_ip_security
WHERE threat_score > 50
ORDER BY threat_score DESC;

3. ipgeolocation_abuse_contact

The contact registered with the Regional Internet Registry for reporting abuse from a network block. Requires a paid plan and costs 1 credit per lookup.

Example:

SELECT ip, route, organization, emails, phone_numbers
FROM ipgeolocation_abuse_contact
WHERE emails != '';

4. ipgeolocation_asn

Autonomous System details, looked up by IP address, by AS number, or both. Requires a paid plan and costs 1 credit per lookup.

The last five columns stay NULL unless you name them in asn_include. That is deliberate: a tier 1 network can announce tens of thousands of prefixes and peer with thousands of other networks, and pulling all of it on every sync makes the table slow to write and awkward to query.

1asn_include: ["upstreams", "downstreams"]

Route counts come back as strings, so cast them when sorting:

SELECT as_number, organization, type, num_of_ipv4_routes
FROM ipgeolocation_asn
ORDER BY CAST(num_of_ipv4_routes AS INTEGER) DESC;

5. ipgeolocation_user_agent

Parses each string in user_agents. The table is skipped when the list is empty. Requires a paid plan and costs 1 credit per string.

Example:

SELECT name, type, device_type, os_name, os_version
FROM ipgeolocation_user_agent
ORDER BY type;

1. Where are these IPs?

SELECT ip, country_name, state_prov, city, latitude, longitude, timezone_name
FROM ipgeolocation_ip_geolocation;

2. Which IPs are hiding behind anonymization?

SELECT ip, is_vpn, is_proxy, is_tor, is_relay, vpn_provider_names, threat_score
FROM ipgeolocation_ip_security
WHERE is_anonymous = true
ORDER BY threat_score DESC;

3. Which IPs belong to cloud infrastructure rather than end users?

SELECT ip, cloud_provider_name, threat_score
FROM ipgeolocation_ip_security
WHERE is_cloud_provider = true;

4. Which automation is undeclared?

Good crawlers identify themselves. This finds traffic that behaves like a bot without doing so.

SELECT ip, bot_type, bot_operator_name, bot_confidence_score, bot_last_seen
FROM ipgeolocation_ip_security
WHERE is_bot = true AND is_known_good_bot = false
ORDER BY bot_confidence_score DESC;

5. Which IPs are corporate gateways?

A gateway can front an entire company, so blocking one is much wider than blocking a single user.

SELECT ip, corporate_gateway_provider_name, corporate_gateway_type
FROM ipgeolocation_ip_security
WHERE is_corporate_gateway = true;

6. Country breakdown across everything you synced

SELECT country_name, COUNT(*) AS ip_count
FROM ipgeolocation_ip_geolocation
GROUP BY country_name
ORDER BY ip_count DESC;

7. Who provides transit to each network?

Requires asn_include: ["upstreams"].

SELECT as_number, organization, country, upstreams
FROM ipgeolocation_asn
WHERE upstreams IS NOT NULL AND upstreams != '';

1. Building a Security Enrichment Table

Most SIEM platforms can join against a lookup table far more cheaply than they can call an external API per event. Syncing your known and suspicious IPs once a day gives the SIEM a local table to work from.

1kind: source
2spec:
3  name: ipgeolocation
4  registry: grpc
5  path: "localhost:7777"
6  tables:
7    - "ipgeolocation_ip_geolocation"
8    - "ipgeolocation_ip_security"
9  destinations: ["postgresql"]
10  spec:
11    api_key: "${IPGEOLOCATION_API_KEY}"
12    ips:
13      - "185.220.101.1"
14      - "2.56.188.34"
15      - "45.142.212.100"
16    rate_limit: 5

Run it on a schedule, point your detection rules at the resulting tables, and the enrichment happens at query time with no per event API cost.

2. Compliance Reporting on Geographic Access

If your service is restricted by jurisdiction, the auditable question is not "which country is this IP in today" but "which country did we record it in when access was granted". A synced table answers that, because the rows are timestamped by the sync that wrote them.

SELECT ip, country_name, country_code2, city, asn_organization
FROM ipgeolocation_ip_geolocation
WHERE country_code2 IN ('RU', 'KP', 'IR', 'SY', 'CU');

Keeping historical snapshots also lets you show what was known at the time rather than what is true now, which is usually what an auditor is asking for.

3. Vendor and Third Party Network Inventory

Platform teams often hold a list of vendor IP ranges in a spreadsheet nobody has checked in two years. Syncing them produces a table you can diff against the previous run.

SELECT query_ip, as_number, organization, type, country, rir
FROM ipgeolocation_asn
WHERE query_ip != ''
ORDER BY organization;

The type column separates hosting providers from ISPs, education networks and enterprise allocations, which is often the first thing to check when a vendor claims traffic came from their corporate office.

4. Incident Response and Abuse Reporting

When malicious traffic comes from a network you do not control, you need the right contact and the right block. One sync gives you both.

SELECT ip, organization, route, emails, phone_numbers, country
FROM ipgeolocation_abuse_contact
WHERE ip = '45.142.212.100';

The route column is the CIDR block the contact is registered for, which tells you the scope a report or a block would cover.

5. Traffic Analysis from Web Server Logs

Extract distinct user agent strings from your access logs, feed them through the user agent table, and you get a browser and bot breakdown without maintaining a parsing library.

The table holds one row per distinct string, so this counts distinct user agent strings by browser and type, not requests:

SELECT type, name, COUNT(*) AS distinct_user_agents
FROM ipgeolocation_user_agent
GROUP BY type, name
ORDER BY distinct_user_agents DESC;

For traffic volume, join the parsed strings back to the access log table they came from, so every request counts:

SELECT u.type, u.name, COUNT(*) AS requests
FROM access_logs l
JOIN ipgeolocation_user_agent u ON l.user_agent = u.user_agent_string
GROUP BY u.type, u.name
ORDER BY requests DESC;

6. Timezone Aware Scheduling

timezone_name returns an IANA identifier, which most date libraries accept directly.

SELECT ip, country_name, city, timezone_name, timezone_offset
FROM ipgeolocation_ip_geolocation;

1. The sync succeeds but a table is empty

Failed lookups are logged as warnings and skipped so one bad address cannot abort a run. An empty table usually means every lookup failed the same way. Re-run with --log-level debug and look for failed to fetch lines, which name the IP and the error.

The usual causes: a paid table on a free key, an ips list made up entirely of private ranges, or user_agents left empty, which skips the user agent table silently.

2. Columns are NULL when you expected values

NULL means the module was never requested. Check the matching flag: include_security, include_abuse, include_geo_accuracy, include_dma_code, include_hostname, or asn_include.

An empty string means something different. It means the module ran and the API had nothing to return, which is normal for dma_code outside the US and for upstreams on a network that buys transit from nobody.

Because of this, write filters positively. WHERE is_vpn = true works whether or not the module ran, while WHERE is_vpn != true drops NULL rows in most SQL engines.

3. HTTP status codes

429 and 5xx responses are retried with backoff of 1 second, then 2, then 4. Other 4xx responses are returned immediately.

4. Rate limits

rate_limit is a token bucket in requests per second. Lower it if your plan is tight or if other systems share the key. concurrency controls parallel table syncs and does not raise the request rate, since the limiter applies across the whole sync.