Skip to content

Commit b2eb58a

Browse files
mafredrimafredri
committed
feat(coderd/database): rewrite GetTemplateInsights to use template_usage_stats 
1 parent a9d5ab0 commit b2eb58a

16 files changed

+172
-169
lines changed

‎coderd/database/dbmem/dbmem.go

+2-2
Original file line numberDiff line numberDiff line change
@@ -3286,8 +3286,8 @@ func (q *FakeQuerier) GetTemplateInsights(_ context.Context, arg database.GetTem
32863286
}
32873287

32883288
result := database.GetTemplateInsightsRow{
3289-
TemplateIDs: templateIDs,
3290-
ActiveUserIDs: activeUserIDs,
3289+
TemplateIDs: templateIDs,
3290+
ActiveUsers: int64(len(activeUserIDs)),
32913291
}
32923292
for _, intervals := range appUsageIntervalsByUser {
32933293
for _, interval := range intervals {

‎coderd/database/querier.go

+9-3
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries.sql.go

+79-38
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/insights.sql

+59-32
Original file line numberDiff line numberDiff line change
@@ -99,40 +99,67 @@ GROUP BY users.id, username, avatar_url
9999
ORDER BY user_id ASC;
100100

101101
-- name: GetTemplateInsights :one
102-
-- GetTemplateInsights has a granularity of 5 minutes where if a session/app was
103-
-- in use during a minute, we will add 5 minutes to the total usage for that
104-
-- session/app (per user).
105-
WITH agent_stats_by_interval_and_user AS (
106-
SELECT
107-
date_trunc('minute', was.created_at),
108-
was.user_id,
109-
array_agg(was.template_id) AS template_ids,
110-
CASE WHEN SUM(was.session_count_vscode) > 0 THEN 60 ELSE 0 END AS usage_vscode_seconds,
111-
CASE WHEN SUM(was.session_count_jetbrains) > 0 THEN 60 ELSE 0 END AS usage_jetbrains_seconds,
112-
CASE WHEN SUM(was.session_count_reconnecting_pty) > 0 THEN 60 ELSE 0 END AS usage_reconnecting_pty_seconds,
113-
CASE WHEN SUM(was.session_count_ssh) > 0 THEN 60 ELSE 0 END AS usage_ssh_seconds
114-
FROM workspace_agent_stats was
115-
WHERE
116-
was.created_at >= @start_time::timestamptz
117-
AND was.created_at < @end_time::timestamptz
118-
AND was.connection_count > 0
119-
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN was.template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
120-
GROUP BY date_trunc('minute', was.created_at), was.user_id
121-
), template_ids AS (
122-
SELECT array_agg(DISTINCT template_id) AS ids
123-
FROM agent_stats_by_interval_and_user, unnest(template_ids) template_id
124-
WHERE template_id IS NOT NULL
125-
)
102+
-- GetTemplateInsights returns the aggregate user-produced usage of all
103+
-- workspaces in a given timeframe. The template IDs, active users, and
104+
-- usage_seconds all reflect any usage in the template, including apps.
105+
--
106+
-- When combining data from multiple templates, we must make a guess at
107+
-- how the user behaved for the 30 minute interval. In this case we make
108+
-- the assumption that if the user used two workspaces for 15 minutes,
109+
-- they did so sequentially, thus we sum the usage up to a maximum of
110+
-- 30 minutes with LEAST(SUM(n), 30).
111+
WITH
112+
insights AS (
113+
SELECT
114+
user_id,
115+
-- See motivation in GetTemplateInsights for LEAST(SUM(n), 30).
116+
LEAST(SUM(usage_mins), 30) AS usage_mins,
117+
LEAST(SUM(ssh_mins), 30) AS ssh_mins,
118+
LEAST(SUM(sftp_mins), 30) AS sftp_mins,
119+
LEAST(SUM(reconnecting_pty_mins), 30) AS reconnecting_pty_mins,
120+
LEAST(SUM(vscode_mins), 30) AS vscode_mins,
121+
LEAST(SUM(jetbrains_mins), 30) AS jetbrains_mins
122+
FROM
123+
template_usage_stats
124+
WHERE
125+
start_time >= @start_time::timestamptz
126+
AND end_time <= @end_time::timestamptz
127+
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
128+
GROUP BY
129+
start_time, user_id
130+
),
131+
templates AS (
132+
SELECT
133+
array_agg(DISTINCT template_id) AS template_ids,
134+
array_agg(DISTINCT template_id) FILTER (WHERE ssh_mins > 0) AS ssh_template_ids,
135+
array_agg(DISTINCT template_id) FILTER (WHERE sftp_mins > 0) AS sftp_template_ids,
136+
array_agg(DISTINCT template_id) FILTER (WHERE reconnecting_pty_mins > 0) AS reconnecting_pty_template_ids,
137+
array_agg(DISTINCT template_id) FILTER (WHERE vscode_mins > 0) AS vscode_template_ids,
138+
array_agg(DISTINCT template_id) FILTER (WHERE jetbrains_mins > 0) AS jetbrains_template_ids
139+
FROM
140+
template_usage_stats
141+
WHERE
142+
start_time >= @start_time::timestamptz
143+
AND end_time <= @end_time::timestamptz
144+
AND CASE WHEN COALESCE(array_length(@template_ids::uuid[], 1), 0) > 0 THEN template_id = ANY(@template_ids::uuid[]) ELSE TRUE END
145+
)
126146

127147
SELECT
128-
COALESCE((SELECT ids FROM template_ids), '{}')::uuid[] AS template_ids,
129-
-- Return IDs so we can combine this with GetTemplateAppInsights.
130-
COALESCE(array_agg(DISTINCT user_id), '{}')::uuid[] AS active_user_ids,
131-
COALESCE(SUM(usage_vscode_seconds), 0)::bigint AS usage_vscode_seconds,
132-
COALESCE(SUM(usage_jetbrains_seconds), 0)::bigint AS usage_jetbrains_seconds,
133-
COALESCE(SUM(usage_reconnecting_pty_seconds), 0)::bigint AS usage_reconnecting_pty_seconds,
134-
COALESCE(SUM(usage_ssh_seconds), 0)::bigint AS usage_ssh_seconds
135-
FROM agent_stats_by_interval_and_user;
148+
COALESCE((SELECT template_ids FROM templates), '{}')::uuid[] AS template_ids, -- Includes app usage.
149+
COALESCE((SELECT ssh_template_ids FROM templates), '{}')::uuid[] AS ssh_template_ids,
150+
COALESCE((SELECT sftp_template_ids FROM templates), '{}')::uuid[] AS sftp_template_ids,
151+
COALESCE((SELECT reconnecting_pty_template_ids FROM templates), '{}')::uuid[] AS reconnecting_pty_template_ids,
152+
COALESCE((SELECT vscode_template_ids FROM templates), '{}')::uuid[] AS vscode_template_ids,
153+
COALESCE((SELECT jetbrains_template_ids FROM templates), '{}')::uuid[] AS jetbrains_template_ids,
154+
COALESCE(COUNT(DISTINCT user_id), 0)::bigint AS active_users, -- Includes app usage.
155+
COALESCE(SUM(usage_mins) * 60, 0)::bigint AS usage_total_seconds, -- Includes app usage.
156+
COALESCE(SUM(ssh_mins) * 60, 0)::bigint AS usage_ssh_seconds,
157+
COALESCE(SUM(sftp_mins) * 60, 0)::bigint AS usage_sftp_seconds,
158+
COALESCE(SUM(reconnecting_pty_mins) * 60, 0)::bigint AS usage_reconnecting_pty_seconds,
159+
COALESCE(SUM(vscode_mins) * 60, 0)::bigint AS usage_vscode_seconds,
160+
COALESCE(SUM(jetbrains_mins) * 60, 0)::bigint AS usage_jetbrains_seconds
161+
FROM
162+
insights;
136163

137164
-- name: GetTemplateInsightsByTemplate :many
138165
WITH agent_stats_by_interval_and_user AS (

‎coderd/insights.go

+6-39
Original file line numberDiff line numberDiff line change
@@ -395,8 +395,8 @@ func (api *API) insightsTemplates(rw http.ResponseWriter, r *http.Request) {
395395
resp.Report = &codersdk.TemplateInsightsReport{
396396
StartTime: startTime,
397397
EndTime: endTime,
398-
TemplateIDs: convertTemplateInsightsTemplateIDs(usage, appUsage),
399-
ActiveUsers: convertTemplateInsightsActiveUsers(usage, appUsage),
398+
TemplateIDs: usage.TemplateIDs,
399+
ActiveUsers: usage.ActiveUsers,
400400
AppsUsage: convertTemplateInsightsApps(usage, appUsage),
401401
ParametersUsage: parametersUsage,
402402
}
@@ -416,55 +416,22 @@ func (api *API) insightsTemplates(rw http.ResponseWriter, r *http.Request) {
416416
httpapi.Write(ctx, rw, http.StatusOK, resp)
417417
}
418418

419-
func convertTemplateInsightsTemplateIDs(usage database.GetTemplateInsightsRow, appUsage []database.GetTemplateAppInsightsRow) []uuid.UUID {
420-
templateIDSet := make(map[uuid.UUID]struct{})
421-
for _, id := range usage.TemplateIDs {
422-
templateIDSet[id] = struct{}{}
423-
}
424-
for _, app := range appUsage {
425-
for _, id := range app.TemplateIDs {
426-
templateIDSet[id] = struct{}{}
427-
}
428-
}
429-
templateIDs := make([]uuid.UUID, 0, len(templateIDSet))
430-
for id := range templateIDSet {
431-
templateIDs = append(templateIDs, id)
432-
}
433-
slices.SortFunc(templateIDs, func(a, b uuid.UUID) int {
434-
return slice.Ascending(a.String(), b.String())
435-
})
436-
return templateIDs
437-
}
438-
439-
func convertTemplateInsightsActiveUsers(usage database.GetTemplateInsightsRow, appUsage []database.GetTemplateAppInsightsRow) int64 {
440-
activeUserIDSet := make(map[uuid.UUID]struct{})
441-
for _, id := range usage.ActiveUserIDs {
442-
activeUserIDSet[id] = struct{}{}
443-
}
444-
for _, app := range appUsage {
445-
for _, id := range app.ActiveUserIDs {
446-
activeUserIDSet[id] = struct{}{}
447-
}
448-
}
449-
return int64(len(activeUserIDSet))
450-
}
451-
452419
// convertTemplateInsightsApps builds the list of builtin apps and template apps
453420
// from the provided database rows, builtin apps are implicitly a part of all
454421
// templates.
455422
func convertTemplateInsightsApps(usage database.GetTemplateInsightsRow, appUsage []database.GetTemplateAppInsightsRow) []codersdk.TemplateAppUsage {
456423
// Builtin apps.
457424
apps := []codersdk.TemplateAppUsage{
458425
{
459-
TemplateIDs: usage.TemplateIDs,
426+
TemplateIDs: usage.VscodeTemplateIds,
460427
Type: codersdk.TemplateAppsTypeBuiltin,
461428
DisplayName: codersdk.TemplateBuiltinAppDisplayNameVSCode,
462429
Slug: "vscode",
463430
Icon: "/icon/code.svg",
464431
Seconds: usage.UsageVscodeSeconds,
465432
},
466433
{
467-
TemplateIDs: usage.TemplateIDs,
434+
TemplateIDs: usage.JetbrainsTemplateIds,
468435
Type: codersdk.TemplateAppsTypeBuiltin,
469436
DisplayName: codersdk.TemplateBuiltinAppDisplayNameJetBrains,
470437
Slug: "jetbrains",
@@ -478,15 +445,15 @@ func convertTemplateInsightsApps(usage database.GetTemplateInsightsRow, appUsage
478445
// condition finding the corresponding app entry in appUsage is:
479446
// !app.IsApp && app.AccessMethod == "terminal" && app.SlugOrPort == ""
480447
{
481-
TemplateIDs: usage.TemplateIDs,
448+
TemplateIDs: usage.ReconnectingPtyTemplateIds,
482449
Type: codersdk.TemplateAppsTypeBuiltin,
483450
DisplayName: codersdk.TemplateBuiltinAppDisplayNameWebTerminal,
484451
Slug: "reconnecting-pty",
485452
Icon: "/icon/terminal.svg",
486453
Seconds: usage.UsageReconnectingPtySeconds,
487454
},
488455
{
489-
TemplateIDs: usage.TemplateIDs,
456+
TemplateIDs: usage.SshTemplateIds,
490457
Type: codersdk.TemplateAppsTypeBuiltin,
491458
DisplayName: codersdk.TemplateBuiltinAppDisplayNameSSH,
492459
Slug: "ssh",

‎coderd/testdata/insights/template/multiple_users_and_workspaces_three_weeks_second_template.json.golden

+1-3
Original file line numberDiff line numberDiff line change
@@ -18,9 +18,7 @@
1818
"seconds": 3600
1919
},
2020
{
21-
"template_ids": [
22-
"00000000-0000-0000-0000-000000000002"
23-
],
21+
"template_ids": [],
2422
"type": "builtin",
2523
"display_name": "JetBrains",
2624
"slug": "jetbrains",

‎coderd/testdata/insights/template/multiple_users_and_workspaces_three_weeks_second_template_only_report.json.golden

+1-3
Original file line numberDiff line numberDiff line change
@@ -18,9 +18,7 @@
1818
"seconds": 3600
1919
},
2020
{
21-
"template_ids": [
22-
"00000000-0000-0000-0000-000000000002"
23-
],
21+
"template_ids": [],
2422
"type": "builtin",
2523
"display_name": "JetBrains",
2624
"slug": "jetbrains",

0 commit comments

Comments
 (0)