Table: shodan_host - Query Shodan Hosts using SQL

Shodan is a search engine that lets users find specific types of computers connected to the internet using a variety of filters. Some have also described it as a search engine of service banners, which are metadata that the server sends back to the client. This can be information about the server software, what options the service supports, a welcome message or anything else that the client can find out before interacting with the server.

Table Usage Guide

The shodan_host table provides insights into devices connected to the internet and their characteristics. As a security analyst, you can explore device-specific details through this table, including their IP addresses, hostnames, operating systems, and potential vulnerabilities. Utilize it to uncover information about devices, such as their geographical locations, open ports, and the services running on them.

Important Notes

You must specify the ip in the where clause to query this table.

Examples

Basic host information

Analyze the settings to understand the basic details of a specific host, such as its location, operating system, and open ports. This can be useful for network administrators to assess the security posture of their systems.

select
  *
from
  shodan_host
where
  ip = '8.8.8.8';

select
  *
from
  shodan_host
where
  ip = '8.8.8.8';

Basic host information

Explore the basic information associated with a specific IP address. This can be useful to understand the characteristics of a host, which can aid in network management and security assessments.

select
  *
from
  shodan_host
where
  ip = '8.8.8.8';

select
  *
from
  shodan_host
where
  ip = '8.8.8.8';

Services open on the host

Explore which services are currently open on a specific host. This is useful for understanding potential vulnerabilities and security risks associated with open services on a host.

select
  ip,
  s.*
from
  shodan_host as h,
  jsonb_array_elements_text(h.ports) as host_port,
  shodan_service as s
where
  ip = '8.8.8.8'
  and host_port :: bigint = s.port;

select
  ip,
  s.*
from
  shodan_host as h,
  json_each(h.ports) as host_port,
  shodan_service as s
where
  ip = '8.8.8.8'
  and host_port.value = s.port;

Location of the host

Analyze the geographical details of a specific internet host. This is useful for understanding the physical location of a host, which can be essential in security analysis or network management scenarios.

select
  ip,
  city,
  country_code
from
  shodan_host
where
  ip = '8.8.8.8';

select
  ip,
  city,
  country_code
from
  shodan_host
where
  ip = '8.8.8.8';

Schema for shodan_host

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
area_code	bigint		Area code for the device's location. Only available for the US.
asn	text		The autonomous system number (ex. AS4837).
city	text		Name of the city where the device is located.
country_code	text		2-letter country code where the device is located.
country_code_3	text		3-letter country code where the device is located.
country_name	text		Name of the country where the device is located.
dma_code	bigint		The designated market area code for the area where the device is located. Only available for the US.
hostnames	jsonb		An array of strings containing all of the hostnames that have been assigned to the IP address for this device.
ip	inet	=	The IP address of the host as a string.
isp	text		The ISP that is providing the organization with the IP space for this device. Consider this the "parent" of the organization in terms of IP ownership.
last_update	timestamp with time zone		Timestamp when the IP was last updated.
latitude	double precision		Latitude for the geolocation of the device.
longitude	double precision		Longitude for the geolocation of the device.
org	text		The name of the organization that is assigned the IP space for this device.
os	text		The operating system that powers the device.
ports	jsonb		Open ports for the IP.
postal_code	text		The postal code for the device's location.
region_code	text		Name of the region where the device is located.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
tags	jsonb		List of tags that describe the characteristics of the device.
vulns	jsonb		A list of vulnerabilities for 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)" -- shodan

You can pass the configuration to the command with the --config argument:

steampipe_export_shodan --config '<your_config>' shodan_host