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);
}
}
