public async Task BackfillReverseDns()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
List <string> entriesToBeUpdated = new List <string>();
Console.WriteLine($"Getting all records with no reverse dns entry at {stopwatch.ElapsedMilliseconds} ms");
using (DbDataReader reader = await PostgreSqlHelper.ExecuteReaderAsync(Environment.GetEnvironmentVariable("ConnectionString"), "SELECT DISTINCT ip_address FROM public.ip_address_details where reverse_dns_data is null"))
{
while (await reader.ReadAsync())
{
int ipAddressOrdinal = reader.GetOrdinal("ip_address");
entriesToBeUpdated.Add(reader.GetFieldValue <IPAddress>(ipAddressOrdinal).ToString());
}
}
Console.WriteLine($"Publishing {entriesToBeUpdated.Count} records at {stopwatch.ElapsedMilliseconds} ms");
List <ReverseDnsBackfillBatch> batchMessages = entriesToBeUpdated.Batch(500).Select(batch => new ReverseDnsBackfillBatch(Guid.NewGuid().ToString(), batch.ToList())).ToList();
IEnumerable <Task> publishTasks = batchMessages.Select(batchMessage => _publisher.Publish(batchMessage, _enricherConfig.SnsTopicArn));
await Task.WhenAll(publishTasks);
Console.WriteLine($"Done at {stopwatch.ElapsedMilliseconds} ms");
Console.WriteLine("Press a key");
Console.ReadLine();
}
public async Task <List <string> > GetPublicSuffixList()
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
List <string> suffixes = new List <string>();
using (DbDataReader reader = await PostgreSqlHelper.ExecuteReaderAsync(connectionString, PublicSuffixDaoResource.SelectPublicSuffixList))
{
while (await reader.ReadAsync())
{
suffixes.Add(reader.GetString("suffix"));
}
}
return(suffixes);
}
private async Task <List <AsInfo> > GetBatch(List <string> ipAddresses, string selectQuery)
{
List <AsInfo> asnInfos = new List <AsInfo>();
if (ipAddresses.Any())
{
string query = string.Join(" UNION ",
ipAddresses.Select((_, i) =>
$"({string.Format(selectQuery, $"@ip_address{i}", $"@original_ip_address{i}")})"));
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>();
for (int i = 0; i < ipAddresses.Count; i++)
{
string ipAddress = ipAddresses[i];
parameters.Add(new NpgsqlParameter($"ip_address{i}", NpgsqlDbType.Inet)
{
Value = IPAddress.Parse(ipAddress)
});
parameters.Add(new NpgsqlParameter($"original_ip_address{i}", NpgsqlDbType.Varchar)
{
Value = ipAddress
});
}
string connectionString = await _connectionInfo.GetConnectionStringAsync();
using (DbDataReader reader =
await PostgreSqlHelper.ExecuteReaderAsync(connectionString, query, parameters.ToArray()))
{
while (await reader.ReadAsync())
{
asnInfos.Add(new AsInfo
{
AsNumber = reader.GetInt32("asn"),
IpAddress = reader.GetString("original_ip_address"),
Description = reader.GetString("description"),
CountryCode = reader.GetString("country")
});
}
}
}
return(asnInfos);
}
public async Task BackfillAsn()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
Console.WriteLine($"Getting all records with no ASN details at {stopwatch.ElapsedMilliseconds} ms");
List <string> ipAddressesToBeUpdated = new List <string>();
using (DbDataReader reader = await PostgreSqlHelper.ExecuteReaderAsync(Environment.GetEnvironmentVariable("ConnectionString"), "SELECT distinct ip_address FROM public.ip_address_details where as_number is null"))
{
while (await reader.ReadAsync())
{
int ipAddressOrdinal = reader.GetOrdinal("ip_address");
ipAddressesToBeUpdated.Add(reader.GetFieldValue <IPAddress>(ipAddressOrdinal).ToString());
}
}
Console.WriteLine($"Publishing {ipAddressesToBeUpdated.Count} records at {stopwatch.ElapsedMilliseconds} ms");
List <AsnBackfillBatch> batchMessages = new List <AsnBackfillBatch>();
foreach (IEnumerable <string> batch in ipAddressesToBeUpdated.Batch(ipAddressesToBeUpdated.Count))
{
batchMessages.Add(new AsnBackfillBatch(Guid.NewGuid().ToString(), batch.ToList()));
}
IEnumerable <Task> publishTasks = batchMessages.Select(batchMessage => _publisher.Publish(batchMessage, _enricherConfig.SnsTopicArn));
var a = publishTasks.Count();
Console.WriteLine($"Created {a} tasks");
await Task.WhenAll(publishTasks);
Console.WriteLine($"Done at {stopwatch.ElapsedMilliseconds} ms");
Console.WriteLine("Press a key");
Console.ReadLine();
}
public async Task <List <IpAddressDetails> > GetIpAddressDetails(List <IpAddressDetailsRequest> ipAddressDetailsRequests)
{
List <IpAddressDetailsRequest> distinctDetailsRequests = ipAddressDetailsRequests
.GroupBy(x => new { x.Date, x.IpAddress })
.Select(x => x.First())
.ToList();
_log.LogInformation($"Retrieving address details for {distinctDetailsRequests.Count} distinct requests from {ipAddressDetailsRequests.Count}");
string connectionString = await _connectionInfo.GetConnectionStringAsync();
List <IpAddressDetails> result = new List <IpAddressDetails>();
List <IGrouping <DateTime, IpAddressDetailsRequest> > requestsByDate = ipAddressDetailsRequests
.GroupBy(x => x.Date)
.ToList();
List <string> queries = new List <string>();
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>();
for (int i = 0; i < requestsByDate.Count; i++)
{
IEnumerable <NpgsqlParameter> ipAddressParams = requestsByDate[i].Select((request, j) =>
{
bool ipAddressValue = IPAddress.TryParse(request.IpAddress, out IPAddress ipAddress);
if (!ipAddressValue)
{
_log.LogInformation($"Unable to parse IP address {request.IpAddress}");
throw new ArgumentException($"Unable to parse IP address {request.IpAddress}");
}
NpgsqlParameter parameter = new NpgsqlParameter($"ip_address_{i}_{j}", NpgsqlDbType.Inet)
{
Value = ipAddress
};
return(parameter);
});
NpgsqlParameter dateParams = new NpgsqlParameter($"date_{i}", NpgsqlDbType.Date)
{
Value = requestsByDate[i].Key
};
parameters.AddRange(ipAddressParams);
parameters.Add(dateParams);
string queryForDate = string.Format(IpAddressIntelligenceDaoResources.SelectIpAddressDetails, string.Join(',', requestsByDate[i].Select((_, j) => $"@ip_address_{i}_{j}")), $"@date_{i}");
queries.Add(queryForDate);
}
string query = string.Join(" union all ", queries);
using (DbDataReader reader = await PostgreSqlHelper.ExecuteReaderAsync(connectionString, query, parameters.ToArray()))
{
while (await reader.ReadAsync())
{
int ipAddressOrdinal = reader.GetOrdinal("ip_address");
string ipAddress = reader.GetFieldValue <IPAddress>(ipAddressOrdinal).ToString();
DateTime date = reader.GetDateTime("date");
int asNumberOrdinal = reader.GetOrdinal("as_number");
int?asNumber = reader.IsDBNull(asNumberOrdinal) ? (int?)null : reader.GetInt32("as_number");
int descriptionOrdinal = reader.GetOrdinal("description");
string description = reader.IsDBNull(descriptionOrdinal) ? null : reader.GetString("description");
int countryCodeOrdinal = reader.GetOrdinal("country_code");
string countryCode = reader.IsDBNull(countryCodeOrdinal) ? null : reader.GetString("country_code");
int blocklistDataOrdinal = reader.GetOrdinal("blocklist_data");
List <BlocklistAppearance> blocklistData = reader.IsDBNull(blocklistDataOrdinal) ? null : JsonConvert.DeserializeObject <List <BlocklistAppearance> >(reader.GetString("blocklist_data"));
int reverseDnsDataOrdinal = reader.GetOrdinal("reverse_dns_data");
List <ReverseDnsResponse> reverseDnsData = reader.IsDBNull(reverseDnsDataOrdinal) ? null : JsonConvert.DeserializeObject <List <ReverseDnsResponse> >(reader.GetString("reverse_dns_data"));
int asnUpdatedOrdinal = reader.GetOrdinal("asn_updated");
DateTime?asnUpdated = reader.IsDBNull(asnUpdatedOrdinal) ? (DateTime?)null : reader.GetDateTime("asn_updated");
int blocklistUpdatedOrdinal = reader.GetOrdinal("blocklist_updated");
DateTime?blocklistUpdated = reader.IsDBNull(blocklistUpdatedOrdinal) ? (DateTime?)null : reader.GetDateTime("blocklist_updated");
int reverseDnsUpdatedOrdinal = reader.GetOrdinal("reverse_dns_updated");
DateTime?reverseDnsUpdated = reader.IsDBNull(reverseDnsUpdatedOrdinal) ? (DateTime?)null : reader.GetDateTime("reverse_dns_updated");
IpAddressDetails ipAddressDetails = new IpAddressDetails(
ipAddress, date, asNumber, description, countryCode, blocklistData, reverseDnsData, asnUpdated,
blocklistUpdated, reverseDnsUpdated);
result.Add(ipAddressDetails);
}
}
_log.LogInformation($"Found {result.Count} IpAddressDetails in database from request for {ipAddressDetailsRequests.Count}");
return(result);
}
public async Task <List <IpAddressDetails> > GetIpAddressDetails(string ipAddress)
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
string command = "SELECT * from public.ip_address_details WHERE ip_address = @ip_address";
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>();
parameters.Add(new NpgsqlParameter($"@ip_address", NpgsqlDbType.Inet)
{
Value = IPAddress.Parse(ipAddress)
});
List <IpAddressDetails> results = new List <IpAddressDetails>();
using (DbDataReader reader = await PostgreSqlHelper.ExecuteReaderAsync(connectionString, command, parameters.ToArray()))
{
while (await reader.ReadAsync())
{
int ipAddressOrdinal = reader.GetOrdinal("ip_address");
string ip = reader.GetFieldValue <IPAddress>(ipAddressOrdinal).ToString();
int asNumberOrdinal = reader.GetOrdinal("as_number");
int?asNumber = reader.IsDBNull(asNumberOrdinal) ? (int?)null : reader.GetInt32("as_number");
int descriptionOrdinal = reader.GetOrdinal("description");
string description = reader.IsDBNull(descriptionOrdinal) ? null : reader.GetString("description");
int countryCodeOrdinal = reader.GetOrdinal("country_code");
string countryCode = reader.IsDBNull(countryCodeOrdinal) ? null : reader.GetString("country_code");
int asnUpdatedOrdinal = reader.GetOrdinal("asn_updated");
DateTime?asnUpdated = reader.IsDBNull(asnUpdatedOrdinal) ? (DateTime?)null : reader.GetDateTime("asn_updated");
int blocklistUpdatedOrdinal = reader.GetOrdinal("blocklist_updated");
DateTime?blocklistUpdated = reader.IsDBNull(blocklistUpdatedOrdinal) ? (DateTime?)null : reader.GetDateTime("blocklist_updated");
int reverseDnsUpdatedOrdinal = reader.GetOrdinal("reverse_dns_updated");
DateTime?reverseDnsUpdated = reader.IsDBNull(reverseDnsUpdatedOrdinal) ? (DateTime?)null : reader.GetDateTime("reverse_dns_updated");
string blocklistData = reader.GetString("blocklist_data");
string reverseDnsData = reader.GetString("reverse_dns_data");
IpAddressDetails ipAddressDetails = new IpAddressDetails(
ip,
reader.GetDateTime("date"),
asNumber,
description,
countryCode,
string.IsNullOrEmpty(blocklistData) ? null : JsonConvert.DeserializeObject <List <BlocklistAppearance> >(blocklistData),
string.IsNullOrEmpty(reverseDnsData) ? null : JsonConvert.DeserializeObject <List <ReverseDnsResponse> >(reverseDnsData),
asnUpdated,
blocklistUpdated,
reverseDnsUpdated);
results.Add(ipAddressDetails);
}
}
return(results);
}