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);
                }
        }
Ejemplo n.º 2
0
        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);