coder · mtojek · Sep 26, 2023 · Sep 21, 2023 · Sep 21, 2023 · Sep 21, 2023
diff --git a/coderd/apidoc/docs.go b/coderd/apidoc/docs.go
diff --git a/coderd/apidoc/swagger.json b/coderd/apidoc/swagger.json
diff --git a/coderd/coderd.go b/coderd/coderd.go
@@ -895,6 +895,7 @@ func New(options *Options) *API {
 		r.Route("/insights", func(r chi.Router) {
 			r.Use(apiKeyMiddleware)
 			r.Get("/daus", api.deploymentDAUs)
+			r.Get("/user-activity", api.insightsUserActivity)
 			r.Get("/user-latency", api.insightsUserLatency)
 			r.Get("/templates", api.insightsTemplates)
 		})

diff --git a/coderd/database/dbauthz/dbauthz.go b/coderd/database/dbauthz/dbauthz.go
@@ -1478,6 +1478,25 @@ func (q *querier) GetUnexpiredLicenses(ctx context.Context) ([]database.License,
 	return q.db.GetUnexpiredLicenses(ctx)
 }
 
+func (q *querier) GetUserActivityInsights(ctx context.Context, arg database.GetUserActivityInsightsParams) ([]database.GetUserActivityInsightsRow, error) {
+	for _, templateID := range arg.TemplateIDs {
+		template, err := q.db.GetTemplateByID(ctx, templateID)
+		if err != nil {
+			return nil, err
+		}
+
+		if err := q.authorizeContext(ctx, rbac.ActionUpdate, template); err != nil {
+			return nil, err
+		}
+	}
+	if len(arg.TemplateIDs) == 0 {
+		if err := q.authorizeContext(ctx, rbac.ActionUpdate, rbac.ResourceTemplate.All()); err != nil {
+			return nil, err
+		}
+	}
+	return q.db.GetUserActivityInsights(ctx, arg)
+}
+
 func (q *querier) GetUserByEmailOrUsername(ctx context.Context, arg database.GetUserByEmailOrUsernameParams) (database.User, error) {
 	return fetch(q.log, q.auth, q.db.GetUserByEmailOrUsername)(ctx, arg)
 }

diff --git a/coderd/database/dbfake/dbfake.go b/coderd/database/dbfake/dbfake.go
@@ -2831,6 +2831,142 @@ func (q *FakeQuerier) GetUnexpiredLicenses(_ context.Context) ([]database.Licens
 	return results, nil
 }
 
+func (q *FakeQuerier) GetUserActivityInsights(ctx context.Context, arg database.GetUserActivityInsightsParams) ([]database.GetUserActivityInsightsRow, error) {
+	err := validateDatabaseType(arg)
+	if err != nil {
+		return nil, err
+	}
+
+	q.mutex.RLock()
+	defer q.mutex.RUnlock()
+
+	type uniqueKey struct {
+		TemplateID uuid.UUID
+		UserID     uuid.UUID
+	}
+
+	combinedStats := make(map[uniqueKey]map[time.Time]int64)
+
+	// Get application stats
+	for _, s := range q.workspaceAppStats {
+		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
+		}
+
+		if len(arg.TemplateIDs) > 0 && !slices.Contains(arg.TemplateIDs, w.TemplateID) {
+			continue
+		}
+
+		key := uniqueKey{
+			TemplateID: w.TemplateID,
+			UserID:     s.UserID,
+		}
+		if combinedStats[key] == nil {
+			combinedStats[key] = make(map[time.Time]int64)
+		}
+
+		t := s.SessionStartedAt.Truncate(time.Minute)
+		if t.Before(arg.StartTime) {
+			t = arg.StartTime
+		}
+		for t.Before(s.SessionEndedAt) && t.Before(arg.EndTime) {
+			combinedStats[key][t] = 60
+			t = t.Add(1 * time.Minute)
+		}
+	}
+
+	// Get session stats
+	for _, s := range q.workspaceAgentStats {
+		if s.CreatedAt.Before(arg.StartTime) || s.CreatedAt.Equal(arg.EndTime) || s.CreatedAt.After(arg.EndTime) {
+			continue
+		}
+		if len(arg.TemplateIDs) > 0 && !slices.Contains(arg.TemplateIDs, s.TemplateID) {
+			continue
+		}
+		if s.ConnectionCount == 0 {
+			continue
+		}
+
+		key := uniqueKey{
+			TemplateID: s.TemplateID,
+			UserID:     s.UserID,
+		}
+
+		if combinedStats[key] == nil {
+			combinedStats[key] = make(map[time.Time]int64)
+		}
+
+		if s.SessionCountJetBrains > 0 || s.SessionCountVSCode > 0 || s.SessionCountReconnectingPTY > 0 || s.SessionCountSSH > 0 {
+			t := s.CreatedAt.Truncate(time.Minute)
+			combinedStats[key][t] = 60
+		}
+	}
+
+	// Use temporary maps for aggregation purposes
+	mUserIDTemplateIDs := map[uuid.UUID]map[uuid.UUID]struct{}{}
+	mUserIDUsageSeconds := map[uuid.UUID]int64{}
+
+	for key, times := range combinedStats {
+		if mUserIDTemplateIDs[key.UserID] == nil {
+			mUserIDTemplateIDs[key.UserID] = make(map[uuid.UUID]struct{})
+			mUserIDUsageSeconds[key.UserID] = 0
+		}
+
+		if _, ok := mUserIDTemplateIDs[key.UserID][key.TemplateID]; !ok {
+			mUserIDTemplateIDs[key.UserID][key.TemplateID] = struct{}{}
+		}
+
+		for _, t := range times {
+			mUserIDUsageSeconds[key.UserID] += t
+		}
+	}
+
+	userIDs := make([]uuid.UUID, 0, len(mUserIDUsageSeconds))
+	for userID := range mUserIDUsageSeconds {
+		userIDs = append(userIDs, userID)
+	}
+	sort.Slice(userIDs, func(i, j int) bool {
+		return userIDs[i].String() < userIDs[j].String()
+	})
+
+	// Finally, select stats
+	var rows []database.GetUserActivityInsightsRow
+
+	for _, userID := range userIDs {
+		user, err := q.getUserByIDNoLock(userID)
+		if err != nil {
+			return nil, err
+		}
+
+		tids := mUserIDTemplateIDs[userID]
+		templateIDs := make([]uuid.UUID, 0, len(tids))
+		for key := range tids {
+			templateIDs = append(templateIDs, key)
+		}
+		sort.Slice(templateIDs, func(i, j int) bool {
+			return templateIDs[i].String() < templateIDs[j].String()
+		})
+
+		row := database.GetUserActivityInsightsRow{
+			UserID:       user.ID,
+			Username:     user.Username,
+			AvatarURL:    user.AvatarURL,
+			TemplateIDs:  templateIDs,
+			UsageSeconds: mUserIDUsageSeconds[userID],
+		}
+
+		rows = append(rows, row)
+	}
+	return rows, nil
+}
+
 func (q *FakeQuerier) GetUserByEmailOrUsername(_ context.Context, arg database.GetUserByEmailOrUsernameParams) (database.User, error) {
 	if err := validateDatabaseType(arg); err != nil {
 		return database.User{}, err

diff --git a/coderd/database/dbmetrics/dbmetrics.go b/coderd/database/dbmetrics/dbmetrics.go