Table: abuseipdb_check_ip - Query AbuseIPDB IP Reports using SQL

AbuseIPDB is a service that helps system administrators, IT security analysts, and developers to understand whether a specific IP address has been reported for suspicious activity. It provides an API to check and report IP addresses associated with malicious activities like spamming, brute-force attacks, and other forms of abuse. AbuseIPDB helps in identifying potential threats and taking necessary actions to prevent cyber attacks.

Table Usage Guide

The abuseipdb_check_ip table provides insights into IP addresses reported for suspicious activities in AbuseIPDB. As a system administrator or IT security analyst, explore IP-specific details through this table, including abuse reports, reputation score, and associated metadata. Utilize it to uncover information about reported IPs, such as their reputation, the number of times they've been reported, and the categories of abuse they've been associated with.

Examples

Get information about an IP

Analyze the settings to understand the potential abuse history and confidence score for a specific IP address. This query is useful for identifying potential security risks and recent abuse reports associated with that IP.

select
  ip_address,
  abuse_confidence_score,
  last_reported_at
from
  abuseipdb_check_ip
where
  ip_address = '76.76.21.21';

select
  ip_address,
  abuse_confidence_score,
  last_reported_at
from
  abuseipdb_check_ip
where
  ip_address = '76.76.21.21';

List all reports for a given IP over the last year

Determine the reports associated with a specific IP address over the past year. This can be useful for understanding the history of suspicious activities and identifying recurring patterns or categories of abuse.

select
  report ->> 'reportedAt' as reported_at,
  report ->> 'comment' as comment,
  report ->> 'categories' as categories
from
  abuseipdb_check_ip,
  jsonb_array_elements(reports) as report
where
  ip_address = '76.76.21.21'
  and max_age_in_days = 365;

select
  json_extract(report.value, '$.reportedAt') as reported_at,
  json_extract(report.value, '$.comment') as comment,
  json_extract(report.value, '$.categories') as categories
from
  abuseipdb_check_ip,
  json_each(reports) as report
where
  ip_address = '76.76.21.21'
  and max_age_in_days = 365;

Top categories for reports against this IP in the last year

Determine the most frequently reported categories associated with a specific IP address over the past year. This can be useful for identifying trends or patterns in abusive behavior for cybersecurity purposes.

select
  category_id.value as category,
  c.title,
  count(*)
from
  abuseipdb_check_ip as ch,
  jsonb_array_elements(ch.reports) as report,
  jsonb_array_elements(report -> 'categories') as category_id,
  abuseipdb_category as c
where
  ip_address = '76.76.21.21'
  and max_age_in_days = 365
  and c.id = category_id.value :: int
group by
  category_id.value,
  c.title
order by
  count desc;

select
  json_extract(category_id.value, '$') as category,
  c.title,
  count(*)
from
  abuseipdb_check_ip as ch,
  json_each(json_extract(ch.reports, '$')) as report,
  json_each(json_extract(report.value, '$.categories')) as category_id,
  abuseipdb_category as c
where
  ch.ip_address = '76.76.21.21'
  and ch.max_age_in_days = 365
  and c.id = cast(json_extract(category_id.value, '$') as int)
group by
  json_extract(category_id.value, '$'),
  c.title
order by
  count(*) desc;

Schema for abuseipdb_check_ip

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
abuse_confidence_score	bigint		Abuse confidence score.
country_code	text		Country code where the IP server is located.
domain	text		Domain name found at the IP.
ip_address	inet	=	IP address to check.
ip_version	bigint		IP address version.
is_public	boolean		True if the IP address is public.
is_whitelisted	boolean		True if the IP address has been whitelisted.
isp	text		ISP hosting the IP.
last_reported_at	timestamp with time zone		Last time when the IP was reported.
max_age_in_days	bigint	=	Max age in days of the report data.
num_distinct_users	bigint		Number of users reporting the IP.
reports	jsonb		Report details.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
total_reports	bigint		Total number of reports for this IP.
usage_type	text		Usage type, e.g. Commercial.

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)" -- abuseipdb

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

steampipe_export_abuseipdb --config '<your_config>' abuseipdb_check_ip