private static IDictionary <string, FormattableString> GetDefaultValueQueries(DevicePersistenceData data) { List <string> fields = InfluxDbQueryBuilder.GetFields(data); var queries = new Dictionary <string, FormattableString>() { { "Last 100 stored values", $"SELECT {string.Join(",", fields)} from \"{data.Measurement}\" WHERE {PluginConfig.DeviceRefIdTag}='{data.DeviceRefId}' ORDER BY time DESC LIMIT 100" }, }; if (!string.IsNullOrWhiteSpace(data.Field)) { var standardFields = Invariant($"MIN(\"{data.Field}\"), MAX(\"{data.Field}\"), MEAN(\"{data.Field}\"), MEDIAN(\"{data.Field}\"), PERCENTILE(\"{data.Field}\", 95) AS \"95 percentile\""); var subQuery24h = Invariant($"SELECT MEAN(\"{data.Field}\") as \"{data.Field}\" FROM \"{data.Measurement}\" WHERE {PluginConfig.DeviceRefIdTag}='{data.DeviceRefId}' AND time > now() - 24h GROUP BY time(1s) fill(previous)"); queries.Add( "Min/Max Values", $"SELECT MIN(\"{data.Field}\"), MAX(\"{data.Field}\") from \"{data.Measurement}\" WHERE {PluginConfig.DeviceRefIdTag}='{data.DeviceRefId}'" ); queries.Add( "Min/Max/Average/Medium/Percentile Values(24h)", $"SELECT {standardFields} FROM ({subQuery24h})" ); queries.Add( "Min/Max/Average/Medium/Percentile Values By Hour(24h)", $"SELECT {standardFields} FROM ({subQuery24h}) GROUP BY time(1h)" ); } return(queries); }
private string GetQueryResultFrame(DevicePersistenceData data, IFrameType frameType, QueryDuration duration, IFrameGrouping grouping) { StringBuilder stb = new StringBuilder(); string iFrameUrl = null; HSHelper hSHelper = new HSHelper(HS); string deviceName = hSHelper.GetName(data.DeviceRefId); switch (frameType) { case IFrameType.TableHistory: var query = InfluxDbQueryBuilder.GetDeviceHistoryTabQuery(data, deviceName, duration); iFrameUrl = BuildTableUri(query, 10); break; case IFrameType.ChartHistory: var chartQuery = InfluxDbQueryBuilder.GetGroupedDeviceHistoryTabQuery(data, deviceName, duration, pluginConfig.DBLoginInformation, GetTimeSpan(grouping)).ResultForSync(); iFrameUrl = BuildChartUri(chartQuery, string.Empty); break; case IFrameType.AverageStats: var statsQuery = InfluxDbQueryBuilder.GetStatsQuery(data, duration, pluginConfig.DBLoginInformation, GetTimeSpan(grouping)).ResultForSync(); iFrameUrl = BuildStatsUri(statsQuery); break; case IFrameType.Histogram: var histogramQuery = InfluxDbQueryBuilder.GetDeviceHistogramTabQuery(data, duration, pluginConfig.DBLoginInformation).ResultForSync(); iFrameUrl = BuildHistogramUri(histogramQuery, duration); break; default: throw new ArgumentOutOfRangeException(nameof(frameType)); } stb.Append(@"<style>iframe{width: 1px;min-width: 100%;border: none; width: 100%; height: 475px}</style>"); stb.Append(Invariant($"<iframe id=\"tableFrame\" src=\"{iFrameUrl}\" scrolling=\"no\"></iframe>")); stb.Append(Invariant($"<script>iFrameResize({{log:false}}, '#{TableFrameId}')</script>")); return(stb.ToString()); }
private string BuildHistogramPage(NameValueCollection parts) { StringBuilder stb = new StringBuilder(); var query = parts[QueryPartId] ?? string.Empty; var duration = parts[QueryDurationId] ?? string.Empty; QueryDuration queryDuration = QueryDuration.D1h; _ = Enum.TryParse <QueryDuration>(duration, out queryDuration); IncludeDataTableFiles(stb); IncludeResourceScript(stb, "iframeResizer.contentWindow.min.js"); try { var culture = CultureInfo.CurrentUICulture; TimeSpan durationTimeSpan = InfluxDbQueryBuilder.GetTimeSpan(queryDuration); var queryData = GetData(HttpUtility.UrlDecode(query)); var histogram = InfluxDBHelper.CreateHistogram(queryData, durationTimeSpan); if (histogram.Count > 0) { //Display the first row/column only stb.Append("<table id=\"results\" class=\"cell-border compact\" style=\"width:100%\">"); stb.Append(@"<thead align='left'><tr>"); stb.Append(@"<th>Value</th>"); stb.Append(@"<th>Total time</th>"); stb.Append(@"<th>Percentage</th>"); stb.Append(@"</tr></thead>"); stb.Append(@"<tbody>"); var firstRow = queryData[0]; foreach (var pair in histogram) { stb.Append(@"<tr class='tablecell'>"); stb.Append(@"<td>"); stb.Append(HtmlEncode(FirstCharToUpper(pair.Key, culture))); stb.Append(@"</td>"); stb.Append(@"<td>"); stb.Append(HtmlEncode(InfluxDBHelper.GetSerieValue(culture, pair.Value))); stb.Append(@"</td>"); stb.Append(@"<td>"); double percentage = 100 * pair.Value.TotalMilliseconds / durationTimeSpan.TotalMilliseconds; stb.Append(HtmlEncode(InfluxDBHelper.GetSerieValue(culture, percentage))); stb.Append(@"</td>"); stb.Append(@"</tr>"); } stb.Append(@"</tbody>"); stb.AppendLine(@"</table>"); } return(stb.ToString()); } catch (Exception ex) { return(Invariant($"<br><div style='color:Red'>{ex.GetFullMessage()}</div><br>")); } }