turbot/abuseipdb
steampipe plugin install abuseipdb

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

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
abuse_confidence_scorebigintAbuse confidence score.
country_codetextCountry code where the IP server is located.
domaintextDomain name found at the IP.
ip_addressinet=IP address to check.
ip_versionbigintIP address version.
is_publicbooleanTrue if the IP address is public.
is_whitelistedbooleanTrue if the IP address has been whitelisted.
isptextISP hosting the IP.
last_reported_attimestamp with time zoneLast time when the IP was reported.
max_age_in_daysbigint=Max age in days of the report data.
num_distinct_usersbigintNumber of users reporting the IP.
reportsjsonbReport details.
total_reportsbigintTotal number of reports for this IP.
usage_typetextUsage 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