coder · mtojek · Oct 19, 2023 · Oct 13, 2023 · Oct 13, 2023 · Oct 13, 2023
diff --git a/cli/server.go b/cli/server.go
@@ -78,6 +78,7 @@ import (
 	"github.com/coder/coder/v2/coderd/httpmw"
 	"github.com/coder/coder/v2/coderd/oauthpki"
 	"github.com/coder/coder/v2/coderd/prometheusmetrics"
+	"github.com/coder/coder/v2/coderd/prometheusmetrics/insights"
 	"github.com/coder/coder/v2/coderd/schedule"
 	"github.com/coder/coder/v2/coderd/telemetry"
 	"github.com/coder/coder/v2/coderd/tracing"
@@ -198,6 +199,21 @@ func enablePrometheus(
 	}
 	afterCtx(ctx, closeWorkspacesFunc)
 
+	insightsMetricsCollector, err := insights.NewMetricsCollector(options.Database, options.Logger, 0, 0)
+	if err != nil {
+		return nil, xerrors.Errorf("unable to initialize insights metrics collector: %w", err)
+	}
+	err = options.PrometheusRegistry.Register(insightsMetricsCollector)
+	if err != nil {
+		return nil, xerrors.Errorf("unable to register insights metrics collector: %w", err)
+	}
+
+	closeInsightsMetricsCollector, err := insightsMetricsCollector.Run(ctx)
+	if err != nil {
+		return nil, xerrors.Errorf("unable to run insights metrics collector: %w", err)
+	}
+	afterCtx(ctx, closeInsightsMetricsCollector)
+
 	if vals.Prometheus.CollectAgentStats {
 		closeAgentStatsFunc, err := prometheusmetrics.AgentStats(ctx, logger, options.PrometheusRegistry, options.Database, time.Now(), 0)
 		if err != nil {

diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -1324,6 +1324,13 @@ func (q *querier) GetTemplateInsightsByInterval(ctx context.Context, arg databas
 	return q.db.GetTemplateInsightsByInterval(ctx, arg)
 }
 
+func (q *querier) GetTemplateInsightsByTemplate(ctx context.Context, arg database.GetTemplateInsightsByTemplateParams) ([]database.GetTemplateInsightsByTemplateRow, error) {
+	if err := q.authorizeContext(ctx, rbac.ActionUpdate, rbac.ResourceTemplate.All()); err != nil {
+		return nil, err
+	}
+	return q.db.GetTemplateInsightsByTemplate(ctx, arg)
+}
+
 func (q *querier) GetTemplateParameterInsights(ctx context.Context, arg database.GetTemplateParameterInsightsParams) ([]database.GetTemplateParameterInsightsRow, error) {
 	for _, templateID := range arg.TemplateIDs {
 		template, err := q.db.GetTemplateByID(ctx, templateID)

diff --git a/coderd/database/dbfake/dbfake.go b/coderd/database/dbfake/dbfake.go
@@ -2500,6 +2500,10 @@ func (q *FakeQuerier) GetTemplateInsights(_ context.Context, arg database.GetTem
 
 	templateIDSet := make(map[uuid.UUID]struct{})
 	appUsageIntervalsByUser := make(map[uuid.UUID]map[time.Time]*database.GetTemplateInsightsRow)
+
+	q.mutex.RLock()
+	defer q.mutex.RUnlock()
+
 	for _, s := range q.workspaceAgentStats {
 		if s.CreatedAt.Before(arg.StartTime) || s.CreatedAt.Equal(arg.EndTime) || s.CreatedAt.After(arg.EndTime) {
 			continue
@@ -2648,6 +2652,101 @@ func (q *FakeQuerier) GetTemplateInsightsByInterval(ctx context.Context, arg dat
 	return result, nil
 }
 
+func (q *FakeQuerier) GetTemplateInsightsByTemplate(_ context.Context, arg database.GetTemplateInsightsByTemplateParams) ([]database.GetTemplateInsightsByTemplateRow, error) {
+	err := validateDatabaseType(arg)
+	if err != nil {
+		return nil, err
+	}
+
+	q.mutex.RLock()
+	defer q.mutex.RUnlock()
+
+	// map time.Time x TemplateID x UserID x <usage>
+	appUsageByTemplateAndUser := map[time.Time]map[uuid.UUID]map[uuid.UUID]database.GetTemplateInsightsByTemplateRow{}
+
+	// Review agent stats in terms of usage
+	templateIDSet := make(map[uuid.UUID]struct{})
+
+	for _, s := range q.workspaceAgentStats {
+		if s.CreatedAt.Before(arg.StartTime) || s.CreatedAt.Equal(arg.EndTime) || s.CreatedAt.After(arg.EndTime) {
+			continue
+		}
+		if s.ConnectionCount == 0 {
+			continue
+		}
+
+		t := s.CreatedAt.Truncate(time.Minute)
+		templateIDSet[s.TemplateID] = struct{}{}
+
+		if _, ok := appUsageByTemplateAndUser[t]; !ok {
+			appUsageByTemplateAndUser[t] = make(map[uuid.UUID]map[uuid.UUID]database.GetTemplateInsightsByTemplateRow)
+		}
+
+		if _, ok := appUsageByTemplateAndUser[t][s.TemplateID]; !ok {
+			appUsageByTemplateAndUser[t][s.TemplateID] = make(map[uuid.UUID]database.GetTemplateInsightsByTemplateRow)
+		}
+
+		if _, ok := appUsageByTemplateAndUser[t][s.TemplateID][s.UserID]; !ok {
+			appUsageByTemplateAndUser[t][s.TemplateID][s.UserID] = database.GetTemplateInsightsByTemplateRow{}
+		}
+
+		u := appUsageByTemplateAndUser[t][s.TemplateID][s.UserID]
+		if s.SessionCountJetBrains > 0 {
+			u.UsageJetbrainsSeconds = 60
+		}
+		if s.SessionCountVSCode > 0 {
+			u.UsageVscodeSeconds = 60
+		}
+		if s.SessionCountReconnectingPTY > 0 {
+			u.UsageReconnectingPtySeconds = 60
+		}
+		if s.SessionCountSSH > 0 {
+			u.UsageSshSeconds = 60
+		}
+		appUsageByTemplateAndUser[t][s.TemplateID][s.UserID] = u
+	}
+
+	// Sort used templates
+	templateIDs := make([]uuid.UUID, 0, len(templateIDSet))
+	for templateID := range templateIDSet {
+		templateIDs = append(templateIDs, templateID)
+	}
+	slices.SortFunc(templateIDs, func(a, b uuid.UUID) int {
+		return slice.Ascending(a.String(), b.String())
+	})
+
+	// Build result
+	var result []database.GetTemplateInsightsByTemplateRow
+	for _, templateID := range templateIDs {
+		r := database.GetTemplateInsightsByTemplateRow{
+			TemplateID: templateID,
+		}
+
+		uniqueUsers := map[uuid.UUID]struct{}{}
+
+		for _, mTemplateUserUsage := range appUsageByTemplateAndUser {
+			mUserUsage, ok := mTemplateUserUsage[templateID]
+			if !ok {
+				continue // template was not used in this time window
+			}
+
+			for userID, usage := range mUserUsage {
+				uniqueUsers[userID] = struct{}{}
+
+				r.UsageJetbrainsSeconds += usage.UsageJetbrainsSeconds
+				r.UsageVscodeSeconds += usage.UsageVscodeSeconds
+				r.UsageReconnectingPtySeconds += usage.UsageReconnectingPtySeconds
+				r.UsageSshSeconds += usage.UsageSshSeconds
+			}
+		}
+
+		r.ActiveUsers = int64(len(uniqueUsers))
+
+		result = append(result, r)
+	}
+	return result, nil
+}
+
 func (q *FakeQuerier) GetTemplateParameterInsights(ctx context.Context, arg database.GetTemplateParameterInsightsParams) ([]database.GetTemplateParameterInsightsRow, error) {
 	err := validateDatabaseType(arg)
 	if err != nil {

diff --git a/coderd/database/dbmetrics/dbmetrics.go b/coderd/database/dbmetrics/dbmetrics.go
diff --git a/coderd/database/dbmock/dbmock.go b/coderd/database/dbmock/dbmock.go
diff --git a/coderd/database/querier.go b/coderd/database/querier.go
diff --git a/coderd/database/queries.sql.go b/coderd/database/queries.sql.go
diff --git a/coderd/database/queries/insights.sql b/coderd/database/queries/insights.sql
@@ -134,6 +134,34 @@ SELECT
 	COALESCE(SUM(usage_ssh_seconds), 0)::bigint AS usage_ssh_seconds
 FROM agent_stats_by_interval_and_user;
 
+-- name: GetTemplateInsightsByTemplate :many
+WITH agent_stats_by_interval_and_user AS (
+	SELECT
+		date_trunc('minute', was.created_at),
+		was.template_id,
+		was.user_id,
+		CASE WHEN SUM(was.session_count_vscode) > 0 THEN 60 ELSE 0 END AS usage_vscode_seconds,
+		CASE WHEN SUM(was.session_count_jetbrains) > 0 THEN 60 ELSE 0 END AS usage_jetbrains_seconds,
+		CASE WHEN SUM(was.session_count_reconnecting_pty) > 0 THEN 60 ELSE 0 END AS usage_reconnecting_pty_seconds,
+		CASE WHEN SUM(was.session_count_ssh) > 0 THEN 60 ELSE 0 END AS usage_ssh_seconds
+	FROM workspace_agent_stats was
+	WHERE
+		was.created_at >= @start_time::timestamptz
+		AND was.created_at < @end_time::timestamptz
+		AND was.connection_count > 0
+	GROUP BY date_trunc('minute', was.created_at), was.template_id, was.user_id
+)
+
+SELECT
+	template_id,
+	COALESCE(COUNT(DISTINCT user_id))::bigint AS active_users,
+	COALESCE(SUM(usage_vscode_seconds), 0)::bigint AS usage_vscode_seconds,
+	COALESCE(SUM(usage_jetbrains_seconds), 0)::bigint AS usage_jetbrains_seconds,
+	COALESCE(SUM(usage_reconnecting_pty_seconds), 0)::bigint AS usage_reconnecting_pty_seconds,
+	COALESCE(SUM(usage_ssh_seconds), 0)::bigint AS usage_ssh_seconds
+FROM agent_stats_by_interval_and_user
+GROUP BY template_id;
+
 -- name: GetTemplateAppInsights :many
 -- GetTemplateAppInsights returns the aggregate usage of each app in a given
 -- timeframe. The result can be filtered on template_ids, meaning only user data