Blocklist.de All IP addresses that have attacked one of our customers/servers in the last 48 hours

TI Feed Blocklist DE All Malicious IP

Query

let ThreatIntelFeed = externaldata(DestIP: string)[@"https://lists.blocklist.de/lists/all.txt"] with (format="txt", ignoreFirstRecord=True);
let IPRegex = '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}';
let MaliciousIP = materialize (
       ThreatIntelFeed
       | where DestIP matches regex IPRegex
       | distinct DestIP
        );
DeviceNetworkEvents
| where RemoteIP in (MaliciousIP)
| extend GeoIPInfo = geo_info_from_ip_address(RemoteIP)
| extend country = tostring(parse_json(GeoIPInfo).country), state = tostring(parse_json(GeoIPInfo).state), city = tostring(parse_json(GeoIPInfo).city), latitude = tostring(parse_json(GeoIPInfo).latitude), longitude = tostring(parse_json(GeoIPInfo).longitude)

About this query

Blocklist.de All IP addresses that have attacked one of our customers/servers in the last 48 hours

Source: Blocklist.de

Feed information: https://www.blocklist.de/en/export.html

Feed link: https://lists.blocklist.de/lists/all.txt

Defender XDR

Sentinel

let ThreatIntelFeed = externaldata(DestIP: string)[@"https://lists.blocklist.de/lists/all.txt"] with (format="txt", ignoreFirstRecord=True);
let IPRegex = '[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}';
let MaliciousIP = materialize (
       ThreatIntelFeed
       | where DestIP matches regex IPRegex
       | distinct DestIP
        );
DeviceNetworkEvents
| where RemoteIP in (MaliciousIP)
| extend GeoIPInfo = geo_info_from_ip_address(RemoteIP)
| extend country = tostring(parse_json(GeoIPInfo).country), state = tostring(parse_json(GeoIPInfo).state), city = tostring(parse_json(GeoIPInfo).city), latitude = tostring(parse_json(GeoIPInfo).latitude), longitude = tostring(parse_json(GeoIPInfo).longitude)

Explanation

This query is designed to identify and provide geographical information about IP addresses that have attacked a customer's servers in the last 48 hours. Here's a simplified breakdown of what the query does:

Data Source: It uses a threat intelligence feed from Blocklist.de, which contains a list of IP addresses known for malicious activity.
IP Extraction: The query extracts IP addresses from this feed using a regular expression that matches the standard IP address format.
Distinct IPs: It ensures that only unique IP addresses are considered by removing duplicates.
Event Matching: The query then checks the DeviceNetworkEvents table to find any network events where the RemoteIP matches one of the malicious IPs identified from the feed.
Geolocation Information: For each matching event, it retrieves geolocation information based on the IP address, including the country, state, city, latitude, and longitude.
Output: The result is a list of network events involving malicious IPs, enriched with geolocation data to help understand where these attacks are originating from.

Both the Defender XDR and Sentinel queries perform the same operations, indicating that the same logic is applied across different platforms to track and analyze malicious network activity.

Details

Bert-Jan Pals

Released: December 1, 2024

Tables

DeviceNetworkEvents

Keywords

BlocklistDevicesIPGeoIPCountryStateCityLatitudeLongitude

Operators

externaldatawithletmatches regexdistinctmaterializewhereinextendgeo_info_from_ip_addresstostringparse_json

Actions

GitHub

KQL Search