diff options
| -rw-r--r-- | src/main/java/com/mesalab/qgw/model/basic/DSLQueryContext.java | 14 | ||||
| -rw-r--r-- | src/main/resources/dsl-sql-template.sql | 2 |
2 files changed, 14 insertions, 2 deletions
diff --git a/src/main/java/com/mesalab/qgw/model/basic/DSLQueryContext.java b/src/main/java/com/mesalab/qgw/model/basic/DSLQueryContext.java index d72f3f27..ce3a7894 100644 --- a/src/main/java/com/mesalab/qgw/model/basic/DSLQueryContext.java +++ b/src/main/java/com/mesalab/qgw/model/basic/DSLQueryContext.java @@ -38,6 +38,8 @@ public class DSLQueryContext implements Serializable { sql = StrUtil.isNotEmpty(table) ? sql.replace("$table", table) : sql; + sql = !CollectionUtils.isEmpty(this.intervals) ? sql.replace("$interval_seconds", Long.toString(getIntervalSeconds(this.intervals))) : sql; + if (StrUtil.isEmpty(this.filter) && CollectionUtils.isEmpty(this.intervals)) { sql = sql.replace("$intervals_and_filter", " 1= 1 "); } else if (StrUtil.isNotEmpty(this.filter) && !CollectionUtils.isEmpty(this.intervals)) { @@ -54,6 +56,16 @@ public class DSLQueryContext implements Serializable { return sql; } + private long getIntervalSeconds(List<String> intervals) { + if (CollectionUtils.isEmpty(intervals) || !intervals.get(0).contains("/")) { + throw new IllegalArgumentException(); + } + String[] split = intervals.get(0).split("/"); + long start = DateUtil.parse(split[0]).getTime() / 1000; + long end = DateUtil.parse(split[1]).getTime() / 1000; + return end - start; + } + private String parseIntervals(List<String> intervals, String timeField, String timeFieldType) { if (CollectionUtils.isEmpty(intervals) || !intervals.get(0).contains("/")) { return null; @@ -64,7 +76,7 @@ public class DSLQueryContext implements Serializable { StringBuffer whereOfTime = new StringBuffer(); if ("unix_timestamp".equalsIgnoreCase(timeFieldType)) { return whereOfTime.append(timeField).append(">= ").append(DateUtil.parse(start).getTime() / 1000).append(" AND ").append(timeField).append("< ").append(DateUtil.parse(end).getTime() / 1000).toString(); - } else{ + } else { return whereOfTime.append(timeField).append(">= '").append(start).append("' AND ").append(timeField).append("< '").append(end).append("'").toString(); } } diff --git a/src/main/resources/dsl-sql-template.sql b/src/main/resources/dsl-sql-template.sql index 1c5e606b..0929ee20 100644 --- a/src/main/resources/dsl-sql-template.sql +++ b/src/main/resources/dsl-sql-template.sql @@ -17,7 +17,7 @@ SELECT protocol_stack_id, SUM(sessions) as sessions,SUM(c2s_bytes) as c2s_bytes, SELECT FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), '$granularity', 'zero')) as stat_time, protocol_stack_id as type, sum(c2s_bytes + s2c_bytes) as bytes from $table where $intervals_and_filter group by FROM_UNIXTIME(TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), '$granularity', 'zero')), protocol_stack_id order by stat_time asc #end #sql("APPLICATION_AND_PROTOCOL_TOP_APPS") -SELECT app_name as app_name, SUM(bytes) as bytes, SUM(sessions) as sessions, MAX(bytes_rate) as max_rate, AVG(bytes_rate) as avg_rate FROM (SELECT app_name as app_name, SUM(sessions) as sessions, SUM(c2s_bytes + s2c_bytes) as bytes, SUM(c2s_bytes + s2c_bytes) * 8 / $granularity_seconds as bytes_rate FROM $table WHERE $intervals_and_filter AND notEmpty(app_name) GROUP BY app_name, TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), '$granularity') ORDER BY bytes DESC ) GROUP BY app_name ORDER BY bytes DESC $limit +SELECT app_name as app_name, SUM(bytes) as bytes, SUM(sessions) as sessions, MAX(bit_rate) as max_rate, SUM(bytes)/$interval_seconds as avg_rate FROM (SELECT app_name as app_name, SUM(sessions) as sessions, SUM(c2s_bytes + s2c_bytes) as bytes, SUM(c2s_bytes + s2c_bytes) * 8 / $granularity_seconds as bit_rate FROM $table WHERE $intervals_and_filter AND notEmpty(app_name) GROUP BY app_name, TIME_FLOOR_WITH_FILL(UNIX_TIMESTAMP(__time), '$granularity') ORDER BY bytes DESC ) GROUP BY app_name ORDER BY bytes DESC $limit #end #sql("APPLICATION_AND_PROTOCOL_APP_RELATED_INTERNAL_IPS") SELECT if(bitAnd(flags, 8) = 8, client_ip, server_ip) AS ip, SUM(sent_bytes + received_bytes) AS bytes FROM $table WHERE $intervals_and_filter GROUP BY ip ORDER BY bytes DESC $limit |