Table: ipstack_ip - Query IP Geolocation using SQL
IP Geolocation is a service within that allows you to get detailed information about any IP address. It provides location details such as country, region, city, zip code, latitude, and longitude. Additionally, it offers connection information like ISP and ASN, as well as security data including whether the IP is a proxy, crawler, or threat.
Table Usage Guide
The ipstack_ip
table provides insights into IP addresses using IP Geolocation. As a network administrator or cybersecurity professional, you can explore detailed information about any IP address, including its location, connection, and security details. Use this table to gain insights into network traffic patterns, identify potential security threats, or troubleshoot network issues.
Important Notes
- Security data is only available to subscribers. Set the security config option to true to use it.
Examples
Query details for any IPv4 address
Explore the geographical and connection details for a specific IPv4 address. This can be useful in understanding the origin of web traffic or troubleshooting network issues.
select *from ipstack_ipwhere ip = '99.84.45.75';
select *from ipstack_ipwhere ip = '99.84.45.75';
Query details for any IPv6 address
Gain insights into the geographical and network-related information for a specific IPv6 address. This can be useful for network troubleshooting, security audits, or understanding user demographics.
select *from ipstack_ipwhere ip = '2001:4860:4860::8888';
select *from ipstack_ipwhere ip = '2001:4860:4860::8888';
Lookup IP details for elastic IPs
Analyze the geographical details of Elastic IP addresses to understand their distribution worldwide. This is beneficial for assessing the spread of your network resources and identifying potential areas of vulnerability or opportunity.
select allocation_id, public_ip, i.city, i.zip, i.region_name, i.country_name, i.continent_name, i.latitude, i.longitudefrom aws_vpc_eip as eip, ipstack_ip as iwhere eip.public_ip = i.ip;
select allocation_id, public_ip, i.city, i.zip, i.region_name, i.country_name, i.continent_name, i.latitude, i.longitudefrom aws_vpc_eip as eip, ipstack_ip as iwhere eip.public_ip = i.ip;
Schema for ipstack_ip
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form. | |
asn | bigint | Autonomous System Number associated with the IP. | |
city | text | Name of the city associated with the IP. | |
continent_code | text | 2-letter continent code associated with the IP. | |
continent_name | text | Name of the continent associated with the IP. | |
country_code | text | 2-letter country code associated with the IP. | |
country_name | text | name of the country associated with the IP. | |
crawler_name | text | Name of the crawler the IP is associated with. Requires security to be enabled. | |
crawler_type | text | Type of crawler the IP is associated with. Requires security to be enabled. | |
currency_code | text | 3-letter code of the main currency associated with the IP. | |
currency_name | text | Name of the given currency. | |
currency_plural | text | Plural name of the given currency. | |
currency_symbol | text | Symbol letter of the given currency. | |
currency_symbol_native | text | Native symbol letter of the given currency. | |
hostname | text | Hostname associated with the IP address, if available. | |
ip | inet | = | Requested IP address. |
is_crawler | boolean | True or false depending on whether or not the given IP is associated with a crawler. Requires security to be enabled. | |
is_proxy | boolean | True or false depending on whether or not the given IP is associated with a proxy. Requires security to be enabled. | |
is_tor | boolean | True or false depending on whether or not the given IP is associated with the anonymous Tor system. Requires security to be enabled. | |
isp | text | Name of the ISP associated with the IP. | |
latitude | double precision | Latitude value associated with the IP. | |
location_calling_code | text | Calling / dial code of the country associated with the IP. (e.g. 351) for Portugal. | |
location_capital | text | Capital city of the country associated with the IP. | |
location_country_flag_emoji | text | Emoji icon for the flag of the country associated with the IP. | |
location_country_flag_emoji_unicode | text | Unicode value of the emoji icon for the flag of the country associated with the IP (e.g. U+1F1F5 U+1F1F9 for the Portuguese flag). | |
location_country_flag_link | text | HTTP URL leading to an SVG-flag icon for the country associated with the IP. | |
location_geoname_id | bigint | Unique geoname identifier in accordance with the Geonames Registry. | |
location_is_eu | boolean | True or false depending on whether or not the county associated with the IP is in the European Union. | |
location_languages | jsonb | Object containing one or multiple sub-objects per language spoken in the country associated with the IP. | |
longitude | double precision | Longitude value associated with the IP. | |
proxy_type | text | Type of proxy the IP is associated with. Requires security to be enabled. | |
region_code | text | Region code of the region associated with the IP (e.g. CA for California). | |
region_name | text | Name of the region associated with the IP. | |
sp_connection_name | text | =, !=, ~~, ~~*, !~~, !~~* | Steampipe connection name. |
sp_ctx | jsonb | Steampipe context in JSON form. | |
threat_level | text | Type of threat level the IP is associated with. Requires security to be enabled. | |
threat_types | jsonb | Object containing all threat types associated with the IP. Requires security to be enabled. | |
timezone_code | text | Universal code of the given time zone. | |
timezone_current_time | timestamp with time zone | Current date and time in the location associated with the IP (e.g. 2018-03-29T22:31:27-07:00). | |
timezone_gmt_offset | bigint | GMT offset of the given time zone in seconds (e.g. -25200 for PST's -7h GMT offset). | |
timezone_id | text | ID of the time zone associated with the IP (e.g. America/Los_Angeles for PST). | |
timezone_is_daylight_saving | boolean | True or false depending on whether or not the given time zone is considered daylight saving time. | |
type | text | IP address type IPv4 or IPv6. | |
zip | text | ZIP code associated with the IP. |
Export
This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.
You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh
script:
/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- ipstack
You can pass the configuration to the command with the --config
argument:
steampipe_export_ipstack --config '<your_config>' ipstack_ip