coder · mtojek · Nov 7, 2023 · Oct 19, 2023 · Oct 19, 2023 · Oct 19, 2023
diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -1265,6 +1265,13 @@ func (q *querier) GetTemplateAppInsights(ctx context.Context, arg database.GetTe
 	return q.db.GetTemplateAppInsights(ctx, arg)
 }
 
+func (q *querier) GetTemplateAppInsightsByTemplate(ctx context.Context, arg database.GetTemplateAppInsightsByTemplateParams) ([]database.GetTemplateAppInsightsByTemplateRow, error) {
+	if err := q.authorizeContext(ctx, rbac.ActionUpdate, rbac.ResourceTemplate.All()); err != nil {
+		return nil, err
+	}
+	return q.db.GetTemplateAppInsightsByTemplate(ctx, arg)
+}
+
 // Only used by metrics cache.
 func (q *querier) GetTemplateAverageBuildTime(ctx context.Context, arg database.GetTemplateAverageBuildTimeParams) (database.GetTemplateAverageBuildTimeRow, error) {
 	if err := q.authorizeContext(ctx, rbac.ActionRead, rbac.ResourceSystem); err != nil {

diff --git a/coderd/database/dbmem/dbmem.go b/coderd/database/dbmem/dbmem.go
@@ -2365,6 +2365,106 @@ func (q *FakeQuerier) GetTemplateAppInsights(ctx context.Context, arg database.G
 	return rows, nil
 }
 
+func (q *FakeQuerier) GetTemplateAppInsightsByTemplate(ctx context.Context, arg database.GetTemplateAppInsightsByTemplateParams) ([]database.GetTemplateAppInsightsByTemplateRow, error) {
+	err := validateDatabaseType(arg)
+	if err != nil {
+		return nil, err
+	}
+
+	q.mutex.RLock()
+	defer q.mutex.RUnlock()
+
+	type uniqueKey struct {
+		TemplateID  uuid.UUID
+		DisplayName string
+		Slug        string
+	}
+
+	// map (TemplateID + DisplayName + Slug) x time.Time x UserID x <usage>
+	usageByTemplateAppUser := map[uniqueKey]map[time.Time]map[uuid.UUID]int64{}
+
+	// Review agent stats in terms of usage
+	for _, s := range q.workspaceAppStats {
+		// (was.session_started_at >= ts.from_ AND was.session_started_at < ts.to_)
+		// OR (was.session_ended_at > ts.from_ AND was.session_ended_at < ts.to_)
+		// OR (was.session_started_at < ts.from_ AND was.session_ended_at >= ts.to_)
+		if !(((s.SessionStartedAt.After(arg.StartTime) || s.SessionStartedAt.Equal(arg.StartTime)) && s.SessionStartedAt.Before(arg.EndTime)) ||
+			(s.SessionEndedAt.After(arg.StartTime) && s.SessionEndedAt.Before(arg.EndTime)) ||
+			(s.SessionStartedAt.Before(arg.StartTime) && (s.SessionEndedAt.After(arg.EndTime) || s.SessionEndedAt.Equal(arg.EndTime)))) {
+			continue
+		}
+
+		w, err := q.getWorkspaceByIDNoLock(ctx, s.WorkspaceID)
+		if err != nil {
+			return nil, err
+		}
+
+		app, _ := q.getWorkspaceAppByAgentIDAndSlugNoLock(ctx, database.GetWorkspaceAppByAgentIDAndSlugParams{
+			AgentID: s.AgentID,
+			Slug:    s.SlugOrPort,
+		})
+
+		key := uniqueKey{
+			TemplateID:  w.TemplateID,
+			DisplayName: app.DisplayName,
+			Slug:        app.Slug,
+		}
+
+		t := s.SessionStartedAt.Truncate(time.Minute)
+		if t.Before(arg.StartTime) {
+			t = arg.StartTime
+		}
+		for t.Before(s.SessionEndedAt) && t.Before(arg.EndTime) {
+			if _, ok := usageByTemplateAppUser[key]; !ok {
+				usageByTemplateAppUser[key] = map[time.Time]map[uuid.UUID]int64{}
+			}
+			if _, ok := usageByTemplateAppUser[key][t]; !ok {
+				usageByTemplateAppUser[key][t] = map[uuid.UUID]int64{}
+			}
+			if _, ok := usageByTemplateAppUser[key][t][s.UserID]; !ok {
+				usageByTemplateAppUser[key][t][s.UserID] = 60 // 1 minute
+			}
+			t = t.Add(1 * time.Minute)
+		}
+	}
+
+	// Sort usage data
+	usageKeys := make([]uniqueKey, len(usageByTemplateAppUser))
+	var i int
+	for key := range usageByTemplateAppUser {
+		usageKeys[i] = key
+		i++
+	}
+
+	slices.SortFunc(usageKeys, func(a, b uniqueKey) int {
+		if a.TemplateID != b.TemplateID {
+			return slice.Ascending(a.TemplateID.String(), b.TemplateID.String())
+		}
+		if a.DisplayName != b.DisplayName {
+			return slice.Ascending(a.DisplayName, b.DisplayName)
+		}
+		return slice.Ascending(a.Slug, b.Slug)
+	})
+
+	// Build result
+	var result []database.GetTemplateAppInsightsByTemplateRow
+	for _, usageKey := range usageKeys {
+		r := database.GetTemplateAppInsightsByTemplateRow{
+			TemplateID:  usageKey.TemplateID,
+			DisplayName: sql.NullString{String: usageKey.DisplayName, Valid: true},
+			SlugOrPort:  usageKey.Slug,
+		}
+		for _, mUserUsage := range usageByTemplateAppUser[usageKey] {
+			r.ActiveUsers += int64(len(mUserUsage))
+			for _, usage := range mUserUsage {
+				r.UsageSeconds += usage
+			}
+		}
+		result = append(result, r)
+	}
+	return result, nil
+}
+
 func (q *FakeQuerier) GetTemplateAverageBuildTime(ctx context.Context, arg database.GetTemplateAverageBuildTimeParams) (database.GetTemplateAverageBuildTimeRow, error) {
 	if err := validateDatabaseType(arg); err != nil {
 		return database.GetTemplateAverageBuildTimeRow{}, err

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
@@ -218,6 +218,53 @@ SELECT
 FROM app_stats_by_user_and_agent
 GROUP BY access_method, slug_or_port, display_name, icon, is_app;
 
+-- name: GetTemplateAppInsightsByTemplate :many
+WITH app_stats_by_user_and_agent AS (
+	SELECT
+		s.start_time,
+		60 as seconds,
+		w.template_id,
+		was.user_id,
+		was.agent_id,
+		was.slug_or_port,
+		wa.display_name,
+		(wa.slug IS NOT NULL)::boolean AS is_app
+	FROM workspace_app_stats was
+	JOIN workspaces w ON (
+		w.id = was.workspace_id
+	)
+	-- We do a left join here because we want to include user IDs that have used
+	-- e.g. ports when counting active users.
+	LEFT JOIN workspace_apps wa ON (
+		wa.agent_id = was.agent_id
+		AND wa.slug = was.slug_or_port
+	)
+	-- This table contains both 1 minute entries and >1 minute entries,
+	-- to calculate this with our uniqueness constraints, we generate series
+	-- for the longer intervals.
+	CROSS JOIN LATERAL generate_series(
+		date_trunc('minute', was.session_started_at),
+		-- Subtract 1 microsecond to avoid creating an extra series.
+		date_trunc('minute', was.session_ended_at - '1 microsecond'::interval),
+		'1 minute'::interval
+	) s(start_time)
+	WHERE
+		s.start_time >= @start_time::timestamptz
+		-- Subtract one minute because the series only contains the start time.
+		AND s.start_time < (@end_time::timestamptz) - '1 minute'::interval
+	GROUP BY s.start_time, w.template_id, was.user_id, was.agent_id, was.slug_or_port, wa.display_name, wa.slug
+)
+
+SELECT
+	template_id,
+	display_name,
+	slug_or_port,
+	COALESCE(COUNT(DISTINCT user_id))::bigint AS active_users,
+	SUM(seconds) AS usage_seconds
+FROM app_stats_by_user_and_agent
+WHERE is_app IS TRUE
+GROUP BY template_id, display_name, slug_or_port;
+
 -- name: GetTemplateInsightsByInterval :many
 -- GetTemplateInsightsByInterval returns all intervals between start and end
 -- time, if end time is a partial interval, it will be included in the results and

diff --git a/coderd/insights.go b/coderd/insights.go
@@ -452,15 +452,15 @@ func convertTemplateInsightsApps(usage database.GetTemplateInsightsRow, appUsage
 		{
 			TemplateIDs: usage.TemplateIDs,
 			Type:        codersdk.TemplateAppsTypeBuiltin,
-			DisplayName: "Visual Studio Code",
+			DisplayName: codersdk.TemplateBuiltinAppDisplayNameVSCode,
 			Slug:        "vscode",
 			Icon:        "/icon/code.svg",
 			Seconds:     usage.UsageVscodeSeconds,
 		},
 		{
 			TemplateIDs: usage.TemplateIDs,
 			Type:        codersdk.TemplateAppsTypeBuiltin,
-			DisplayName: "JetBrains",
+			DisplayName: codersdk.TemplateBuiltinAppDisplayNameJetBrains,
 			Slug:        "jetbrains",
 			Icon:        "/icon/intellij.svg",
 			Seconds:     usage.UsageJetbrainsSeconds,
@@ -474,15 +474,15 @@ func convertTemplateInsightsApps(usage database.GetTemplateInsightsRow, appUsage
 		{
 			TemplateIDs: usage.TemplateIDs,
 			Type:        codersdk.TemplateAppsTypeBuiltin,
-			DisplayName: "Web Terminal",
+			DisplayName: codersdk.TemplateBuiltinAppDisplayNameWebTerminal,
 			Slug:        "reconnecting-pty",
 			Icon:        "/icon/terminal.svg",
 			Seconds:     usage.UsageReconnectingPtySeconds,
 		},
 		{
 			TemplateIDs: usage.TemplateIDs,
 			Type:        codersdk.TemplateAppsTypeBuiltin,
-			DisplayName: "SSH",
+			DisplayName: codersdk.TemplateBuiltinAppDisplayNameSSH,
 			Slug:        "ssh",
 			Icon:        "/icon/terminal.svg",
 			Seconds:     usage.UsageSshSeconds,