Kusto ASN Table

Deprecation Warning - 15 June 2024

Disclaimer

This datatable may be deprecated or removed forever at my sole discretion sometime soon unless bandwidth utilisation decreases.

What is happening?

  • This single datatable is responsible for a huge amount (~1TB) of data per day
  • A huge spike started from 26 May 2024 and continues to increase daily
  • This is putting me in bad standing with my service providers
  • The very limited analytics that I can see seem to indicate a service or services is likely incorrectly using this resource
  • I am having to return dummy data in this datatable to highlight this problem to hopefully trigger action
  • Dummy data will be returned as per the timetable below, outside of these times the data will be available as normal
  • This site is provided for free as a hobby and I can not afford the hosting costs associated with this bandwidth if I need to move hosting providers

What needs to happen?

  • I need to reduce the bandwidth and requests for this datatable to I can continue to host all these resources across this site
  • If usage does NOT decrease unfortunately this datatable will return a 404 error (ie be removed fully) by the end of the June 2024
  • If usage does decrease it will continue to be made available

What can you do to help?

  • Please check how you’re using this data automatically or in scripts and if possible reduce usage
  • Ideas how to do this: ** Import this datatable daily to your own KQL and use that instead of the external datatable ** Increase the duration for search windows on alerts so this data is downloaded less often
  • If you are responsible for Kusto/2024.06.09.1700-2422-xxxxxxxx-WeeklyProdPubExt please https://www.gypthecat.com/contact-the-cat and let’s chat
  • If you have any of other ideas please https://www.gypthecat.com/contact-the-cat, happy to leverage other people’s thoughts on this

What if you need this data to be available thousands of times a day or want it yourself?

What if you need this data while it is being poisoned?

  • I would ask you take the time to address if you are using the data correctly, and if so you can download the full datatable from https[:]//firewalliplists.gypthecat[.]com/lists/kusto/kusto-cidr-asn-password.csv.zip
  • The zip password is “gypthecat.com”
  • If this file is abused it will be removed since it’s not fixing the problem

Why are you poisoning the data?

  • I can’t expect if you’ve used this dataset programmatically or as part of your own scripts that you’ll be checking this site regularly
  • As such this is the only way I can think of to get your attention
  • Except banning whole IP addresses or user agents there is no other way I can think of highlighting this problem
  • Poisoning this data will dramatically decrease bandwidth even if the requests continue
  • I am genuinely expecting that if this datatable is being used programmatically it will be spotted pretty quickly early on in this journey
  • I do apologies for poisoning the data but please know it’s been a heavy decision to do it this way
  • You can view the poisoned datatable at any time https[:]//firewalliplists.gypthecat[.]com/lists/kusto/kusto-cidr-asn-temp.csv.zip

I am genuinely happy to talk through this, please feel free to https://www.gypthecat.com/contact-the-cat.

Timetable for deprecation if usage doesn’t decrease:

Date Start Time End Time Length Requests Notes
15 June 2024 N/A N/A N/A 191.6k Baseline Figure
16 June 2024 ~0200 UTC0 ~0300 UTC0 1 hour
17 June 2024 ~0200 UTC0 ~0300 UTC0 1 hour
18 June 2024 ~0100 UTC0 ~0300 UTC0 2 hour
19 June 2024 ~0000 UTC0 ~0300 UTC0 3 hour
20 June 2024 ~2300 UTC0 (- 1d) ~0300 UTC0 4 hour
21 June 2024 ~1900 UTC0 (- 1d) ~0300 UTC0 8 hour
22 June 2024 ~1500 UTC0 (- 1d) ~0300 UTC0 12 hour
23 June 2024 ~1100 UTC0 (- 1d) ~0300 UTC0 18 hour
24 June 2024 ~1100 UTC0 (- 1d) ~0300 UTC0 18 hour
25 June 2024 ~0300 UTC0 ~0300 UTC0 24 hour
~26 June 2024 N/A N/A Offline Datatable will return a 404 error

Description


This data gives ASN Number and ASN name for any given IP addresses.

Source


The excellent GeoLite2 ASN from MaxMind.

This product includes GeoLite2 data created by MaxMind, available from https://www.maxmind.com.

Why should I use this data?


Allows the ability to track IP addresses across service providers as well as investigate the whole address space for specific details.

Updates


Daily at around 0300UTC. The source data may or may not be updated as regularly.


https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-asn.csv.zip

Schema

Column Name Data Type Notes
CIDR string
CIDRASN int
CIDRASNName string
CIDRSource string

Base Kusto Table


externaldata (CIDR:string, CIDRASN:int, CIDRASNName:string, CIDRSource:string) ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-asn.csv.zip'] with (ignoreFirstRecord=true)

Base Kusto Function


let CIDRASN = (externaldata (CIDR:string, CIDRASN:int, CIDRASNName:string, CIDRSource:string) ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-asn.csv.zip'] with (ignoreFirstRecord=true));

Self Contained Kusto



// ASN POC
// Test randomly generated IP addresses
//*** Variables start
let NumberOfIPsToTest = 100;
//*** Variables end
let CIDRASN = (externaldata (CIDR:string, CIDRASN:int, CIDRASNName:string)
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-asn.csv.zip']
with (ignoreFirstRecord=true));
let IPsTesting = materialize(
range Position from 1 to (NumberOfIPsToTest) step 1 //Generate x random IP Addresses for testing
| extend IpAddress = strcat(toint(rand(255)), '.', toint(rand(255)), '.', toint(rand(255)), '.', toint(rand(255))));
IPsTesting
| evaluate ipv4_lookup(CIDRASN, IpAddress, CIDR, return_unmatched=true)
| order by Position asc


// Shows ASN registered to specifc names and shows geographical details
// *** Variables start
let NamesToSearch = dynamic(['Google', 'Alphabet']);
// *** Variables end
let CIDRASN = (externaldata (CIDR:string, CIDRASN:int, CIDRASNName:string)
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-asn.csv.zip']
with (ignoreFirstRecord=true));
let CIDRRanges = (externaldata (CIDRCountry:string, CIDR:string, CIDRCountryName:string, CIDRContinent:string, CIDRContinentName:string, CIDRSource:string)
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-countries.csv.zip']
with (ignoreFirstRecord=true));
CIDRASN
| where CIDRASNName has_any (NamesToSearch)
| extend ExampleIpAddress = strcat(substring(CIDR, 0, indexof(CIDR, ".", 0, -1, 3)), '.', split(split(CIDR, '.')[-1], '/')[0]+1) //Generate a single IP address not a Network Address
| evaluate ipv4_lookup(CIDRRanges, ExampleIpAddress, CIDR, return_unmatched=true)
| project-away *1
| order by parse_ipv4(ExampleIpAddress) asc


// Which ASN Owners have the most IP address?
let CIDRASN = (externaldata (CIDR:string, CIDRASN:int, CIDRASNName:string)
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-asn.csv.zip']
with (ignoreFirstRecord=true));
CIDRASN
| extend NumberOfIPs = pow(2, 32 - toint(split(CIDR, '/')[-1]))
| summarize TotalIPs = sum(NumberOfIPs) by CIDRASN, CIDRASNName
| order by TotalIPs desc

MDE Example



// What connections have we seen to a specified ASN?
let CIDRASN = (externaldata (CIDR:string, CIDRASN:int, CIDRASNName:string, CIDRSource:string) ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-asn.csv.zip'] with (ignoreFirstRecord=true));
let CIDRASNOfInterest = CIDRASN
| where CIDRASN == 3214;
DeviceNetworkEvents
| evaluate ipv4_lookup(CIDRASNOfInterest, RemoteIP, CIDR, return_unmatched=false)

Sentinel & Azure Log Analytics Example


Coming soon.