public async Task Handle(AggregateReportRecordBatch message)
{
Stopwatch stopwatch = Stopwatch.StartNew();
_log.LogInformation($"Enricher received batch of {message.Records?.Count ?? 0} aggregate reports");
List <IpAddressDetailsRequest> requests = message
.Records.Select(x => new IpAddressDetailsRequest(x.HostSourceIp, x.EffectiveDate))
.ToList();
List <IpAddressDetails> responses = await _ipAddressProcessor.Process(requests);
List <AggregateReportRecordEnriched> enrichedReports = new List <AggregateReportRecordEnriched>();
foreach (AggregateReportRecord aggregateReportRecord in message.Records)
{
IpAddressDetails ipAddressDetails = responses.FirstOrDefault(x => x.IpAddress == aggregateReportRecord.HostSourceIp);
if (ipAddressDetails is null)
{
_log.LogInformation($"Unable to enrich message for ip {aggregateReportRecord.HostSourceIp} and date {aggregateReportRecord.EffectiveDate}");
}
OrganisationalDomain organisationalDomain = await _organisationalDomainProvider.GetOrganisationalDomain(aggregateReportRecord.HeaderFrom.Trim().Trim('.').ToLower());
AggregateReportRecordEnriched aggregateReportRecordEnriched = _aggregateReportRecordEnrichedFactory.Create(ipAddressDetails, aggregateReportRecord, organisationalDomain.OrgDomain, message.CorrelationId, message.Id);
enrichedReports.Add(aggregateReportRecordEnriched);
}
foreach (AggregateReportRecordEnriched aggregateReportRecordEnriched in enrichedReports)
{
await _publisher.Publish(aggregateReportRecordEnriched, _enricherConfig.SnsTopicArn);
}
_log.LogInformation($"Enricher published batch of {enrichedReports.Count} enriched aggregate reports from request for {message.Records.Count} in {stopwatch.Elapsed.Milliseconds} ms");
stopwatch.Stop();
}
public async Task AddGeoLocationData_ValidObjectPassed_NewDataIsAdded()
{
//Arrange
var mockAddressDetailsRepository = new Mock <IIpAddressDetailsRepository>();
var addressDetailsService = new IpAddressDetailsService(mockAddressDetailsRepository.Object);
var iIpAddressDetails = new IpAddressDetails()
{
City = "City",
ContinentCode = "Code",
ContinentName = "ConnectionName",
CountryCode = "ContryCode",
CountryName = "CountryName",
DatabaseId = new MongoDB.Bson.ObjectId(),
Hostname = "hostName",
Id = 1,
Ip = "127.0.0.1"
};
//Action
await addressDetailsService.AddGeoLocationData(iIpAddressDetails);
//Assert
mockAddressDetailsRepository.Verify(m => m.Add(iIpAddressDetails), Times.Once);
}
public void ProviderShouldBeDescriptionIfOrgDomainMismatchOrUnknown(string organisationalDomain, string description, string expectedProvider)
{
IpAddressDetails ipAddressDetails = CreateIpAddressDetails(organisationalDomain: organisationalDomain, description: description);
string provider = _providerResolver.GetProvider(ipAddressDetails);
Assert.AreEqual(expectedProvider, provider);
}
public void ProviderShouldDefaultToDescription()
{
IpAddressDetails ipAddressDetails = CreateIpAddressDetails(description: "testDescription");
string provider = _providerResolver.GetProvider(ipAddressDetails);
Assert.AreEqual("testDescription", provider);
}
public void ProviderShouldBeOverriddenBasedOnOrgDomainMappings(string organisationalDomain, string expectedProvider)
{
IpAddressDetails ipAddressDetails = CreateIpAddressDetails(organisationalDomain: organisationalDomain);
string provider = _providerResolver.GetProvider(ipAddressDetails);
Assert.AreEqual(expectedProvider, provider);
}
public void ProviderShouldLastResortToUnrouted()
{
IpAddressDetails ipAddressDetails = CreateIpAddressDetails();
string provider = _providerResolver.GetProvider(ipAddressDetails);
Assert.AreEqual("Unrouted", provider);
}
public AggregateReportRecordEnriched Create(IpAddressDetails response, AggregateReportRecord source, string orgDomain,
string correlationId, string causationId)
{
return(new AggregateReportRecordEnriched(
Guid.NewGuid().ToString(),
source.Id,
correlationId,
causationId,
source.ReporterOrgName,
source.ReportId,
source.EffectiveDate,
source.DomainFrom,
source.Adkim,
source.Aspf,
source.P,
source.Sp,
source.Pct,
source.Fo,
source.HostSourceIp,
source.Count,
source.Disposition,
source.Dkim,
source.Spf,
source.EnvelopeTo,
source.EnvelopeFrom,
source.HeaderFrom,
orgDomain,
source.SpfAuthResults,
source.SpfPassCount,
source.SpfFailCount,
source.DkimAuthResults,
source.DkimPassCount,
source.DkimFailCount,
source.Forwarded,
source.SampledOut,
source.TrustedForwarder,
source.MailingList,
source.LocalPolicy,
source.Arc,
source.OtherOverrideReason,
response?.ReverseDnsResponses != null && response.ReverseDnsResponses.Any() ? response.ReverseDnsResponses.OrderBy(x => x.Host).First().Host : "",
response?.ReverseDnsResponses != null && response.ReverseDnsResponses.Any() ? response.ReverseDnsResponses.OrderBy(x => x.Host).First().OrganisationalDomain : "",
_providerResolver.GetProvider(response),
response?.AsNumber ?? 0,
response?.Description,
response?.CountryCode,
response?.BlockListOccurrences?.Count(x => x.Flag == "proxy") ?? 0,
response?.BlockListOccurrences?.Count(x => x.Flag == "suspiciousnetwork") ?? 0,
response?.BlockListOccurrences?.Count(x => x.Flag == "highjackednetwork") ?? 0,
response?.BlockListOccurrences?.Count(x => x.Flag == "endusernetwork") ?? 0,
response?.BlockListOccurrences?.Count(x => x.Flag == "spamsource") ?? 0,
response?.BlockListOccurrences?.Count(x => x.Flag == "malware") ?? 0,
response?.BlockListOccurrences?.Count(x => x.Flag == "enduser") ?? 0,
response?.BlockListOccurrences?.Count(x => x.Flag == "bouncereflector") ?? 0));
}
public async Task Add(IpAddressDetails ipAddressDetail)
{
try
{
await _context.GeoLocations.InsertOneAsync(ipAddressDetail);
}
catch (Exception ex) when(ex is MongoException || ex is TimeoutException)
{
throw new ApplicationException(Resources.DatabaseUnavailableException, ex);
}
}
public void BlocklistFlagsSetCorrectly(string flagName, string countField)
{
IpAddressDetails response = CreateResponse();
response.BlockListOccurrences.Add(new BlocklistAppearance(flagName, "source", "description"));
AggregateReportRecord source = CreateSource();
AggregateReportRecordEnriched result = _aggregateReportRecordEnrichedFactory.Create(response, source, "", "", "");
Assert.AreEqual(1, typeof(AggregateReportRecordEnriched).GetProperty(countField)?.GetValue(result, null));
}
public IActionResult Review(int EventID)
{
QuestionableEvent chosenEvent = _context.QuestionableEvents.Find(EventID);
IpAddressDetails details = ipStackClient.GetIpAddressDetails(chosenEvent.IPAddressField);
return(View(new QuestionableEventReviewViewModel
{
ReviewEvent = chosenEvent,
SupplmentaryInformation = details
}));
}
public async Task <bool> UpdateGeoLocationData(IpAddressDetails ipAddressDetail)
{
var ipAddressFromDatabase = await _ipAddressDetailsRepository.GetById(ipAddressDetail.Id);
if (ipAddressFromDatabase == null)
{
throw new InvalidOperationException(Resources.UpdateGeoLocationAddressException);
}
ipAddressDetail.DatabaseId = ipAddressFromDatabase.DatabaseId;
return(await _ipAddressDetailsRepository.Update(ipAddressDetail));
}
public async Task <bool> Update(IpAddressDetails ipAddressDetail)
{
try
{
var updateResult = await _context.GeoLocations.ReplaceOneAsync(ip => ip.Id == ipAddressDetail.Id, ipAddressDetail);
return(updateResult.IsAcknowledged && updateResult.ModifiedCount > 0);
}
catch (Exception ex) when(ex is MongoException || ex is TimeoutException)
{
throw new ApplicationException(Resources.DatabaseUnavailableException, ex);
}
}
public async Task UpdateGeoLocationData_ObjectExistsInDatabase_ObjectIsUpdated()
{
//Arrange
var iIpAddressDetailsFromDb = new IpAddressDetails()
{
City = "City",
ContinentCode = "Code",
ContinentName = "ConnectionName",
CountryCode = "ContryCode FROM DB",
CountryName = "CountryName FROM DB",
DatabaseId = new MongoDB.Bson.ObjectId(),
Hostname = "FROM DB",
Id = 1,
Ip = "127.0.0.1"
};
var detailsToUpdateOnDb = new IpAddressDetails()
{
City = "NEW CITY",
ContinentCode = "Code",
ContinentName = "ConnectionName",
CountryCode = "ContryCode",
CountryName = "CountryName",
DatabaseId = new MongoDB.Bson.ObjectId(),
Hostname = "hostName",
Id = 1,
Ip = "127.0.0.1"
};
var mockAddressDetailsRepository = new Mock <IIpAddressDetailsRepository>();
mockAddressDetailsRepository.Setup(m => m.GetById(It.IsAny <long>())).ReturnsAsync(iIpAddressDetailsFromDb);
mockAddressDetailsRepository.Setup(m => m.Update(It.IsAny <IpAddressDetails>())).Callback(() => iIpAddressDetailsFromDb.City = detailsToUpdateOnDb.City).ReturnsAsync(true);
var addressDetailsService = new IpAddressDetailsService(mockAddressDetailsRepository.Object);
//Action
var result = await addressDetailsService.UpdateGeoLocationData(detailsToUpdateOnDb);
//Assert
mockAddressDetailsRepository.Verify(m => m.GetById(It.IsAny <long>()), Times.Once);
Assert.AreEqual(iIpAddressDetailsFromDb.City, detailsToUpdateOnDb.City);
Assert.IsTrue(result);
}
public async Task Migrate()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
Console.WriteLine($"Getting all existing records");
List <(DateTime, ReverseDnsResult)> existingReverseDnsRecords = await GetExistingReverseDnsResults(stopwatch);
Console.WriteLine($"Grouping all existing records at {stopwatch.ElapsedMilliseconds} ms");
IEnumerable <IGrouping <DateTime, ReverseDnsResult> > recordsGroupedByDate = existingReverseDnsRecords.GroupBy(x => x.Item1, y => y.Item2);
double updateTally = 0;
foreach (IGrouping <DateTime, ReverseDnsResult> recordGrouping in recordsGroupedByDate)
{
DateTime date = recordGrouping.Key;
List <ReverseDnsResult> recordsToSaveForDate = recordGrouping.ToList();
List <IpAddressDetails> ipAddressDetailsForDate = new List <IpAddressDetails>();
foreach (ReverseDnsResult reverseDnsResult in recordsToSaveForDate)
{
IpAddressDetails ipAddressDetails = new IpAddressDetails(reverseDnsResult.OriginalIpAddress, date, null, null, null, null, reverseDnsResult.ForwardResponses, null, null, date);
ipAddressDetailsForDate.Add(ipAddressDetails);
}
List <Task> insertsToDo = ipAddressDetailsForDate.Batch(1000).Select(batch => _ipAddressDetailsDao.SaveIpAddressDetails(batch.ToList())).ToList();
await Task.WhenAll(insertsToDo);
updateTally += ipAddressDetailsForDate.Count;
double percentDone = updateTally / 4500000d;
Console.Write($"\rUpdated {updateTally} = {percentDone:P2}% after {stopwatch.Elapsed.Minutes} mins {stopwatch.Elapsed.Seconds} secs");
}
Console.WriteLine($"\nDone updating at {stopwatch.ElapsedMilliseconds} ms");
Console.WriteLine("Press a key");
Console.ReadLine();
}
private string TryGetProviderByOrgDomain(IpAddressDetails ipAddressDetails)
{
string provider = null;
string orgDomain = ipAddressDetails?.ReverseDnsResponses.Select(x => x.OrganisationalDomain)
.FirstOrDefault();
if (orgDomain != null && _reverseDnsOverrides.ContainsKey(orgDomain))
{
provider = _reverseDnsOverrides[orgDomain];
}
else if (ipAddressDetails?.ReverseDnsResponses != null &&
ipAddressDetails.ReverseDnsResponses.Any() &&
ipAddressDetails.ReverseDnsResponses[0].OrganisationalDomain != "Mismatch" &&
ipAddressDetails.ReverseDnsResponses[0].OrganisationalDomain != "Unknown")
{
provider = ipAddressDetails.ReverseDnsResponses[0].OrganisationalDomain;
}
return(provider);
}
public string GetProvider(IpAddressDetails ipAddressDetails)
{
if (ipAddressDetails == null)
{
throw new ArgumentException("IpAddressDetails required");
}
string ipAddress = ipAddressDetails.IpAddress;
string provider = TryGetProviderByIpAddress(ipAddress) ??
TryGetProviderByOrgDomain(ipAddressDetails) ??
ipAddressDetails.Description ??
"Unrouted";
if (provider == null)
{
throw new ApplicationException($"Unable to resolve provider{Environment.NewLine}{JsonConvert.SerializeObject(ipAddressDetails)}");
}
return(provider);
}
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);
}
public async Task Handle(ReverseDnsBackfillBatch message)
{
Stopwatch stopwatch = Stopwatch.StartNew();
_log.LogInformation($"Received batch of {message.IpsToBackfill?.Count ?? 0} reverse dns entries to backfill with reverseDns lookups");
if (message.IpsToBackfill == null || message.IpsToBackfill.Count == 0)
{
return;
}
foreach (string ipAddress in message.IpsToBackfill)
{
DateTime now = DateTime.UtcNow;
ReverseDnsResult currentReverseDnsResult = await Lookup(ipAddress);
List <IpAddressDetails> entries = await _ipAddressAddressDetailsDao.GetIpAddressDetails(ipAddress);
List <IpAddressDetails> entriesWithReverseDns = entries.Where(x => x.ReverseDnsResponses != null).ToList();
List <IpAddressDetails> entriesWithoutReverseDns = entries.Where(x => x.ReverseDnsResponses == null).ToList();
List <IpAddressDetailsUpdateDto> entriesToUpdate = new List <IpAddressDetailsUpdateDto>();
foreach (IpAddressDetails existingEntry in entriesWithoutReverseDns)
{
if (entriesWithReverseDns.Any())
{
IpAddressDetails nearestNeighbour = entriesWithReverseDns.OrderBy(x => Math.Abs(x.Date.Ticks - existingEntry.Date.Ticks)).First();
long timeBetweenEntryAndNow = Math.Abs(now.Ticks - existingEntry.Date.Ticks);
long timeBetweenEntryAndNearestNeighbour = Math.Abs(nearestNeighbour.Date.Ticks - existingEntry.Date.Ticks);
if (timeBetweenEntryAndNearestNeighbour < timeBetweenEntryAndNow)
{
IpAddressDetailsUpdateDto entryToUpdate = new IpAddressDetailsUpdateDto(
existingEntry.IpAddress,
existingEntry.Date,
nearestNeighbour.ReverseDnsResponses,
nearestNeighbour.ReverseDnsLookupTimestamp);
entriesToUpdate.Add(entryToUpdate);
}
else
{
IpAddressDetailsUpdateDto entryToUpdate = new IpAddressDetailsUpdateDto(
existingEntry.IpAddress,
existingEntry.Date,
currentReverseDnsResult.ForwardResponses,
now);
entriesToUpdate.Add(entryToUpdate);
}
}
else
{
IpAddressDetailsUpdateDto entryToUpdate = new IpAddressDetailsUpdateDto(
existingEntry.IpAddress,
existingEntry.Date,
currentReverseDnsResult.ForwardResponses,
now);
entriesToUpdate.Add(entryToUpdate);
}
}
await _ipAddressAddressDetailsDao.UpdateReverseDns(entriesToUpdate);
}
_log.LogInformation(
$"Processed batch of {message.IpsToBackfill?.Count} reverse dns entries in {stopwatch.ElapsedMilliseconds} ms");
}
public async Task AddGeoLocationData(IpAddressDetails ipAddressDetail)
{
ipAddressDetail.Id = await _ipAddressDetailsRepository.GetNextAvailableId();
await _ipAddressDetailsRepository.Add(ipAddressDetail);
}
private void getDetailsOnIp(string ip)
{
respone = client.GetIpAddressDetails(ip);
}
