private static string GetInfluxDBDuration(QueryDuration duration) { switch (duration) { case QueryDuration.D1h: return("1h"); case QueryDuration.D6h: return("6h"); case QueryDuration.D12h: return("12h"); case QueryDuration.D24h: return("24h"); case QueryDuration.D7d: return("7d"); case QueryDuration.D30d: return("30d"); case QueryDuration.D60d: return("60d"); case QueryDuration.D180d: return("180d"); case QueryDuration.D365d: return("365d"); default: throw new ArgumentOutOfRangeException(nameof(duration)); } }
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()); }
private static TimeSpan GetDefaultInfluxDBGroupInterval(QueryDuration duration) { switch (duration) { case QueryDuration.D1h: return(TimeSpan.FromSeconds(1)); case QueryDuration.D6h: return(TimeSpan.FromSeconds(5)); case QueryDuration.D12h: return(TimeSpan.FromSeconds(15)); case QueryDuration.D24h: return(TimeSpan.FromMinutes(1)); case QueryDuration.D7d: return(TimeSpan.FromMinutes(5)); case QueryDuration.D30d: return(TimeSpan.FromMinutes(30)); case QueryDuration.D60d: return(TimeSpan.FromHours(1)); case QueryDuration.D180d: return(TimeSpan.FromHours(12)); case QueryDuration.D365d: return(TimeSpan.FromHours(24)); default: throw new ArgumentOutOfRangeException(nameof(duration)); } }
public static TimeSpan GetTimeSpan(QueryDuration duration) { switch (duration) { case QueryDuration.D1h: return(TimeSpan.FromHours(1)); case QueryDuration.D6h: return(TimeSpan.FromHours(6)); case QueryDuration.D12h: return(TimeSpan.FromHours(12)); case QueryDuration.D24h: return(TimeSpan.FromHours(24)); case QueryDuration.D7d: return(TimeSpan.FromHours(24 * 7)); case QueryDuration.D30d: return(TimeSpan.FromHours(30 * 24)); case QueryDuration.D60d: return(TimeSpan.FromHours(60 * 24)); case QueryDuration.D180d: return(TimeSpan.FromHours(180 * 24)); case QueryDuration.D365d: return(TimeSpan.FromHours(365 * 24)); default: throw new ArgumentOutOfRangeException(nameof(duration)); } }
private static string BuildHistogramUri(string finalQuery, QueryDuration queryDuration) { return(BuildUri(pageUrl, new NameValueCollection() { { PageTypeId, DeviceHistogramPageType }, { QueryPartId, finalQuery }, { QueryDurationId, queryDuration.ToString() }, })); }
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")); }
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})")); }
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()); }
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()); }
public static string GetDeviceHistoryTabQuery(DevicePersistenceData data, string deviceName, QueryDuration queryDuration) { StringBuilder stb = new StringBuilder(); stb.Append("SELECT "); stb.Append(GetFields(data)[0]); stb.Append(" AS \""); stb.Append(deviceName); stb.Append("\" from \""); stb.Append(data.Measurement); stb.Append("\" WHERE "); stb.Append(PluginConfig.DeviceRefIdTag); stb.Append("='"); stb.AppendFormat(CultureInfo.InvariantCulture, "{0}", data.DeviceRefId); stb.Append("'"); stb.AppendFormat(CultureInfo.InvariantCulture, " AND time > now() - {0} ORDER BY time DESC", GetInfluxDBDuration(queryDuration)); return(stb.ToString()); }
public string GetDeviceHistoryTab(int refId) { var dataKeyPair = pluginConfig.DevicePersistenceData.FirstOrDefault(x => x.Value.DeviceRefId == refId); var data = dataKeyPair.Value; if ((data != null) && !string.IsNullOrWhiteSpace(pluginConfig.DBLoginInformation.DB)) { bool hasNumericData = !string.IsNullOrWhiteSpace(data.Field); IFrameType DefaultFrameType = hasNumericData ? IFrameType.ChartHistory : IFrameType.TableHistory; QueryDuration DefaultDuration = QueryDuration.D6h; IFrameGrouping DefaultGrouping = IFrameGrouping.Auto; StringBuilder stb = new StringBuilder(); IncludeDataTableFiles(stb); IncludeResourceScript(stb, "iframeSizer.min.js"); stb.Append(@"<table style='width:100%;border-spacing:0px;'"); stb.Append("<tr height='5'><td></td></tr>"); stb.Append("<tr><td>"); stb.Append("Type:"); NameValueCollection iframeType = new NameValueCollection(); AddEnumValue(iframeType, IFrameType.TableHistory); if (hasNumericData) { AddEnumValue(iframeType, IFrameType.ChartHistory); AddEnumValue(iframeType, IFrameType.AverageStats); } stb.Append(FormDropDown(IFrameTypeId, iframeType, DefaultFrameType.ToString(), 150, string.Empty, true, DeviceUtiltyPageName)); stb.Append(" Duration:"); NameValueCollection duration = CreateNameValueCreation <QueryDuration>(); stb.Append(FormDropDown(IFrameDurationId, duration, DefaultDuration.ToString(), 100, string.Empty, true, DeviceUtiltyPageName)); stb.Append(" Grouping:"); NameValueCollection grouping = CreateNameValueCreation <IFrameGrouping>(); stb.Append(FormDropDown(IFrameGroupingId, grouping, DefaultGrouping.ToString(), 100, string.Empty, true, DeviceUtiltyPageName, hasNumericData)); stb.Append("</td></tr>"); stb.Append("<tr height='5'><td></td></tr>"); stb.Append("<tr><td class='tablecell'>"); stb.Append(DivStart(resultsDivPartId, string.Empty)); stb.Append(GetQueryResultFrame(data, DefaultFrameType, DefaultDuration, DefaultGrouping)); stb.Append(DivEnd()); stb.Append("</td></tr>"); stb.Append("<tr height='5'><td></td></tr>"); stb.Append("<tr><td>"); stb.Append(PageTypeButton(Invariant($"Edit{data.Id}"), "Edit", EditDevicePageType, id: data.Id)); stb.Append(" "); stb.Append(PageTypeButton(Invariant($"Queries{data.Id}"), "More Queries", HistoryDevicePageType, id: data.Id)); stb.Append(" "); stb.Append(FormButton(DoneButtonId, "Done", string.Empty, DeviceUtiltyPageName)); stb.Append("</td></tr>"); stb.Append("</table>"); return(stb.ToString()); } return(string.Empty); }
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>")); } }