Repository: akvorado/akvorado

Issue:
Home graph widget filtering/configuration
At the moment, the graph displayed at the home page is hardcoded to the data rate inbound from external interfaces ( https://github.com/akvorado/akvorado/blob/2303b579e443284808f9fa7c8195c2c16ae8b60f/console/widgets.go#L225 )

For our use case, a "total traffic" (without filtering per direction) or an option to filter for outbound traffic on external interfaces would be more relevant.
This could be done by rebuilding this query as "total flows", or with a configureable query filter portion.
Before implementing something like this, I'd like to discuss what is the best way.

I could also think of reusing the `console/graph/line`-endpoint for this, allowing all filters - which would be a more complex change however.


Discussion:
@vincentbernat: I think a configurable query filter portion would work.

Solution patch:
diff --git a/console/config.go b/console/config.go
index 8549cc59..e03233e1 100644
--- a/console/config.go
+++ b/console/config.go
@@ -26,6 +26,8 @@ type Configuration struct {
 	DimensionsLimit int `validate:"min=10"`
 	// CacheTTL tells how long to keep the most costly requests in cache.
 	CacheTTL time.Duration `validate:"min=5s"`
+	// HomepageGraphFilter defines the filtering string to use for the homepage graph
+	HomepageGraphFilter string
 }
 
 // VisualizeOptionsConfiguration defines options for the "visualize" tab.
@@ -55,9 +57,10 @@ func DefaultConfiguration() Configuration {
 			Dimensions: []query.Column{query.NewColumn("SrcAS")},
 			Limit:      10,
 		},
-		HomepageTopWidgets: []string{"src-as", "src-port", "protocol", "src-country", "etype"},
-		DimensionsLimit:    50,
-		CacheTTL:           30 * time.Minute,
+		HomepageTopWidgets:  []string{"src-as", "src-port", "protocol", "src-country", "etype"},
+		DimensionsLimit:     50,
+		CacheTTL:            30 * time.Minute,
+		HomepageGraphFilter: "InIfBoundary = 'external'",
 	}
 }
 
diff --git a/console/data/docs/02-configuration.md b/console/data/docs/02-configuration.md
index 31e6742c..cd53fd5c 100644
--- a/console/data/docs/02-configuration.md
+++ b/console/data/docs/02-configuration.md
@@ -772,6 +772,10 @@ The console itself accepts the following keys:
  - `homepage-top-widgets` to define the widgets to display on the home page
  - `dimensions-limit` to set the upper limit of the number of returned dimensions
  - `cache-ttl` sets the time costly requests are kept in cache
+ - `homepage-graph-filter` sets the filter for the graph on the
+    homepage (default: `InIfBoundary = 'external'`). 
+    This is a SQL expression, passed into the clickhouse query directly. 
+    It can also be empty, in which case the sum of all flows captured will be displayed.
 
 Here is an example:
 
diff --git a/console/widgets.go b/console/widgets.go
index dbe94ec7..9e508b97 100644
--- a/console/widgets.go
+++ b/console/widgets.go
@@ -213,6 +213,11 @@ LIMIT 5
 }
 
 func (c *Component) widgetGraphHandlerFunc(gc *gin.Context) {
+	// first step: define which filter to use
+	filter := c.config.HomepageGraphFilter
+	if filter != "" {
+		filter = fmt.Sprintf("AND %s", filter)
+	}
 	ctx := c.t.Context(gc.Request.Context())
 	now := c.d.Clock.Now()
 	query := c.finalizeQuery(fmt.Sprintf(`
@@ -222,7 +227,7 @@ SELECT
  SUM(Bytes*SamplingRate*8/{{ .Interval }})/1000/1000/1000 AS Gbps
 FROM {{ .Table }}
 WHERE {{ .Timefilter }}
-AND InIfBoundary = 'external'
+%s
 GROUP BY Time
 ORDER BY Time WITH FILL
  FROM {{ .TimefilterStart }}
@@ -234,7 +239,8 @@ ORDER BY Time WITH FILL
 			End:               now,
 			MainTableRequired: false,
 			Points:            200,
-		})))
+		}),
+		filter))
 	gc.Header("X-SQL-Query", query)
 
 	results := []struct {



Test patch:
diff --git a/console/widgets_test.go b/console/widgets_test.go
index e1aa949f..afdc3836 100644
--- a/console/widgets_test.go
+++ b/console/widgets_test.go
@@ -230,23 +230,13 @@ func TestWidgetTop(t *testing.T) {
 }
 
 func TestWidgetGraph(t *testing.T) {
-	_, h, mockConn, mockClock := NewMock(t, DefaultConfiguration())
-
-	base := time.Date(2009, time.November, 10, 23, 0, 0, 0, time.UTC)
-	mockClock.Set(base.Add(24 * time.Hour))
-	expected := []struct {
-		Time time.Time `json:"t"`
-		Gbps float64   `json:"gbps"`
+	testcases := []struct {
+		config Configuration
+		query  string
 	}{
-		{base, 25.3},
-		{base.Add(time.Minute), 27.8},
-		{base.Add(2 * time.Minute), 26.4},
-		{base.Add(3 * time.Minute), 29.2},
-		{base.Add(4 * time.Minute), 0},
-		{base.Add(5 * time.Minute), 24.7},
-	}
-	mockConn.EXPECT().
-		Select(gomock.Any(), gomock.Any(), strings.TrimSpace(`
+		{
+			config: DefaultConfiguration(),
+			query: `
 SELECT
  toStartOfInterval(TimeReceived + INTERVAL 144 second, INTERVAL 432 second) - INTERVAL 144 second AS Time,
  SUM(Bytes*SamplingRate*8/432)/1000/1000/1000 AS Gbps
@@ -257,23 +247,82 @@ GROUP BY Time
 ORDER BY Time WITH FILL
  FROM toDateTime('2009-11-10 23:00:00', 'UTC')
  TO toDateTime('2009-11-11 23:00:00', 'UTC') + INTERVAL 1 second
- STEP 432`)).
-		SetArg(1, expected).
-		Return(nil)
+ STEP 432`,
+		},
+		{
+			config: func() Configuration {
+				c := DefaultConfiguration()
+				c.HomepageGraphFilter = ""
+				return c
+			}(),
+			query: `
+SELECT
+ toStartOfInterval(TimeReceived + INTERVAL 144 second, INTERVAL 432 second) - INTERVAL 144 second AS Time,
+ SUM(Bytes*SamplingRate*8/432)/1000/1000/1000 AS Gbps
+FROM flows
+WHERE TimeReceived BETWEEN toDateTime('2009-11-10 23:00:00', 'UTC') AND toDateTime('2009-11-11 23:00:00', 'UTC')
 
-	helpers.TestHTTPEndpoints(t, h.LocalAddr(), helpers.HTTPEndpointCases{
+GROUP BY Time
+ORDER BY Time WITH FILL
+ FROM toDateTime('2009-11-10 23:00:00', 'UTC')
+ TO toDateTime('2009-11-11 23:00:00', 'UTC') + INTERVAL 1 second
+ STEP 432`,
+		},
 		{
-			URL: "/api/v0/console/widget/graph",
-			JSONOutput: gin.H{
-				"data": []gin.H{
-					{"t": "2009-11-10T23:00:00Z", "gbps": 25.3},
-					{"t": "2009-11-10T23:01:00Z", "gbps": 27.8},
-					{"t": "2009-11-10T23:02:00Z", "gbps": 26.4},
-					{"t": "2009-11-10T23:03:00Z", "gbps": 29.2},
-					{"t": "2009-11-10T23:04:00Z", "gbps": 0},
-					{"t": "2009-11-10T23:05:00Z", "gbps": 24.7},
+			config: func() Configuration {
+				c := DefaultConfiguration()
+				c.HomepageGraphFilter = "OutIfBoundary = 'external'"
+				return c
+			}(),
+			query: `
+SELECT
+ toStartOfInterval(TimeReceived + INTERVAL 144 second, INTERVAL 432 second) - INTERVAL 144 second AS Time,
+ SUM(Bytes*SamplingRate*8/432)/1000/1000/1000 AS Gbps
+FROM flows
+WHERE TimeReceived BETWEEN toDateTime('2009-11-10 23:00:00', 'UTC') AND toDateTime('2009-11-11 23:00:00', 'UTC')
+AND OutIfBoundary = 'external'
+GROUP BY Time
+ORDER BY Time WITH FILL
+ FROM toDateTime('2009-11-10 23:00:00', 'UTC')
+ TO toDateTime('2009-11-11 23:00:00', 'UTC') + INTERVAL 1 second
+ STEP 432`,
+		},
+	}
+	for _, tcase := range testcases {
+		_, h, mockConn, mockClock := NewMock(t, tcase.config)
+
+		base := time.Date(2009, time.November, 10, 23, 0, 0, 0, time.UTC)
+		mockClock.Set(base.Add(24 * time.Hour))
+		expected := []struct {
+			Time time.Time `json:"t"`
+			Gbps float64   `json:"gbps"`
+		}{
+			{base, 25.3},
+			{base.Add(time.Minute), 27.8},
+			{base.Add(2 * time.Minute), 26.4},
+			{base.Add(3 * time.Minute), 29.2},
+			{base.Add(4 * time.Minute), 0},
+			{base.Add(5 * time.Minute), 24.7},
+		}
+		mockConn.EXPECT().
+			Select(gomock.Any(), gomock.Any(), strings.TrimSpace(tcase.query)).
+			SetArg(1, expected).
+			Return(nil)
+
+		helpers.TestHTTPEndpoints(t, h.LocalAddr(), helpers.HTTPEndpointCases{
+			{
+				URL: "/api/v0/console/widget/graph",
+				JSONOutput: gin.H{
+					"data": []gin.H{
+						{"t": "2009-11-10T23:00:00Z", "gbps": 25.3},
+						{"t": "2009-11-10T23:01:00Z", "gbps": 27.8},
+						{"t": "2009-11-10T23:02:00Z", "gbps": 26.4},
+						{"t": "2009-11-10T23:03:00Z", "gbps": 29.2},
+						{"t": "2009-11-10T23:04:00Z", "gbps": 0},
+						{"t": "2009-11-10T23:05:00Z", "gbps": 24.7},
+					},
 				},
 			},
-		},
-	})
+		})
+	}
 }