internal async Task <string> EnsureTableExistsAsync(DateTimeOffset testAggregationPeriod)
{
using (var context = new Context(DbContextOptions))
using (var sqlConnection = new SqlConnection(context.Database.GetDbConnection().ConnectionString))
{
await sqlConnection.OpenAsync();
var tableName = await AggregationWriter.EnsureTableExistsAsync(sqlConnection, _configuration.SqlCommandTimeoutSeconds, testAggregationPeriod);
sqlConnection.Close();
return(tableName);
}
}
private async Task GetAndWriteAggregations(
DatamartClient datamartClient,
Configuration configuration,
ILogger logger,
List <DeviceDataSourceInstanceStoreItem> databaseDeviceDataSourceInstances,
TimeSpan configurationLevelAggregationDuration,
CancellationToken cancellationToken)
{
// To ignore a period of uncertainty whether the Collector has been
// able to publish its measurement data to the LogicMonitor API,
// we consider "now" to be X hours ago.
// This is "B" in the diagram below.
var utcNow = DateTimeOffset.UtcNow;
var lateArrivingDataWindowStart = utcNow.AddHours(-configuration.LateArrivingDataWindowHours);
var aggregationsToWrite = new List <DeviceDataSourceInstanceAggregatedDataBulkWriteModel>();
var stopwatch = new Stopwatch();
// Determine the aggregation duration at the datasource level
var dataSourceAggregationDuration = configurationLevelAggregationDuration;
// Get data for each instance
logger.LogInformation($"Syncing {databaseDeviceDataSourceInstances.Count} DeviceDataSourceInstances...");
foreach (var databaseDeviceDataSourceInstanceGroup in
databaseDeviceDataSourceInstances
.GroupBy(ddsi => ddsi.LastAggregationHourWrittenUtc ?? DateTime.MinValue)
)
{
var lastAggregationHourWrittenUtc = new DateTimeOffset(databaseDeviceDataSourceInstanceGroup.Key, TimeSpan.Zero);
// Handle that groups of LastAggregationHourWrittenUtc need to be batched to deal with the LogicMonitor restriction
foreach (var databaseDeviceDataSourceInstanceGroupBatch in databaseDeviceDataSourceInstanceGroup
.Select((item, itemIndex) => (item, itemIndex))
.GroupBy(x => x.itemIndex / _configuration.DeviceDataSourceInstanceBatchSize))
{
var batchIndex = databaseDeviceDataSourceInstanceGroupBatch.Key;
var instanceIdList = databaseDeviceDataSourceInstanceGroupBatch
.Select(t => t.item.Id)
.ToList();
var rangeDescription = $"Batch {batchIndex + 1}: {instanceIdList.Count} instances starting {databaseDeviceDataSourceInstanceGroup.Key:yyyy-MM-dd HH:mm:ss}: {string.Join(",", instanceIdList)}...";
logger.LogDebug(rangeDescription);
try
{
stopwatch.Restart();
var totalRowsLoadedFromApi = 0;
// A: The last time we got measurement up to for this DeviceDataSourceInstance
var lastUpdatedDateTimeUtc = lastAggregationHourWrittenUtc;
// If we have never fetched data, determine the minimum data fetch date
if (lastUpdatedDateTimeUtc < configuration.StartDateTimeUtc)
{
lastUpdatedDateTimeUtc = configuration.StartDateTimeUtc.UtcDateTime;
}
// Due to limitations on the DataFetch Logicmonitor.Api endpoint, we can only go back a max of 24 hours
// If lastUpdatedDateTimeUtc < 23 hours ago then set to 23 hours ago
const int MaxHoursBack = 23;
if (lastUpdatedDateTimeUtc < DateTimeOffset.UtcNow.AddHours(-MaxHoursBack))
{
var originalLastUpdatedDateTimeUtc = lastUpdatedDateTimeUtc;
var hoursAgo23 = DateTimeOffset.UtcNow.AddHours(-MaxHoursBack);
lastUpdatedDateTimeUtc = DateTimeOffset.UtcNow.Date.AddHours(-MaxHoursBack);
while (lastUpdatedDateTimeUtc < hoursAgo23)
{
// Increment by the aggregation duration until we're within the window
lastUpdatedDateTimeUtc = lastUpdatedDateTimeUtc.Add(dataSourceAggregationDuration);
}
logger.LogDebug($"lastUpdatedDateTimeUtc {originalLastUpdatedDateTimeUtc} is more than {MaxHoursBack} hours ago so setting to {lastUpdatedDateTimeUtc}.");
}
var timeCursor = lastUpdatedDateTimeUtc;
// A is now calculated
// .........---------|----:----:-|-----NOW
// A B
// A is where we have data in the database up to
// B is where we want to fetch it up to
// The maximum data that can be retrieved from LM at once with full resolution is 8 hours
// We fetch the data in blocks of no more than 8 hours.
// Determine the last datetimeoffset we want to get data up to
var blockIndex = 0;
while (timeCursor < lateArrivingDataWindowStart)
{
var blockStart = timeCursor;
while (timeCursor - blockStart < EightHours)
{
if (timeCursor + dataSourceAggregationDuration >= lateArrivingDataWindowStart)
{
break;
}
timeCursor += dataSourceAggregationDuration;
}
// We have a block of time to fetch, starting at blockStart and ending at timeCursor.
// Is the block zero length?
if (timeCursor == blockStart)
{
// If we've genuinely done nothing, then log it so terminating after this is shown to be intentional
if (blockIndex == 0)
{
logger.LogDebug($"BlockIndex is 0, nothing to do for batch {batchIndex + 1}.");
}
break;
}
var blockEnd = timeCursor;
// Fetch the data and loop
var instancesFetchDataResponse = await datamartClient.GetFetchDataResponseAsync(instanceIdList,
blockStart,
blockEnd,
cancellationToken
).ConfigureAwait(false);
var rowsRetrieved = instancesFetchDataResponse.InstanceFetchDataResponses.Sum(r => r.Timestamps.Length);
logger.LogDebug($"Loaded {rowsRetrieved} entries.");
//if (rowsRetrieved > 0)
totalRowsLoadedFromApi += rowsRetrieved;
// Create a new DbContext to clear out tracked objects
using (var dataContext = new Context(datamartClient.DbContextOptions))
{
using (var sqlConnection = new SqlConnection(dataContext.Database.GetDbConnection().ConnectionString))
{
await sqlConnection.OpenAsync().ConfigureAwait(false);
aggregationsToWrite.Clear();
// Iterate over the retrieved DeviceDataSourceInstances
foreach (var instanceFetchDataResponse in instancesFetchDataResponse.InstanceFetchDataResponses)
{
// Get the configuration for this DataSourceName
var dataSourceConfigurationItem = configuration.DataSources.SingleOrDefault(dsci => dsci.Name == instanceFetchDataResponse.DataSourceName);
var deviceDataSourceInstanceIdAsInt = int.Parse(instanceFetchDataResponse.DeviceDataSourceInstanceId);
if (instanceFetchDataResponse.Timestamps.Length > 0)
{
// Process only the configured DataPoints to retrieve
// Add data to the context for each of the dataPointNames
foreach (var dataPointModel in dataSourceConfigurationItem.DataPoints)
{
// Get the index into the timestamps and values
var dataPointIndex = Array.FindIndex(instanceFetchDataResponse.DataPoints, dpName => dpName == dataPointModel.Name);
if (dataPointIndex == -1)
{
// We have a datapoint in our configuration that isn't being returned for this DataSource, therefore we cant write it out
continue;
}
// Validate the result is good to zip up
if (instanceFetchDataResponse.Timestamps.Length != instanceFetchDataResponse.DataValues.Length)
{
logger.LogError($"Expected count of {nameof(instanceFetchDataResponse.Timestamps)} ({instanceFetchDataResponse.Timestamps.Length}) and count of {nameof(instanceFetchDataResponse.DataValues)} ({instanceFetchDataResponse.DataValues.Length}) to match.");
// We've logged, try the next DataPoint
continue;
}
var data = instanceFetchDataResponse.Timestamps.Zip(
instanceFetchDataResponse.DataValues.Select(v => v[dataPointIndex]),
(timeStampMs, value)
=> new
{
DateTime = DateTimeOffset.FromUnixTimeMilliseconds(timeStampMs).UtcDateTime,
DataPointName = dataPointModel.Name,
Value = (double?)(value is string?null: value),
DeviceDataSourceInstanceId = deviceDataSourceInstanceIdAsInt
})
.ToList();
// Data: :-------------------------------:
// Data fetched is a block :---.---.---.---.---.---.---.---:
//... where the maximum size is 8 hours, with an integer number data aggregation chunks
// We need to aggregate this in blocks of aggregationDuration
var databaseDataPoint = await dataContext
.DataSourceDataPoints
.SingleOrDefaultAsync(dp => dp.Name == dataPointModel.Name && dp.DataSource.Name == instanceFetchDataResponse.DataSourceName)
.ConfigureAwait(false);
// Aggregate it in blocks of DataAggregationDuration
var aggregationTimeCursor = blockStart;
aggregationTimeCursor += dataSourceAggregationDuration;
var deviceDataSourceInstanceAggregatedDataStoreItems = data
.GroupBy(d => ((int)(d.DateTime - blockStart).TotalSeconds) / ((int)dataSourceAggregationDuration.TotalSeconds))
.Select(chunkedData =>
{
var periodStart = (blockStart + TimeSpan.FromSeconds(chunkedData.Key * dataSourceAggregationDuration.TotalSeconds)).UtcDateTime;
return(new DeviceDataSourceInstanceAggregatedDataBulkWriteModel
{
DeviceDataSourceInstanceId = deviceDataSourceInstanceIdAsInt,
DataPointId = databaseDataPoint.DatamartId,
PeriodStart = periodStart,
PeriodEnd = periodStart.Add(dataSourceAggregationDuration),
DataCount = chunkedData.Count(d => d.Value != null),
NoDataCount = chunkedData.Count(d => d.Value == null),
Sum = chunkedData.Sum(d => d.Value ?? 0),
SumSquared = chunkedData.Sum(d => d.Value == null ? 0 : d.Value.Value * d.Value.Value),
Max = chunkedData.Max(d => d.Value),
Min = chunkedData.Min(d => d.Value)
});
})
.ToList();
aggregationsToWrite.AddRange(deviceDataSourceInstanceAggregatedDataStoreItems);
// Increment the blockIndex
blockIndex++;
}
}
// We always want to write something out about where we've attempted to get data until
// TODO write out aggregationsToWrite using bulk write in a transaction with the progress on day boundaries
if (aggregationsToWrite.Count > 0)
{
foreach (var blockToWrite in aggregationsToWrite.GroupBy(a => a.PeriodStart.Date))
{
await AggregationWriter.WriteAggregations(
sqlConnection,
configuration.SqlCommandTimeoutSeconds,
configuration.SqlBulkCopyTimeoutSeconds,
deviceDataSourceInstanceIdAsInt,
blockToWrite.Key,
blockToWrite,
logger);
}
}
else
{
await AggregationWriter.WriteProgressBoundaryAsync(
sqlConnection,
configuration.SqlCommandTimeoutSeconds,
deviceDataSourceInstanceIdAsInt,
blockEnd.UtcDateTime,
null);
}
}
}
}
}
}
catch (Exception e)
{
logger.LogWarning(e, $"{rangeDescription} failed due to {e}");
}
}
}
logger.LogInformation($"Syncing data complete.");
}
internal Task DropAggregationTableAsync(DateTimeOffset testAggregationPeriod) => AggregationWriter.DropTableAsync(DbContextOptions, testAggregationPeriod, _logger);
internal Task AgeAggregationTablesAsync(int countAggregationDaysToRetain) => AggregationWriter.PerformAgingAsync(DbContextOptions, _configuration.SqlCommandTimeoutSeconds, countAggregationDaysToRetain, _logger);
internal Task <List <string> > GetAggregationTablesAsync() => AggregationWriter.GetTablesAsync(DbContextOptions);