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_atfrom abuseipdb_check_ipwhere ip_address = '76.76.21.21';
select ip_address, abuse_confidence_score, last_reported_atfrom abuseipdb_check_ipwhere 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 categoriesfrom abuseipdb_check_ip, jsonb_array_elements(reports) as reportwhere 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 categoriesfrom abuseipdb_check_ip, json_each(reports) as reportwhere 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 cwhere ip_address = '76.76.21.21' and max_age_in_days = 365 and c.id = category_id.value :: intgroup by category_id.value, c.titleorder 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 cwhere 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.titleorder by count(*) desc;
Schema for abuseipdb_check_ip
Name | Type | Operators | Description |
---|---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. | |
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. | |
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