public static async Task <string> GetStatsQuery(DevicePersistenceData data, QueryDuration queryDuration, InfluxDBLoginInformation loginInformation, TimeSpan?groupInterval) { groupInterval = groupInterval ?? GetDefaultInfluxDBGroupInterval(queryDuration); string subquery = await CreateRegularTimeSeries(data, queryDuration, loginInformation, groupInterval.Value).ConfigureAwait(false); StringBuilder stb = new StringBuilder(); stb.Append("SELECT "); stb.Append(Invariant($"MIN(\"{data.Field}\")")); stb.Append(Invariant($",MAX(\"{data.Field}\")")); stb.Append(Invariant($",MEAN(\"{data.Field}\")")); stb.Append(Invariant($",MEDIAN(\"{data.Field}\")")); stb.Append(Invariant($",MODE(\"{data.Field}\")")); stb.Append(Invariant($",PERCENTILE(\"{data.Field}\", 95) as \"95 Percentile\"")); stb.Append(Invariant($",STDDEV(\"{data.Field}\") as \"Standard Deviation\"")); stb.AppendFormat(CultureInfo.InvariantCulture, "FROM (SELECT * FROM ({0}) WHERE time >= now() - {1})", subquery, GetInfluxDBDuration(queryDuration)); stb.Append(" LIMIT 100000"); return(stb.ToString()); }
public static async Task <DateTime?> GetTimeValueForQuery(string query, InfluxDBLoginInformation loginInformation) { var queryData = await ExecuteInfluxDBQuery(query, loginInformation).ConfigureAwait(false); if (queryData.Count > 0) { return((DateTime)queryData[0][TimeColumn]); } return(null); }
public static async Task <object> GetSingleValueForQuery(string query, InfluxDBLoginInformation loginInformation) { var queryData = await ExecuteInfluxDBQuery(query, loginInformation).ConfigureAwait(false); if (queryData.Count > 0) { var firstRow = queryData[0]; return(firstRow.FirstOrDefault(x => x.Key != TimeColumn).Value); } return(null); }
public static async Task <string> GetDeviceHistogramTabQuery(DevicePersistenceData data, QueryDuration queryDuration, InfluxDBLoginInformation loginInformation) { string duration = GetInfluxDBDuration(queryDuration); // Find last element before duration string query = Invariant($"SELECT last(*) from \"{data.Measurement}\" WHERE \"{PluginConfig.DeviceRefIdTag}\" = '{data.DeviceRefId}' and time < now() - {duration} order by time asc"); var time = await InfluxDBHelper.GetTimeValueForQuery(query, loginInformation).ConfigureAwait(false); string timeRestriction = time.HasValue ? Invariant($"time >= {new DateTimeOffset(time.Value).ToUnixTimeSeconds()}s") : Invariant($"time >= now() - {duration}"); return(Invariant($"SELECT {GetFields(data)[0]} FROM \"{data.Measurement}\" WHERE \"{PluginConfig.DeviceRefIdTag}\" = '{data.DeviceRefId}' AND {timeRestriction} ORDER BY time ASC")); }
public DeviceRootDeviceManager(IHSApplication HS, InfluxDBLoginInformation dbLoginInformation, IReadOnlyDictionary <string, ImportDeviceData> importDevicesData, CancellationToken cancellationToken) { this.HS = HS; this.dbLoginInformation = dbLoginInformation; this.importDevicesData = importDevicesData; this.cancellationToken = cancellationToken; this.combinedToken = CancellationTokenSource.CreateLinkedTokenSource(cancellationToken); var hsDevices = GetCurrentDevices(); CreateDevices(hsDevices); StartDeviceFetchFromDB(hsDevices); Children = hsDevices.Children; }
private static async Task <string> CreateRegularTimeSeries(DevicePersistenceData data, QueryDuration queryDuration, InfluxDBLoginInformation loginInformation, TimeSpan groupByInterval, bool fileLinear = false) { string duration = GetInfluxDBDuration(queryDuration); // Find last element before duration string query = Invariant($"SELECT last(*) from \"{data.Measurement}\" WHERE \"{PluginConfig.DeviceRefIdTag}\" = '{data.DeviceRefId}' and time < now() - {duration} order by time asc"); var time = await InfluxDBHelper.GetTimeValueForQuery(query, loginInformation).ConfigureAwait(false); string timeRestriction = time.HasValue ? Invariant($"time >= {new DateTimeOffset(time.Value).ToUnixTimeSeconds()}s") : Invariant($"time >= now() - {duration}"); string fillOption = fileLinear ? "linear" : "previous"; return(Invariant($"SELECT MEAN(\"{data.Field}\") as \"{data.Field}\" from \"{data.Measurement}\" WHERE \"{PluginConfig.DeviceRefIdTag}\" = '{data.DeviceRefId}' and {timeRestriction} GROUP BY time({(int)groupByInterval.TotalSeconds}s) fill({fillOption})")); }
public static async Task <string> GetGroupedDeviceHistoryTabQuery(DevicePersistenceData data, string deviceName, QueryDuration queryDuration, InfluxDBLoginInformation loginInformation, TimeSpan?groupInterval) { groupInterval = groupInterval ?? GetDefaultInfluxDBGroupInterval(queryDuration); string subquery = await CreateRegularTimeSeries(data, queryDuration, loginInformation, groupInterval.Value).ConfigureAwait(false); StringBuilder stb = new StringBuilder(); stb.Append("SELECT "); stb.Append(Invariant($"\"{data.Field}\" as \"{deviceName}\"")); stb.AppendFormat(CultureInfo.InvariantCulture, "FROM (SELECT * FROM ({0}) WHERE time >= now() - {1})", subquery, GetInfluxDBDuration(queryDuration)); return(stb.ToString()); }
private void HandleSaveDBSettingPostBack(NameValueCollection parts) { StringBuilder results = new StringBuilder(); // Validate if (!System.Uri.TryCreate(parts[DBUriKey], UriKind.Absolute, out Uri dbUri)) { results.AppendLine("Url is not Valid.<br>"); } string database = parts[DBKey]; if (string.IsNullOrWhiteSpace(database)) { results.AppendLine("Database is not Valid.<br>"); } string username = parts[UserKey]; string password = parts[PasswordKey]; string retention = parts[RetentionKey]; try { using (var influxDbClient = new InfluxDBClient(dbUri.ToString(), username, password)) { var databases = influxDbClient.GetInfluxDBNamesAsync().ResultForSync(); var selectedDb = databases.Where((db) => { return(db == database); }).FirstOrDefault(); if (selectedDb == null) { results.AppendLine("Database not found on server.<br>"); } else { if (!string.IsNullOrWhiteSpace(retention)) { var retentionPolcies = influxDbClient.GetRetentionPoliciesAsync(selectedDb).ResultForSync(); if (!retentionPolcies.Any(r => r.Name == retention)) { results.AppendLine("Retention policy not found for database.<br>"); } } } } } catch (Exception ex) { results.AppendLine(Invariant($"Failed to connect to InfluxDB with {ex.GetFullMessage()}")); } if (results.Length > 0) { this.divToUpdate.Add(ErrorDivId, results.ToString()); } else { this.divToUpdate.Add(ErrorDivId, string.Empty); var dbConfig = new InfluxDBLoginInformation(dbUri, PluginConfig.CheckEmptyOrWhitespace(username), PluginConfig.CheckEmptyOrWhitespace(password), PluginConfig.CheckEmptyOrWhitespace(parts[DBKey]), PluginConfig.CheckEmptyOrWhitespace(retention)); this.pluginConfig.DBLoginInformation = dbConfig; this.pluginConfig.DebugLogging = parts[DebugLoggingId] == "checked"; this.pluginConfig.FireConfigChanged(); } }
public static async Task <IList <IDictionary <string, object> > > ExecuteInfluxDBQuery(string query, InfluxDBLoginInformation loginInformation) { using (var influxDbClient = new InfluxDBClient(loginInformation.DBUri.ToString(), loginInformation.User, loginInformation.Password)) { var series = await influxDbClient.QueryMultiSeriesAsync(loginInformation.DB, query, precision : TimePrecision.Seconds).ConfigureAwait(false); var accumatedList = new List <IDictionary <string, object> >(); foreach (var serie in series) { accumatedList.AddRange(serie.Entries.Select(x => (IDictionary <string, object>)x)); } return(accumatedList); } }